Oracle常用系统表查询

-- 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;
set long 417;
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 us