ORACLE System Table Overview, oracle Overview

ORACLE System Table Overview, oracle Overview

Oracle System Table Overview

The following is a comprehensive introduction to Oracle system tables: 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 bytablespace_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, DATABASELINK, FUNCTION, INDEX, LIBRARY, PACKAGE, package body,

PROCEDURE, SEQUENCE, SYNONYM, TABLE, TRIGGER, TYPE, UNDEFINED, and VIEW.

4. Table:

Select * from dba_tables;

Analyze my_table computestatistics;-> the last 6 columns of dba_tables

Select extent_id, bytes from dba_extents

Where segment_name = 'customer' andsegment_type = 'table'

Order byextent_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

Selecti. 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. snapshot:

Select * from dba_snapshots;

Snapshots and partitions must have corresponding tablespaces.

10. Synonym:

Select * from dba_synonyms

Where table_owner = 'spgroup ';

// If owner is PUBLIC, then the synonyms isa public synonym.

If owner is one of users, then thesynonyms 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 spnewusing '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 wherename = '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_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 notnull 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 constranitbook_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. Job

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.

Start with dba .....

Dba_users database user information

Dba_segments table segment information

Dba_extents data zone information

Dba_objects database object information

Dba_tablespaces database tablespace Information

Dba_data_files Data File Settings

Dba_temp_files temporary data file information

Dba_rollback_segs rollback segment information

Dba_ts_quotas user tablespace quota Information

Dba_free_space idle space information of the database

Dba_profiles database user resource limit information

Dba_sys_privs user's system permission information

Dba_tab_privs object permission information

Column object permission information of dba_col_privs users

Dba_role_privs user's role information

Dba_audit_trail audit trail record information

Dba_stmt_audit_opts audit settings

Dba_audit_object audit result information

Dba_audit_session audit result information

Index information of dba_indexes user mode

Start with user _

User_objects user object information

User_source information of all resource objects of database users

User_segments user's table segment information

User_tables user's table Object Information

Table column information of user_tab_columns

User_constraints object constraint information

User_sys_privs system permission information of the current user

User_tab_privs object permission information of the current user

User_col_privs table column permission information of the current user

User_role_privs role permission information of the current user

User_indexes user index information

Table column information corresponding to the index of user_ind_columns

Table column information corresponding to the constraints of the user_cons_columns user

User_clusters all cluster information of the user

Content of the user_clu_columns user's cluster

User_cluster_hash_expressions information of the hash Cluster

Starting with v $

V $ database information

V $ datafile Data File Information

V $ controlfile Control File Information

V $ logfile redo log information

V $ instance database instance information

V $ log group information

V $ loghist log history

V $ sga database SGA Information

V $ parameter initialization parameter information

V $ process database server process information

V $ bgprocess background process information of the database

V $ controlfile_record_section information of each part of the control file

V $ thread Information

V $ datafile_header information recorded in the Data File Header

V $ archived_log archive log information

V $ archive_dest configuration information for archiving logs

V $ dml ddl result information of logmnr_contents archive log analysis

V $ dictionary file information of logmnr_dictionary Log Analysis

V $ logmnr_logs log list information for log analysis

V $ tablespace Information

V $ tempfile temporary file information

V $ filestat data file I/O statistics

V $ undostat Undo data information

V $ rollname online rollback segment information

V $ session information

V $ transaction information

V $ rollstat rollback segment statistics

V $ pwfile_users privileged user information

V $ sqlarea resources accessed by SQL statements currently queried and related information

V $ SQL and v $ sqlarea are basically the same related information.

V $ sysstat database system status information

Start with all _

Information of all users in the all_users Database

Information of all objects in the all_objects Database

All_def_audit_opts all default audit settings

All table objects in all_tables

All_indexes information of all database object Indexes

Start with session _

Session_roles session role information

Session_privs session permission information

Start with index _

Index_stats index settings and storage Information

Pseudo table

Dual System pseudo list information

1. sysobjects

System Object table. Save the objects of the current database, such as constraints, default values, logs, rules, and stored procedures.

Description of important sysobjects fields:

SysObjects (

Name sysname, -- object Name

Id int, -- object id

Xtype char (2), -- object Type

Type char (2), -- Object type (exactly the same as xtype? A little depressing ...)

Uid smallint, -- ID of the object owner

...... -- Other fields are not commonly used.

)

Note: The xtype and type are exactly the same. The data is as follows:

C = CHECK Constraints

D = DEFAULT value or DEFAULT Constraint

F = foreign key constraint

FN = scalar function

IF = embedded table functions

K = primary key or UNIQUE constraint

L = Log

P = Stored Procedure

R = rule

RF = copy and filter the Stored Procedure

S = system table

TF = table functions

TR = trigger

U = User table

V = View

X = Extended Stored Procedure

This table contains all objects in the database, such as the table Stored Procedure view.

2. sysColumns database Field table. All fields in the current database are retained.

Important Field explanation:

SysColumns (

Name sysname, -- field name

Id int, -- the ID of the table to which the field belongs

Xtype tinyInt, -- this field type, associated with the policypes table

Length smallint, -- physical storage length of the field

......

)

For example, you need to query the fields in a table and the length of these fields.

3. sysUsers

System group and user of the current database.

SysUsers (

Uid smallint, -- User ID

Name smallint, -- name

Uid varbinary (85), -- belongs to a login

......

)

Manage database users

4. sysdenpends

The dependency of the current database. For example, when I want to modify a structure, I am afraid that the modification will affect the stored procedure of other view functions. This can be queried before modification. View function stored procedures that call this table can be modified after modification,

Generally, the system tables used by programmers are basically the same. Other Special system tables (mainly in master or tempdb) are used.

A useful example: (query the table creator)

SELECT owner FROM DBA_ALL_TABLES WHEREtable_name = upper ('table name ');

System Parameters of the Oracle database are stored in the database. You can use SQLPLUS to query the system parameters in SYSYTEM.

1. Several important tables or views are as follows:

V $ controlfile: control file information;

V $ datafile: data file information;

V $ log: log File Information;

V $ process: processor information;

V $ session: session information;

V $ transaction: transaction information;

V $ resource: resource information;

V $ sga: information about the global zone of the system.

The 'v $ 'in the view name above is only a character in the view name. Similar to the preceding view or table, there are still many, located:

$ ORACLE_HOME/RDBMS/ADMIN/CATALOG. SQL file.

These views or tables can be queried using the SELECT statement in SQLPLUS.

2. Data Dictionary View

Tables and columns

DBA_TABLES, ALL_TABLES, and USER_TABLES display general information about database tables.

DBA_TAB_COLUMNS, ALL_TAB_COLUMNS, and USER_TAB_COLUMNS display the column information of each database table.

Note: DBA_OBJECTS, ALL_OBJECTS, and USER_OBJECTS display the information of the mode object, including the table.

Integrity constraints

DBA_CONSTRAINTS, ALL_CONSTRAINTS, and USER_CONSTRAINST display general information about constraints.

DBA_CONS_COL