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

Source: Internet
Author: User
Tags rollback

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.