Oracle system table Query __oracle

Select 1 from dba_tables t WHERE t.table_name = ' tbl_swifts_oper_info ' and t.tablespace_name= ' USERS ';

The data dictionary dict always belongs to the Oracle user sys.

1, Users:

Select username from dba_users;

The order of the tune

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;//free table space

SELECT * FROM Dba_data_files

where tablespace_name= ' RBS ';//table space corresponding to the 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, VIEW.

4. Table:

SELECT * from Dba_tables;

Analyze my_table Compute statistics;->dba_tables after 6 columns

Select Extent_id,bytes from Dba_extents

where Segment_name= ' CUSTOMERS ' and segment_type= ' ' TABLE '

The extent information used by the extent_id;//table. Segment_type= ' ROLLBACK ' View the space allocation information of the rollback segment

Column information:

SELECT DISTINCT table_name

From User_tab_columns

where column_name= ' so_type_id ';

5, Index:

SELECT * from dba_indexes;//index, including primary key index

SELECT * FROM dba_ind_columns;//indexed columns

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 ';//join use

6, Sequence:

SELECT * from Dba_sequences;

7, view:

SELECT * from Dba_views;

SELECT * from All_views;

Text can be used to query the script generated by the view

8. Cluster:

SELECT * from Dba_clusters;

9. Snapshot:

SELECT * from Dba_snapshots;

Snapshots, partitions should have corresponding table spaces.

10. Synonyms:

SELECT * FROM dba_synonyms

where table_owner= ' SPGroup ';

If owner is public,then the synonyms are 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 a database chain under Spbase

Create DATABASE link 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 procedures, where functions are searched from dba_objects.

Its text: Select text from User_source where name= ' book_sp_example ';

Build Error: SELECT * from User_errors;

Oracle always places stored procedures, functions, and other software in the system table space.

13, Constraints:

(1) The constraint is associated with the table and can be set up, modified, and deleted by the 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) Primary and foreign keys are called table constraints, and constraints such as NOT NULL and unique are called column constraints. Primary keys and foreign keys are usually placed under the field list as separate naming constraints, and column constraints can be placed in the same row as the column definition, which is more readable.

(3) Column constraints can be seen from the table definition, that is, the describe table constraint, which is the primary key and the 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) Define constraints can be nameless (System automatically generate constraint name) and its own definition constraint name (especially 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, Roll back paragraph:

Before all the changes are saved to disk, the rollback segment retains all the information needed to recover the transaction, and must be sized to the transactions that occur in the database (DML statements can be rolled back, create,drop,truncate etc. DDL cannot be rolled back).

The number of rollback segments = Concurrent Transaction/4, but not more than 50, so that each rollback segment is large 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 the rollback segment online

Alter rollback segment R04 online;

The size and dynamic growth of the rollback segment are monitored with dba_extents,v$rollback_segs.

Interval information for rolling back segments

SELECT * FROM Dba_extents

where segment_type= ' ROLLBACK ' and segment_name= ' RB1 ';

Rollback segment information where bytes shows the number of bytes in the current rollback segment

SELECT * FROM Dba_segments

where segment_type= ' ROLLBACK ' and segment_name= ' RB1 ';

Assign a return segment to a thing

Set TRANSACTION use rollback segment RBS_CVT

Rollback segment retraction can be used for bytes.

Alter rollback segment RBS_CVT shrink;

Select Bytes,extents,max_extents from Dba_segments

where segment_type= ' ROLLBACK ' and segment_name= ' rbs_cvt ';

Current status information for the rollback segment:

SELECT * FROM Dba_rollback_segs

where segment_name= ' RB1 ';

than the multiple rollback segment state status, the rollback segment belongs to the instance Instance_num

Check Optimization value Optimal

Select N.name,s.optsize

From V$rollname N,v$rollstat s

where N.usn=s.usn;

Rolling back data in a segment

Set TRANSACTION use rollback segment rb1;/* ROLLBACK segment Name * *

Select N.name,s.writes

From V$rollname N,v$rollstat s

where N.usn=s.usn;

When the transaction is finished, query $rollstat again, comparing the difference of writes (the number of bytes in the rollback segment) to determine the size of the transaction.

Querying transactions in a rollback segment

Column RR heading ' RB Segment ' format A18

Column US heading ' Username ' format A15

Column OS heading ' OS User ' format A10

Column te heading ' Terminal ' format A10

Select R.name Rr,nvl (s.username, ' no transaction ') Us,s.osuser os,s.terminal te

From V$lock l,v$session S,v$rollname R

where L.SID=S.SID (+)

and Trunc (l.id1/65536) =r.usn

and l.type= ' TX '

and l.lmode=6

Order BY R.name;

15. Homework

Query Job Information

Select Job,broken,next_date,interval,what from User_jobs;

Select Job,broken,next_date,interval,what from Dba_jobs;

Querying a running job

SELECT * from Dba_jobs_running;

Use the package exec Dbms_job.submit (: V_num, ' A; ', sysdate, ' sysdate + (10/(24*60*60)) ') to join the job. Interval of 10 seconds

EXEC dbms_job.submit (: V_num, ' A; ', sysdate, ' sysdate + (11/(24*60)) ') joins the job. Use the package exec Dbms_job.remove (21) To delete the 21st operation at intervals of 11 minutes.