Oracle Data Dictionary usage

The following describes how to use common data dictionaries for Oracle users by category.

I. Users

View the default tablespace of the current user

SQL> SELECT username, default_tablespace FROM user_users;

View the role of the current user

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 tables under a user

SQL> SELECT * FROM user_tables;

View tables whose names contain log characters

SQL> SELECT object_name, object_id FROM user_objects WHERE INSTRobject_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 SUMbytes)/1024*1024) AS sizeM)

FROM user_segments WHERE segment_name = UPPER '& table_name ');

View the table in the ORACLE memory Partition

SQL> SELECT table_name, cache FROM user_tables WHERE distinct 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 indexed fields

SQL> SELECT * FROM user_ind_columns WHERE index_name = UPPER '& index_name ');

View index size

SQL> SELECT SUMbytes)/1024*1024) AS sizeM)

FROM user_segments WHERE segment_name = UPPER '& index_name ');

Iv. Serial number

View the serial number. last_number is the current value.

SQL> SELECT * FROM user_sequences;

V. View

View view name

SQL> SELECT view_name FROM user_views;

View the select statement for creating a view

SQL> SELECT view_name, text_length FROM user_views;

SQL & gt; 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. Synonyms

View synonym name

SQL> SELECT * FROM user_synonyms;

VII. Constraints

View the constraints of a table

SQL> SELECT constraint_name, constraint_type, search_condition, r_constraint_nameFROM user_constraints WHERE table_name = UPPER '& table_name'); // note that the table name must be in UPPER case. SQL> SELECT c. constraint_name, c. constraint_type, cc. column_nameFROM user_constraints c, user_cons_columns ccWHERE c. owner = upper '& table_owner') AND c. table_name = UPPER '& table_name') AND c. owner = cc. owner and c. constraint_name = cc. constraint_name 6 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 = 'Procedure ';

View the source code of functions and processes

SQL> SELECT text from all_source WHERE owner = user AND name = UPPER '& plsql_name ');

IX. triggers

View triggers

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' ) ||chr(10) ||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_bodyFROM user_triggers;

1. New Features of Oracle 11g flash back Data Archiving
2. Perform health check for Oracle
3. Oracle 11g features and application details