Oracle data dictionary and oracle dictionary

Oracle data dictionary and oracle dictionary

I. Data Dictionary
A data dictionary is a place where oracle stores database information. Almost all system information and object information can be queried in the data dictionary. Data dictionary is the core information of the oracle database system. It is a set of tables and views that provide database information. These tables and views are read-only. It is created with the establishment of the database. When the database executes a specific action, the data dictionary is automatically updated. Data Overview and data dictionary to record, verify, and manage ongoing operations.
In oracle, the sys user is the owner of the data dictionary. The data dictionary ensures that in the system tablespace system of all databases, no user has the right to change the mode object or the row in the data dictionary in sys mode. That is to say, the data dictionary can only be queried and cannot be modified manually.

Data Dictionary usage
By accessing the data dictionary, oracle can easily obtain information about a user's object and storage structure. After the system executes the DDL statement, oracle promptly modifies the data dictionary. Any user can only use the data dictionary to obtain database information in the form of reading.

Information stored in Data Dictionary

• Data User Name
• Permissions and roles granted to users
• The name of the mode object, such as tables, views, indexex, procedures, functions, packages, and triggers.
• Detailed information of integrity constraints;
• Default value for each field;
• Database space usage;
• Audit function. The cataudit. SQL file in the Oracle_Home \ productdb_l \ rdbms \ admin directory is used to create a data dictionary view for audit.
• Strict management of objects and users (applicable to highly confidential management );
• Other general database information.

Data Dictionary view with three prefixes
User _: view that can be read by any user. Each user reads different views. It only provides the object information under the current user. For example, select object_name, object_type from user_objects;

All _: user view that can be read by all users. It provides object information related to users. For example, you can query all objects accessible to the current user.
Select owner, object_name, object_type from all_objects;
Dba _: Provides views that can only be read by the database administrator, including object information in all user views. For example, select owner, object_name, object_type from sys. dba_objects;

2. Data Dictionary-related queries

1. query users

Select username from dba_users; -- only users with administrator permissions can query select username from all_users; -- available to the current or any user -- view the default tablespace select username, default_tablespace from user_users of the current user; -- select * from user_role_privs; -- select * from user_sys_privs; select * from user_tab_privs;

2. query tablespaces (only users with DBA permissions can query)

Select * from dba_data_files; select * from dba_tablespaces; -- tablespace select tablespace_name, sum (bytes), sum (blocks) from dba_free_space group by tablespace_name; -- select * from dba_data_files where tablespace_name = 'users'; -- select * from dba_segments where tablespace_name = 'users '; -- query the space used by the user mode object select name, type, source_size, code_size from user_object_size;

3. query database objects (only users with DBA permissions can query)

select * from dba_objectsselect * from dba_objects where object_type = upper('package body');select * from dba_objects where OBJECT_TYPE='TABLE' and OWNER='SCOTT'

You can query the following object types (object_type) based on the owner)

Cluster databaselink
Function index
Library package
Package body procedure
Sequence synonym
Table trigger
Type undefined
View
4. query a table

-- The extent information used by the table. Segment_type = 'rollback'

Select * from dba_tables; select extent_id, bytes from dba_extents where segment_name = 'customer' and segment_type = 'table' order by extent_id; -- view the space allocation information column information of the rollback segment. SELECT * FROM user_tab_columns; select distinct table_name from user_tab_columns where column_name = 'id '; -- view all the tables under the current user select * from user_tables; -- view the tables whose names contain log characters select object_name, object_id from user_objects where instr (object_name, 'log')> 0; -- view the creation time of a table select object_name, created from user_objects where object_name = upper ('& table_name'); -- view the size of a table select sum (bytes) /(1024*1024) as "size (M)" from user_segments where segment_name = upper ('& table_name'); -- view the table in oracle memory select table_name, cache from user_tables where instr (cache, 'y')> 0;

5. query Indexes

Select * from dba_indexes; -- index, including primary key index select * from all_indexes; select * from dba_ind_columns; -- index column select I. index_name, I. uniqueness, c. column_name from user_indexes I, user_ind_columns c where I. index_name = c. index_name and I. table_name = 'person '; -- connection usage -- view the number and category of indexes select index_name, index_type, table_name from user_indexes order by table_name; -- view the indexed field select * from user_ind_columns where index_name = upper ('& index_name'); -- view the index size select sum (bytes)/(1024*1024) as "size (M)" from user_segments where segment_name = upper ('& index_name ');

6. query Sequence

Select * from dba_sequences; select * from all_sequences; view the serial number. last_number is the current value select * from user_sequences;

7. query view

select * from dba_views;select * from all_views;

Available directory desc all_views; To view structure
The Text column can be used to query the scripts produced by the view.

-- Query the current user view name select view_name from user_views; -- query the select statement select view_name, text_length from user_views; set long 2000; -- set the size of set long based on the text_length value of the view. select text from user_views where view_name = upper ('& view_name ');

8. query Clustering

select * from dba_clusters;

9. querying snapshots

select * from dba_snapshots;

Corresponding tablespace should exist in snapshots and partitions

10. query Synonyms

select * from dba_synonyms where table_owner='SCOTT';select * from ALL_synonyms where table_owner='SYSTEM';

If a user table can be accessed, synonyms can also be accessed. If a user table cannot be accessed, synonyms cannot be accessed.

11. query database chains

select * from dba_db_links;

12. query trigger (12)

select * from dba_triggers;

Stored Procedure, function search from dba_objects

Query the text select text from user_source where name = 'Pro _ PERSON_FINDBYID ';

Oracle always stores stored procedures and functions in the system tablespace.

13. view the status of functions and processes

Select object_name, status from user_objects where object_type = 'function'; select object_name, status from user_objects where object_type = 'Procedure '; -- view the source code select * from all_source where owner = 'wuxx' and name = upper ('& plsql_name ');

14. query Constraints

Constraints are associated with tables. You can create, modify, and delete constraints in create table or alter table table_name add/drop/modify.
Constraints can be temporarily prohibited, such:
Alter table book_example
Disable constraint book_example_l;
Data integrity constraints

select constraint_name, constraint_type, table_name from dba_constraints;

15. query rollback segments
Before all the modification results are saved to the disk, the rollback segment maintains all the information required by the transaction to be restored. The transaction size must be determined based on the transaction sent by the database. (DML statements can be rolled back. DDL statements such as create, drop, and truncate cannot be rolled back)
Number of rollback segments = concurrent transactions/4, but not more than 50; each rollback segment is sufficient to handle a complete transaction;

create rollback segment r05 tablespace rbs;create rollback segment rbs_cvt tablespace rbs storage(initial 1M next 500k);

16. query a job

Select job, broken, next_date, interval, what from user_jobs; select job, broken, next_date, interval, what from dba_jobs; -- select * from dba_jobs_running;

Use the package exec dbms_job.sumit (: v_num, 'a; ', sysdate, 'sysdate + (10/(24*60*60)') to join the job. Interval: 10 seconds

Use the package exec dbms_job.sumit (: v_num, 'a; ', sysdate, 'sysdate + (10/(24*60)') to join the job. Interval: 11 minutes
Use the table exec dbms_job.remove (21) to delete job 21.

Other Information Query
Query the space used or in use by the optimization mode object

select name, type, source_size, code_size from user_object_size;

Default Value of the query field

select table_name, column_name, data_default, low_value, hight_value from dba_tab_columns;

How Does oracle export data dictionaries?

-- Query the data dictionary of A table. 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 empty FROM sys. user_tab_columns A where. table_name = 'tam _ address' -- 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,. DATA_DEFAULT AS default value,. can nullable as be null? B. comments AS remarks FROM sys. all_tab_columns A, sys. DBA_COL_COMMENTS bwhere. owner = B. owner and. table_name = B. table_name and. COLUMN_NAME = B. COLUMN_NAME and. owner = 'guoqiang 'ANDA. TABLE_NAME IN ('tb _ subobject', 'tb _ SUBJECT_BALANCE ', 'tb _ voer er', 'tb _ VOUCHER_DETAILS ', 'tb _ customer', 'tb _ VOUCHER_CLASSIFY_MODE ', 'tb _ VOUCHER_TYPE ', 'tb _ asset', 'tb _ ASSET_CATALOG', 'tb _ upload', 'tb _ M_DM_PROFIT_RPT ', 'tb _ M_DM_REVENUE_RPT ', 'tb _ M_DM_COST_RPT ') order by. TABLE_NAMESELECT. TABLE_NAME AS table name,. COLUMN_NAME AS field name, DECODE (. CHAR_LENGTH, 0, DECODE (. DATA_SCALE, NULL,. DATA_TYPE,. DATA_TYPE | '(' |. DATA_PRECISION ...... remaining full text>

Is there a Chinese document for oracle Data dictionary?

Data Dictionary tables are internal confidential. Not even English. To view the dynamic view, go to www.oracle.com/pls/db102/homepage.
But wood has Chinese characters...