学步园

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