6 useful Oracle commands

Written on 26 March 2011, 12:43pm

Tagged with: , ,

This is more of a reference post to store some useful Oracle features. Or a cheat sheet if you wish 🙂

1. How to find the maximum column size in a table


select
       max(vsize(mycol))
from
       mytable;

2. How to paginate in Oracle using rownum

Method 1:


select * from 
( 
       select rownum r, a.* from 
       (
             YOUR_QUERY
       ) a 
       where rownum <= 10
) 
where r >= 1;

Method 2:


select * from 
(
       select rownum r, a.* from 
       (
              YOUR_QUERY
       ) a
)
where r between 1 and 10;

In my experience, the first method is faster.
Update Jan 2015: You can’t have subqueries in a query of queries.

3. How to get info about the table spaces


select tablespace_name, file_name, bytes from dba_data_files;
select distinct(owner), tablespace_name from dba_segments;

4. How to get info about your materialized views


select * from all_refresh;

5. How to import/export data


--Export command:
exp username/password@connection file=username.dmp log=username.log 
--To exp a single table:
exp username/password@connection file=emp.dmp log=emp.log tables=emp rows=yes indexes=no
--To export multiple tables:
exp username/password@connection file=empdept.dmp log=empdept.log tables=(emp,dept)

--Import command:
imp username/password@connection file=username.dmp full=yes
--Import a single table:
imp username/password@connection file=emp.dmp fromuser=username touser=newusername tables=emp
--Import multiple tables:
imp username/password@connection file=empdept.dmp fromuser=username touser=newusername tables=(emp,dept)

6. How to get info about schemas/tables


--List all the tables in the current schema:
SELECT table_name FROM user_tables;
--List all tables current user has access to:
SELECT table_name FROM all_tables;
--List all schemas:
SELECT username FROM all_users ORDER BY username; 
--Who am I?
SELECT username FROM user_users;

ColdFusion has also a great tool to retrieve information about Oracle: the cfdbinfo tag. Here’s an in-depth analysis of the cfdbinfo usage.

Later edit:


--Get the schema of a table:
SELECT * FROM all_ind_columns WHERE table_name='t_foo'
--Get all the columns for a table that you own
SELECT * FROM user_tab_columns WHERE table_name = 't_foo'

Where to go from here

Comments (2)

  1. Bala — August 11, 2015 at 10:50

    Hi Jackie,I’m trying to sign in and the coeuptmr generated password won’t work. I have copied and pasted and put it in manually, but I still get the wrong password’ message. Can you help? : )Charlene

    Reply

Leave a response