How do users use data dictionary

xiaoxiao2021-03-06  170

How do users use data dictionary

Oracle's Data Dictionary is one of the important components of the database. As the database is generated, with the change of the database changes,

Refigure some tables and views under SYS users. The data dictionary name is a capital character.

There are user information in the data dictionary, the user's permission information, all data object information, table constraints, and views of the statistical analysis database.

We cannot manually modify the information in the data dictionary.

Many times, a general Oracle user does not know how to effectively use it.

Dictionary all data dictionary table name and explanation, there is a synonym Dict

Dict_Column All Data Dictionary Table Field Name and Explanation

If we want to query the data dictionary related to the index, you can use the following SQL statement:

SQL> Select * from Dictionary where INSTR (Comments, 'INDEX')> 0;

If we want to know the detailed meaning of the user_indexes table, you can use the following SQL statement:

SQL> Select Column_name, Comments from Dict_Columns Where Table_Name = 'USER_INDEXES'

If you push, you can easily know the detailed names and interpretation of the data dictionary, and you don't have to view Oracle's other documentation.

Below, some Oracle User's Common Data Dictionary is listed below by category.

First, users

View the current user's default table space

SQL> SELECT UserName, default_tablespace from user_users;

View the current user's role

SQL> SELECT * from user_role_privs;

View current users' system permissions and table level permissions

SQL> SELECT * from user_sys_privs;

SQL> SELECT * from user_tab_privs;

Second, the table

View users all the tables

SQL> SELECT * from user_tables;

View name containing log characters

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, create 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 in the memory area placed in Oracle

SQL> SELECT TABLE_NAME, Cache from User_Tables Where Instr (Cache, 'Y')> 0;

Third, index

View indexes and categories

SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME from User_indexes Order by Table_Name;

View the field of indexed indexes

SQL> SELECT * from user_ind_columns where index_name = Upper ('& index_name ";

View the size of the index

SQL> SELECT SUM (BYTES) / (1024 * 1024) AS "SIZE (M)" from user_segments

Where segment_name = Upper ('& index_name "; four, serial number

View the serial number, last_number is the current value

SQL> Select * from user_sequences;

Five, view

View the name of the view

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 the SET LONG according to the text_length value of the view.

SQL> Select text from user_views where view_name = Upper ('& view_name');

Sixth, synonym

View the name of the synonym

SQL> Select * from user_synonyms;

Seven, constraint conditions

View a constraint condition for a table

SQL> Select construint_name, construct_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;

Eight, storage functions and processes

View the status of functions and procedures

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 function and process source code

SQL> Select text from all_source where = user and name = Upper ('& plsql_name ";

Nine, trigger

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

CHR (10) ||

Triggering_event || CHR (10) ||

'On "' || Table_owner || '". "||

Table_name || '"" || chr (10) || Decode (trigger_type,' own), 0, null,

'For easy row') || CHR (10),

Trigger_body

From user_triggers;

转载请注明原文地址:https://www.9cbs.com/read-128533.html

New Post(0)