Teaching notes-common Oracle Data Dictionary

-- User
-- View the default tablespace of the current user
Select username, default_tablespace from user_users;
-- View the role of the current user
Select * from user_role_privs;
-- View the system and table-level permissions of the current user
Select * from user_sys_privs;
Select * from user_tab_privs;
-- Display the permissions of the current session
Select * from session_privs;
-- Displays the system permissions of a specified user.
Select * from dba_sys_privs where grantee = 'game ';
-- Display privileged users
Select * from v $ pwfile_users;
-- View All tables under a user
Select * from user_tables;
-- View column attributes of all tables under a user
Select * from USER_TAB_COLUMNS where table_name =: table_Name;
-- Display User Information (tablespace to which the user name is case sensitive)
Select default_tablespace, temporary_tablespace
From dba_users where username = 'Scott ';
-- Display the user's PROFILE
Select profile from dba_users where username = 'Scott ';
 
-- Table
-- View All tables under a user
Select * from user_tables;
-- View tables whose names contain log characters
Select object_name, object_id from user_objects
Where instr (object_name, 'log')> 0;
-- View the creation time of a table
Select object_name, created from user_objects where object_name = upper ('& table_name ');
-- View the size of a table
Select sum (bytes)/(1024*1024) as "size (M)" from user_segments
Where segment_name = upper ('& table_name ');
-- View the table in the Oracle memory Partition
Select table_name, cache from user_tables where instr (cache, 'y')> 0;
 
-- Index
-- View the number and category of Indexes
Select index_name, index_type, table_name from user_indexes order by table_name;
-- View the indexed fields
Select * from user_ind_columns where index_name = upper ('& index_name ');
-- View the index size
Select sum (bytes)/(1024*1024) as "size (M)" from user_segments
Where segment_name = upper ('& index_name ');
 
-- Serial number
-- View the serial number. last_number is the current value.
Select * from user_sequences;
 
-- View
-- View name
Select view_name from user_views;
-- View the select statement for creating a view
Set view_name, text_length from user_views;
Set long 2000; Description: You can set the size of set long based on the text_length value of the view.
Select text from user_views where view_name = upper ('& view_name ');
 
-- Synonym
-- View the synonym name
Select * from user_synonyms;
 
-- Constraints
-- View the constraints of a 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;
 
-- Storage functions and processes
-- View the status of functions and processes
Select object_name, status from user_objects where object_type = 'function ';
Select object_name, status from user_objects where object_type = 'Procedure ';
-- View the source code of functions and processes
Select text from all_source where owner = user and name = upper ('& plsql_name ');
 
This article is from the "IT thin" blog