Oracle常用系統資料表查詢

-- 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');