|
The data dictionary dict always belongs to the Oracle user SYS.
1. User:
Select username from dba_users;
Change Password
Alter user spgroup identified by spgtest;
2. tablespace:
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; // idle tablespace Select * From dba_data_files
Where tablespace_name = 'rbs '; // data file corresponding to the tablespace 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, and view.
4. Table:
Select * From dba_tables;
Analyze my_table compute statistics;-> the last 6 columns of dba_tables
Select extent_id, bytes from dba_extents
Where segment_name = 'customer' and segment_type = 'table'
Order by extent_id; // The extent information used by the table. Segment_type = 'rollback'
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; // 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 '; // used for join
6. sequence:
Select * From dba_sequences;
7. View:
Select * From dba_views;
Select * From all_views;
Text can be used to query the scripts generated by the view.
8. Clustering:
Select * From dba_clusters;
9. Snapshots:
Select * From dba_snapshots;
Snapshots and partitions must have corresponding tablespaces.
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;
Create 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 = '20180101' and line_nbr = '20180101 ';
12. Trigger:
Select * From dba_trigers;
Stored Procedures, and functions are searched from dba_objects.
Text: select text from user_source where name = 'book _ sp_example ';
Creation error: Select * From user_errors;
Oracle always places stored procedures, functions, and other software in the system tablespace.
13. constraints:
(1) 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_1;
Alter table book_example
Enable constraint book_example_1;
(2) Primary keys and Foreign keys are called table constraints, while constraints such as not null and unique are called column constraints. Generally, the primary key and foreign key are placed under the field list as separate naming constraints, and the column constraint can be placed in the same row defined by the column, which is more readable.
(3) The column constraint can be seen from the table definition, that is, describe; The Table constraint is the primary key and foreign key, and can be queried 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) The defined constraint can be unknown (the system automatically generates the constraint name) and its own defined constraint name (especially the primary key and foreign key)
Example: Create Table book_example
(Identifier number not null );
Create Table book_example
(Identifier number constranit book_example_1 not null );
14. rollback segment:
Before all the modification results are saved to the disk, keep all information required by the firm in the rollback segment, the Database Transaction size must be determined accordingly (DML statements can be rolled back, create, drop, truncate, and other DDL statements cannot be rolled back ).
Number of rollback segments = concurrent transactions/4, but cannot exceed 50; Make sure that each rollback segment is sufficient to process a complete transaction;
Create rollback segment r05
Tablespace RBS;
Create rollback segment rbs_cvt
Tablespace RBS
Storage (initial 1 m next 500 k );
Online rollback segments
Alter rollback segment r04 online;
Use dba_extents and V $ rollback_segs to monitor the size and dynamic growth of rollback segments.
Range information of rollback segments
Select * From dba_extents
Where segment_type = 'rollback' and segment_name = 'rb1 ';
Segment information of the rollback segment, in which bytes displays the number of bytes of the current rollback segment
Select * From dba_segments
Where segment_type = 'rollback' and segment_name = 'rb1 ';
Specify regression segments for transactions
SET transaction use rollback segment rbs_cvt
For bytes, you can use rollback segments.
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 of the rollback segment:
Select * From dba_rollback_segs
Where segment_name = 'rb1 ';
Compared with the status of multiple rollback segments, the instance instance_num to which the rollback segment belongs
Query Optimization value optimal
Select N. Name, S. optsize
From v $ rollname N, V $ rollstat s
Where n. USN = S. USN;
Data in the rollback 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;
After the transaction is processed, query $ rollstat again to compare the difference value of writes (number of bytes of rollback segment entries) to determine the transaction size.
Query transactions in rollback segments
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;
Query running jobs
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: 10 seconds
Exec dbms_job.submit (: v_num, 'a; ', sysdate, 'sysdate + (11/(24*60)') to join the job. Use the package exec dbms_job.remove (21) to delete job 21 at an interval of 11 minutes. |
