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

Query tables with high fragmentation (actual usage less than 30%), that is, tables that can be shrunk

Why is the condition block > 100? because in some very small tables, only a few rows of data are actually very small, but the one-time allocation of block is 5 (11g starts to allocate 1m block size at one time by default, see the NEXT parameter of create table storged). Five block is too different from several rows of small table data.

In the algorithm, / 0.9 is because the pfree of a block is generally 10%, so a block only uses 90% at most, and when a row of data is larger than 8KB, it is easy to generate row links and store a row in fragments, and the same block is less than 90%.

AVG_ROW_LEN is relatively accurate, for example, the table of personal experiments has 6 fields, one number, and the other 5 are all char (100). But the actual data are all '111111' 7 digits, and the AVG_ROW_LEN display is still 513.

SELECT TABLE_NAME, (BLOCKS*8192/1024/1024) "theoretical size M"

(NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9) "actual size M"

Round ((NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9) / (BLOCKS*8192/1024/1024), 3) * 100 |'%'"actual utilization%"

FROM USER_TABLES where blocks > 100 and (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9) / (BLOCKS*8192/1024/1024) 30% means that the index fragment is large), that is, the index that needs to be rebuilt

Select name,del_lf_rows,lf_rows, round (del_lf_rows/decode (lf_rows,0,1,lf_rows) * 100Power0) | |'% 'frag_pct from index_stats where round (del_lf_rows/decode (lf_rows,0,1,lf_rows) * 100recover0) > 30

Table with high cluster factor clustering_factor

The closer the cluster factor is to the number of blocks, the better. Approaching the number of rows means that the row distribution of the index column with equal column value is extremely hash, and it is possible to scan the whole table instead of the index scan.

Select tab.table_name,tab.blocks,tab.num_rows,ind.index_name,ind.clustering_factor

Round (nvl (ind.clustering_factor,1) / decode (tab.num_rows,0,1,tab.num_rows), 3) * 100 |'%'"Cluster factor is close to the number of rows"

From user_tables tab, user_indexes ind where tab.table_name=ind.table_name

And tab.blocks > 100

And nvl (ind.clustering_factor,1) / decode (tab.num_rows,0,1,tab.num_rows) between 0.35 and 3

Or

Select tab.owner,tab.table_name,tab.blocks,tab.num_rows,ind.index_name,ind.clustering_factor

Round (nvl (ind.clustering_factor,1) / decode (tab.num_rows,0,1,tab.num_rows), 3) * 100 |'%'"Cluster factor is close to the number of rows"

From dba_tables tab, dba_indexes ind where tab.table_name=ind.table_name and tab.owner

Not in ('SYS','SYSTEM','WMSYS','DBSNMP','CTXSYS','XDB','ORDDATA','SYSMAN','CATALOG','APEX_030200','MDSYS','OLAPSYS','EXFSYS')

And tab.blocks > 100

And nvl (ind.clustering_factor,1) / decode (tab.num_rows,0,1,tab.num_rows) between 0.35 and 3

Check spid according to sid or sid according to spid

Select s. Sidrecery s.serialjurisdiction P.spidparentin s.terminalparentin. LOGONjurisdiction TIMEparentin s.status.PROGRAM.PROGRAM.CLIENTIDENTIFIERjurisdiction s.machineparaments.actions.Process "client machine process number", s.osuser from v$session sPower vmachines process p where s.paddr=p.addr and s.sid=XX or p.spid=YY

Check the specific sql statement according to sid (do not add conditions vaccounsession.statusstatements' ACTIVE',. For example, toad opens two connection sessions to the same database and executes some statements. When one window queries select * from v$session, it will find that the other window is INACTIVE in v$session.status, it does not mean that the other window has not executed the sql statement, and the current window is in active state. The corresponding statement of sql_id is select * from v$session instead of the previously executed sql statement, and ACTIVE indicates that sql is currently being executed. A sid may have executed many sql, so sometimes the required sql can not be found by the following. For example, querying a deadlock source sid may be just a select statement, but the sql that really caused the deadlock may not be found, because this sid may have lasted for a long time, and some of the sql executed before the sid may have been cleared in the v$sql.)

Select username,sid,SERIAL#,LOGON_TIME,status,PROGRAM,CLIENT_IDENTIFIER,machine,action,PROCESS "client machine process number", osuser,sql_text from v$session ameme vainsqltextprocess withdrawing newlines b

Where DECODE (a.sql_hash_value, 0, prev_hash_value, sql_hash_value) = b.hash_value and a.sid=&sid order by piece

Or

Select username,sid,SERIAL#,LOGON_TIME,status,sql_fulltext,PROGRAM,CLIENT_IDENTIFIER,machine,a.action,PROCESS "client machine process number", osuser from v$session ameme vSecretsql b

Where DECODE (a.sql_hash_value, 0, prev_hash_value, sql_hash_value) = b.hash_value and a.sid=&sid

Query specific sql statements according to spid (do not add conditions vaccounsession.statusstatements' ACTIVE', for example, toad opens two connection sessions to the same database, both execute some statements. When one window queries select * from v$session, it will find that the other window is INACTIVE in v$session.status, it does not mean that the other window has not executed the sql statement, and the current window is in active state. The corresponding statement of sql_id is select * from v$session instead of the previously executed sql statement, and ACTIVE indicates that sql is currently being executed.)

Select ss.SID,ss.SERIAL#,ss.LOGON_TIME,pr.SPID,sa.SQL_FULLTEXT,ss.machine, ss.TERMINAL,ss.PROGRAM,ss.USERNAME,ss.CLIENT_IDENTIFIER,ss.action,ss.PROCESS "client machine process number", ss.STATUS, ss.OSUSER,ss.status,ss.last_call_et,sa.sql_text

From v$process pr, v$session ss, v$sql sa

Where pr.ADDR = ss.PADDR

And DECODE (ss.sql_hash_value, 0, prev_hash_value, sql_hash_value) = sa.hash_value

And pr.spid=&spid

Check which IP the SQL of the historical session_id comes from (of course, this is a misunderstanding, it's all historical, how is it possible to find the spid? in fact, if you look at the trace file name, you can know that there is a sid and a specific sql in the spid,trace file. If there is an incident in the trace, then the trace cannot see the specific sql, but you can see the specific sql in the incident file. For example, 17751 in DW_ora_17751.trc is spid. There is such content as Incident 115created, dump file: / XX/incident/incdir_115/DW_ora_17751_i115.trc, then you can see the specific sql statement in DW_ora_17751_i115.trc)

The following appears in DB_ora_29349.trc

* SESSION ID: (5057.12807) 2016-10-26 1415 1415 52.726

Check through the table V$ACTIVE_SESSION_HISTORY, as follows

Select a.sqlorganizid.machine.* from V$ACTIVE_SESSION_HISTORY a where a.session_id=5057 and a.SESSION_SERIAL#=12807

Query the IP of the above machine

Select s. Sidmemory s. Serialcalendar where s.paddr=p.addr and s. LOGONEY timekeeper. Machinedepartment p.spiddepartment p.terminal from v$session sdagitte vandals process p where s.paddr=p.addr and s.machinekeeper localhost'

Execute netstat-anp on the oracle server via the spid above | grep spid

[oracle@dwdb trace] $netstat-anp | grep 17630

Tcp 210 0 192.168.64.228:11095 192.168.21.16:1521 ESTABLISHED 17630/oracleDB

Tcp 0 0:: ffff:192.168.64.228:1521:: ffff:192.168.64.220:59848 ESTABLISHED 17630/oracleDB

There are two, indicating that they are from 220, connected to the 228 database server, but connected to the 16 server through the dblink of the 228 server.

Query deadlock blocked session sid

The simplest SQL

Select * from V$SESSION_BLOCKERS

Select * from dba_waiters

The most commonly used SQL

Select sid,status,LOGON_TIME,sql_id,blocking_session "deadlock direct source", FINAL_BLOCKING_SESSION "deadlock final source", event,seconds_in_wait "session lock time _ S", LAST_CALL_ET "session STATUS duration _ S" from v$session where state='WAITING' and BLOCKING_SESSION_STATUS='VALID' and FINAL_BLOCKING_SESSION_STATUS='VALID'

You can put both SID into v$session and find that the LOGON_TIME field FINAL_BLOCKING_SESSION is earlier than SID

BLOCKING_SESSION:Session identifier of the blocking session. This column is valid only if BLOCKING_SESSION_STATUS has the value VALID.

FINAL_BLOCKING_SESSION:Session identifier of the blocking session. This column is valid only if FINAL_BLOCKING_SESSION_STATUS has the value VALID.

If you encounter a RAC environment, be sure to use gv$ to check, and execute alter system kill session 'sid,serial#' to the corresponding instance of RAC.

By substituting the above blocked session sid into the following statement, you can find the locked object and which line of the object (if sid is a blocking source session, then row_wait_obj#=-1, indicating the lock holder, is the source of the deadlock)

Select s.sidwery s.usernamerect d.ownerred.objectkeeper nameparamagram. Rowflowers waitworthy objures paramedics. Rowflowers waitworthy rowbooks. Rowbooks waitworthy filebooks. Rowboys waitcodes block.rowflowers waitbooks block# from v$session sdbathing objects d where s.row_wait_obj#=d.object_id and s.sid in (XX,XX)

Query locked DDL objects

Select d. Sessionholders. MKLMIGEM 'and d.SESSION_ID=s.sid. Name from dba_ddl_locks dre. Name MIGEM' and d.SESSION_ID=s.sid

Query inactive sessions for more than two hours

Select s. Sidrecery s.serialcalendar P.spidparentin s.LOGONjurisdiction times.LASTRECALLREET.statusparentials. PROGRAMIDEIFIERgrates. Machineparentions.terminalforces.actionwrence s.Process "client machine process number", s.osuser from v$session sforce vmachines process p

Where s.paddr=p.addr and s.sid in (select sid from v$session where machine&DB server name and status='INACTIVE' and sql_id is null and LAST_CALL_ET > 7200)

Queries block other sessions for more than 30 minutes and are themselves inactive

Select username,sid,serial#,status,seconds_in_wait,LAST_CALL_ET from v$session where sid in (select FINAL_BLOCKING_SESSION from v$session where state='WAITING' and BLOCKING_SESSION_STATUS='VALID' and FINAL_BLOCKING_SESSION_STATUS='VALID') and status='INACTIVE' and sql_id is null and seconds_in_wait > 1800

Queries may have connection pooling idle connections that are too large for initial configuration (there are many connections with the status of INACTIVE for the same program from the same machine)

Select count (ss.SID), ss.machine,ss.status,ss.TERMINAL,ss.PROGRAM,ss.USERNAME,ss.CLIENT_IDENTIFIER

From v$session ss group by ss.machine,ss.status,ss.TERMINAL,ss.PROGRAM,ss.USERNAME,ss.CLIENT_IDENTIFIER having count (ss.SID) > 10

Query the currently executing sql

SELECT s. Sidparter s. Serialtheatrical. Usernamecamera spidrect vaccounsql.sqlroomidmmachineparamagne.terminalpens. Programgrammsqltransitext.

FROM vandalism processrewrech session sreco vaccounsql

WHERE addr=paddr and s.sql_id=v$sql.sql_id AND sql_hash_value=hash_value and s.STATUSystals active

Query the executing SCHEDULER_JOB

Select owner,job_name,sid,b.SERIAL#,b.username,spid from ALL_SCHEDULER_RUNNING_JOBS,v$session bjorn vicious process where session_id=sid and paddr=addr

Query the executing dbms_job

Select job,b.sid,b.SERIAL#,b.username,spid from DBA_JOBS_RUNNING a, v$session bje vain process where a.sid=b.sid and paddr=addr

To query the average amount of PGA memory consumed by a session session and process, check the following avg_used_ M value

Select round (sum (pga_used_mem) / 1024comp1024re0) total_used_M, round (sum (pga_used_mem) / count (1) / 1024comp1024dy0) avg_used_M

Round (sum (pga_alloc_mem) / 1024comp1024re0) total_alloc_M, round (sum (pga_alloc_mem) / count (1) / 1024comp1024dy0) avg_alloc_M from v$process

TOP 10 order of execution times

Select *

From (select executions,username,PARSING_USER_ID,sql_id,sql_text

From vandals ql.dbathing users where user_id=PARSING_USER_ID order by executions desc)

Where rownum sysdate-3 order by first_time,minutes

Or

Select sequence#,to_char (first_time,'yyyy-mm-dd hh34:mi:ss') First_time,First_change#,switch_change# from

V$loghist where first_time > sysdate-3 order by 1

Or

SELECT TO_CHAR (first_time, 'MM/DD') DAY

SUM (DECODE (TO_CHAR (first_time, 'HH24'),' 00, 1, 0)) H00

SUM (DECODE (TO_CHAR (first_time, 'HH24'),' 01, 1, 0)) H01

SUM (DECODE (TO_CHAR (first_time, 'HH24'),' 02, 1, 0)) H02

SUM (DECODE (TO_CHAR (first_time, 'HH24'),' 03, 1, 0)) H03

SUM (DECODE (TO_CHAR (first_time, 'HH24'),' 04, 1, 0)) H04

SUM (DECODE (TO_CHAR (first_time, 'HH24'),' 05years, 1, 0)) H05

SUM (DECODE (TO_CHAR (first_time, 'HH24'),' 06, 1, 0)) H06

SUM (DECODE (TO_CHAR (first_time, 'HH24'),' 07, 1, 0)) H07

SUM (DECODE (TO_CHAR (first_time, 'HH24'),' 08, 1, 0)) H08

SUM (DECODE (TO_CHAR (first_time, 'HH24'),' 09, 1, 0)) H09

SUM (DECODE (TO_CHAR (first_time, 'HH24'),' 10 cycles, 1, 0)) H10

SUM (DECODE (TO_CHAR (first_time, 'HH24'),' 11, 1, 0)) H11

SUM (DECODE (TO_CHAR (first_time, 'HH24'),' 12, 1, 0)) H12

SUM (DECODE (TO_CHAR (first_time, 'HH24'),' 13, 1, 0)) H13

SUM (DECODE (TO_CHAR (first_time, 'HH24'),' 14, 1, 0)) H14

SUM (DECODE (TO_CHAR (first_time, 'HH24'),' 15 years, 1, 0)) H15

SUM (DECODE (TO_CHAR (first_time, 'HH24'),' 16 cycles, 1, 0)) H16

SUM (DECODE (TO_CHAR (first_time, 'HH24'),' 17, 1, 0)) H17

SUM (DECODE (TO_CHAR (first_time, 'HH24'),' 18, 1, 0)) H18

SUM (DECODE (TO_CHAR (first_time, 'HH24'),' 19 years, 1, 0)) H19

SUM (DECODE (TO_CHAR (first_time, 'HH24'),' 20 years, 1, 0)) H20

SUM (DECODE (TO_CHAR (first_time, 'HH24'),' 21, 1, 0)) H21

SUM (DECODE (TO_CHAR (first_time, 'HH24'),' 22 cycles, 1, 0)) H22

SUM (DECODE (TO_CHAR (first_time, 'HH24'),' 23, 1, 0)) H23

COUNT (*) TOTAL

FROM (SELECT ROWNUM RN, FIRST_TIME FROM V$LOG_HISTORY WHERE first_time > sysdate-18

And FIRST_TIME > ADD_MONTHS (SYSDATE,-1) ORDER BY FIRST_TIME)

GROUP BY TO_CHAR (first_time, 'MM/DD')

ORDER BY MIN (RN)

When querying how many seconds it takes for a lgwr process to write a log, how many seconds does it take to execute a lgwr? if state is waiting, how many seconds does the seconds_in_wait take under a waiting number seq#, that is, how many seconds does it take for a lgwr process to write an IO?

Select event,state,seq#,seconds_in_wait,program from v$session where program like'% LGWR%' and state='WAITING'

Query tables without indexes

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

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

Query the average number of session, average OS load, average db time, and average number of transactions per second in an AWR cycle

Select to_char (max (BEGIN_TIME), 'yyyy-mm-dd hh34:mi') | | to_char (max (end_time),'-- hh34:mi') time

Snap_id

Trunc (sum (case metric_name when 'Session Count' then average end), 2) sessions

Trunc (sum (case metric_name when 'Current OS Load' then average end), 2) OS_LOAD

(trunc (sum (case metric_name when 'Database Time Per Sec' then average end), 2) / 100) * (ceil ((max (end_time)-max (BEGIN_TIME)) * 24060060)) Database_Time_second

Trunc (sum (case metric_name when 'User Transaction Per Sec' then average end), 2) User_Transaction_Per_Sec

From dba_hist_sysmetric_summary

Group by snap_id

Order by snap_id

-- the unit of Database Time Per Sec corresponding value is one percentile / second.

-- (/ 100) * (ceil ((max (end_time)-max (BEGIN_TIME)) * 240606060)) represents the total number of seconds in each snap cycle. The default is the number of days when the two times of oracle are subtracted, and * 24060600060 is the number of seconds of difference.

-- the DB TIME found by this SQL is more accurate, which is consistent with the db time on awr.

Query produces objects with more hot blocks

X$bh. Tch (Touch) indicates that the higher the number of visits, the hotspot competition problem will exist.

SELECT e.owner, e.segment_name, e.segment_type

FROM dba_extents e

(SELECT *

FROM (SELECT addr,ts#,file#,dbarfil,dbablk,tch

FROM x$bh

ORDER BY tch DESC)

WHERE ROWNUM

< 11) b WHERE e.relative_fno = b.dbarfil AND e.block_id b.dbablk; 手工创建快照的语句 exec dbms_workload_repository.create_snapshot; AWR设置每隔30分钟收集一次报告，保留14天的报告 exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>

14,24,60, interval= > 30)

Select * from dba_hist_wr_control

AWR baseline viewing and creation

Select * from dba_hist_baseline

Exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id= > 7550 endgame snapshots = > 7660 penal baselinesnapname = > 'am_baseline')

Export SQL statements for AWR reports

Select * from dba_hist_snapshot

Select * from table (dbms_workload_repository.awr_report_html (DBID, INSTANCE_NUMBER, startsnapid,endsnapid))

Select * from TABLE (DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html (DBID, INSTANCE_NUMBER, startsnapid,endsnapid, DBID, INSTANCE_NUMBER, startsnapid,endsnapid))

Export the latest ADDM report (requires sys users)

Select dbms_advisor.get_task_report (task_name) from dba_advisor_tasks

Where task_id = (

Select max (t.task_id) from dba_advisor_tasks t, dba_advisor_log l where

T.task_id=l.task_id and t.advisorship nameplate ADDM' and l.statussubmission)

Select task_id,task_name,description from dba_advisor_tasks order by 1 desc

Select dbms_advisor.get_task_report (task_name) from dba_advisor_tasks where task_id = XX

Query the execution plan of a SQL

Select * from table (dbms_xplan.display_cursor ('sql_id',0,'advanced'))

The above 0 indicates that v$sql.child_number is 0. If a sql_id has multiple lines in v$sql indicating that there are multiple child_number, write the value of where you want to see the execution plan of child_number. For example, to see the execution plan of child_number 2, change the 0 of the above sql to 2.

In the description of the function display_cursor in the official document, there is no advanced parameter value, only BASIC, TYPICAL and ALL, but in practice, it is found that the parameter value of advanced shows more content than these parameter values.

With sequence.

Select * from table (xplan.display_cursor ('vaccounsql.sqlpromoidprecinct 0preceded'))

However, create the xplan package before executing

SQL > CREATE PUBLIC SYNONYM XPLAN FOR SYS.XPLAN

SQL > grant execute on sys.xplan to public

Query the configuration information of Rman

SELECT NAME,VALUE FROM V$RMAN_CONFIGURATION

Query Rman backup set details (unexpired, expired and deleted cannot be found)

SELECT B.RECID BackupSet_ID

A.SET_STAMP

DECODE (B.INCREMENTAL_LEVEL

'', DECODE (BACKUP_TYPE, 'Archivelog',' Full')

1, 'Incr-1'

0, 'Incr-0 level'

B.INCREMENTAL_LEVEL)

"Type LV"

B.CONTROLFILE_INCLUDED "contains CTL"

DECODE (A.STATUS

'All, 'AVAILABLE'

'During, 'DELETED'

'Xantha, 'EXPIRED'

'ERROR')

"STATUS"

A.DEVICE_TYPE "Device Type"

A.START_TIME "Start Time"

A.COMPLETION_TIME "Completion Time"

A.ELAPSED_SECONDS "Elapsed Seconds"

A.BYTES/1024/1024/1024 "Size (G)"

A.COMPRESSED

A.TAG "Tag"

A.HANDLE "Path"

FROM GV$BACKUP_PIECE A, GV$BACKUP_SET B

WHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO'

ORDER BY A.COMPLETION_TIME DESC

Query the progress of Rman backup

SELECT SID, SERIAL#, opname,ROUND (SOFAR/TOTALWORK*100) | |'%'"% _ COMPLETE'

TRUNC (elapsed_seconds/60) | |':'| | MOD (elapsed_seconds,60) elapsed

TRUNC (time_remaining/60) | |':'| | MOD (time_remaining,60) remaining

CONTEXT,target,SOFAR, TOTALWORK

FROM V$SESSION_LONGOPS

WHERE OPNAME LIKE 'RMAN%'

AND OPNAME NOT LIKE'% aggregate%'

AND TOTALWORK! = 0

AND SOFAR TOTALWORK

Query the SQL_ID and sql_fulltext of sql statements that have performed a full table scan

Select s.sid,s.serial#,s.inst_id,s.sql_id,s.username,s.target,s.ELAPSED_SECONDS,s.START_TIME,s.LAST_UPDATE_TIME,v.sql_fulltext

From gv$session_longops spen gvendsql v

Where s.OPNAME = 'Table Scan'

And s.SQL_PLAN_OPERATION = 'TABLE ACCESS'

And s.SQL_PLAN_OPTIONS = 'FULL'

And s.sql_id=v.sql_id

Order by s.LAST_UPDATE_TIME desc

Query how long it takes for dead transactions to roll back

X$KTUXE: [K] ernel [T] ransaction [U] ndo Transa [x] tion [E] ntry (table)

One of the important functions of the X$KTUXE table is that it can obtain the dead transaction information that can not be observed through v$transaction. When a database is interrupted abnormally or the transaction recovery is delayed, after the database is started, the transaction information cannot be observed through V$TRANSACTION, but X$KTUXE can help us to obtain this information. The KTUXECFL in this table represents the Flag tag of the transaction, through which you can find those Dead transactions:

SQL > select distinct KTUXECFL,count (*) from x$ktuxe group by KTUXECFL

KTUXECFL COUNT (*)

-

DEAD 1

NONE 2393

KTUXESIZ is used to record the number of rollback segments used by the transaction. You can evaluate the recovery progress by observing this field. For example, the following transaction rollback takes about 3 hours:

SQL > select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ from x$ktuxe where KTUXECFL = 'DEAD'

ADDR KTUXEUSN KTUXESLT KTUXESQN KTUXESIZ

--

FFFFFFFF7D07B91C 10 39 2567412 1086075

SQL > select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ from x$ktuxe where KTUXECFL = 'DEAD'

ADDR KTUXEUSN KTUXESLT KTUXESQN KTUXESIZ

--

FFFFFFFF7D07B91C 10 39 2567412 1086067

SQL > declare

L_start number

L_end number

Begin

Select ktuxesiz into l_start from x$ktuxe where KTUXEUSN=10 and KTUXESLT=39

Dbms_lock.sleep (60)

Select ktuxesiz into l_end from x$ktuxe where KTUXEUSN=10 and KTUXESLT=39

Dbms_output.put_line ('time_H:' | | round (l_end/ (l_start-l_end) / 60J2)

End

/

Time_H:3

Assign some YYY tables below XXX users to user,XXX\ YYY in uppercase

Set serveroutput on

-- XXX should be capitalized

Declare tablename varchar2 (200)

Begin

For x IN (SELECT * FROM dba_tables where owner='XXX' and table_name like'% YYY%') loop

Tablename:=x.table_name

Dbms_output.put_line ('GRANT SELECT ON XXX.' | | tablename | |' to user')

EXECUTE IMMEDIATE 'GRANT SELECT ON XXX.' | | tablename | |' TO user'

End loop

End

Oracle finds out all the system and object permissions that a user has

System permissions (consistent with the results of users querying select * from session_privs)

SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'user name'

UNION ALL

SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE IN

(SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'user name')

Object permissions (consistent with the result of users querying select * FROM TABLE_PRIVILEGES where GRANTEE=' current user'by themselves)

SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'user name'

UNION ALL

SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE IN

(SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'user name')

Query the roles owned by a user

Select * from dba_role_privs where GRANTEE=' username'

Query users with DBA role permissions

Select * from dba_role_privs where GRANTED_ROLE='DBA'

Query the system permissions that a role has

Select * from ROLE_SYS_PRIVS where role=' role name'

Clear the execution plan for a SQL

Exec DBMS_SHARED_POOL.PURGE ('vascsqlarea.ADDRESSMagi vSecretsqlarea.HASHANGVALUE')

Query whether the password has an expiration limit. The default is 180 days, which is generally changed to unlimited.

Select * from dba_profiles where profile='DEFAULT' and RESOURCE_NAME like 'PASSWORD%'

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED

Query and modify implicit parameters (must be operated under sysdba permissions)

Select a.ksppinm name, b.ksppstvl value, a.ksppdesc description

From x$ksppi a, x$ksppcv b

Where a.indx = b.indx and a.ksppinm like'% _ small_table_threshold%'

Alter system set "_ small_table_threshold" = value scope=both sid='*'

If you don't add sid, you can modify it in all instances of RAC by default.

It should be noted that double quotation marks must be added. In addition, there can be no spaces in the quotation marks, only the name of the parameter can be included.

Evaluate how much PGA should be set

Select PGA_TARGET_FOR_ESTIMATE from (select * from V$PGA_TARGET_ADVICE where ESTD_OVERALLOC_COUNT=0 order by 1) where rownum=1

Evaluate how much SGA should be set

Select SGA_SIZE from (select * from V$SGA_TARGET_ADVICE where ESTD_DB_TIME_FACTOR=1 order by 1) where rownum=1

See how much shared pool is left.

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

Count the capacity of all tables (including partition fields, LOB fields)

Usually execute select distinct SEGMENT_TYPE from dba_segments where owner'SYS' and tablespace_name'SYSAUX' first to view all the segment_type

Generally speaking, the following SQL is sufficient

SELECT

Owner,table_name, TRUNC (sum (bytes) / 1024 plus 1024) Meg

FROM

(SELECT segment_name table_name, owner, bytes

FROM dba_segments

WHERE segment_type = 'TABLE'

UNION ALL

SELECT s.segment_name table_name, pt.owner, s.bytes

FROM dba_segments s, dba_part_tables pt

WHERE s.segment_name = pt.table_name

AND s.owner = pt.owner

AND s.segment_type = 'TABLE PARTITION'

UNION ALL

SELECT i.table_name, i.owner, s.bytes

FROM dba_indexes i, dba_segments s

WHERE s.segment_name = i.index_name

AND s.owner = i.owner

AND s.segment_type = 'INDEX'

UNION ALL

SELECT pi.table_name, pi.owner, s.bytes

FROM dba_part_indexes pi, dba_segments s

WHERE s.segment_name = pi.index_name

AND s.owner = pi.owner

AND s.segment_type = 'INDEX PARTITION'

UNION ALL

SELECT l.table_name, l.owner, s.bytes

FROM dba_lobs l, dba_segments s

WHERE s.segment_name = l.segment_name

AND s.owner = l.owner

AND s.segment_type = 'LOBSEGMENT'

UNION ALL

SELECT l.table_name, l.owner, s.bytes

FROM dba_lobs l, dba_segments s

WHERE s.segment_name = l.index_name

AND s.owner = l.owner

AND s.segment_type = 'LOBINDEX'

Union all

SELECT l.table_name, l.owner, s.bytes

FROM dba_lobs l, dba_segments s

WHERE s.segment_name = l.segment_name

AND s.owner = l.owner

AND s.segment_type = 'LOB PARTITION'

)

GROUP BY owner,table_name

HAVING SUM (bytes) / 1024 Compact 1024 > 10

ORDER BY SUM (bytes) desc

View the SID of the current session

Select * from V$MYSTAT where rownumselect value from v$dataguard_stats where name='apply lag'

Or

Library sqlplus > select ceil ((sysdate-next_time) * 24060) "M" from v$archived_log where applied='YES' AND SEQUENCE#= (SELECT MAX (SEQUENCE#) FROM V$ARCHIVED_LOG WHERE applied='YES')

Check whether a package or stored procedure is being called. If there is a result below, it cannot be compiled at this time, otherwise it will be locked.

Select * from V$DB_OBJECT_CACHE where pin > 0 and name=upper ('XX')

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.