Features of common Oracle system data dictionary tables and system packages

Object Information]
View tables: user_tables, all_tables, and dba_tables

View table fields: user_tab_columns, all _ tab_columns, dba_tab_columns

View table comments: user _ tab_comments, all_tab_comments, dba_tab_comments

View field comments: user_col_comments, all_col_comments, dba_col_comments

View index information: user_indexes, all_indexes, dba_indexes

View the index fields: user_ind_columns, all_ind_columns, dba_ind_columns

View constraints: user_constraints, all_constraints, dba_constraints

View the constraints field: user_cons_columns, all_cons_columns, dba_cons_columns

View trigger information: user_triggers, all_triggers, all_triggers

View sequence information: user_sequences, all_sequences, dba_sequences

View information: user_views, all_views, dba_views

View synonym information: user_synonyms, all_synonyms, dba_synonyms

View DBLINK information: user_db_links, all_db_links, dba_db_links

View JOB information: user_jobs, all_jobs, dba_jobs

View all object information (process PROCEDURE, FUNCTION, package and package body, JOB and LOB fields, tables, views, indexes, sequences, triggers): user_objects, all_objects, dba_objects

View process, function, trigger, package and package content: user_source, all_source, dba_source

[Example]

-- View all tables under a user

SELECT * FROM user_tables;

-- View the creation time of a table

SELECT object_name, created

FROM user_objects

WHERE object_name = upper ('& table_name ');

-- View the index Field

SELECT * FROM user_ind_columns WHERE index_name = upper ('& index_name ');

-- View the serial number. last_number is the current value.

SELECT * FROM user_sequences;

-- View the view and its SELECT statement

SELECT * FROM user_views;

-- View the constraints of a table

SELECT * FROM user_constraints WHERE table_name = upper ('& table_name ');

-- View the process status

SELECT * FROM user_objects WHERE object_type = 'processed ';

-- View the process Source code

SELECT * FROM user_source where name = upper ('& procedure_name ');

-- View tables whose names contain log characters

SELECT object_name, object_id

FROM user_objects

WHERE instr (object_name, 'log')> 0;

-- View the table creation statement

SELECT DBMS_METADATA.GET_DDL ('Procedure ', 'procedure _ name', 'User _ name') FROM dual;

-- View definitions of remote database objects

SELECT DBMS_LOB.SUBSTR @ dblinkname (DBMS_METADATA.GET_DDL @ dblinkname ('table', 'tablename', 'Username') from dual @ dblinkname;

[Tablespace and data file information]
View the user's default tablespace and temporary tablespace: user_users, all_users, dba_users

View tablespace information: user_tablespaces, all_tablespaces, dba_tablespaces

View the tablespace free space: dba_free_space

View tablespace data files and usage: dba_data_files

View data file details: v $ datafile

View basic data Segment Information: user_segments, all_segments, dba_segments

View the basic information of the data zone: user_extents, all_extents, dba_extents

View temporary tablespace information: dba_temp_files

Rollback Segment Information: dba_rollback_segs

[Example]

-- View the user's default tablespace and temporary tablespace

SELECT * FROM user_users;

-- View the basic information about the tablespaces that the user has permission to operate

SELECT * FROM user_tablespaces;

-- View the remaining space in the tablespace

SELECT tablespace_name, SUM (bytes), SUM (blocks)

FROM dba_free_space

Group by tablespace_name;

-- View the tablespace data file and its location

Select * from dba_data_files;

-- View detailed information about a data file

SELECT * FROM v $ datafile

[User, role, and permission information]
View all roles: dba_roles

View user roles: user_role_privs and dba_role_privs

View user permissions: user_sys_privs and dba_sys_privs

View role permissions: role_sys_privs

Permission to view user operation tables: user_tab_privs, all_tab_privs, and dba_tab_privs

[Database information]
View control file information: v $ controlfile

View system parameter information: v $ parameter

Database system running information: v $ datafile

View the basic information of the current instance: v $ instance

Valid parameter information of the instance: v $ system_parameter

View log file location: v $ logfile

View log File information: v $ log

View Log thread information: v $ thread

View archiving log location: v $ archived_dest

View Archived log information: v $ archived_log

Log historical SWITCH Information: v $ loghist

View the number of connections allowed by the data: show parameter processes

[Memory and process information]
Basic information of SGA: v $ sga

SGA details: v $ sgastat

Share SQL details in the SQL area (the SQL text is CLOB, and each SQL statement is recorded only once): v $ SQL

Share SQL details in the SQL area (the SQL text is CLOB, and different users execute the same SQL statement multiple times): v $ sqlarea

Share The SQL text information in the SQL area (only records the text and splits it into multiple rows): v $ sqltext

Database cache performance statistics: v $ librarycache

Online rollback segment name: v $ rollstat

Background process information: v $ bgprocess

Session Info: v $ session

[Performance information]
File IO information: v $ filestat

Latch statistics: v $ latch

Block competition statistics: v $ waitstat

Total Event wait time: v $ system_event

Event wait information: v $ session_wait

Sorting operation information: v $ sort_usage

Locked object information: v $ locked_object

Session IO information: v $ sess_io

Session wait information: v $ session_wait

[Example]

-- View the SID of the current session

Select userenv ('Sid ') from dual;

-- View the information of the currently locked object

SELECT o. object_name,

S. sid,

S. serial #,

S. username,

S. osuser,

S. machine,

S. terminal

FROM v $ locked_object l, dba_objects o, v $ session s

WHERE l. object_id = o. object_id

AND l. session_id = s. sid;

-- Kill the process locked by the system object

ALTER system KILL session 'Sid, serial #';

-- How can we monitor the waiting of cases?

SELECT event,

SUM (decode (wait_Time, 0, 0, 1) "Prev ",

SUM (decode (wait_Time, 0, 1, 0) "Curr ",

COUNT (*) "Tot"

FROM v $ session_wait

Group by event

Order by 4;

-- How to query processes with relatively large sorting volumes?

SELECT B. tablespace,

B. segfile #,

B. segblk #,

B. blocks,

A. sid,

A. serial #,

A. username,

A. osuser,

A. status

FROM v $ session a, v $ sort_usage B

WHERE a. saddr = B. session_addr

Order by B. tablespace, B. segfile #, B. segblk #, B. blocks;

-- How can I view the contention of Rollback segments?

Select name, waits, gets, waits/gets "Ratio"

FROM v $ rollstat C, v $ rollname D

Where c. usn = D. usn;

-- How to monitor the I/O ratio of a tablespace?

Select B. tablespace_name NAME,

B. file_name "file ",

A. phyrds pyr,

A. phyblkrd pbr,

A. phywrts pyw,

A. phyblkwrt pbw

FROM v $ filestat A, dba_data_files B

Where a. file # = B. file_id

Order by B. tablespace_name;

-- How to monitor the I/O ratio of a file system?

SELECT substr (C. file #, 1, 2 )"#",

Substr (C. name, 1, 30) "Name ",

C. status,

C. bytes,

D. phyrds,

D. phywrts

FROM v $ datafile C, v $ filestat D

Where c. file # = D. file #;

-- How to monitor the hit rate of SGA?

SELECT a. value + B. value "logical_reads ",

C. value "phys_reads ",

Round (100 * (a. value + B. value)-c. value)/(a. value + B. value) "BUFFER HIT RATIO"

FROM v $ sysstat a, v $ sysstat B, v $ sysstat c

WHERE a. statistic # = 38

AND B. statistic # = 39

AND c. statistic # = 40;

-- How to monitor the hit rate of the dictionary buffer in SGA?

SELECT parameter,

Gets,

Getmisses,

Getmisses/(gets + getmisses) * 100 "miss ratio ",

(1-(SUM (getmisses)/(SUM (gets) + SUM (getmisses) * 100 "Hit ratio"

FROM v $ rowcache

WHERE gets + getmisses <> 0

Group by parameter, gets, getmisses;

-- How to monitor the hit rate of shared cache in SGA should be less than 1%

Select sum (pins) "Total Pins ",

SUM (reloads) "Total Reloads ",

SUM (reloads)/SUM (pins) * 100 libcache

FROM v $ librarycache;

Select sum (pinhits-reloads)/SUM (pins) "hit radio ",

SUM (reloads)/SUM (pins) "reload percent"

FROM v $ librarycache;

-- How do I know the user sessions with many CPUs?

SELECT a. sid,

Spid,

Status,

Substr (a. program, 1, 40) prog,

A. terminal,

Osuser,

VALUE/60/100 VALUE

FROM v $ session a, v $ process B, v $ sesstat c

WHERE c. statistic # = 11

AND c. sid = a. sid

AND a. paddr = B. addr

Order by value desc;

[Common system package]
Dbms_output is used for input and output information.

Put (): output in the current row

Put_line (): wrap the line at the end of the current row and output it in the new row.

Newline: line feed

Getline: Gets the single row information of the buffer.

Getlines: Gets the multi-row information of the buffer.

Dbms_job is used to arrange and manage job queues.

Submit (): create a job

Remove (): remove a job

Change (): modify a job

What (), next_date (), interval (): change the job task, next execution time, interval

Broken (), run (): pause a job, run a job

Dbms_lob use and maintain the LOB field

Write: write data to LOB.

Read: read data from LOB.

Instr: check the character position from the LOB

Substr: take characters from LOB

Getlength: return the length of the LOB.

Dbms_metadata.get_ddl: generate the ddl information of the database object

Dbms_random: quickly generates random numbers

Initialize: initialize the dbms_random package. The random seed must be provided.

Seed: reset the random seed.

Random: generate random numbers

Dbms_flashback: The flashback feature that activates or disables a session. For normal users, authorization is required.

Enable_at_time: activates the flashback of the session in time mode.

Enable_at_system_change_number: activates the flashback of the session in the system change number (scn) mode.

Get_system_change_number: obtains the current scn value of the system.

Disable: disable the flashback mode of the session.

Dbms_ddl: DDL Information Management

Alter_compile: recompilation process, functions, and packages

Analyze_object: Analysis Table, index, cluster, and statistical data generation

Dbms_stat: used to collect, view, and modify the optimization statistics of database objects.

Get_column_stats: obtains column statistics.

Get_index_stats: obtains the index statistics.

Get_system_stats: obtains system statistics from a statistical table or data dictionary.

Get_table_stats: obtains table statistics.

Dbms_session

Dbms_rowid: get the information of the row ID (rowid) and create a ROWID

Dbms_shared_pool: Operation sharing pool

Sizes: displays objects larger than the specified size in the shared pool.

Keep: used to bind a specific object to a shared pool.

Unkeep: clears objects bound to the shared pool.