View user objects in oracle

Oracle viewing user objects 1. Users

View the current user's default tablespace SQL> select username, default_tablespace from user_users; view the current user's role SQL> select * from user_role_privs; view the system and table-level permissions of the current user. SQL> select * from user_sys_privs; SQL> select * from user_tab_privs;

 

Ii. Table

View all the user's tables SQL> select * from user_tables; select table _ name from user_tables; view the table SQL with the name containing log characters> select object_name, object_id from user_objects where instr (object_name, 'log')> 0; view the creation time of a table SQL> select object_name, created from user_objects where object_name = upper ('& table_name '); view the size of a table SQL> select sum (bytes)/(1024*1024) as "size (M)" from user_segments where segment_name = upper ('& table_name '); view the table SQL in the ORACLE memory partition> select table_name, cache from user_tables where instr (cache, 'y')> 0;

 

Iii. Index

View the number and category of indexes SQL> select index_name, index_type, table_name from user_indexes order by table_name; view the indexed field SQL> select * from user_ind_columns where index_name = upper ('& index_name'); view the index size SQL> select sum (bytes)/(1024*1024) as "size (M)" from user_segments where segment_name = upper ('& index_name ');

 

4. view the serial number. last_number is the current value.

SQL> select   *   from   user_sequences; 

 

V. view name

SQL> select   view_name   from   user_views; 

 

View the select statement for creating a view

SQL> set view_name, text_length from user_views; SQL> set long 2000; description: you can set the size of set long based on the text_length value of the view. SQL> select text from user_views where view_name = upper ('& view_name ');

 

6. View synonym name

SQL> select   *   from   user_synonyms; 

 

VII. View constraints of a table

SQL> select   constraint_name,   constraint_type,search_condition,   r_constraint_name from   user_constraints   where   table_name   =   upper('&table_name'); SQL> 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; 

 

8. storage functions and processes

View the status of functions and processes SQL> select object_name, status from user_objects where object_type = 'function'; SQL> select object_name, status from user_objects where object_type = 'processed '; view the source code of the function and process SQL> select text from all_source where owner = user and name = upper ('& plsql_name ');

 

9. view a trigger

set   long   50000; set   heading   off; set   pagesize   2000; select 'create   or   replace   trigger   "'   ¦ ¦　trigger_name   ¦ ¦   '"'   ¦ ¦   chr(10) ¦ ¦ decode(   substr(   trigger_type,   1,   1   ),   'A',   'AFTER',   'B',   'BEFORE',   'I',   'INSTEAD   OF')   ¦ ¦ triggering_event   ¦ ¦   chr(10)   ¦ ¦             'ON   "'   ¦ ¦   table_owner   ¦ ¦   '"."'   ¦ ¦ table_name   ¦ ¦   '"'   ¦ ¦   chr(10)   ¦ ¦ decode(   instr(   trigger_type,   'EACH   ROW'   ),   0,   null,   'FOR   EACH   ROW'   )   ¦ ¦   chr(10)   ,             trigger_body from   user_triggers;