Shulou(Shulou.com)06/01 Report--

-- monitor whether the index is used

Alter index & index_name monitoring usage

Alter index & index_name nomonitoring usage

Select * from v$object_usage where index_name = & index_name

-- find the Istroke O distribution of the data file

Select df.name,phyrds,phywrts,phyblkrd,phyblkwrt,singleblkrds,readtim,writetim

From v$filestat fs,v$dbfile df

Where fs.file#=df.file# order by df.name

-- find the value of a hidden parameter

Col ksppinm format a54

Col ksppstvl format a54

Select ksppinm, ksppstvl

From x$ksppi pi, x$ksppcv cv

Where cv.indx=pi.indx and pi.ksppinm like'_% 'escape'\ 'and pi.ksppinm like'% meer%'

-- find the larger latch in the system

Select name,sum (gets), sum (misses), sum (sleeps), sum (wait_time)

From v$latch_children

Group by name having sum (gets) > 50 order by 2

-- find the switching frequency of archived logs (the production system may take a long time)

Select start_recid,start_time,end_recid,end_time,minutes from (select test.*, rownum as rn

From (select b.recid start_recid,to_char (b.firstwatch timejour hh34:mi:ss' MMMMurdd) start_time

A.recid end_recid,to_char (a. Firstwatch time.mai YyyymuryMMMurdd hh34:mi:ss') end_time,round (a.first_time-b.first_time) * 24) * 60BEI 2) minutes

From v$log_history a journal logarithmic history b where a.recid=b.recid+1 and b.first_time > sysdate-1

Order by a.first_time desc) test) y where y.rn

< 30 --求回滚段正在处理的事务 select a.name,b.xacts,c.sid,c.serial#,d.sql_text from v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction e where a.usn=b.usn and b.usn=e.xidusn and c.taddr=e.addr and c.sql_address=d.address and c.sql_hash_value=d.hash_value order by a.name,c.sid,d.piece; --求出无效的对象 select 'alter procedure '||object_name||' compile;' from dba_objects where status='INVALID' and wner='&' and object_type in ('PACKAGE','PACKAGE BODY'); / select owner,object_name,object_type,status from dba_objects where status='INVALID'; --求process/session的状态 select p.pid,p.spid,s.program,s.sid,s.serial# from v$process p,v$session s where s.paddr=p.addr; --求当前session的状态 select sn.name,ms.value from v$mystat ms,v$statname sn where ms.statistic#=sn.statistic# and ms.value >

0

-- find the index information of the table

Select ui.table_name,ui.index_name

From user_indexes ui,user_ind_columns uic

Where ui.table_name=uic.table_name and ui.index_name=uic.index_name

And ui.table_name like'& table_name%' and uic.column_name='&column_name'

-- displays the foreign key information of the table

Col search_condition format a54

Select table_name,constraint_name

From user_constraints

Where constraint_type ='R 'and constraint_name in (select constraint_name from user_cons_columns where column_name='&1')

Select rpad (child.table_name,25,'') child_tablename

Rpad (cp.column_name,17,'') referring_column,rpad (parent.table_name,25,'') parent_tablename

Rpad (pc.column_name,15,'') referred_column,rpad (child.constraint_name,25,'') constraint_name

From user_constraints child,user_constraints parent

User_cons_columns cp,user_cons_columns pc

Where child.constraint_type ='R 'and child.r_constraint_name = parent.constraint_name and

Child.constraint_name = cp.constraint_name and parent.constraint_name = pc.constraint_name and

Cp.position = pc.position and child.table_name ='& table_name'

Order by child.owner,child.table_name,child.constraint_name,cp.position

-displays the partitions and subpartitions of the table (user_tab_subpartitions)

Col table_name format a16

Col partition_name format a16

Col high_value format a81

Select table_name,partition_name,HIGH_VALUE from user_tab_partitions where table_name='&table_name'

-- generate an execution plan using dbms_xplan

Explain plan set statement_id ='& sql_id' for & sql

Select * from table (dbms_xplan.display)

-- ask for redo information for a transaction (bytes)

Select s.name,m.value

From v$mystat m.j.m.vault statname s

Where m.statistic#=s.statistic# and s.name like'% redo size%'

-- ask for more than 5% of the objects cached in cache

Select o.ownerdir o.objectkeeper type pr o.objectmaker namequarter count (b.objd)

From v$bh b,dba_objects o

Where b.objd = o.object_id

Group by o.owner,o.object_type,o.object_name

Having count (b.objd) > (select to_number (value) * 0.05from v$parameter where name = 'db_block_buffers')

Ask who is blocking a session (10g)

Select sid, username, event, blocking_session

Seconds_in_wait, wait_time

From v$session where state in ('WAITING') and wait_class! =' Idle'

-- ask for the OS process ID of session

Col program format a54

Select p.spid "OS Thread", b.name "Name-User", s.program

From v$process p, v$session s, v$bgprocess b

Where p.addr = s.paddr and p.addr = b.paddr

UNION ALL

Select p.spid "OS Thread", s.username "Name-User", s.program

From v$process p, v$session s where p.addr = s.paddr and s.username is not null

-- check for session blocking

Col user_name format a32

Select / * + rule * / lpad ('', decode (l.xidusn, 0pje 3d0)) | | l.oracle_username user_name, o.owner.o.objectbearnamemems.sidjudics.serial.serial.o.owner.0.owner

From v$locked_object, dbathing objects, dbathing sessions.

Where l.object_id=o.object_id and l.session_id=s.sid order by o.object_id,xidusn desc

Col username format a15

Col lock_level format a8

Col owner format a18

Col object_name format a32

Select / * + rule * / s.username, decode (l.typeMagnetizer lock', 'tx','row lock', null) lock_level, o.ownerMagol o.objectsancnamelamers.sidmemers.serial#

From v$session sjaw vandlock lje dbaum objects o

Where l.sid = s.sid and l.id1 = o.object_id (+) and s.username is not null

-- asking for waiting events and session information / seeking session waiting and conversation information

Select se.sid,s.username,se.event,se.total_waits,se.time_waited,se.average_wait

From v$session sjorific session session event se

Where s.username is not null and se.sid=s.sid and s. StatusThe active 'and se.event not like'% SQL*Net%' order by s.username

Select s.sid,s.username,sw.event,sw.wait_time,sw.state,sw.seconds_in_wait

From v$session sjiggery vandalism session wait sw

Where s.username is not null and sw.sid=s.sid and sw.event not like'% SQL*Net%' order by s.username

-- ask for the file_id/block_id of session waiting

Col event format a24

Col p1text format a12

Col p2text format a12

Col p3text format a12

Select sid,event,p1text, p1, p2text, p2, p3text, p3

From v$session_wait

Where event not like'% SQL%' and event not like'% rdbms%' and event not like'% mon%' order by event

Select name,wait_time from v$latch l where exists (select 1 from (select sid,event,p1text, p1, p2text, p2, p3text, p3)

From v$session_wait

Where event not like'% SQL%' and event not like'% rdbms%' and event not like'% mon%'

) x where x.p1 = l.latch#)

-- find the object that the session is waiting for

Col owner format a18

Col segment_name format a32

Col segment_type format a32

Select owner,segment_name,segment_type

From dba_extents

Where file_id = & file_id and & block_id between block_id and block_id + blocks-1

-- ask for block information in buffer cache

Select o.OBJECT_TYPE, substr (o.OBJECT.NAME Magi 1 and 10) objname, b.objd, b.status, count (b.objd)

From v$bh b, dba_objects o

Where b.objd = o.data_object_id and o.owner ='& 1' group by o.object_type, o.objectpapernameauthor.objd, b.status

-- seek the space usage of log files

Select le.leseq current_log_sequence#, 100*cp.cpodr_bno/le.lesiz percentage_full

From x$kcccp cp,x$kccle le

Where le.leseq = cp.cpodr_seq

-- ask for the object in waiting

Select / * + rule * / s.sid, s.username, w.event, o.owner, o.segment_name, o.segment_type

O.partition_name, w.seconds_in_wait seconds, w.state

From v$session_wait w, v$session s, dba_extents o

Where w.event in (select name from v$event_name where parameter1 = 'file#'

And parameter2 = 'block#' and name not like' control%')

And o.owner 'sys' and w.sid = s.sid and w.p1 = o.file_id and w.p2 > = o.block_id and w.p2

< o.block_id + o.blocks --求当前事务的重做尺寸 select value from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size'; --唤醒smon去清除临时段 column pid new_value Smon set termout off select p.pid from sys.v_$bgprocess b,sys.v_$process p where b.name = 'SMON' and p.addr = b.paddr / set termout on oradebug wakeup &Smon undefine Smon --求回退率 select b.value/(a.value + b.value),a.value,b.value from v$sysstat a,v$sysstat b where a.statistic#=4 and b.statistic#=5; --求DISK READ较多的SQL select st.sql_text from v$sql s,v$sqltext st where s.address=st.address and s.hash_value=st.hash_value and s.disk_reads >

300

-- seek the serious SQL of DISK SORT

Select sess.username, sql.sql_text, sort1.blocks

From v$session sess, v$sqlarea sql, v$sort_usage sort1

Where sess.serial# = sort1.session_num

And sort1.sqladdr = sql.address

And sort1.sqlhash = sql.hash_value and sort1.blocks > 200

-- seek the creation code of the object

Column column_name format a36

Column sql_text format a99

Select dbms_metadata.get_ddl ('TABLE','&1') from dual

Select dbms_metadata.get_ddl ('INDEX','&1') from dual

-- find the index of the table

Set linesize 131

Select a.index_name,a.column_name,b.status, b.index_type

From user_ind_columns a,user_indexes b

Where a.index_name=b.index_name and a. Tablekeeper nameplate

Find the number of rows in the index

Select index_name,blevel,num_rows,CLUSTERING_FACTOR,status from user_indexes where num_rows > 10000 and blevel > 0

Select table_name,index_name,blevel,num_rows,CLUSTERING_FACTOR,status from user_indexes where status' VALID'

-- ask for the SID,SERIAL# of the current session

Select sid, serial# from v$session where audsid = SYS_CONTEXT ('USERENV','SESSIONID')

-- find the unused space of the tablespace

Col mbytes format 9999.9999

Select tablespace_name,sum (bytes) / 1024 Universe 1024 mbytes from dba_free_space group by tablespace_name

-- find the trigger defined in the table

Select table_name,index_type,index_name,uniqueness from user_indexes where table_name='&1'

Select trigger_name from user_triggers where table_name='&1'

-- find a table with no defined index

Select table_name from user_tables where table_name not in (select table_name from user_ind_columns)

-- perform common procedures

Exec print_sql ('select count (*) from tab')

Exec show_space2 ('table_name')

-- ask for free memory

Select * from v$sgastat where name='free memory'

Select a.name b.value sum (b.value) from v$statname a meme esstat b where a.statistic# = b.statistic# group by a.name

Check to see who is using that available rollback segment, or check to see that some available user is using the rollback segment

Find out the growing transaction of the rollback section, and then see how to deal with it, whether it can be commit, no more

Just see if you can kill it, and so on, to view the user information and rollback segment information of the rollback segment currently in use:

Set linesize 121

SELECT r.name "ROLLBACK SEGMENT NAME", l.sid "ORACLE PID", p.spid "SYSTEM PID", s.username "ORACLE USERNAME"

FROM v$lock l, v$process p, v$rollname r, v$session s

WHERE l.sid = p.pid (+) AND s.sid=l.sid AND TRUNC (l.id1 (+) / 65536) = r.usn AND l.type (+) = 'TX' AND l.lmode (+) = 6 ORDER BY r.name

-- View the information of the user's rollback segment

Select s.username, rn.name from v$session s, v$transaction t, v$rollstat r, v$rollname rn

Where s.saddr = t.ses_addr and t.xidusn = r.usn and r.usn = rn.usn

-- generate an execution plan

Explain plan set statement_id='a1' for & 1

-- View the execution plan

Select lpad (', 2* (level-1)) | | operation operation,options,OBJECT_NAME,position from plan_table

Start with id=0 and statement_id='a1' connect by prior id=parent_id and statement_id='a1'

Carry out the plan

1) according to SID, find the HASH_VALUE and ADDRESS of the corresponding SQL from v$sql

SELECT a.sql_text, a.address, a.hash_value

FROM v$sql a, v$session b

Where a.hash_value = b.sql_hash_value

And b.sid = & sid

Alan Lee (160921) 22:58:07

2) find the real execution plan from v$sql_plan according to the values of hash_value and address.

Set line 200

Col oper format a100

Select lpad (oper,length (oper) + level*2,'') oper,cost

From (

Select object_name | |':'| | operation | |''| | options as oper,cost,id,parent_id

From v$sql_plan

Where hash_value = & hash_value

And address ='& address'

)

Start with id=0

Connect by prior id = parent_id

Alan Lee (160921) 22:58:26

With these two steps, you can find out what execution plan the actual running SQL is using.

Set autotrace traceonly statistics

Set autotrace traceonly explain

Set autotrace traceonly on explain

-- View the use of memory

Select decode (greatest (class,10), 10 class,1,'Data',2,'Sort',4,'Header',to_char decode (class)), 'Rollback') "Class"

Sum (decode (bitand (flag,1), 1mine0jue 1)) "Not Dirty", sum (decode (bitand (flag,1), 1JEI 1je 0) "Dirty"

Sum (dirty_queue) "On Dirty", count (*) "Total"

From x$bh group by decode (greatest (class,10), 10dint decode (class,1,'Data',2,'Sort',4,'Header',to_char (class)), 'Rollback')

-- View tablespace status

Select tablespace_name,extent_management,segment_space_management from dba_tablespaces

Select table_name,freelists,freelist_groups from user_tables

-- check the system request

SELECT DECODE (name, 'summed dirty write queue length', value) /

DECODE (name, 'write requests', value) "Write Request Length"

FROM v$sysstat WHERE name IN ('summed dirty queue length',' write requests') and value > 0

-- calculate the data buffer hit rate

Select a.value + b.value "logical_reads", c.value "phys_reads"

Round ((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# = 40 and b.statistic# = 41 and c.statistic# = 42

SELECT name, (1-(physical_reads/ (db_block_gets+consistent_gets) * 100 H_RATIO FROM v$buffer_pool_statistics

-- View memory usage

Select least (max (b.value) / (1024 / 1024), sum (a.bytes) / (1024 / 1024)) shared_pool_used

Max (b.value) / (1024 / 1024) shared_pool_size,greatest (max (b.value) / (1024 / 1024), sum (a.bytes) / (1024 / 1024))-

(sum (a.bytes) / (1024 / 1024)) shared_pool_avail, ((sum (a.bytes) / (1024 / 1024)) / (max (b.value) / (1024 / 1024) * 100 avail_pool_pct

From v$sgastat a, v$parameter b where (a.pool='shared pool' and a.name not in ('free memory')) and b.

-- check the memory usage of users

Select username, sum (sharable_mem), sum (persistent_mem), sum (runtime_mem)

From sys.v_$sqlarea a, dba_users b

Where a.parsing_user_id = b.user_id group by username

-- check the caching of objects

Select OWNER,NAMESPACE,TYPE,NAME,SHARABLE_MEM,LOADS,EXECUTIONS,LOCKS,PINS,KEPT

From v$db_object_cache where type not in ('NOT LOADED','NON-EXISTENT','VIEW','TABLE','SEQUENCE')

And executions > 0 and loads > 1 and kept='NO' order by owner,namespace,type,executions desc

Select type,count (*) from v$db_object_cache group by type

-- View the library cache hit ratio

Select namespace,gets, gethitratio*100 gethitratio,pins,pinhitratio*100 pinhitratio,RELOADS,INVALIDATIONS from v$librarycache

-- check the hash of some users

Select a.username, count (b.hash_value) total_hash,count (b.hash_value)-count (unique (b.hash_value)) same_hash

(count (unique (b.hash_value)) / count (b.hash_value)) * 100 u_hash_ratio

From dba_users a, v$sqlarea b where a.user_id=b.parsing_user_id group by a.username

-- check the dictionary hit rate

Select (sum (getmisses) / sum (gets)) ratio from v$rowcache

-- View the usage of the undo segment

SELECT d.segment_name,extents,optsize,shrinks,aveshrink,aveactive,d.status

FROM v$rollname nrecoverable rollstat sdbathing rollbackstarted segs d

WHERE d.segment_id=n.usn (+) and d.segment_id=s.usn (+)

-- invalid object

Select owner,object_type,object_name from dba_objects where status='INVALID'

Select constraint_name,table_name from dba_constraints where status='INVALID'

-- find a process and track it

Select s.sidparary s.serial# from v$session smeme vested process p where s.paddr=p.addr and p.spid=&1

Exec dbms_system.SET_SQL_TRACE_IN_SESSION (& 1Jing Zhi 2JI true)

Exec dbms_system.SET_SQL_TRACE_IN_SESSION (& 1 ~ 2 ~ 2 ~ false)

-- find the locked object

Select do.object_name,session_id,process,locked_mode

From v$locked_object lo, dba_objects do where lo.object_id=do.object_id

-- ask for the tracking file of the current session

SELECT p1.value | |'/'| | p2.value | |'_ ora_' | | p.spid | | '.ora' filename

FROM v$process p, v$session s, v$parameter p1, v$parameter p2

WHERE p1.name = 'user_dump_dest' AND p2.name =' instance_name'

AND p.addr = s.paddr AND s.audsid = USERENV ('SESSIONID') AND p.background is null AND instr (p.programgramme') = 0

-- find the file and block number of the object

Select segment_name,header_file,header_block

From dba_segments where segment_name like'& 1'

-- ask for the fallback segment and block number when the object has a transaction

Select a.segment_name,a.header_file,a.header_block

From dba_segments a,dba_rollback_segs b

Where a.segment_name=b.segment_name and b.segmentmentalization

Online redefinition table for 9i

/ * you need to create a primary key if the online redefined table does not have a primary key * /

Exec dbms_redefinition.can_redef_table ('cybercafe','announcement')

Create table anno2 as select * from announcement

Exec dbms_redefinition.start_redef_table ('cybercafe','announcement','anno2')

Exec dbms_redefinition.sync_interim_table ('cybercafe','announcement','anno2')

Exec dbms_redefinition.finish_redef_table ('cybercafe','announcement','anno2')

Drop table anno2

Exec dbms_redefinition.abort_redef_table ('cybercafe','announcement','anno2')

Commonly used logmnr scripts (cybercafe)

Exec sys.dbms_logmnr_d.build (dictionary_filename = > 'esal',dictionary_location = >' / home/oracle/logmnr')

Exec sys.dbms_logmnr.add_logfile (logfilename= >'/ home/oracle/oradata/esal/archive/1_24050.dbf', ptions= > sys.dbms_logmnr.new)

Exec sys.dbms_logmnr.add_logfile (logfilename= >'/ home/oracle/oradata/esal/archive/1_22912.dbf', ptions= > sys.dbms_logmnr.addfile)

Exec sys.dbms_logmnr.add_logfile (logfilename= >'/ home/oracle/oradata/esal/archive/1_22913.dbf', ptions= > sys.dbms_logmnr.addfile)

Exec sys.dbms_logmnr.add_logfile (logfilename= >'/ home/oracle/oradata/esal/archive/1_22914.dbf', ptions= > sys.dbms_logmnr.addfile)

Exec sys.dbms_logmnr.start_logmnr (dictfilename= >'/ home/oracle/logmnr/esal.ora')

Create table logmnr2 as select * from v$logmnr_contents

Deadlock problem: 1) the process of finding deadlocks:

Sqlplus "/ as sysdba"

SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,l.ORACLE_USERNAME

L. OSIS USERNAME FROM V$LOCKED_OBJECT l.Process Variance session S WHERE l.SESSION_ID=S.SID

2) kill the process of dropping the deadlock:

Alter system kill session 'sid,serial#'; (where sid=l.session_id)

3) if it can't be solved

Select pro.spid from v$session ses,v$process pro where ses.sid=XX and ses.paddr=pro.addr

Where sid is replaced with a deadlocked sid.

Exit

-- Dictionary related to permissions

ALL_COL_PRIVS represents the authorization on the column, and the user and PUBLIC are granted

ALL_COL_PRIVS_MADE represents the authorization on the column, and the user is the owner and grantee

ALL_COL_RECD represents the authorization on the column, and the user and PUBLIC are granted

ALL_TAB_PRIVS represents authorization on the object, and the user is PUBLIC or the grantee or the user is the owner

ALL_TAB_PRIVS_MADE represents permissions on an object, and the user is the owner or grantee

ALL_TAB_PRIVS_RECD represents a permission on an object, and the user is PUBLIC or grantee

All authorizations on the DBA_COL_PRIVS database column

DBA_ROLE_PRIVS displays roles that have been granted to users or other roles

DBA_SYS_PRIVS has granted system permissions to the user or role

All permissions on DBA_TAB_PRIVS database objects

ROLE_ROLE_PRIVS displays the roles granted to the user

ROLE_SYS_PRIVS displays the system permissions granted to users through roles

ROLE_TAB_PRIVS displays the object permissions granted to the user through roles

SESSION_PRIVS displays all system permissions that are now available to the user

USER_COL_PRIVS displays the permissions on the column, and the user is the owner, grantee, or grantee

USER_COL_PRIVS_MADE displays the granted permissions on the column, and the user is the owner or grantee

USER_COL_PRIVS_RECD displays the granted permissions on the column, and the user is the owner or grantee

USER_ROLE_PRIVS displays all roles granted to the user

USER_SYS_PRIVS displays all system permissions granted to the user

USER_TAB_PRIVS displays all object permissions granted to the user

USER_TAB_PRIVS_MADE displays the object permissions that have been granted to other users, who are the owners

USER_TAB_PRIVS_RECD displays the object permissions that have been granted to other users, who are the grantees

How to use dbms_stats to analyze tables and schemas?

Exec dbms_stats.gather_schema_stats (ownname= >'& USER_NAME',estimate_percent= > dbms_stats.auto_sample_size

Method_opt = > 'for all columns size auto',degree= > DBMS_STATS.DEFAULT_DEGREE)

Exec dbms_stats.gather_schema_stats (ownname= >'& USER_NAME',estimate_percent= > dbms_stats.auto_sample_size,cascade= > true)

/ *

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]

FOR COLUMNS [size clause] column | attribute [size_clause] [, column | attribute [size_clause].]

Where size_clause is defined as size_clause: = SIZE {integer | REPEAT | AUTO | SKEWONLY}

Integer--Number of histogram buckets. Must be in the range [1254].

REPEAT--Collects histograms only on the columns that already have histograms.

AUTO--Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.

SKEWONLY--Oracle determines the columns to collect histograms based on the data distribution of the columns

, /

Common system tables, views, and roles

View information about the user: dba_users

View information about roles: dba_roles,dba_role_privs,role_sys_privs

View information about system permissions: dba_sys_privs

View the current database tablespace status: dba_tablespaces

View the user's system permissions: user_sys_privs

View the permissions granted by one user to another: user_tab_privs_made

View the column-level permissions granted by one user to another user: user_col_privs_made

View the permissions accepted by a user: user_tab_privs_recd

View the column-level permissions accepted by a user: user_col_privs_recd

View information about the user's role: user_role_privs

View information about system permissions granted to a role: role_sys_privs

View information about object permissions granted to a role: role_tab_privs

View the table information owned by the current user: user_tables

View the table information that the current user has permission to access: all_tables

View column information for all tables owned by the current user: user_tab_columns

View the column information in the table that the current user can access: all_tab_columns

View all constraint information owned by the current user: user_constraint

View the relationships of all constraints and columns owned by the current user: user_cons_constraint

View the comments in the table: user_tab_comments

View the column comments in the table: user_col_comments

Table that provides exercises: dual

View the name and abbreviation of the relevant time zone: v$timezone_names

V$OPTION: displays the installed Oracle option

Select * from v$option

Get the details of the Oracle version

Select * from v$version

Get the details of the initialization parameters

Select name,value,description from v$parameter

Get the details of the current routine

Select * from v$instance

1. Users

View the current user's default tablespace

SQL > select username,default_tablespace from user_users

View the role of the current user

SQL > select * from user_role_privs

View the system permissions and table-level permissions of the current user

SQL > select * from user_sys_privs; or

Select username, default_tablespace, temporary_tablespace, priv granted_role, default_role from dba_users u, (select grantee,granted_role priv,default_role from dba_role_privs union all select grantee,privilege priv,'' from dba_sys_privs c) r where u.username = r.grantee order by username

SQL > select * from user_tab_privs

Displays the permissions for the current session

SQL > select * from session_privs

Displays the system permissions of the specified user

SQL > select * from dba_sys_privs where grantee='GAME'

2. Table

View all the tables under the user

SQL > select * from user_tables

View a table whose name contains log characters

SQL > select object_name,object_id from user_objects

Where instr (object_name,'LOG') > 0

View the creation time of a table

SQL > select object_name,created from user_objects where object_name=upper ('& table_name')

View the size of a table

SQL > select sum (bytes) / (1024024) as "size (M)" from user_segments

Where segment_name=upper ('& table_name')

View the table in the memory area of ORACLE

SQL > select table_name,cache from user_tables where instr (cache,'Y') > 0

3. Index

View the number of indexes and categories

SQL > select index_name,index_type,table_name from user_indexes order by table_name

View the fields indexed by the index

SQL > select * from user_ind_columns where index_name=upper ('& index_name')

View the size of the index

SQL > select sum (bytes) / (1024024) as "size (M)" from user_segments

Where segment_name=upper ('& index_name')

4. Serial number

Check the serial number. Last_number is the current value.

SQL > select * from user_sequences

5. View

View the name of the view

SQL > select view_name from user_views

View the select statement that creates the view

SQL > set view_name,text_length from user_views

SQL > set long 2000; description: you can set the size of the set long according to the text_ value of the view

SQL > select text from user_views where view_name=upper ('& view_name')

6. Synonyms

View the name of the synonym

SQL > select * from user_synonyms

7. Constraint conditions

View the constraints of a table

SQL > select constraint_name, constraint_type,search_condition, r_constraint_name

From user_constraints where table_name = upper ('& table_name')

SQL > select c. Column intact name.

From user_constraints c,user_cons_columns cc

Where c.owner = upper ('& table_owner') and c.table_name = upper ('& table_name')

And c.owner = cc.owner and c.constraint_name = cc.constraint_name

Order by cc.position

8. Store functions and procedures

View the status of functions and procedures

SQL > select object_name,status from user_objects where object_type='FUNCTION'

SQL > select object_name,status from user_objects where object_type='PROCEDURE'

View the source code of functions and procedures

SQL > select text from all_source where wner=user and name=upper ('& plsql_name')

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.