Summary of Oracle Data Dictionary Learning

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

I. Users

View the current user's default tablespace SQL> SELECT username, default_tablespace FROM user_users;

View the role SQL of the current user> 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 tables under the user SQL> SELECT * FROM user_tables;

View the table SQL> 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 index size SQL> SELECT SUM (bytes)/(1024*1024) AS size (M) 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 SQL> SELECT 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. Synonyms

View the 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_name

2 FROM user_constraints WHERE table_name = UPPER ('& table_name ');

// Note: The table name must be in uppercase.

SQL> SELECT c. constraint_name, c. constraint_type, cc. column_name

2 FROM user_constraints c, user_cons_columns cc

3 WHERE c. owner = upper ('& table_owner ')

4 AND c. table_name = UPPER ('& table_name ')

5 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 = 'processed ';

View the source code of the function AND process 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, 'ach row'), 0, null,
'For each row') | chr (10 ),
Trigger_body
FROM user_triggers;