Data Dictionary Dict is always belonging to Oracle User Sys.
1, users:
SELECT Username from DBA_USERS;
Changeword
Alter User SPGROUP Identified by SPGTEST;
2, table space:
Select * from DBA_DATA_FILES;
Select * from dba_tablespapa; // 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 object:
Select * from DBA_Objects;
Cluster, Database Link, Function, INDEX, LIBRARY, PACKAGE, PACKAGE,
Procedure, Sequence, Synonym, Table, Trigger, Type, undefined, view.
4, table:
Select * from DBA_TABLES;
Analyze my_table compute statistics; -> 6 columns after DBA_TABLES
SELECT EXTENT_ID, BYTES from DBA_EXTENTS
WHERE segment_name = 'Customers' and segment_type = 'table'
ORDER BY EXTENT_ID; / / The information used by the extent. Segment_type = 'rollback' View spatial allocation information of the rollback segment
Columns:
Select Distinct Table_name
From user_tab_columns
WHERE color_name = 'so_type_id';
5, index:
Select * from DBA_Indexes; // Index, including primary key
Select * from DBA_IND_COLUMNS; / / Index Column
Select I.index_name, I.Unique, C.COLUMN_NAME
From user_indexes i, user_ind_columns c
Where I.index_name = c.index_name
And i.table_name = 'ACC_NBR'; // Database
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 *. DBA_SNAPSHOTS
Snapshots, the partition should have the corresponding table space.
10, synonym:
Select * from dba_synonyms
Where Table_Owner = 'SPGROUP'
// if Owner is public, The Synynyms IS a public synonym.
IF Owner IS One of Users, THE SYNONYMS IS A Private Synonym.11, Database Chain:
SELECT * FROM DBA_DB_LINKS;
Create a database chain in SPBASE
Create Database Link DBL_SPNEW
Connect to spnew identified by spnew sale '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, functions are found from DBA_Objects.
Its text: Select text from user_source where name = 'book_sp_example';
Establish an error: SELECT * from user_ERRORS;
Oracle always places software such as stored procedures, functions in the System tablespace.
13, constraint:
(1) Constraint is related to the table, can be established, modified, 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) Primary key and foreign bonds are called table constraints, while NOT NULL and UNIQUE are called column constraints. 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 can be unknown (system automatically generate constraint name) and define constraints (especially primary keys, foreign bonds)
Such as: CREATE TABLE BOOK_EXAMPLE
Identifier Number NOT NULL;
Create Table Book_example
(Identifier Number Constranit Book_example_1 NOT NULL);
14, rollback segments:
Before all modifications are stored in disk, keep all information required to restore this transaction in the rollback segment, and must determine their size accordingly (DML statement to roll back, Create, Drop, Truncate, etc. Can't roll back).
The number of rollback segments = concurrent transaction / 4, but not more than 50; make each return segment sufficient to handle a complete transaction;
Create Rollback Segment R05TABLESPACE RBS;
Create Rollback Segment RBS_CVT
TableSpace RBS
Storage (Initial 1M Next 500K);
Let go back to roll
Alter rollback segment r04 online;
Use DBA_EXTENTS, V $ ROLLBACK_SEGS to monitor the size and dynamic growth of the rollback segment.
Round section information
SELECT * from DBA_EXTENTS
Where segment_type = 'rollback' and segment_name = 'rb1';
The segment of the segment, where Bytes displays the number of bytes of current rollback segments.
Select * from DBA_SEGMENTS
Where segment_type = 'rollback' and segment_name = 'rb1';
Specify a regression section for things
Set Transaction Use Rollback Segment RBS_CVT
Return 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';
The current status information of the rollback segment:
SELECT * from DBA_ROLLBACK_SEGS
Where segment_name = 'rb1';
STATUS, returning segment is instance instance_num
Check optimization value Optimal
Select n.name, S.Optsize
From V $ ROLLNAME N, V $ ROLLSTAT S
WHERE N.USN = S.USN;
Data in the roll
Set Transaction Use Rollback Segment RB1; / * Roll Rap Size * /
Select n.Name, S.Writes
From V $ ROLLNAME N, V $ ROLLSTAT S
WHERE N.USN = S.USN;
When the transaction is processed, Query $ ROLLSTAT, compare the differences of Writes (Numbers of the Route By Types), and determine the size of the transaction.
Query the transaction in the rollover 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. 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; Query is running
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 for 10 seconds
EXEC DBMS_Job.Submit (: v_num, 'a;', sysdate, 'sysdate (11 / (24 * 60))') joins the job. Interval 11 minutes Use the package EXEC DBMS_Job.Remove (21) to delete the No. 21 job.
