Oracle System Table Query Data Dictionary Dict Always belong to Oracle User Sys. 1. User: Select UserName from DBA_USERS; Change Channel Alter User SPGROUP Identified by Spgtest; 2, Table Space: SELECT * FROM DBA_DATA_FILES; SELECT * FROM DBA_TABLESPACES; // Table Space
SELECT TABLESPACE_NAME, SUM (BYTES), SUM (Blocks) from DBA_Free_Space Group by TableSpace_name; // Idle Table Space
Select * from DBA_DATA_FILES where TableSpace_name = 'RBS'; // Table Space Corresponding Data File
select * from dba_segments where tablespace_name = 'INDEXS'; 3, database objects: select * from dba_objects; CLUSTER, DATABASE LINK, FUNCTION, INDEX, LIBRARY, PACKAGE, PACKAGE BODY, PROCEDURE, SEQUENCE, SYNONYM, TABLE, TRIGGER, TYPE, UNDEFINED . 4, the table: select * from dba_tables; analyze my_table compute statistics; -> dba_tables 6 after column select extent_id, bytes from dba_extents where segment_name = 'CUSTOMERS' and segment_type = 'TABLE' order by extent_id; // extent information table used . segment_type = 'ROLLBACK' rollback space allocation information see column information: select distinct table_name from user_tab_columns where column_name = 'SO_TYPE_ID'; 5, index: select * from dba_indexes; // index, comprising a primary key index 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 = 'ACC_NBR'; // use coupling 6, SEQ: select * from dba_sequences ; 7, view: Select * from dba_views; Select * from all_views; Text can be used to query the view generated script 8, cluster: select * from dba_clusters; 9, Snapshot: SELECT * from DBA_SNAPSHOTS; snapshot, partition should have corresponding table space.
10, synonyms: select * from dba_synonyms where table_owner = 'SPGROUP'; // if owner is PUBLIC, then the synonyms is a public synonym if owner is one of users, then the synonyms is a private synonym 11, database chain: select * from dba_db_links; build the database chain create database link at spbase dbl_spnew connect to spnew identified by spnew using 'jhhx'; insert into acc_nbr @ dbl_spnew select * from acc_nbr where nxx_nbr = '237' and line_nbr = '8888'; 12, Trigger: Select * from DBA_TRIGERS; stored procedure, functions look up from DBA_Objects. Its text: Select text from user_source where name = 'book_sp_example'; established an error: select * from user_errors; Oracle always places stored procedures, functions, etc. Software in the System tablespace. 13. Constraint: (1) Constraint is related to the table, can establish, modify, and delete constraints at Create Table or Alter Table Table_name Add / Drop / Modify. Constraints can be temporarily prohibited, such as: Alter Table Book_example DISABLE CONSTRAINT BOOK_EXAMPLE_1; ALTER TABLE BOOK_EXAMPLE ENABLE CONSTRAINT BOOK_EXAMPLE_1; (2) The primary key and foreign key are called table constraints, while NOT NULL and UNIQUE are constrained as a column constraint. The primary key and foreign key are usually placed under the field list, and the column constraints can be placed in the same line definition, which is more readable. (3) Column constraints can be seen from the table definition, namely the describe; table constraint, primary key and foreign key, can be found from DBA_CONSTRAINTS and DBA_CONS_COLUMNS.
select * from user_constraints where table_name = 'BOOK_EXAMPLE'; select owner, CONSTRAINT_NAME, TABLE_NAME from user_constraints where constraint_type = 'R' order by table_name; (4) defining constraints may be unknown (the system automatically generates the constraint name) and its own definition of the constraint name (especially It is the primary key, foreign key), such as: Create Table Book_Example (Identifier Number Not Null); Create Table Book_example (Identifier Number Constranit Book_example_1 Not Null); 14, Rolling Segment: Before all modifications are stored in disk, return to roll Keep all the information required to restore the transaction, you must determine its size accordingly with the transaction that occurs in the database (DML statement can be rolled back, CREATE, DROP, TRUNCATE, etc. DDL can't roll back). The number of rollback segments = concurrency transaction / 4, but not more than 50; make each return segment size enough to handle a complete transaction; Create Rollback Segment R05 TableSpace RBS; Create Rollback Segment RBS_CVT TABLESPACE RBS Storage (Initial 1M Next 500K); Make back the rollback segment online ALTER ROLLBACK Segment R04 Online; Monitor the size and dynamic growth of the rollback segment with DBA_EXTENTS, V $ ROLLBACK_SEGS. Rollback section information select * from dba_extents where segment_type = 'ROLLBACK' and segment_name = 'RB1'; rollback segment information, wherein the display current rollback bytes bytes select * from dba_segments where segment_type = 'ROLLBACK 'and segment_name =' rb1 '; specifying the return paragraph set Transaction Use Rollback Segment RBS_CVT to use the rollback segment retraction for BYTES.
