Oracle queries and compiles data dictionaries, and oracle compiles dictionaries

Oracle queries and compiles data dictionaries, and oracle compiles dictionaries

If you need to write a data dictionary during project delivery, you can use the following method to first run a line-by-line SQL statement.

Select. TABLE_NAME AS table name,. COLUMN_NAME AS field name, DECODE (. CHAR_LENGTH, 0, DECODE (. DATA_SCALE, NULL,. DATA_TYPE,. DATA_TYPE | '(' |. DATA_PRECISION | ',' |. DATA_SCALE | '),. DATA_TYPE | '(' |. CHAR_LENGTH | ') as Field Type 1,. DATA_TYPE AS field type,. DATA_PRECISION AS valid bit,. DATA_SCALE AS precision value,. CHAR_LENGTH AS field length,. can nullable as be null? B. comments AS remarks FROM sys. user_tab_columns A, user_col_comments B where. TABLE_NAME = B. table_name and. COLUMN_NAME = B. column_name and. table_name = 'CC _ application ';

Note: The table name must be in uppercase and the result is as follows:

In this way, you can select all columns, right-click, select copy, find an excel file, and then copy it out of excel. The format-cell-border adds a border for excel Data, then select copy to word. If the data copied to word is too long, you can select this column in word -- auto-adjust -- Adjust the table according to the window

How to query common data dictionaries in ORACLE

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;

View All tables under a user
SQL> select * from user_tables;

Display User Information (tablespace)
Select default_tablespace, temporary_tablespace
From dba_users where username = 'game ';

1. User

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;

Displays the permissions of the current session.
SQL> select * from session_privs;

Displays the system permissions of a specified user.
SQL> select * from dba_sys_privs where grantee = 'game ';

Show privileged users
Select * from v $ pwfile_users;

Display User Information (tablespace)
Select default_tablespace, temporary_tablespace
From dba_users where username = 'game ';

Show user PROFILE
Select profile from dba_users where username = 'game ';

2. Tables

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 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 in the ORACLE memory Partition
SQL> select table_name, cache from user _ ...... remaining full text>

How Does oracle use the data dictionary to view where the diary file is stored?

ORACLE Data dictionary is an important part of a database. It is generated with the database and changes with the database changes,
It is reflected in some tables and Views under the sys user. The data dictionary name is an uppercase English character.
The data dictionary contains user information, user 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.
Generally, ORACLE users do not know how to use it effectively.
The name and interpretation of all data dictionary tables. It has a synonym, dict.
Dict_column field names and explanations in all data dictionary tables
To query index-related data dictionaries, use the following SQL statement:
SQL> select * from dictionary where instr (comments, 'index')> 0;
If you want to know the detailed meaning of each field name in the user_indexes table, you can use the following SQL statement:
SQL> select column_name, comments from dict_columns where table_name = 'user _ indexes ';
You can easily know the detailed name and explanation of the data dictionary without viewing other ORACLE documents.
The following describes how to use common data dictionaries for ORACLE users by category.
1. User
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;
2. Tables
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 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 in the ORACLE memory Partition
SQL> select table_name, cache from user_tables where instr (cache, 'y')> 0;
3. 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 & #3 ...... remaining full text>