-- get all dictionary for oracle db
select * from dict;
select * from dictionary;
-- get all columns for dictionarys
select * from dict_columns;
-- get the default name-space for current user
select username,default_tablespace from user_users;
-- get roles for current user
select * from user_role_privs;
-- get system privilage and table privilage for current user
select * from user_sys_privs;
select * from user_tab_privs;
-- get all tables for current user
select * from user_tables;
-- get all tables whoes name includes log
select object_name,object_id
from user_objects
where instr(object_name,'LOG')>0
-- get the create date for a table
select object_name,created
from user_objects
where object_name=upper('&table_name');
-- get the size for the given table
select sum(bytes)/(1024*1024) as "size(M)"
from user_segments
where segment_name=upper('&table_name');
-- get tables in ram
select table_name,cache from user_tables where instr(cache,'Y')>0
-- get index couter and types
select index_name,index_type,table_name
from user_indexes
order by table_name
-- get the columns indexed by the given index
select * from user_ind_columns where index_name=upper('&index_name')
-- get the size for the given index
select sum(bytes)/(1024*1024) as "size(M)"
from user_segments
where segment_name=upper('&index_name')
-- get the last number for all sequence
select * from user_sequences
-- get all views
select view_name from user_views
-- get the sql to defined the given view
select view_name,text_length
from user_views;
select text
from user_views
where view_name=upper('&view_name');
-- get all synonyms
select * from user_synonyms
-- get constraints for given table
select constraint_name, constraint_type,search_condition, r_constraint_name
from user_constraints
where table_name = upper('&table_name');
select c.constraint_name,c.constraint_type,cc.column_name
from user_constraints c,user_cons_columns cc
where c.owner = upper('&table_owner') and c.table_name = upper('&table_name') and c.owner = cc.owner and c.constraint_name = cc.constraint_name
order by cc.position;
-- check status for functions and procedures
select object_name,status
from user_objects
where object_type='FUNCTION';
select object_name,status
from user_objects
where object_type='PROCEDURE';
-- get source for functions and procedures
select text from all_source where owner=user and name=upper('&plsql_name');