oracle-Common system data dictionary table, System Package function classification

Recently, because of the use of some information about the system, simply collected a little about the Oracle database system of some system tables, functions, etc., convenient for later viewing. To be added later.

Original address: 53189513;

"View some tables and other structure information for a user's subordinates"

View tables: User_tables, All_tables, dba_tables
View table fields: User_tab_columns, All_ tab_columns, Dba_tab_columns
View Table notes: User_ tab_comments, all_tab_comments, dba_tab_comments
View field notes: User_col_comments, all_col_comments, Dba_col _comments
View index information: user_indexes, all_indexes, dba_indexes
View the fields in which the index is located: User_ind_columns, All_ind_columns, Dba_ind_ Columns
View constraint information: user_constraints, all_constraints, dba_constraints
View the field in which the constraint is located: User_cons_columns, All_cons_ columns, dba_cons_columns
Viewing trigger information: User_triggers, all_triggers, all_triggers
Viewing 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 (procedure procedure, function functions, package and package bodies, job and LOB fields, tables, views, indexes, sequences, triggers): User_objects, all_objects, dba_objects
viewing procedures, Functions, triggers, packages, and package body contents: User_source, All_source, Dba_source

Special statements:
--View the Build table statement
SELECT Dbms_metadata. GET_DDL (' PROCEDURE ', ' procedure_name ', ' user_name ') from dual;
--View the definition of a remote database object
SELECT [email protected] ([email protected] (' TABLE ', ' TABLENAME ', ' USERNAME ')) from [email protected];

"Tablespace and data file information"

View user default tablespace and temporary tablespace: user_users, All_users, dba_users
View tablespace information: user_tablespaces, All_tablespaces, dba_tablespaces
See table space free space: Dba_free_space
View tablespace data files and usage: dba_data_files
View data files More information: V$datafile
View data Segments basic information: User_segments, all_segments, dba_segments
View data area basic information: user_extents, all_extents, dba_extents
View temporal tablespace Information: dba_temp_files
Rollback Segment Information: Dba_rollback_segs

"Users, roles, and permissions information"

View all roles: Dba_roles
View user roles: User_role_privs, Dba_role_privs
View User rights: User_sys_privs, Dba_sys_privs
View Role Permissions: Role_sys_privs
View User Action Table permissions: User_tab_privs, All_tab_privs, Dba_tab_privs

"Database Information"

To view Control file information: V$controlfile
View system parameter information: V$parameter
Database system Operation information: V$datafile
View current instance basic information: v$instance
Instance current valid parameter information: V$system_parameter
View Log file location: V$logfile
View Log file information: V$log
View Log thread information: V$thread
View Archive Log Location: V$archived_dest
View archive log information: V$archived_log
Log history switch Information: V$loghist
To view the number of connections allowed for data: Show parameter processes

"Memory and process information"

Basic information for the SGA area: V$SGA
More information of SGA area: V$sgastat
Shared SQL area SQL details (SQL text is CLOB, each SQL is logged only once): V$sql
Shared SQL area SQL details (SQL text is CLOB, different users perform the same SQL multiple records): V$sqlarea
Text information for shared SQL area SQL (only text is recorded and split into multiple lines): V$sqltext
Statistics for library cache performance: V$librarycache
The name of the online rollback segment: V$rollstat
Background process information: v$bgprocess
Session Information: V$session

"Performance Information"

File IO information: v$filestat
Latch statistics: V$latch
Block Competition Statistics: V$waitstat
Total wait time for the event: v$system_event
Event Wait Information: v$session_wait
Sort operation Information: V$sort_usage
Locked Object information: V$locked_object
IO information for the session: V$sess_io
Waiting information for the session: V$session_wait

For example

--View the SID of the current session
Select Userenv (' Sid ') from dual;

--View the currently locked object information
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 system object lock process
ALTER System KILL Session ' sid,serial# ';

--How to monitor the waiting of the case?
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 do I query the process of doing a relatively large sort?
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 do I see the race condition of the rollback segment?
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 table space?
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 file system?
Select substr (c.file#, 1, 2) "#", substr (C.name, 1,) "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 SGA hit rate?
Select A.value + b.value "logical_reads", C.value "Phys_reads", round ((A.value + b.value)-C.value)/(A.value + B.V alue)) "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 buffers in the SGA?
Select Parameter,gets,getmisses,getmisses/(gets + getmisses) * "Miss Ratio", (1-(SUM (getmisses)/(sum () + SUM ( getmisses))) * "Hit ratio"
From V$rowcache
WHERE gets + getmisses <> 0
GROUP by parameter, gets, getmisses;

--How to monitor the hit ratio of shared buffers in the SGA should be less than 1%
Select SUM (Pins) "Total pins", sum (reloads) "Total reloads", sum (reloads)/SUM (Pins) * Libcache
From V$librarycache;
Select SUM (pinhits-reloads)/sum (Pins) "Hit Radio", SUM (reloads)/sum (Pins) "Reload percent"
From V$librarycache;

--How to know the user session with more CPU?
SELECT A.sid,spid,status,substr (A.program, 1, max) 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 Packages"

Dbms_output for input and output information
Put (): Output in Current line
Put_Line (): Wraps at the end of the current line and outputs in a new row
NewLine: Line break
Getline: Gets the single-line information for the buffer
Getlines: Get multi-line information for buffer
Dbms_job for scheduling and managing job queues
Submit (): New Job
Remove (): Remove Job
Change (): Modify Job
What (), next_date (), Interval (): Change job task, next execution time, time interval
Broken (), run (): Pause job, running job
Dbms_lob using and maintaining LOB fields
Write: Writing data to the LOB
READ: reading data from LOB
InStr: Check the character position from the LOB
SUBSTR: Fetching characters from lobs
GetLength: Return LOB length
DBMS_METADATA.GET_DDL: Generating DDL information for database objects
Dbms_random: Fast generation of random numbers
Initialize: Initialize Dbms_random package, must provide random number seed
Seed: Reset random number Seed
Random: Production of stochastic
Dbms_flashback: Activates or disables the flashback feature of the session, which is required for ordinary users to use authorization
Enable_at_time: Activating session Flashback in a time-based manner
Enable_at_system_change_number: Flashback to activate session with system change number (SCN)
Get_system_change_number: Gets the current SCN value of the system
Disable: Flashback mode for Forbidden Sessions
DBMS_DDL:DDL Information Management
Alter_compile: recompiling procedures, functions, and packages
Analyze_object: Analyze tables, indexes, clusters, and generate statistical data
Dbms_stat: Used to collect, view, and modify optimization statistics for database objects
Get_column_stats: Obtaining Statistics for columns
Get_index_stats: Getting the statistics for the index
Get_system_stats: Obtaining system statistics from statistical tables or data dictionaries
Get_table_stats: Obtaining statistics for a table
Dbms_session
Dbms_rowid: Gets the information of the row identifier (ROWID) and establishes the ROWID
Dbms_shared_pool: Manipulating shared pools
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 that are bound to a shared pool

oracle-Common system data dictionary table, System Package function classification