|
|
 |
|
|
|
|
|
|
|
SHORTCUTS |
|
|
|
 |
|
|
| List of shortcuts available in Smenu
environment |
|
|
| It is my intention
to add all shortctuct with examples here, but it is a tedious work as
there are around 150 of them and many have tens of options, so
please keep patient for the full list |
|
|
| Here is a sample
of some of the most used shortcuts in Smenu. Each of them is
illustrated with an example. There are more shortcuts, many of them for
specialized area like Replication or rman. For a complete list of
shortcuts, type at the Unix prompt 'spm'
|
|
Redo logs
& Archived logs
|
|
| rdl |
Show Redo log |
| apl |
Show aplied logs |
Undo
|
|
| rlbs |
Show rollback(undo) stats,
transactions, status, usage
|
tx
|
Show transactions,
incoming, outgoing, in doubt distributed transactions, purge them
|
User management
|
|
| ros |
List system privilege for
a user |
| quota |
List user quotas on
tablespaces |
| dus |
User space usage |
aud
|
Audit user objects, last
connection, active statement
|
Sessions
|
|
| sa |
Single Session overiew |
| sl |
Session list |
| sla |
Object accessed by session
with event associcated |
| slh |
Session hits figures |
sstv
|
Set trace in session
|
slf
|
Show failed over sessions
|
ssp
|
suspend user session
(using oradebug)
|
SQL
|
|
| st |
Display sql text for a
fivent hash value |
soc
|
view open cursor in system
|
| sq |
Dispaly sql figures from
sqlarea |
src
|
Show source code
(pkg/func/proc/views)
|
spx
|
Show parallel query slave
|
slh
|
Show sql figures related
to v$sql
|
SQL WORKA REA
|
|
| slo |
Show long ops |
| lom |
Large object in memory |
| sw |
Show sql workarea values |
bufs
|
Average number of buffers
to scan at the end of the LRU, to find a free buffer
|
bufg
|
Report distribution in DB
buffer per type
|
bufk
|
Report Objects that could
be pinned in mem
|
sfl
|
Shared pool free list
|
sga
|
Shared mem usage
|
sls
|
Show system events
|
sle
|
Show session events
|
|
|
Tablespace and datafiles
|
|
| frg |
Tablespace list and Free
frags |
| lstd |
List data files
attributes, generate statement to shink datafiles
|
| fsi |
Show tablespace default
values |
Table & indexes
|
|
tbl
-h
|
Show tbl help
|
| tbl -p |
Show table info
(partitioned) |
| tbl -c |
Show table constraints |
| tbl -s |
Show table columns stats
|
| tbl -ext |
Show external |
| idx -t |
Show all indexes for table |
| idx -u |
Show user indexes |
| idx -i |
Show info on a specific
index |
| sta |
Everything about statistics
|
Waits & locks
|
|
spl start
|
Start sample wait state |
spl
|
Query sample of Oracle
wait interface (special page)
|
| law |
Report latch current
activity |
| las |
Latch sleeps |
| lat |
Latch lists |
database info
|
|
cpu
|
Response time breakdown
|
| dblk |
List db link |
| cpt |
Fast list of all objects
count in db per user
|
| parg |
Display dataguard
parameters |
| memu |
Memory usage |
| wab |
How background process
activity |
| up |
Db uptime |
sts
|
show archive status
|
Misc
|
|
| vsh |
Edit the text of behind a
shorcut |
sstp
|
Run statpack
|
sstl
|
Show statpack
snapshots
|
| vsl |
List all shortcuts (or
only shortcuts starting from a letter 'vsl n') |
| dgs |
Show dataguard status |
Top
|
|
| Reporting on the
Oracle Wait Interface (OWI). |
Smenu can start a monitor process over the v$session_wait and some associated views in
order to determine what is happening on the DB. You can initiate a sampling of the Oracle
Wait interface using Smenu 'spl' command. Later you can query these data using 'owi'.
Once the sampler is active, it is easy to get an overwview of the DB activity from the dumped history.
The couple 'spl' and 'owi' is a pratical and extremly fast application of the Oracle Wait Interface
to determine what the database is doing. You will be able to say who impacted the DB, the SQL text,
the weight of the impact and the duration of this impact. this is an essential for DBA that want
to tune a production DB.
|
|
|
|
|
befsam18:/export/TAR/oracle/scripts/smenu/tmp> spl -t -hv 581503425
Using default SAMPLE_WORK_DIR
Using default SPL
MACHINE - ORACLE_SID : CUSTPRD Page: 1
Date - Thursday 17th November 2005 16:51:00 Username - SYS
Report - Top 50 events for CUSTPRD.17111051 nline
. Type 'owi' for help Displaying 50 rows
Total number of entries in waits file : 20942
Sample
Nbr Wait Event name SQL_HASH_VALUE WAIT_TIME Perc Object name
--------- ------------------------------ -------------- ---------- ------- -----------------------------
2132 latch free 581503425 16677 10.18 CUSTOWNER.DELIVERY_POINT
2033 latch free 581503425 15520 9.71 CUSTOWNER.PSS_STR_FK_I
1003 latch free 581503425 10971 4.79
526 latch free 581503425 3754 2.51 CUSTOWNER.POSTAL_STREET_SEGMENT
340 db file sequential read 581503425 2549 1.62 CUSTOWNER.PSS_STR_FK_I
147 db file sequential read 581503425 644 0.70 CUSTOWNER.POSTAL_STREET_SEGMENT
136 db file sequential read 581503425 1177 0.65 CUSTOWNER.DELIVERY_POINT
113 latch free 581503425 1213 0.54 CUSTOWNER.DELIVERY_SEGMENT
72 latch free 581503425 668 0.34 CUSTOWNER.STREET
28 db file sequential read 581503425 94 0.13
20 db file sequential read 581503425 65 0.10 CUSTOWNER.DELIVERY_SEGMENT
20 db file sequential read 581503425 189 0.10 CUSTOWNER.PDP_LOGISTIC_PLATF
19 db file sequential read 581503425 114 0.09 CUSTOWNER.STREET
10 latch free 581503425 17 0.05 CUSTOWNER.PDP_LOGISTIC_PLATF
3 db file sequential read 581503425 4 0.01 CUSTOWNER.DELIVERY_SEGMENT_PK
There are multiple way to queries the dumped files.
[befsam18:POLDEV]:/opt/smenu/tmp> spl -t2
MACHINE - ORACLE_SID : POLDEV Page: 1
Date - Tuesday 15th November 2005 14:17:38 Username - SYS
Report - Top 10 events for POLDEV.14154553 nline
. Type 'owi' for help
Total number of entries in file : 2801
Sample
NBR_WAIT Event name SQL_HASH_VALUE P1 P2 WAIT_TIME Perc
---------- ------------------------------ -------------- ---------- ---------- ---------- -------
901 SQL*Net more data to client 754619862 1952673792 1988 1453460 32.17
871 wakeup time manager 870116171 0 0 12831 31.10
73 jobq slave wait 3261540815 0 0 2681 2.61
70 jobq slave wait 656697148 0 0 2432 2.50
64 jobq slave wait 857248648 0 0 2123 2.28
26 control file parallel write 0 3 3 23 0.93
12 db file parallel write 0 2 0 19 0.43
9 jobq slave wait 980021054 0 0 549 0.32
6 db file parallel write 0 3 0 9 0.21
5 db file parallel write 0 1 0 6 0.18
Get the delta of v$sql per minute, so you can determine the peaks, and check if the peaks time
correspond to the know slowness. Keep in mind that a sampler can run without problems for hours.
befsam18:/export/TAR/oracle/scripts/smenu/tmp> spl -de 2751746987
Using default SAMPLE_WORK_DIR
Using default SPL
MACHINE - ORACLE_SID : CUSTPRD Page: 1
Date - Thursday 17th November 2005 16:59:50 Username - SYS
Report - List Delta for 2751746987 nline
. Type 'owi' for help Displaying 10 rows
Start date : 17/11/2005 11:10:53 Total entries in delta file : 148623
End date : 17/11/2005 12:53:53 SQL_HASH_VALUE : 2751746987
. ROWS
TIME PROCESSED DISK_READS EXECUTIONS BUFFER_GETS SORTS CPU_TIME
-------- ------------ ---------- ---------- ----------- ---------- ---------
11:10:53 186703128 25555 286168 462793398 0 32903.21
--
11:11:56 68646 0 95 160371 0 11.06
11:12:58 62586 0 91 149745 0 10.74
11:14:01 60066 0 93 150580 0 11.36
11:15:04 52664 0 86 135662 0 10.33
11:16:06 57973 0 98 151940 0 11.46
11:17:09 59748 0 101 157203 0 11.96
11:18:11 63264 0 91 151129 0 10.67
11:19:14 58259 0 103 157453 0 12.12
11:20:17 67281 0 102 165716 0 12.03
11:21:19 62722 0 85 145150 0 10.5
11:22:22 66739 0 83 147140 0 9.91
11:23:24 36616 0 70 103782 0 8.24
11:24:26 64711 0 85 147049 0 10.59
Retrieve now the text of the sql_hash_value 2751746987
s05143:/export/TAR/oracle/scripts/smenu/tmp> spl -hv 2751746987
Using default SPL
SELECT STN.STR_DIST_ID DIST_ID,
STN.DIST_NAME SCUST_DIST_NAME,
STN.DIST_LANGUAGE SCUST_DIST_LANGUAGE,
STN.DIST_NAME_NORM,
SCUST_DIST_NAME_NORM
FROM DIST_NAME STN JOIN DIST STR ON STR.DIST_ID = STN.STR_DIST_ID
WHERE STR.ACTIVE = 'Y' AND STR.AAR_ADMIN_AREA_ID = :B1
|
|
| Top |
|
| Starting Smenu
sampler to grasp the wait states: |
'spl' stand for 'sampler'. It is a set of scripts issued to exploit the Oracle wait interface.
'spl' generate a sampling process to download in an ascii files, all waits event from
v$session_wait every second. It also generate an file will all sql text during the sampling
duration. Also the figures for the sql from v$sql are also downloaded.
These files are queried through external tables (dba_external_locations) whose definition
is contained in SBIN/SELECT. This last script also guarantee that the correct file is associted
with the correct table definition.
For instance : SELECT "count(1) from sample_sql_w_SID.date" where 'sample_sql_w_SID.date' is
an ascii file will create a external table definition into the DB and link it with the file.
This allow the use of SQL over ascii files. All our files are field '|' separated.
There is an interface 'owi', that contains predefined queries to allow queries the files.
For instance, owi -def -t will return the 10 top wait event
-------------------------------------------------------------
Date : 15/11-13:53 Host : befsam18
Oracle SID : POLDEV menu : sm/3.1
Last Selection :
*************************************************************
* *
* Sample Oracle waits interface *
* *
*************************************************************
Sample file out dir : /export/TAR/oracle/scripts/smenu/tmp
duration of sample : 900 (-1 => infinite)
Interval wait state : (s) 1
Dump sql test every : (s) 60
Dump SQL Fig every : (s) 180 (-1 => no delta)
Execution immediate : YES
Sample:
--------
1 : start sampler all wait state
2 : start sampler for sql with wait state
3 : stop samplers for this instance
Define values
-------------
d ) change sample work dir
l ) change sample duration
i ) change interval wait state
q ) change interval sql dump
n ) change interval delta stat dump
x ) toggle execution immediate
s ) Save this setting
e ) exit
Your choice :
The sampler are extremly easy to start :
[s05096:POLDEV]:/export/TAR/oracle/scripts/smenu/tmp> spl start
/export/TAR/oracle/scripts/smenu/tmp/sampler_sql_w_POLDEV.ksh
Sampler started for waits and sql text
[s05096:POLDEV]:/export/TAR/oracle/scripts/smenu/tmp> Sending output to nohup.out
[s05096:POLDEV]:/export/TAR/oracle/scripts/smenu/tmp> ls -lt
total 40648
-rw-r--r-- 1 oracle dba 1170 Nov 17 2005 nohup.out
-rw-r--r-- 1 oracle dba 42432 Nov 17 2005 sample_delta_w_POLDEV.17170402
-rw-r--r-- 1 oracle dba 118 Nov 17 2005 sample_sql_w_POLDEV.17170402
-rw-r--r-- 1 oracle dba 13107 Nov 17 2005 sample_sys_w_POLDEV.17170402
-rw-r--r-- 1 oracle dba 367019 Nov 17 2005 sample_txt_w_POLDEV.17170402
Here the files generated
[befsam18:POLDEV]:/export/TAR/oracle/scripts/smenu/tmp> more sample_sql_w_POLDEV.17170402
20051117170406|8|29|373|SQL*Net more data from client|-1|2|1650815232|driver id|39|#bytes|....
20051117170452|4|9494|143|control file parallel write|0|0|2|files|2|blocks|2|requests|39785....
.
.
and to stop ...
[befsam18:POLDEV]:/export/TAR/oracle/scripts/smenu/tmp> spl stop
|
|
| Top |
|
| Show background
process activity |
[befsam18:POLDEV]:/export/TAR/oracle/scripts/smenu/scripts> wab
MACHINE befsam18 - ORACLE_SID : POLDEV Page: 1
Date - Tuesday 15th November 2005 12:39:39
Username - SYS
System-wide Wait Analysis Detail Analysis for current wait events nline
Time Avg Avg Max
System Event Total Waited Wait Wait Wait
ID Prcs Name Waits (in secs) secs secs (in secs)
------ ---------- ------------------------------ ------------ ------------ ------ ------ ---------
3 LGWR log file parallel write 5,949,677 22,552 ###### 0 4
58 CIA_OWNER db file sequential read 4,444,436 5,025 0 0 7
4 CKPT direct path read 3,991,782 1,908 0 0 14
4 CKPT direct path write 776,114 3,705 0 0 31
23 CIA_USER db file sequential read 489,196 336 0 0 4
4 CKPT control file parallel write 406,518 30,819 0 0 14
9 ARC0 async disk IO 393,527 2,745 0 0 5
10 ARC1 async disk IO 393,360 2,675 0 0 5
4 CKPT control file sequential read 341,120 146 0 0 4
2 DBW0 db file parallel write 333,079 56,251 0 0 41
48 CIA_USER db file sequential read 311,422 366 0 0 4
88 CIA_USER db file sequential read 293,608 220 0 0 4
88 CIA_USER buffer busy waits 249,424 157 7 0 1
48 CIA_USER buffer busy waits 241,699 154 11 0 1
61 CITS_USER db file sequential read 215,511 266 0 0 4
9 ARC0 log file sequential read 205,939 3,927 0 0 4
10 ARC1 log file sequential read 205,855 3,872 0 0 4
3 LGWR LGWR wait for redo copy 191,702 31 229 0 0
|
|
| Top |
| Show external
tables: |
This option
of the 'tbl' list all external references in
the DB.
You can pick one the tables
and add '-del'
and the reference to external table is dropped. |
[befsam18:POLDEV]:/export/TAR/oracle/scripts/smenu/scripts> tbl -ext
MACHINE befsam18 - ORACLE_SID : POLDEV Page: 1
Date - Tuesday 15th November 2005 12:36:15
Username - SYS
List external table(s)
Owner
Table name DIRECTORY_NAME File path
---------------------------- ------------------ -------------------------------------------------------------------
CUST_OWNER.OBJ_REFER_CDB_DEL EXT_CDB_DATA_DIR /custs/loader/data/cdb/CDB_DELTA.BPZ
CUST_OWNER.OBJ_VAR_DELT EXT_CDB_DATA_DIR /custs/loader/data/cdb/DELTA.PAR
CUST_OWNER.OBJ_CUSTPHONE_EXT EXT_CUSTPHONE_DATA /custs/loader/data/postphone/CUSTPHONE_EXT_REQ.DAT
SYS.SAMPLE_SQL_W SAMPLER_W_DIR /export/TAR/oracle/scripts/smenu/tmp/sample_sql_w_CUSTPRD.18135711
SYS.SAMPLE_TXT_W SAMPLER_W_DIR /export/TAR/oracle/scripts/smenu/tmp/sample_txt_w_CUSTPRD.18135711
SYS.SAMPLE_DELTA_W SAMPLER_W_DIR /export/TAR/oracle/scripts/smenu/tmp/sample_delta_w_CUSTPRD.16174144
|
|
| Top |
|
| Show table columns
stats |
[befsam18:POLDEV]:/export/TAR/oracle/scripts/smenu/scripts> tbl -t cust_operation -s
MACHINE befsam18 - ORACLE_SID : POLDEV Page: 1
Date - Tuesday 15th November 2005 12:35:11
Username - SYS
Show table/col stats
. -p (sub)partitions -a Chained rows -n <xxx> Tables name like %xxx%
. -u limit to owner -c List constraints -s list col stats -w list size
. tbl -h for extended help
. use : idx -t <table_name> to list associated indexes
Num Col
COLUMN_NAME NUM_DISTINCT DENSITY GLO NUM_NULLS Bucket Len Last Analysed
------------------------------ ------------ ------------- --- ---------- ------- ---- ------------------
PID 168495420 .000000006 YES 0 2 7 12-12-04 17:21:29
REC_CD 2 .500000000 YES 0 2 2 12-12-04 17:21:29
SEQ_NR 1462216 .000000684 YES 0 2 6 12-12-04 17:21:29
CUST_CD_PRODUCT 39 .025641026 YES 0 2 3 12-12-04 17:21:29
CUST_CD_SUBPRODUCT 70 .014285714 YES 0 2 3 12-12-04 17:21:29
CUST_CD_COMM_STRUCT 3 .333333333 YES 0 2 2 12-12-04 17:21:29
CUST_CD_DEBIT_CREDIT 2 .500000000 YES 0 2 2 12-12-04 17:21:29
CUST_DT 234 .004273504 YES 0 2 8 12-12-04 17:21:29
MIR_DT 161 .006211180 YES 0 2 8 12-12-04 17:21:29
VALUE_DT 258 .003875969 YES 0 2 8 12-12-04 17:21:29
LOCALE_REF_NR 8 .125000000 YES 0 2 4 12-12-04 17:21:29
COMM_STRUCT 8882438 .000000113 YES 0 2 5 12-12-04 17:21:29
CALCED_AMOUNT 470540 .000002125 YES 0 2 5 12-12-04 17:21:29
ORIG_AMOUNT 471380 .000002121 YES 0 2 5 12-12-04 17:21:29
ORIG_AMOUNT_CURRENCY 2 .500000000 YES 0 2 4 12-12-04 17:21:29
|
|
| Top |
|
| Show table size
and rows (partionned) |
[befsam18:POLDEV]:/export/TAR/oracle/scripts/smenu/scripts> tbl -t cust_operation -p
. -p (sub)partitions -a Chained rows -n <xxx> Tables name like %xxx%
. -u limit to owner -c List constraints -s list col stats -w list size
. tbl -h for extended help
. use : idx -t <table_name> to list associated indexes
MACHINE befsam18 - ORACLE_SID : POLDEV
Page: 1
Date - Tuesday 15th November 2005 12:31:42
Username - SYS Show partitions for table: CUST_OPERATION
The '+' signal aggregate stats : means stats derived from others stats
Part Partition Subpart
Pos Partition name Num rows Subartition name Tablespace name Num rows Last Analyzed
----- ----------------- ------------ ------------------- -------------------- --------- -----------------
1 P2004Q3 72690730 P2004Q3_AD CUST_D_DEP4_Q3 37738052 16-10-05 10:03:03
P2004Q3_AR CUST_D_DEP4_Q3 19788244 16-10-05 10:57:23
2 P_DEP4_09 +27659618 P_DEP4_09_AD CUST_D_DEP4_Q3 18593170 16-10-05 11:50:41
+27659618 P_DEP4_09_AR CUST_D_DEP4_Q3 9066448 16-10-05 12:17:05
3 P_DEP4_10 +29183679 P_DEP4_10_AD CUST_D_DEP4_Q4 19149279 16-10-05 13:09:41
P_DEP4_10_AR CUST_D_DEP4_Q4 10034400 16-10-05 13:39:04
4 P_DEP4_11 +27393862 P_DEP4_11_AD CUST_D_DEP4_Q4 19048196 16-10-05 14:32:23
P_DEP4_11_AR CUST_D_DEP4_Q4 8345666 16-10-05 14:57:06
5 P_DEP4_12 +31347808 P_DEP4_12_AD CUST_D_DEP4_Q4 21638292 16-10-05 15:57:50
P_DEP4_12_AR CUST_D_DEP4_Q4 9709516 16-10-05 16:26:40
6 P_DEP5_01 +29060245 P_DEP5_01_AD CUST_D_DEP5_Q1 20054330 16-10-05 17:22:08
P_DEP5_01_AR CUST_D_DEP5_Q1 9005915 16-10-05 17:49:02
7 P_DEP5_02 +26362484 P_DEP5_02_AD CUST_D_DEP5_Q1 18297265 16-10-05 18:40:10
P_DEP5_02_AR CUST_D_DEP5_Q1 8065219 16-10-05 19:04:10
8 P_DEP5_03 +29026298 P_DEP5_03_AD CUST_D_DEP5_Q1 20024209 16-10-05 19:59:14
P_DEP5_03_AR CUST_D_DEP5_Q1 9002089 16-10-05 20:29:12
9 P_DEP5_04 +29753910 P_DEP5_04_AD CUST_D_DEP5_Q2 19171723 16-10-05 21:51:13
P_DEP5_04_AR CUST_D_DEP5_Q2 10582187 16-10-05 22:22:12
10 P_DEP5_05 +29632590 P_DEP5_05_AD CUST_D_DEP5_Q2 19651923 16-10-05 23:20:39
P_DEP5_05_AR CUST_D_DEP5_Q2 9980667 16-10-05 23:52:43
11 P_DEP5_06 +29883970 P_DEP5_06_AD CUST_D_DEP5_Q2 19773040 23-10-05 19:21:54
P_DEP5_06_AR CUST_D_DEP5_Q2 10110930 24-10-05 00:24:46
|
|
| Top |
Show
'tbl' help
|
[asdb:/opt/smenu/scripts]> tbl -h
tbl -t <table_name> -p -d
tbl -t <table_name> -c -d
tbl -t <table_name> -a -d
tbl -t <table_name> -s
tbl -n <table_name> (Add a % before, after or at both end) to the
part name
tbl -n Mytab%
tbl -u <owner> -t <table_name>
tbl -ext
tbl -ext <table_name> -drop
-a Chained rows
-c Constraints
-d Table description
-ext query external table
without a table : list all
with table : list this table, drop it if
-drop is added
-drop drop external table
-n <xxx> Tables name like %xxx%
-p (sub)partitions
-s List stats info gathered on columns
-u limit to owner
Also you can use : 'idx -t <table_name>' to list associated
indexes. It is intentional
that -drop only access external table. If you want to drop a table,
connect into db.
The time it takes gives you a chance to realize what you do.
|
|
|
|
|
|
|
|
|
| Show table
constraint: |
[befsam18:POLDEV]:/export/TAR/oracle/scripts/smenu/scripts> tbl -t cust_operation -c
MACHINE befsam18 - ORACLE_SID : POLDEV Page: 1
Date - Tuesday 15th November 2005 12:28:47
Username - SYS
List Constraints which point to the Table CUST_OPERATION
. -p (sub)partitions -a Chained rows -n <xxx> Tables name like %xxx%
. -u limit to owner -c List constraints -s list col stats -w list size
. tbl -h for extended help
. use : idx -t <table_name> to list associated indexes
. S= Status T= Contraint Type : R=Reference, C=check
Type of
CONSTRAINT_NAME Constraint Col or Condition Pos Status
------------------------------ ---------- ---------------------------------------- ---- ----------
PK_CUST_OPERATION PRIMARY PID 1 DISABLED
SYS_C0071325 Check "PID" IS NOT NULL 1 ENABLED
SYS_C0071326 Check "CUST_CD_PRODUCT" IS NOT NULL 1 ENABLED
SYS_C0071327 Check "CUST_CD_SUBPRODUCT" IS NOT NULL 1 ENABLED
SYS_C0071328 Check "CUST_CD_COMM_STRUCT" IS NOT NULL 1 ENABLED
SYS_C0071329 Check "CUST_CD_DEBIT_CREDIT" IS NOT NULL 1 ENABLED
Remote Remote Delete
Foreign Keys: Table Columns Table.Colmuns Constraint name Pos S Rule
------------------------ ---------------------- ------------------------------ ---------------------- ---- - ------
(FK_CUST_OPERATION_LOAD CUST_OPERATION.LOADER -> LOADER_LOG_CUST.PID (PK_LOADER_LOG) 1 D
ER_LOG) _LOG_PID
(FK_CUST_OPERATION_LOAD CUST_OPERATION.LOADER -> LOADER_LOG.PID (PK_LOADER_LOG) 1 D
ER_LOG) _LOG_PID
(FK_CUST_FACS_LNKRDF_PAR CUST_OPERATION.LNKRDF -> CUST_PARTY.PID (CUST_PARTY) 1 D
TY) _PID
(FK_CUST_FACS_OWNER_PAR CUST_OPERATION.OWNER (CUST_PARTY) 1 D
TY) _PID
'-----------------------------'
. Who reference 'CUST_OPERATION' ?
'-----------------------------'
Remote Local
Constraint name Table.Colmuns
------------------------------------------------------------- --------------------------------------------------------
[POL_OWNER].CUST_COMM.PID (FK_FACS_COMM) --> [POL_OWNER].CUST_OPERATION.PID (PK_CUST_OPERATION)
[POL_OWNER].CUST_ASSIGNMENT.PID (FK_CUST_ASSIGNMENT_FACSACTI --> [POL_OWNER].CUST_OPERATION.PID (PK_CUST_OPERATION)
[POL_OWNER].CUST_USESYS2.PID (FK_CUST_USESYS2_OPERATION) --> [POL_OWNER].CUST_OPERATION.PID (PK_CUST_OPERATION)
[POL_OWNER].CUST_USESYS4.PID (FK_CUST_USESYS4_OPERATION) --> [POL_OWNER].CUST_OPERATION.PID (PK_CUST_OPERATION)
[POL_OWNER].CUST_USESYS6.PID (FK_CUST_USESYS6_OPERATION) --> [POL_OWNER].CUST_OPERATION.PID (PK_CUST_OPERATION)
[POL_OWNER].CUST_USESYS_FACSFER5.PID (FK_CUST_USESYS_FACS --> [POL_OWNER].CUST_OPERATION.PID (PK_CUST_OPERATION)
[POL_OWNER].CUST_USESYS_OLTB.PID (FK_CUST_USESYS_OLTB_FACS --> [POL_OWNER].CUST_OPERATION.PID (PK_CUST_OPERATION))
[POL_OWNER].CUST_USESYS8.PID (FK_CUST_USESYS8_OPERATION) --> [POL_OWNER].CUST_OPERATION.PID (PK_CUST_OPERATION)
[POL_OWNER].CUST_USESYS7.PID (FK_CUST_USESYS7_OPERATION) --> [POL_OWNER].CUST_OPERATION.PID (PK_CUST_OPERATION)
[POL_OWNER].CUST_INTEREST_SETTLEMENT.PID (FK_CUST_INTEREST_SE --> [POL_OWNER].CUST_OPERATION.PID (PK_CUST_OPERATION)
TTLEM_FACS)
|
|
| Top |
|
| Show long
operations: |
[befsam18:POLTEST]:/u11/oradata/POLTEST> slo -f
MACHINE befsam18 - ORACLE_SID : POLTEST Page: 1
Date - Tuesday 15th November 2005 12:25:02
Username - SYS
Long SQL : display status
' -t by time loaded
' -r Limit number of lines -f show only longops not yet finished
' -x Limit list to <sid>'
Time SQL
Sid Message Start time remain Hash Value
---- ------------------------------------------------------------------------------------ -------------- ------ ------------
10 RMAN: full datafile restore: Set Count 3281: 4390442 out of 27140480 Blocks done 15/11 08:11:43 65544 4187928929
12 RMAN: aggregate input: restore 347: 4376448 out of 43499844 Blocks done 15/11 08:11:48 83453 2746682842
|
|
| Top |
|
| Edit the text
behind a shortcut : |
[befsam18:POLDEV]:/export/TAR/oracle/scripts/smenu/scripts> vsh up
Put you directly in vi mode in "$SBIN/smenu/module2/s1/smenu_uptime.sh"
# B. Polarski
# 23 May 2005
WK_SBIN=$SBIN/module2/s1
HOST=`hostname`
HOST=`echo $HOST | awk '{ printf ("%-+15.15s",$1) }'`
cd $WK_SBIN
# --------------------------------------------------------------------------
. $SBIN/scripts/passwd.env
. ${GET_PASSWD} $S_USER $ORACLE_SID
if [ "x-$CONNECT_STRING" = "x-" ];then
echo "could no get a the password of $S_USER"
exit 0
fi
# --------------------------------------------------------------------------
sqlplus -s "$CONNECT_STRING" <<EOF
ttitle skip 2 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '
column nline newline
set pagesize 66
set linesize 170
set termout on pause off
set embedded on
set verify off
set heading off
select 'Date - '||to_char(sysdate,'Day Ddth Month YYYY HH24:MI:SS'),
'Username - '||USER nline,
'DB uptime ' nline
from sys.dual
/
set head on
COL startup_time FORMAT A34 HEADING ' Startup time'
COL status FORMAT A10 heading 'Status'
SELECT ' ' || to_char(startup_time,'HH24:MI:SS YYYY-MON-DD') startup_time, status
from v\$instance
/
EOF
|
|
| Top |
|
| Show SQL area figures |
[befsam18:POLDEV]:/export/TAR/oracle/scripts/smenu/scripts> sw
MACHINE befsam18 - ORACLE_SID : POLDEV Page: 1
Date - Tuesday 15th November 2005 12:14:34
Username - SYS
Display sql work area values
Operation Total Active Sql plan Estimated Used
SID HASH_VALUE Type Executions Time Identifier Size Memory
---- ------------ ------------------------ ---------- ------- ----------- ----------- -----------
79 4241872393 SORT 0 0 2 73728 0
79 4241872393 SORT 0 0 4 19456 0
79 4241872393 SORT 0 0 8 73728 0
79 4241872393 SORT 0 0 6 73728 0
61 754619862 HASH-JOIN 917 0 1 643072 983040
61 754619862 HASH-JOIN 917 0 2 692224 777216
25 3331488749 SORT 54316 0 1 19456 16384
88 3331488749 SORT 54316 0 1 19456 16384
42 2863008485 SORT 1 0 1 73728 0
27 2683914826 IDX MAINTENANCE (SOR 76479 0 0 73728 0
78 1232104503 SORT 25981 0 1 19456 16384
11 rows selected.
You can couple sw and sx to get track SQL activity : for instance, image we are after a session 402
/opt/smenu> sw 402
MACHINE befsam18 - ORACLE_SID : POLDEV Page: 1
Date - Friday 28th April 2006 22:35:01
Username - SYS
Display sql work area values
Operation Total Active Sql plan Estimated Used
SID HASH_VALUE Type Executions Time Identifier Size Memory
---- ------------ ------------------------ ---------- ------- ----------- ----------- -----------
402 2089214424 IDX MAINTENANCE (SOR 0 0 0 73728 0
402 2089214424 HASH-JOIN 0 0 1 14175232 0
402 2089214424 HASH-JOIN 0 0 3 1371664384 0
402 2089214424 IDX MAINTENANCE (SOR 0 0 0 73728 0
402 2089214424 HASH-JOIN 0 0 1 14175232 0
402 2089214424 HASH-JOIN 0 0 3 1371664384 0
6 rows selected.
/opt/smenu> sx 2089214424
MACHINE befsam18 - ORACLE_SID : POLDEV Page: 1
Date - Friday 28th April 2006 22:35:39
Username - SYS
Show explain plan for query hash_value = 2089214424
Search
ID PARENT OPERATION COST CARDINALITY Cols OBJECT_NODE OBJECT_NAME
---- ------ ------------------------------ ---------- ----------- ------ ---------------- ------------------
0 INSERT STATEMENT 1854022 0
1 0 HASH JOIN RIGHT OUTER 1854022 3494780 0
2 1 TABLE ACCESS FULL 2716 214694 0 PURCHASING_ORDERS
3 1 HASH JOIN 1814967 3494780 0
4 3 PARTITION LIST SINGLE 1776563 3846741 0
5 4 TABLE ACCESS BY LOCAL INDE 1776563 3846741 0 CCA_LINE_ITEMS
6 5 INDEX RANGE SCAN 13983 3911212 1 CCALI_PK
7 3 INDEX FAST FULL SCAN 387 570397 0 DPO_PK
|
|
|
|
| Top |
Report Objects that
could be pinned in mem
|
/opt/smenu> bufk
MACHINE befsam18
- ORACLE_SID : POLDEV
Page: 1
Date
- Friday 28th April
2006 22:42:29
Username
- SYS
Report Objects that
could be pinned in mem
Type bufk <n>
to consider only tables with at least <n> buffer, default is 80
Sql statement
-------------------------------------------------------------------------------------------------------
alter TABLE
OPS$FIN.EXTRACTION_RUNS storage (buffer_pool keep);
|
|
|
|
|
|
|
|
|
| Top |
|
|
|
|
|
|
|
| Show text of sql: |
[befsam18:POLDEV]:/export/TAR/oracle/scripts/smenu/scripts> st 754619862
MACHINE befsam18 - ORACLE_SID : POLDEV Page: 1
Date - Tuesday 15th November 2005 12:14:44
Username - SYS
get sql text
. Type sq -hv 754619862 to see stats on this sql
SQL_TEXT
----------------------------------------------------------------
select this.PID as PID2_, this.DELETE_DT as DELETE_DT2_, this.LA
ST_SUCCESSFUL_LOGIN as LAST_SUC3_2_, this.TEAM_ID as TEAM_ID2_,
this.PROFILE_PID as PROFILE_5_2_, this.LOGINID as LOGINID2_, pro
file1_.PID as PID0_, profile1_.NAME as NAME0_, profile1_.LEADING
_CAPABILITY as LEADING_3_0_, profile1_.NAME_FR as NAME_FR0_, pro
file1_.NAME_NL as NAME_NL0_, team2_.PID as PID1_, team2_.LANGUAG
E_CD as LANGUAGE2_1_, team2_.NAME as NAME1_ from U_USER this lef
t outer join U_PROFILE profile1_ on this.PROFILE_PID=profile1_.P
ID left outer join U_TEAM team2_ on this.TEAM_ID=team2_.PID wher
e (this.TEAM_ID in (:1, :2, :3, :4, :5, :6, :7, :8, :9) and this
.DELETE_DT is null)
11 rows selected.
SID
----------
61
|
|
|
|
|
| Top |
|
| Show sql stats: |
[befsam18:POLDEV]:/export/TAR/oracle/scripts/smenu/scripts> sq -hv 754619862
MACHINE befsam18 - ORACLE_SID : POLDEV Page: 1
Date - Tuesday 15th November 2005 12:15:23
Username - SYS
Locating CPU-Heavy SQL with more than : Usefull figures
Sorts options : -e Executions -b Buffer_gets -d disk_reads
. -c by cpu -w Rows processed -s sorts -r Limit number of lines
. -t by time loaded -f fetches -o optimizer cost
. -x Limit list to active SQLactive, with -t, gives elapsed secs for the SQL'
. 'st <sql hash_value>' to get SQL text, -hv <SQL_HASH_VALUE>
. 'sx <sql hash_value>' to get explain plan
Sorts
Per Nbr Disk Buff Gets Rows per Optim cpu Last time SQL
Exec usr Fetches Execs load reads Per Exec Exec cost Time(s) Loaded Type HASH_VALUE
------ ---- --------- --------- ----- ---------- ---------- --------- -------- ------- ---------- ----- ----------
0 1 917 918 1 20286 147 74 23 9 14/11 08:0 6 754619862
|
|
|
|
|
| Top |
|
| Show rollback
Stats: |
[befsam18:POLDEV]:/export/TAR/oracle/scripts/smenu/scripts> rlbs
Date - Tuesday 15th November 2005 12:10:15
Username - SYS
Report - Rollback Segment size and Highwatermark
Type 'alter rollback segment <name> shrink;' to attempt reset size
Shrinks : Cumulative nbr of times Oracle has truncated exts from rollback segment
wrapped : Cumulative nbr of times a transaction continues writing from one extent
' in a rollback segment to another existing extent
Actv High Num Curr
Rollback On Trans Shrks Wrapd Watermark Ext Ext Actual Size
--------------- -- ------ ------- ------- -------------- ----- ----- --------------
SYSTEM Y 0 0 0 442,368 7 2 442,368
_SYSSMU1$ Y 0 122 2,044 3,869,360,128 185 60 3,869,360,128
_SYSSMU2$ Y 0 157 2,230 2,776,743,936 9 2 51,494,912
_SYSSMU3$ Y 0 74 1,531 310,493,184 13 6 85,049,344
_SYSSMU4$ Y 0 126 1,707 1,275,183,104 42 11 115,458,048
_SYSSMU5$ Y 0 175 2,150 3,447,832,576 125 118 965,853,184
_SYSSMU6$ Y 0 149 2,033 4,068,589,568 9 7 117,555,200
_SYSSMU7$ Y 0 171 2,195 4,227,973,120 27 6 209,829,888
_SYSSMU8$ Y 0 169 2,014 3,691,102,208 21 18 56,737,792
_SYSSMU9$ Y 0 155 1,956 4,289,839,104 20 19 209,829,888
_SYSSMU10$ Y 0 182 2,021 4,286,693,376 8 6 109,166,592
_SYSSMU11$ Y 0 6 249 243,318,784 51 2 93,569,024
_SYSSMU12$ Y 0 0 55 42,975,232 56 55 42,975,232
_SYSSMU13$ Y 0 3 53 38,780,928 28 1 27,377,664
_SYSSMU14$ Y 0 2 58 42,975,232 39 2 38,912,000
_SYSSMU15$ Y 0 0 52 39,829,504 53 52 39,829,504
|
|
|
|
|
| Top |
|
| Show redo logs: |
[befsam18:POLDEV]:/export/TAR/oracle/scripts/smenu/scripts>rdl
MACHINE befsam18 - ORACLE_SID : POLDEV Page: 1
Date - Tuesday 15th November 2005 12:08:55
Username - SYS List - Last Redo Logs
Size
Group Member Archived Status SEQUENCE# (MB)
----- --------------------------------------------- ---------- ---------- ---------- ----
4 /u11/oradata/POLDEV/redo04.log NO CURRENT 99455 100
5 /u11/oradata/POLDEV/redo05.log YES INACTIVE 99453 100
6 /u11/oradata/POLDEV/redo06.log YES INACTIVE 99454 100
Num Seq Date FIRST_CHANGE# NEXT_CHANGE# Name
------ -------- ------------------ ------------- ------------ ------------------------------------------
1 99,454 15-NOV-05 1.8684E+11 1.8684E+11 /archive/POLDEV/1_99454.dbf
2 99,453 15-NOV-05 1.8684E+11 1.8684E+11 /archive/POLDEV/1_99453.dbf
3 99,452 15-NOV-05 1.8684E+11 1.8684E+11 /archive/POLDEV/1_99452.dbf
4 99,451 15-NOV-05 1.8684E+11 1.8684E+11 /archive/POLDEV/1_99451.dbf
5 99,450 15-NOV-05 1.8684E+11 1.8684E+11 /archive/POLDEV/1_99450.dbf
6 99,449 15-NOV-05 1.8684E+11 1.8684E+11 /archive/POLDEV/1_99449.dbf
7 99,448 15-NOV-05 1.8684E+11 1.8684E+11 /archive/POLDEV/1_99448.dbf
8 99,447 15-NOV-05 1.8684E+11 1.8684E+11 /archive/POLDEV/1_99447.dbf
9 99,446 15-NOV-05 1.8684E+11 1.8684E+11 /archive/POLDEV/1_99446.dbf
|
|
|
|
|
| Top |
|
| Memory usage: |
[befsam18:POLDEV]:/export/TAR/oracle/scripts/smenu/scripts> memu
MACHINE befsam18 - ORACLE_SID : POLDEV Page: 1
Date - Tuesday 15th November 2005 12:07:11
Username - SYS
Report Shared Memory Usage Report
Fixed Obj Shared User Cursor Pool free Mem for
in Memory (m) SQL (m) Mem (bytes) size(m) Allocation(m) Used pool(m)
------------- ------------ ------------- ------------- ------------- ------------
117.95 107.55 139875.00 160.00 16.50 237
|
|
|
|
|
| Top |
Report distribution in DB buffer per type
|
/opt/smenu> bufg
MACHINE befsam18
- ORACLE_SID : POLDEV
Page: 1
Date
- Friday 28th April
2006 21:21:36
Username
- SYS
Report distribution
in DB buffer per type
Class
Not Dirty Dirty On
Dirty Total
---------- ----------
---------- ---------- ----------
7
3
0
0 3
Data
28867
215
0 29082
Header
2868
0
0 2868
Rollback
294
14
0 308
|
|
| Top |
Average number of
buffers to scan at the end of the LRU, to find a free buffer
|
/opt/smenu> bufs
MACHINE befsam18
- ORACLE_SID :
POLDEV
Page: 1
Date
- Friday 28th April
2006 21:16:53
Username
- SYS
Average number of
buffers to scan at the end of the LRU, to find a free buffer
=========================================================================
AVG_SCAN :
Normally you would expect to see 1 or 2 buffers scanned, on
average. If more than
this number are being scanned, you can increase
the size of the
buffer cache or tune the DBWR.
DIRTY BUFF : number
of buffers that were dirty at the end of the LRU
=========================================================================
Average
buffer scan Number of dirty
in LRU to find a free
one Buffer at end LRU
-------------------------
----------------------
.98384716
.063564165
|
|
|
|
|
|
|
|
|
| Top |
|
|
|
|
|
|
|
| Latch current
activity: |
[befsam18:POLDEV]:/export/home/oracle> law
MACHINE befsam18 - ORACLE_SID : POLDEV Page: 1
Date - Tuesday 15th November 2005 11:48:41
Username - SYS
Report Latch current activity
law -d to see details for buffer busy wait and latch children
. Number Seconds
Sid Event name Latch Name Sleeps Waiting P1 P1RAW P2 P2RAW
----- ------------------------ ------------------------ --------- -------- ---------- -------- ---------- -------
1 pmon timer latch wait list 0 295 300 0000012C 0 00
5 smon timer latch wait list 0 2131 300 0000012C 0 00
11 wakeup time manager latch wait list 0 10 0 00 0 00
213 latch free shared pool 1 7 2147544044 8000EBEC 105 00000069
[befsam18:POLDEV]:/export/home/oracle> law -d
MACHINE befsam18 - ORACLE_SID : POLDEV Page: 1
Date - Tuesday 15th November 2005 11:48:44
Username - SYS
Report Latch current activity
. Number Seconds
Sid Event name Latch Name Sleeps Waiting P1 P1RAW P2 P2RAW
----- ------------------------ ------------------------ --------- -------- ---------- -------- ---------- -------
1 pmon timer latch wait list 0 297 300 0000012C 0 00
pmon timer
5 smon timer latch wait list 0 2133 300 0000012C 0 00
smon timer
11 wakeup time manager latch wait list 0 12 0 00 0 00
wakeup time manager
233 latch free library cache 1 0 4087615656 F3A410A8 106 0000006A
Address --> DBF34ECC SQL--> SELECT cva.characteristic_value_id, decode(nvl('', '-1'), cva.dossier_id,
cva.dossier_id, to_number(Null)) FROM PUB_PLOC_VALUES_SN pva , PUB_CHARACTERISTIC_VALUES_SN cva WHERE
pva.ploc_id = 'PLO0000129211' AND pva.characteristic_value_id = cva.characteristic_value_id
latch free
|
|
|
|
|
| Top |
|
| Large object in
memory: |
[befsam18:POLDEV]:/export/home/oracle> lom -r 10 -e
MACHINE befsam18 - ORACLE_SID : POLDEV Page: 1
Date - Tuesday 15th November 2005 11:35:52
Username - SYS
Shared memory - List of Large Objects in memory
type lom -t <num> to display <num> caracters for cursors text
type lom -r <num> to limit display to <num> rows
type lom -u <OWNER> to show only rows of <OWNER>
type lom -e to sort by executions
type lom -l to sort by loads
Size in Nbr Kept
Owner Name Object Nam Type Mem Loads Exec Lck Pins Mem
------------- ------------------------- ---------- ---------------- --------- ----- ---------- --- ---- ----
SYS STANDARD BODY PKG BODY 26312 1 184638182 25 0 NO
POL_OWNER PKG_CONVERSION BODY PKG BODY 10593 1 166708171 48 0 NO
SYS DBMS_LOB BODY PKG BODY 11340 5 39767979 1 0 NO
SELECT COUNT(*) F CURSOR CURSOR 13240 1 4461835 24 0 NO
POL_OWNER PKG_LOADER BODY PKG BODY 31089 1 1924403 0 0 NO
SYS DBMS_SYS_SQL BODY PKG BODY 46080 1 1271882 10 0 NO
SYS DBMS_SQL BODY PKG BODY 16580 1 1271882 10 0 NO
select userpol0_.PID CURSOR CURSOR 36916 1 1058050 0 0 NO
select events0_.TASK_P CURSOR CURSOR 18168 1 479563 0 0 NO
|
|
|
|
|
| Top |
|
| Latch sleeps: |
[befsam18:POLDEV]:/export/home/oracle> las
MACHINE befsam18 - ORACLE_SID : POLDEV Page: 1
Date - Tuesday 15th November 2005 11:32:33
Username - SYS
Report Latch sleeps statistics
Wait
Latch type Impact Sleep rate holding latch LEVEL#
------------------------------ ----------- ---------- -------------- ----------
library cache 73508 0.00% 72238 5
library cache pin 32261 0.00% 32041 4
shared pool 19732 0.00% 17648 7
cache buffers chains 16394 0.00% 1049033 1
cache buffers lru chain 5880 0.00% 5796 2
redo allocation 3047 0.00% 2997 5
row cache objects 2271 0.00% 2269 4
parallel query alloc buffer 1512 0.64% 1241 6
query server process 1020 31.49% 830 2
row cache enqueue latch 948 0.00% 948 5
checkpoint queue latch 688 0.00% 627 5
session allocation 664 0.00% 622 5
library cache pin allocation 227 0.00% 227 4
mostly latch-free SCN 184 0.00% 184 6
enqueues 131 0.00% 0 5
process queue reference 100 0.00% 54 4
multiblock read objects 84 0.00% 84 3
enqueue hash chains 83 0.00% 6 4
messages 53 0.00% 49 8
|
|
|
|
|
| Top |
|
| Latch lists |
[befsam18:ERHTST]:/export/TAR/oracle/scripts/smenu/tmp> lat
MACHINE befsam18 - ORACLE_SID : ERHTST Page: 1
Date - Tuesday 15th November 2005 11:31:01
Username - SYS
Show Latch statistics
This report show the distribution of sleeps for all misses. Cspin1 show the % of sleeps that
occured only once. On a multi processor system (SMP) it is possible that a process holding a
latch is running on one of the other CPUs and so will potentially release the latch in the
next few intstructions (latches prompt are usually held for prompt only very short periods of
time). SPIN_COUNT controls how many times the process will re-try to obtain the latch before
backing off and going to sleep. This basically means the process is in a tight CPU loop
continually trying to get the latch for SPIN_COUNT attempts. ie values are: 64 for 2 processors,
128 for up to 4 processors etc.. If a system is not tight on CPU resource SPIN_COUNT can be
left at higher values but anything above 2000 is unlikely to be of any benefit.
The key value here is thus 'spin_gets' which report the spuccessful spin for latch.
no rows selected
Spin
Latch name Gets Misses % Miss gets Sleep1 Sleep2 Sleep3 Sleep4 Sleep5
------------------------------- ------------ --------- --------- ------ ------ ------ ------ ------ ------
cache buffers chains 1270405411 86545 0.0068 0.0 0.0 0.0 0.0 0.0 0.0
checkpoint queue latch 126478081 3890 0.0031 99.9 0.1 0.0 0.0 0.0 0.0
library cache 107145357 351141 0.3277 99.8 0.2 0.0 0.0 0.0 0.0
session idle bit 54314712 1596 0.0029 0.0 0.0 0.0 0.0 0.0 0.0
library cache pin 48526070 51295 0.1057 99.8 0.2 0.0 0.0 0.0 0.0
simulator hash latch 45699347 138 0.0003 100.0 0.0 0.0 0.0 0.0 0.0
redo allocation 32231619 43076 0.1336 99.9 0.1 0.0 0.0 0.0 0.0
enqueue hash chains 31535935 9417 0.0299 99.6 0.4 0.0 0.0 0.0 0.0
shared pool 25704642 76275 0.2967 99.8 0.2 0.0 0.0 0.0 0.0
enqueues 24107465 72645 0.3013 99.9 0.1 0.0 0.0 0.0 0.0
library cache pin allocation 20875318 10719 0.0513 99.8 0.2 0.0 0.0 0.0 0.0
row cache objects 18343537 21472 0.1171 99.9 0.1 0.0 0.0 0.0 0.0
row cache enqueue latch 17873904 4677 0.0262 99.9 0.1 0.0 0.0 0.0 0.0
cache buffers lru chain 16086160 29598 0.1840 99.9 0.1 0.0 0.0 0.0 0.0
undo global data 15262773 1051 0.0069 99.7 0.3 0.0 0.0 0.0 0.0
session allocation 11761945 27709 0.2356 99.8 0.2 0.0 0.0 0.0 0.0
global tx hash mapping 11507727 505 0.0044 100.0 0.0 0.0 0.0 0.0 0.0
simulator lru latch 9865381 867 0.0088 99.8 0.2 0.0 0.0 0.0 0.0
messages 9571637 1903 0.0199 99.9 0.1 0.0 0.0 0.0 0.0
transaction branch allocation 8621109 9554 0.1108 99.7 0.3 0.0 0.0 0.0 0.0
redo writing 5072277 151 0.0030 100.0 0.0 0.0 0.0 0.0 0.0
multiblock read objects 3692552 32 0.0009 100.0 0.0 0.0 0.0 0.0 0.0
dml lock allocation 2323225 953 0.0410 99.6 0.4 0.0 0.0 0.0 0.0
post/wait queue 1726442 22 0.0013 100.0 0.0 0.0 0.0 0.0 0.0
mostly latch-free SCN 1484047 2071 0.1396 99.8 0.2 0.0 0.0 0.0 0.0
lgwr LWN SCN 1477351 258 0.0175 99.6 0.4 0.0 0.0 0.0 0.0
Consistent RBA 1297044 4 0.0003 75.0 25.0 0.0 0.0 0.0 0.0
|
|
|
|
|
| Top |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Information on
index with subpartitions details: |
[befsam18:POLDEV]:/export/home/oracle> idx -i IDX_CUST_LNKRDF_PID_CALC_AMOUNT -p
MACHINE befsam18 - ORACLE_SID : POLDEV Page: 1
Date - Tue 15 November 2005 11:28:01
Username - SYS
Table List - Index stats for IDX_CUST_LNKRDF_PID_CALC_AMOUNT
. If you have a $ in name type \$
. idx -u <OWNER> [-c] All table/indexes for the schema
. idx -t <TABLE_NAME> [-s][-c] -s for size(m) -c count unusable sub indx
. idx -i <INDEX_NAME> [-p [-w]] -p show sub part -w row count -g show stats
. idx -h for a more detailed help
. U = Unique P=Degree of parallelism S=Status
Table,name Table owner
------------------------------ ------------------------------
CUST_OPERATION POL_OWNER
Free Pct
Column Col Global user Pct Free List Direct
Index Name U Name Pos Ord S P Part Stats JOI Stat Free List Group Access
-------------------------------- - ------------------------ ---- ---- - -- ---- ------ --- ----- ----- ---- ----- -------
IDX_CUST_LNKRDF_PID_CALC_AMOUNT N LNKRDF_PID 1 ASC N 1 YES YES NO NO
N CALCED_AMOUNT 2 ASC YES YES NO NO
Avg data Avg leaf
Index Leaf Blocks Blocks Clust Distinct
Blevel type Blocks Per Key Per Key Factor Keys NUM_ROWS Selectivity last analysed Compress
------ -------- --------- -------- -------- ---------- ---------- ---------- ----------- -------------- --------
2 NORMAL 546423 608 1 331223170 544291 383844958 705.2 23-09-05 12:43 DISABLED
Part System generated
Pos Partition name Subartition name STATUS Num rows Distinct rows Size (m) last analysed
----- ----------------------------------- ------------------------- ------ ------------------------- -------- -------------
1 P2004Q3 P2004Q3_AD USABLE 38231299 418733 843 16-10-05 10:03
P2004Q3_AR 20432317 406501 460 16-10-05 11:00
2 P_DEP4_09 P_DEP4_09_AD USABLE 18668513 510840 412 16-10-05 11:52
P_DEP4_09_AR 8824353 460125 198 16-10-05 12:18
3 P_DEP4_10 P_DEP4_10_AD USABLE 19250691 523672 425 16-10-05 13:11
P_DEP4_10_AR 10160619 485194 228 16-10-05 13:39
4 P_DEP4_11 P_DEP4_11_AD USABLE 18482260 529848 408 16-10-05 14:35
P_DEP4_11_AR 8206438 432079 183 16-10-05 14:58
5 P_DEP4_12 P_DEP4_12_AD USABLE 20221807 499766 446 16-10-05 15:59
P_DEP4_12_AR 9652770 474896 216 16-10-05 16:27
6 P_DEP5_01 P_DEP5_01_AD USABLE 20611351 516405 455 16-10-05 17:23
P_DEP5_01_AR 8665556 399057 195 16-10-05 17:49
7 P_DEP5_02 P_DEP5_02_AD USABLE 17817795 532180 393 16-10-05 18:43
P_DEP5_02_AR 8295089 447290 186 16-10-05 19:05
8 P_DEP5_03 P_DEP5_03_AD USABLE 21722421 568725 480 16-10-05 20:01
P_DEP5_03_AR 8502881 414637 191 16-10-05 20:31
9 P_DEP5_04 P_DEP5_04_AD USABLE 19594364 512636 433 16-10-05 21:52
P_DEP5_04_AR 10460747 518967 236 16-10-05 22:23
10 P_DEP5_05 P_DEP5_05_AD USABLE 19552501 566630 434 16-10-05 23:22
P_DEP5_05_AR 10072070 525478 227 16-10-05 23:54
11 P_DEP5_06 P_DEP5_06_AD USABLE 19803849 552330 437 24-10-05 09:11
P_DEP5_06_AR 9866496 494406 222 24-10-05 00:26
|
|
|
|
|
| Top |
|
|
|
| show all indexes
for table: |
[befsam18:POLDEV]:/export/home/oracle> idx -t cust_operation
MACHINE befsam18 - ORACLE_SID : POLDEV Page: 1
Date - Tue 15 November 2005 11:26:35
Username - SYS
Table List - Table/Index Cross Reference for
. idx -u <OWNER> [-c] All table/indexes for the schema
. idx -t <TABLE_NAME> [-s][-c] -s for size(m) -c count unusable sub indx
. idx -i <INDEX_NAME> [-p [-w]] -p show sub part -w row count -g show stats
. idx -h for a more detailed help
. U = Unique P=Degree of parallelism S=Status
Table Index Column Clust Distinct
Name Name U Name Factor Keys NUM_ROWS S last analys P
-------------- ------------------------------ - -------------------- ---------- ---------- ---------- - ----------- --
CUST_OPERATION IDX_CUST_LNKRDF_MIR_NR_EXTERN N SYS_NC00087$ 179913751 107901 398462438 N 23-09 12:27 1
IDX_CUST_LNKRDF_PID N LNKRDF_PID 160972531 176404 401954779 N 23-09 12:43 1
IDX_CUST_LNKRDF_PID_CALC_AMOUN N LNKRDF_PID 331223170 544291 383844958 N 23-09 12:43 1
CALCED_AMOUNT 331223170 544291 383844958 N 23-09 12:43 1
IDX_CUST_LNKRDF_PID_ORIG_AMOUN N LNKRDF_PID 350759264 529373 408438447 N 23-09 12:42 1
ORIG_AMOUNT 350759264 529373 408438447 N 23-09 12:42 1
IDX_CUST_OWNER_PID N OWNER_PID 182076989 193582 374591583 N 23-09 12:41 1
IDX_CUST_OWNER_PID_CALC_AMOUNT N OWNER_PID 346062972 617736 388792840 N 23-09 12:41 1
CALCED_AMOUNT 346062972 617736 388792840 N 23-09 12:41 1
IDX_CUST_OWNER_PID_ORIG_AMOUNT N OWNER_PID 345192299 669057 383880447 N 23-09 12:40 1
ORIG_AMOUNT 345192299 669057 383880447 N 23-09 12:40 1
IDX_CUST_OPERATION_CONTEXT N CONTEXT_NR 265829180 57808 324735510 N 23-09 12:39 1
IDX_CUST_OPERATION_ACTION_DT N ACTION_DT 14955954 507 68638990 N 23-09 12:38 1
IDX_CUST_OPERATION_PID N PID 400942078 408790438 408790439 N 23-09 12:38 1
IDX_CUST_OPERATION_CUST_DT N CUST_DT 41934857 335 383512092 N 23-09 12:37 1
IDX_CUST_FACS_MIR_DT N MIR_DT 36019135 278 385582147 N 23-09 12:37 1
IDX_CUST_FACS_USEING_REJ_CD N USEING_REJECTION_CD 5190 19 5190 N 12-12 18:20 1
IDX_CUST_FACS_USE_ORIG_REF_NR N USE_ORIG_REF_NR 67063620 66986198 71824060 N 23-09 12:34 1
IDX_CUST_FACS_CEC_REJECTION_CD N CEC_REJECTION_CD 10051 3 10051 N 23-09 12:33 1
IDX_CUST_FACS_COMM_STRUCT N COMM_STRUCT 154929545 260634 389539961 N 23-09 12:31 1
IDX_CUST_FACS_OPERATION_REF_NR N OPERATION_REF_NR 375241257 711794 386651072 N 23-09 12:30 1
IDX_CUST_FACS_REF_NR N REF_NR 393588287 681492 403959308 N 23-09 12:30 1
IDX_CUST_FACS_CUST_CD_PRODUCT N CUST_CD_PRODUCT 11725 39 11725 N 12-12 19:34 1
IDX_CUST_FAC_CUST_CD_SUBPRODUC N CUST_CD_SUBPRODUCT 31141 69 31141 N 23-09 12:34 1
|
|
|
|
|
| Top |
|
|
|
| Show all user
indexes |
[befsam18:POLDEV]:/export/home/oracle> idx -u FTT_OWNER
MACHINE befsam18 - ORACLE_SID : POLDEV Page: 1
Date - Tue 15 November 2005 11:24:14
Username - SYS
Table List - Table/Index Cross Reference for 'FTT_OWNER'
. idx -u <OWNER> [-c] All table/indexes for the schema
. idx -t <TABLE_NAME> [-s][-c] -s for size(m) -c count unusable sub indx
. idx -i <INDEX_NAME> [-p [-w]] -p show sub part -w row count -g show stats
. idx -h for a more detailed help
. U = Unique P=Degree of parallelism S=Status
Table Index Column Clust Distinct
Name Name U Name Factor Keys NUM_ROWS S last analys P
-------------------- ------------------------------ - -------------------- ---------- ---------- ---------- - ----------- --
RESMATO IDX_RESMATO_MIR_NR N MIR_NR 362582 3443 17111840 V 03-02 15:32 1
IDX_RESMATO_CHQ_NR_DOC_PID N CHEQUE_NR V 1
DOCUMENT_PID V 1
IDX_RESMATO_CREDIT_MIR_NR N CREDIT_MIR_NR 7745046 22685 16661426 V 03-02 15:35 1
IDX_RESMATO_DEBIT_MIR_NR N DEBIT_MIR_NR 4260470 17154 16143727 V 03-02 15:36 1
IDX_RESMATO_DNR N DNR 5608194 17116336 17116337 V 03-02 15:36 1
IDX_RESMATO_DOCUMENT_PID N DOCUMENT_PID 2399687 10957919 10957920 V 03-02 15:34 1
IDX_RESMATO_DOC_TYPE N DOC_TYPE 2124 43 2124 V 03-02 15:33 1
IDX_RESMATO_INTERVENIENT_ID N INTERVENIENT_ID 328894 4498 15983661 V 03-02 15:33 1
IDX_RESMATO_PID U PID 206146 16073588 16073588 V 03-02 15:34 1
IDX_RESMATO_PRS_ID N PRS_ID 443327 7614 16218604 V 03-02 15:34 1
IDX_RESMATO_SELLABLE N SELLABLE 501644 72 501644 V 03-02 15:37 1
IDX_RESMATO_SHEET_PID N SHEET_PID 0 0 0 V 03-02 15:34 1
IDX_RESMATO_OPERATION_DT N OPERATION_DT 233870 169 17054476 V 03-02 15:35 1
DOCUMENT IDX_DOCUMENT_PID U PID 63470 25709158 25709158 V 03-02 15:48 1
GENERIC_DOCUMENT IDX_GENERIC_DOCUMENT_SOURCE N SOURCE 50 1 5014 V 29-03 08:38 1
IDX_GEN_DOCUMENT_PID U PID 2386 5014 5014 V 29-03 08:38 1
|
|
|
|
|
| Top |
|
|
|
| Object accessed by
session with event associcated |
[befsam18:ERHTST]:/export/TAR/oracle/scripts/smenu/tmp> sla 41 -e
MACHINE befsam18 - ORACLE_SID : ERHTST Page: 1
Date - Tuesday 15th November 2005 11:20:49
Username - SYS
Show Objects accessed(v$access)
type 'sla <sid> for objects accessed only by <sid>
type 'sla <sid> -e to add <sid> events wait
type 'sla -n to sort object by name
Sid Username Object Owner Type Object
----- -------------------- -------------------- ------------------------ ----------------------------
41 ERH_USER ERH_OWNER SYNONYM PCK$ADDRESS
41 ERH_USER ERH_OWNER PACKAGE PCK$OMUSADDRESSVALIDATION
41 ERH_USER ERH_OWNER PACKAGE PCK$OMUSTYPES
41 ERH_USER ERH_OWNER NON-EXISTENT PLITBLM
41 ERH_USER ERH_OWNER TABLE AMORPDP
41 ERH_USER PUBLIC SYNONYM PLITBLM
41 ERH_USER AMOROWNER PACKAGE PCK$ADDRESSMATCH
41 ERH_USER SYS PACKAGE PLITBLM
41 ERH_USER SYS PACKAGE STANDARD
Total Total Total Time (secs) Average (ms)
Event type Waits Timeouts Timeouts Waited Wait
------------------------------ ---------- ---------- ---------- ------------- ------------
SQL*Net message to client 462 0 0 .0 .0
SQL*Net message from client 461 0 0 1625.0 352.0
SQL*Net message to dblink 84 0 0 .0 .0
SQL*Net message from dblink 84 0 0 7144.4 8505.0
log file sync 53 0 0 .2 .0
db file sequential read 2 0 0 .0 .0
|
|
|
|
|
| Top |
|
|
|
| Show dataguard
status |
[befsam18:POLTEST2]:/export/home/oracle> dgs
Date - Tuesday 15th November 2005 10:43:31
Username - SYS
Report - Dataguard Status
Type 'dgs 50' to see 50 lines, default is 10
Error
FACILITY SEVERITY Date Message Code
-------------------- ------------- ------------------- -------------------------------------------------------- --------
Log Apply Services Warning 15/11/2005 08:01:15 Media Recovery Waiting for thread 1 seq# 2126 0
Log Apply Services Informational 15/11/2005 08:00:50 Media Recovery Log /archive/standby/POLTEST2/POLTEST_1_2 0
125.arc
Log Apply Services Warning 14/11/2005 23:00:31 Media Recovery Waiting for thread 1 seq# 2125 0
Log Apply Services Informational 14/11/2005 23:00:31 Media Recovery Log /archive/standby/POLTEST2/POLTEST_1_2 0
124.arc
Log Apply Services Informational 14/11/2005 23:00:26 Media Recovery Log /archive/standby/POLTEST2/POLTEST_1_2 0
123.arc
Log Apply Services Warning 14/11/2005 18:15:57 Media Recovery Waiting for thread 1 seq# 2123 0
Log Apply Services Informational 14/11/2005 18:15:57 Media Recovery Log /archive/standby/POLTEST2/POLTEST_1_2 0
122.arc
Log Apply Services Informational 14/11/2005 18:15:41 Media Recovery Log /archive/standby/POLTEST2/POLTEST_1_2 0
121.arc
Log Apply Services Warning 14/11/2005 13:07:19 Media Recovery Waiting for thread 1 seq# 2121 0
Log Apply Services Informational 14/11/2005 13:06:58 Media Recovery Log /archive/standby/POLTEST2/POLTEST_1_2 0
120.arc
|
|
|
|
|
| Top |
|
|
|
| User space usage: |
[befsam18:ERHTST]:/export/TAR/oracle/scripts/smenu/tmp> dus
MACHINE befsam18 - ORACLE_SID : ERHTST Page: 1
Date - Tuesday 15th November 2005 10:37:00
Username - SYS
Database usage per user and tablespace
Total space Total space Percent in
User Tablespace By user(mb) in Tablespace Tablespace
------------------------------ --------------------------- ----------- ------------- ----------
ERH_OWNER OMUS_DATA 10,779 10,895 98%
OMUS_INDX 27,338 29,000 94%
ENAZZA_OWNER ENAZZA_DATA 53 500 10%
ENAZZA_INDX 159 500 31%
OUTLN SYSTEM 0 300 0%
PERFSTAT TOOLS 395 500 79%
QCOADM QCODATA 333 500 66%
QCOINDX 21 200 10%
|
|
|
|
|
| Top |
|
|
|
| DB uptime |
[befsam18:ERHTST]:/export/TAR/oracle/scripts/smenu/tmp> up
MACHINE befsam18 - ORACLE_SID : ERHTST
Date - Tuesday 15th November 2005 10:35:50
Username - SYS
DB uptime
Startup time Status
---------------------------------- ----------
18:28:31 2005-NOV-07 OPEN
|
|
|
|
|
| Top |
|
|
|
| Fast list of all
objects in db |
[befsam18:ERHTST]:/export/TAR/oracle/scripts/smenu/tmp> cpt
MACHINE befsam18 - ORACLE_SID : ERHTST Page: 1
Date - Tuesday 15th November 2005 10:33:46
Username - SYS Schema Objects
SCHEMA CLSTR TABLE INDEX SEQNC TRIGR FUNCT PROCD PACKG VIEWS SYNYM OTHER
------------- ------ ------ ------ ----- ----- ----- ------ ----- ------ ------ ------
SYS 10 348 336 40 1 37 23 301 1986 8 905
SYSTEM 128 177 19 2 2 1 10 59 35
PUBLIC 1950 10
ERH_OWNER 69 245 53 3 5 36 10 9
BOREP_USER 35 0
DBSNMP 4 0
ENAZZA_OWNER 19 49 16 1
OUTLN 3 3 1 1
PERFSTAT 36 37 1 1 48
QCOADM 129 153 18 4 7 46 47 6 148
QCOUSER 43 0
11 rows selected.
Invalid objects :
=================
SCHEMA CLSTR TABLE INDEX SEQNC TRIGR FUNCT PROCD PACKG VIEWS SYNYM OTHER
------------- ------ ------ ------ ----- ----- ----- ------ ----- ------ ------ ------
SYS 1
|
|
|
|
|
| Top |
|
|
|
| List all shortcuts
starting with s: |
[befsam18:ERHTST]:/u01/oracle/admin/FMSREP/bdump> vsl s
List of Shortcuts starting with 's' :
========================================
snd $SBIN/scripts/smenu_sendmail.sh -u $DEF_MAIL -s test -a
sel $SBINS/SELECT # Select prg using awk
smd cd $SBIN # go to SBIN
smg cd $SBIN/scripts/logscan # gp to LBIN
sml cd $SBIN/log # go to SBIN/log
spm more $SBINS/addpar.sh # More of addpar.sh
sp cat $SBINS/addpar_s.txt # Show Smenu Shortcuts
spo cat $SBINS/addpar_su.txt # Show only User Shortcuts
spb cat $SBINS/addpar_sb.txt # Show only DB Shortcuts
spu cat $SBINS/addpar_u.txt # Show Unix Shortcuts
stb $SBIN/scripts/smenu_stress_db.ksh # Run stress test
sm cd $SBIN;$SBIN/smenu.sh # Call smenu
sts $SBIN/module2/s1/smenu_view_archive_mode.sh # show archive status
slh $SBIN/module2/s2/smenu_session_hit.sh # Show sessions hit ratio
sq $SBIN/module2/s2/smenu_get_sql_figures.sh # Figures for SQL
sqn $SBIN/module2/s2/smenu_get_sqltext_60char.ksh # Show first 60 char
sqt $SBIN/module2/s2/smenu_get_sql_sid_text.sh # Figures, SQL, SID
slo $SBIN/module2/s2/smenu_long_ops.ksh # Show first 60 char
soc $SBIN/module2/s2/smenu_open_cursor.ksh # view open cursor in system
sa $SBIN/module2/s2/smenu_session_activity.sh # check user activity
st $SBIN/module2/s2/smenu_get_sql_text.ksh # Get the sql text for an address
sw $SBIN/module2/s2/smenu_sql_work_area.ksh # Display sql work area values
sl $SBIN/module2/s2/smenu_sessions_overview.sh # Show open sessions info
slio $SBIN/module2/s2/smenu_user_io_obj.sh # Sess io and object accessed
srt $SBIN/module2/s2/smenu_session_sort.sh # Show session currently sorting
slf $SBIN/module2/s2/smenu_failed_over_sess.sh # Show failed over sessions
sls $SBIN/module2/s6/smenu_system_event.sh # Show system events
sle $SBIN/module2/s6/smenu_session_event.sh # Show sessions events
sla $SBIN/module2/s6/smenu_object_accessed.sh # Show sess. Obj accessed
spx $SBIN/module2/s7/smenu_show_pq_slave.ksh # Show parallel query slave
sfl $SBIN/module2/s8/smenu_shared_pool_free_list.sh # Shared pool free list
spl $SBIN/module3/s1/smenu_menu_monitor.ksh # owi selection screen
smtp $SBIN/module3/s3/smenu_show_missing_tab_parts.ksh # Show missing tab parts
smtga $SBIN/module3/s3/smenu_sub_gen_scr_exch_all_subpart.ksh # gen all sub exch
sstr $SBIN/module3/s4/smenu_choose_session_to_trace.sh # Set trace in session
sstv $SBIN/module3/s4/smenu_choose_session_to_set_event.ksh # Set trace in session
ssts $SBIN/module3/s4/smenu_choose_session_to_dbms_supp.ksh # Set trace in session
sstp $SBIN/module3/s4/smenu_statpack.ksh # Run statpack
sstl $SBIN/module3/s4/smenu_show_stats_list.ksh # Show statpack snapshots
sx $SBIN/module3/s4/smenu_dyn_explain_plan.ksh # Display dynamic explain plan
sta $SBIN/module3/s6/smenu_gather_stat_tbl.ksh # gather stats on table
|
|
|
|
|
| Top |
|
|
|
| List error in
dataguard site: |
[POLTEST]:/export/home/oracle> aplerr
Date - Tuesday 15th November 2005 10:30:30
Username - SYS
Report - log ship error to standby
ID DB_status Destination Error
---------- --------- ----------------------------------- -----------------------------------------------
1 VALID /archive/POLTEST
2 DEFERRED POLTEST
3 INACTIVE
4 INACTIVE
5 INACTIVE
6 INACTIVE
7 INACTIVE
8 INACTIVE
9 INACTIVE
10 INACTIVE
11 VALID /archive/standby/POLTEST
11 rows selected.
Check for archive log gaps:
===========================
no rows selected
Check Managed standby processes:
================================
PROCESS Status
------- --------------------
ARCH CONNECTED
ARCH CONNECTED
RFS RECEIVING
MRP0 WAIT_FOR_LOG
RFS RECEIVING
|
|
|
|
|
| Top |
|
|
|
| Display usual
dataguard parameters |
[befsam18:POLTEST2]:/export/home/oracle> parg
Date - Tuesday 15th November 2005 10:28:23
Username - SYS
Report - Standby pararmeters
Is Sys
Parameter Value Modifiable
---------------------------- ----------------------------------------------------------------- ----------
compatible 9.2.0.0.0 FALSE
db_file_name_convert POLTEST, POLTEST2 FALSE
db_name POLTEST FALSE
instance_name POLTEST2 FALSE
local_listener (ADDRESS=(HOST=befsam18)(PROTOCOL=TCP)(PORT=1525)) IMMEDIATE
lock_name_space FALSE
log_archive_dest_1 LOCATION=/archive/POLTEST2 IMMEDIATE
log_archive_dest_2 SERVICE=POLTEST IMMEDIATE
log_archive_dest_state_1 enable IMMEDIATE
log_archive_dest_state_2 defer IMMEDIATE
log_archive_format POLTEST_%t_%s.arc FALSE
log_archive_start TRUE FALSE
log_file_name_convert POLTEST, POLTEST2 FALSE
remote_archive_enable true FALSE
service_names POLTEST2, POLTEST IMMEDIATE
standby_archive_dest /archive/standby/POLTEST2 IMMEDIATE
standby_file_management auto
|
|
|
|
|
| Top |
|
|
|
| Show applied logs: |
This is taken from a dataguard site:
[befsam18:POLTEST2]:/export/home/oracle> apl
MACHINE - ORACLE_SID : POLTEST2 Page: 1
Date - Tuesday 15th November 2005 10:26:07
Username - SYS
Report - Applied archive logs
Given an number if you want to see more lines : apl 50
Standby Deleted
DEST_ID SEQUENCE# First time Next time APP Status Dest By Rman REGISTR
---------- ---------- --------------------- --------------------- --- ---------- ------- ------- -------
2 2125 2005-11-14 23:00:20 2005-11-15 08:00:35 YES A NO NO RFS
2 2124 2005-11-14 23:00:17 2005-11-14 23:00:20 YES A NO NO RFS
2 2123 2005-11-14 18:15:34 2005-11-14 23:00:17 YES A NO NO RFS
2 2122 2005-11-14 18:15:22 2005-11-14 18:15:34 YES A NO NO RFS
2 2121 2005-11-14 13:06:44 2005-11-14 18:15:22 YES A NO NO RFS
2 2120 2005-11-13 23:00:09 2005-11-14 13:06:44 YES A NO NO RFS
2 2119 2005-11-13 23:00:07 2005-11-13 23:00:09 YES A NO NO RFS
2 2118 2005-11-13 18:15:25 2005-11-13 23:00:07 YES A NO NO RFS
2 2117 2005-11-13 18:15:20 2005-11-13 18:15:25 YES A NO NO RFS
2 2116 2005-11-12 23:00:10 2005-11-13 18:15:20 YES A NO NO RFS
2 2115 2005-11-12 23:00:07 2005-11-12 23:00:10 YES A NO NO RFS
2 2114 2005-11-12 18:15:17 2005-11-12 23:00:07 YES A NO NO RFS
2 2113 2005-11-12 18:15:12 2005-11-12 18:15:17 YES A NO NO RFS
2 2112 2005-11-12 11:40:05 2005-11-12 18:15:12 YES A NO NO RFS
2 2111 2005-11-11 23:00:11 2005-11-12 11:40:05 YES A NO NO RFS
2 2110 2005-11-11 23:00:07 2005-11-11 23:00:11 YES A NO NO RFS
2 2109 2005-11-11 18:15:34 2005-11-11 23:00:07 YES A NO NO RFS
2 2108 2005-11-11 18:15:30 2005-11-11 18:15:34 YES A NO NO RFS
2 2107 2005-11-11 14:08:05 2005-11-11 18:15:30 YES A NO NO RFS
2 2106 2005-11-10 23:00:13 2005-11-11 14:08:05 YES A NO NO RFS
2 2105 2005-11-10 23:00:08 2005-11-10 23:00:13 YES A NO NO RFS
2 2104 2005-11-10 18:16:47 2005-11-10 23:00:08 YES A NO NO RFS
2 2103 2005-11-10 18:16:41 2005-11-10 18:16:47 YES A NO NO RFS
2 2102 2005-11-10 14:21:04 2005-11-10 18:16:41 YES A NO NO RFS
2 2101 2005-11-10 08:00:33 2005-11-10 14:21:04 YES A NO NO RFS
2 2100 2005-11-09 23:00:19 2005-11-10 08:00:33 YES A NO NO RFS
2 2099 2005-11-09 23:00:16 2005-11-09 23:00:19 YES A NO NO RFS
2 2098 2005-11-09 18:15:26 2005-11-09 23:00:16 YES A NO NO RFS
2 2097 2005-11-09 18:15:22 2005-11-09 18:15:26 YES A NO NO RFS
2 2096 2005-11-09 14:26:42 2005-11-09 18:15:22 YES A NO NO RFS
[POLDEV:/opt/smenu/]> apl -h
Display archive info :
apl -a <arch nbr> # Show archive info whose number is given
apl -s <SCN> # Show archives which contains this SCN
apl -m # Show higher applied up to now, return none if you are a standby db
apl -n : # Show filename and SCN instead of time
apl -r <n> # List number of line
apl -d <dest_id>
|
|
|
|
|
| Top |
|
|
|
| Session hits: |
[befsam18:ERHTST]:/u01/oracle/admin/FMSREP/bdump> slh 16
MACHINE befsam18 - ORACLE_SID : ERHTST Page: 1
Date - Tuesday 15th November 2005 10:24:14
Username - SYS
Session Hit
: -p Sort by Parse -c Sort by CPU -d sort by Physical read
: Type slh <sid> to limit to one session -b sort by block change
Hard Block Consiste Consiste Physical
SID USERNAME OSUSER OSPID STA COM HASH_VALUE TYP Cpu Parse Changes Changes Gets Reads %HIT
--- ---------- -------- ------------ --- --- ---------- ----- --------- ------- -------- -------- -------- -------- ----
16 ERH_USER sumo 1872 INA 0 2236747712 USER 0.10 0 15 6 19 0 100
|
|
|
|
|
| Top |
|
|
|
| Single session
overview |
/u01/oracle/admin/FMSREP/bdump> sa 16
MACHINE befsam18 - ORACLE_SID : ERHTST Page: 1
Date - Tuesday 15th November 2005 10:22:36
Username - SYS
Session - Session Activity Overview
Sid Osuser Osuser Program LOGON_TIME
---- ------------------ ------------ ------------------------------ ------------
16 ERH_USER sumo JDBC Thin Client 10-nov 14:02
Sql in work area:
SQL Hash value Sql
---- ----------- --------------------------------------------------------------------------------
Curr 2236747712 BEGIN pck$sumoaddressvalidation.GetAddress(:1, :2, :3, :4, :5, :6, :7, :8, :9, :
10, :11); END;
Prev 2236747712 BEGIN pck$sumoaddressvalidation.GetAddress(:1, :2, :3, :4, :5, :6, :7, :8, :9, :
10, :11); END;
Total
Disk Rows Session
SQL Buffer Gets Executions Gets/Exec Reads Processed SORTS PARSE_CALLS Hard Parse CPU_TIME
---- ----------- ---------- --------- --------- ----------- ---------- ----------- ---------- ----------
Curr 2770490 1805307 2 6690 1805284 0 1806839 0 6882.27
Prev 0 1806839 0 6882.27
Event and his latch wait:
|
|
|
|
|
| Top |
|
|
|
| Report session |
[befsam18:ERHTST]:/u01/oracle/admin/FMSREP/bdump> sl
***************************************************************
TIPS : If program column is not wide enough, You may increase
the size of program column by typing : "sl 50"
***************************************************************
MACHINE befsam18 - ORACLE_SID : ERHTST Page: 1
Date - Tuesday 15th November 2005 10:20:54
Username - SYS
Sessions - Generic info Overview
Sess Last
USERNAME ID OSUSER Serial PID# OS-PID activity STATUS PROGRAM
--------------- ----- ------------ ------ -------- ----------- -------- -------- -------------------------------
ERH_USER 16 sumo 192 1872 418,095 INACTIVE JDBC Thin Client
AMOR_USER 40 merteri 10742 23602 17388 2,642 INACTIVE oracle@s05114 (TNS V1-V3)
ERH_USER 41 sumo 1920 12761 417,443 INACTIVE JDBC Thin Client
ERH_USER 51 sumo 14980 20461 71 INACTIVE JDBC Thin Client
ENAZZA_USER 55 sumo 23547 5218 34 INACTIVE JDBC Thin Client
SYS 58 oracle 7576 6075 6064 0 ACTIVE sqlplus@befsam18 (TNS V1-V3)
6 rows selected.
waiting sessions :
no rows selected
who is doing full_table_scans ?
-------------------------------
14
|
|
|
|
|
| Top |
|
Auditing
|
|
/opt/oracle> aud
Auditing suppose that init.ora parameter 'audit_trail'=TRUE or there is
no rows kept
Command :
aud -a -p -o -m -u <USER>
-a : List Active Statement Audit Options
-p : List Active Privilege Audit Options
-o : List Active Objects Audit Options
-m : List last connection for user
/opt/smenu> aud -a
MACHINE
BEFSAM18 - ORACLE_SID :
POLDEV
Page: 1
Date
- Friday 28th April
2006 19:45:25
Username
- SYS List Audit Statement in DB
(aud -h for help)
User
audited
Auditing
action
SUCCESS FAILURE
-------------------------
-------------------------------------------------- ---------- ----------
ADMXB
CREATE
SESSION
BY ACCESS BY ACCESS
CREATE
SESSION
BY ACCESS BY ACCESS
OPS$ORACLE
CREATE
TABLE
BY ACCESS BY ACCESS
OPS$ORACLE
CREATE ANY
TABLE
BY ACCESS BY ACCESS
ADMXB
ALTER ANY
TABLE
BY ACCESS BY ACCESS
ADM_OF
ALTER ANY
TABLE
BY ACCESS BY ACCESS
OPS$ORACLE
DROP ANY
TABLE
BY ACCESS BY ACCESS
OPS$ORACLE
INSERT ANY
TABLE
BY SESSION BY SESSION
OPS$ORACLE
UPDATE ANY
TABLE
BY SESSION BY SESSION
ADMXB
ALTER
TABLE
BY ACCESS BY ACCESS
ADMXB
ALTER ANY
CLUSTER
BY ACCESS BY ACCESS
OPS$ORACLE
INSERT
TABLE
BY SESSION BY SESSION
OPS$ORACLE
DELETE
TABLE
BY SESSION BY SESSION
ADMXB
DROP ANY
SYNONYM
BY ACCESS BY ACCESS
ADM_OF
ALTER ANY
SEQUENCE
BY ACCESS BY ACCESS
ADM_OF
ALTER ANY
ROLE
BY ACCESS BY ACCESS
ADMXB
DROP ANY
PROCEDURE
BY ACCESS BY ACCESS
/opt/smenu> aud -m
MACHINE
BEFSAM18 - ORACLE_SID :
POLDEV
Page: 1
Date
- Friday 28th April
2006 19:45:25
Username
- SYS (aud -h for help)
USERNAME
LAST_LOGOFF
------------------------------
-------------------
AMAUOPOR
14-02-2006 12:08:44
AMPB_REP_DISC
23-11-2005 16:45:33
AWALFERS
27-01-2006 14:54:06
AZENG
15-11-2005 08:25:40
BYDERATOR
25-08-2005 11:59:54
BUD_ENTRY_STAFF
25-08-2005 09:33:03
CFREFWIC
27-01-2006 14:37:29
|
|
|
| Top
|
|
| Report free frags: |
Shortcuts 'frg'
This is an important shortcut, used to display the db logical layout. Often used to check fast
if temp is present and has datafiles. After an rman restore, it is often forgotten. I also use it alot in mail.
exchange as People like to see figures. important columns is the 'Available' and '%'.
[befsam18:POLDEV]:/home/oracle> frg
MACHINE befsam18 - ORACLE_SID : POLTEST Page: 1
Date - Tuesday 11th October 2005 09:14:16
Username - SYS
FREE - Free space summary by Tablespace
Type 'frg b' or 'frg g' for results in bytes or Giga
Free Largest Total Available %
Tablespace Frags Frag (Megs) (Megs) (Megs) Used
-------------------- -------- ------------ ------------ ------------ ----
TEMP 0 0.0 32,767.0 0.0 100
INDX 1 24.9 25.0 24.9 0
TOOLS 2 6.4 1,623.4 7.0 100
USERS 2 4.2 101.3 5.1 95
SYSTEM 2 9.9 400.0 10.2 97
FPO_NDX 11 222.9 1,400.0 233.9 83
QCODATA 1 163.0 300.0 163.0 46
QCOINDX 1 35.0 200.0 35.0 83
STG_NDX 1 99.7 100.0 99.7 0
FPO_DATA 279 999.9 207,328.0 2,101.8 99
STG_DATA 176 1,485.0 30,000.0 21,214.0 29
UNDOTBS1 17 3,968.0 8,000.0 7,897.7 1
-------- ------------ ------------
sum 493 282,244.7 31,792.3
|
|
|
|
|
| Top |
|
|
|
| List datafile
attribute: |
One of the most used: it shows for each tablespaces, the path, size and autoextend status. Usefull to check
after an 'frg' if there is space allowed beyond a 99% full. The column size(meg) gives the actual size,
while 'max Size' gives the max allowed size for a datafile to growth. the id attribute is often usefull
when you have queries with FILE_ID or FILE#. Note that 'lstd' produces a list file in SBIN/tmp which contains
only the path. Usefull for rename of move of series of datafile. I often use this with a grep.
ie ) lstd | grep stg_data will return only the sta_data datafiles. this allow you to cacht the max allow growth
for a tablespace or the next file to number in a series.
|
|
|
|
|
|
|
|
[befsam18:CUSTCPD]:/home/oracle> lstd
Repartition following type :
---------------------------
CONTROL FILES : 2
REDO LOGS : 4
DATA FILES : 40
---------------------------
List of object files : 41
---------------------------
Size Max Next
Datafile/redo/control (meg) Type id Av Size (meg) Tablespace
==============================================================================================================
Control file
/u11/oradata/CUSTCPD/control01.ctl Control file
/u12/oradata/CUSTCPD/control02.ctl Control file
/u11/oradata/CUSTCPD/redo01.log 25 Redo gr:1 c
/u12/oradata/CUSTCPD/redo02.log 25 Redo gr:2 i
/u11/oradata/CUSTCPDredo03.log 25 Redo gr:3 i
/u12/oradata/CUSTCPD/redo04.log 25 Redo gr:4 i
/u12/oradata/CUSTCPD/qcodata01.dbf 300 Dbf id:23 n 0 0 QCODATA
/u12/oradata/CUSTCPD/qcoidx01.dbf 200 Dbf id:24 n 0 0 QCOIDX
/u11/oradata/CUSTCPD/stg_data01.dbf 2000 Dbf id:6 n 0 0 STG_DATA
/u11/oradata/CUSTCPD/stg_data02.dbf 2000 Dbf id:12 n 0 0
/u11/oradata/CUSTCPD/stg_data03.dbf 2000 Dbf id:13 n 0 0
/u11/oradata/CUSTCPD/stg_data04.dbf 2000 Dbf id:14 n 0 0
/u11/oradata/CUSTCPD/stg_data05.dbf 2000 Dbf id:15 n 0 0
/u11/oradata/CUSTCPD/stg_data06.dbf 2000 Dbf id:16 n 0 0
/u11/oradata/CUSTCPD/stg_ndx01.dbf 2000 Dbf id:17 n 0 0 STG_NDX
/u11/oradata/CUSTCPD/stg_ndx02.dbf 2000 Dbf id:18 n 0 0
/u11/oradata/CUSTCPD/stg_ndx03.dbf 2000 Dbf id:19 n 0 0
/u11/oradata/CUSTCPD/system01.dbf 325 Dbf id:1 y 32768 1 SYSTEM
/u11/oradata/CUSTCPD/tools01.dbf 330 Dbf id:3 y 500 10 TOOLS
/u12/oradata/CUSTCPD/cli_data01.dbf 2000 Dbf id:5 y 4000 10 TXP_DATA
/u12/oradata/CUSTCPD/cli_data02.dbf 4000 Dbf id:39 n 0 0
/u12/oradata/CUSTCPD/cli_data03.dbf 4000 Dbf id:40 n 0 0
/u12/oradata/CUSTCPD/cli_data04.dbf 4000 Dbf id:44 n 0 0
/u12/oradata/CUSTCPD/cli_data05.dbf 2000 Dbf id:47 y 4000 10
/u12/oradata/CUSTCPD/cli_data06.dbf 128 Dbf id:22 y 4000 10
/u11/oradata/CUSTCPD/cli_ndx1.dbf 3016 Dbf id:4 y 4000 8 TXP_NDX
/u11/oradata/CUSTCPD/clindx2.dbf 3408 Dbf id:45 y 4000 8
/u12/oradata/CUSTCPD/clindx3.dbf 3224 Dbf id:46 y 4000 8
/u12/oradata/CUSTCPD/clindx4.dbf 2000 Dbf id:20 y 4000 10
/u12/oradata/CUSTCPD/clindx5.dbf 2240 Dbf id:21 y 4000 10
/u11/oradata/CUSTCPD/undotbs01.dbf 2200 Dbf id:2 n 0 0 UNDOTBS
/u11/oradata/CUSTCPD/undotbs02.dbf 2200 Dbf id:7 n 0 0
/u11/oradata/CUSTCPD/undotbs03.dbf 200 Dbf id:8 n 0 0
/u11/oradata/CUSTCPD/undotbs04.dbf 500 Dbf id:9 n 0 0
/u11/oradata/CUSTCPD/undotbs05.dbf 500 Dbf id:10 n 0 0
/u11/oradata/CUSTCPD/undotbs06.dbf 500 Dbf id:11 n 0 0
/u12/oradata/CUSTCPD/undotbs07.dbf 200 Dbf id:58 n 0 0
/u11/oradata/CUSTCPD/temp03.dbf 100 Tmp id:3 y 4000 100 TEMP
/u12/oradata/CUSTCPD/temp02.dbf 4000 Tmp id:2 y 4000 100
/u12/oradata/CUSTCPD/tempcli01.dbf 4000 Tmp id:1 n 0 0
Total of object files : 41
---------------------------
The detail of the repartition of disk may be found
on /export/TAR/oracle/scripts/smenu/tmp in the file : disk_CUSTCPD.txt
[asdb:/opt/smenu/scripts]> lstd -s
alter database datafile '/home/oracle/oradata/asdb/undotbs01.dbf' resize 181m;
alter database datafile '/home/oracle/oradata/asdb/sysaux01.dbf' resize 501m;
alter database datafile '/home/oracle/oradata/asdb/users01.dbf' resize 1m;
alter database datafile '/home/oracle/oradata/asdb/dcm.dbf' resize 181m;
alter database datafile '/home/oracle/oradata/asdb/portal.dbf' resize 101m;
alter database datafile '/home/oracle/oradata/asdb/ptldoc.dbf' resize 1m;
alter database datafile '/home/oracle/oradata/asdb/ptlidx.dbf' resize 41m;
alter database datafile '/home/oracle/oradata/asdb/ptllog.dbf' resize 1m;
alter database datafile '/home/oracle/oradata/asdb/uddisys01.dbf' resize 21m;
alter database datafile '/home/oracle/oradata/asdb/b2b_dt.dbf' resize 61m;
alter database datafile '/home/oracle/oradata/asdb/b2b_rt.dbf' resize 41m;
alter database datafile '/home/oracle/oradata/asdb/b2b_idx.dbf' resize 21m;
alter database datafile '/home/oracle/oradata/asdb/b2b_lob.dbf' resize 21m;
|
|
|
|
|
| Top |
|
|
|
| List db links |
Very usefull to see what dblink exists in which shema the password of the db link if you need to save
it for future recreate.
|
|
|
|
|
|
|
|
[befsam18:POLDEV]:/export/home/oracle> dblk
MACHINE befsam18 - ORACLE_SID : POLDEV Page 1
Date - Thursday 13th October 2005 15:01:38
Username - SYS
List of Database links
DbLink DbLink DbLink DbLink
Owner Name User Passwd Host
---------- ------------------------------------------- -------------------- ------------ ----------------
GVC GVCPDPDEV GVC GVC PDPDEV
PUBLIC LINK_ROEGTA VARMOND VARMOND ROEGTA
PUBLIC LINK_OMUS AMOR_USER AMOR_USER ERHDEV
UNIT_RIKEL LNK_D22 UNITTEST UNITTEST AMORD22
|
|
|
|
|
| Top |
|
|
|
| Space Quota: |
Note often used but usefull when you need to check if a user has enough quota
befsma18:/home/oracle> quot
MACHINE befsam18 - ORACLE_SID : CUSTWBT2 Page: 1
Date - Thursday 13th October 2005 13:10:25
Username - SYS
Display Quota per user
. Quota -1 is unlimited
User Tablespace BYTES BLOCKS QUOTA
-------------------- -------------------- ---------- ---------- ----------
CUSTCOMMON CUSTDATA 0 0 -1
CUSTCOMMON 0 0 -1
CUSTLOG CUSTDATA 851968 104 -1
CUSTIDX 0 0 -1
CUSTTESTDATA CUSTDATA 8388608 1024 -1
CUSTIDX 589824 72 -1
CUSTWEB CUSTIDX 2463301632 300696 -1
CUSTDATA 4019257344 490632 -1
HST50 CUSTIDX 589824 72 -1
CUSTDATA 655360 80 -1
MISTEST CUSTDATA 196608 24 -1
SHARED CUSTDATA 983040 120 -1
WBD2_SHARED TEMP 0 0 -1
CUSTDATA 983040 120 -1
|
|
|
|
|
| Top |
|
|
|
| Show tablespace
default values: |
fsi
MACHINE s05096 - ORACLE_SID : POLDEV Page: 1
Date - Tuesday 11th October 2005 11:53:48
Username - SYS
Tablespace default values
Initial Next Min Pct Auto Extent Alloc.
Tablespace Name Extent(k) Extent(k) Ext. Increase Status Type Ext Manag. Type.
----------------- ---------- ---------- ------ -------- -------- ------------ ---- ---------- --------
DATA01 64 1 ONLINE PERMANENT N LOCAL SYSTEM
DATA02 64 1 ONLINE PERMANENT N LOCAL SYSTEM
DATA03 64 1 ONLINE PERMANENT N LOCAL SYSTEM
SYSTEM 16 16 1 50 ONLINE PERMANENT Y DICTIONARY USER
TEMP 1,024 1,024 1 0 ONLINE TEMPORARY N LOCAL UNIFORM
UNDOTBS 64 1 ONLINE UNDO Y LOCAL SYSTEM
|
|
|
|
|
| Top |
|
|
|
| Statistics : We
have a special page just for the 'sta' command. The help show how
extensive is this, but find here
the full story. |
sta -h
Gather statistics utility
This script output on the screen the gather statistics statement. You can execute it if you add -x
sta -u <OWNER> -t <TABLE> [-p <Percent>] [-s <stattab>] [-o <stat owner>] [-n <statid>]
-part [partname] -col <col_name> ... -col <col_name> -c -cl -cp -x
sta -u <OWNER> -i <INDEX> [-p <Percent>] [-s <stattab>] [-o <stat owner>] [-n <statid>]
-part [partname] -cp -cl -x
sta -u <OWNER> -m [-s <stat table>] [-b <TABLESPACE>]
sta -u <OWNER> -l [-s <stattab>]
sta -e -s <stattab> [-t <TABLE>] [-i <INDEX>] [-u <OWNER>] -c
sta -e -u <OWNER> -y
sta -a -s <stattab> [-t <TABLE>] [-i <INDEX>] [-u <OWNER>] -c
sta -del -s <stattab> [-t <TABLE>] [-i <INDEX>] [-u <OWNER>] -c
-a : import stats from <stattab> into schema -u <OWNER> or into table -t <TABLE> or -i <INDEX> -n <statid>
-c : Set cascade=TRUE to gather statistics also on the indexes for this table. Index statistics gathering is
not parallelized. This option is equivalent to running the GATHER_INDEX_STATS procedure on each of the
table's indexes. (default is FALSE). This parameter is only for table
-d : degree (default to 2x cpu)
-del : delete stats from <stattab> and schema -u <OWNER> -n <statid>
-t <TABLE> -part [partname] -cp -cl -ci
-cp : delete cascade to partitions if partname is NULL
-cl : delete cascade to columns
-ci : delete cascade to indexes
-i <INDEX> -part [partname] -cp
-g : granularity, values are : DEFAULT Gather global- and partition-level statistics
PARTITION Gather partition-level statistics.
SUBPARTITION Gather subpartition-level statistics.
GLOBAL Gather global statistics
(default) * ALL Gather all (subpartition, partition, and global) statistics
-e : export stats to <stattab> from schema -u <OWNER>, from table -t <TABLE> or from index -i <INDEX>
-i : the index
-l : list existing stat table and statid. With -s <stattab>, gives details per type for each object
-n : statid
-m : Create stat table given by -s <table stat>
-b <TABLESPACE> create the stat table in TABLE_SPACE otherwise it created in user default tbs
-o : <stat owner> can be different than the objects analyzed
-p : Percent sample on each objects (default to 5%)
-part: Partition name
-s : <stattab> Table that will hold stat of the table. (default is sm_stattab)
-t : the table
-col <columns name> ..... -col <columns name> gather stats only for columns name list
-u : the table(s) or index(es) owner
-v : show execution
-x : execute the output of this scripts
-y : export or import will do the system stats
Note : sta -u <user> -l -s <stattab> give types per object.
Use tbl [-u <user>] -t <table> -s to see stats on column for a table
|
|
|
|
|
| Top |
|
|
|
| ros |
List system and objects privileges for a given user. If user
is ommited, list all users/privileges. |
|
[befsam18:POLDEV]:/export/home/oracle> ros STRMADMIN
MACHINE befsam18 - ORACLE_SID : POLDEV Page: 1
Date - Wednesday 30th November 2005 10:58:28
Username - SYS
List System Privilege(s) for User STRMADMIN
. Type: ros to limit to one user
Default Temporary
Username Tablespace Tablespace Profile Role (admin)
----------------------- --------------------- --------------- ------------ -----------------------
STRMADMIN TBS_STREAM TBS_TEMP_STREAM DEFAULT CREATE RULE-
CREATE RULE SET-
UNLIMITED TABLESPACE-
OBJECT_TYPE Username Owner Table Column Privilege Admin
------------------ ---------------- ---------------- -------------------- ------- -------------- ----
TABLE STRMADMIN POL SHARED_CUSTOMERS ALTER NO
TABLE STRMADMIN POL SHARED_CUSTOMERS DEBUG NO
TABLE STRMADMIN POL SHARED_CUSTOMERS DELETE NO
TABLE STRMADMIN POL SHARED_CUSTOMERS FLASHBACK NO
TABLE STRMADMIN POL SHARED_CUSTOMERS INDEX NO
TABLE STRMADMIN POL SHARED_CUSTOMERS INSERT NO
TABLE STRMADMIN POL SHARED_CUSTOMERS QUERY REWRITE NO
TABLE STRMADMIN POL SHARED_CUSTOMERS REFERENCES NO
TABLE STRMADMIN POL SHARED_CUSTOMERS SELECT NO
TABLE STRMADMIN POL SHARED_CUSTOMERS UPDATE NO
TABLE STRMADMIN POL SHARED_CONTAINERS ALTER NO
TABLE STRMADMIN POL SHARED_CONTAINERS DEBUG NO
TABLE STRMADMIN POL SHARED_CONTAINERS DELETE NO
TABLE STRMADMIN POL SHARED_CONTAINERS FLASHBACK NO
TABLE STRMADMIN POL SHARED_CONTAINERS INDEX NO
TABLE STRMADMIN POL SHARED_CONTAINERS INSERT NO
TABLE STRMADMIN POL SHARED_CONTAINERS ON COMMIT REFR NO
ESH
TABLE STRMADMIN POL SHARED_CONTAINERS QUERY REWRITE NO
TABLE STRMADMIN POL SHARED_CONTAINERS REFERENCES NO
TABLE STRMADMIN POL SHARED_CONTAINERS SELECT NO
TABLE STRMADMIN POL SHARED_CONTAINERS UPDATE NO
VIEW STRMADMIN SYS DBA_APPLY_ERROR SELECT NO
PACKAGE STRMADMIN SYS DBMS_APPLY_ADM EXECUTE NO
PACKAGE STRMADMIN SYS DBMS_AQADM EXECUTE NO
PACKAGE STRMADMIN SYS DBMS_CAPTURE_ADM EXECUTE NO
PACKAGE STRMADMIN SYS DBMS_FLASHBACK EXECUTE NO
PACKAGE STRMADMIN SYS DBMS_PROPAGATION_ADM EXECUTE NO
PACKAGE STRMADMIN SYS DBMS_STREAMS_ADM EXECUTE NO
|
|
|
|
|
| Top |
|
Show source code
|
This is an easy and fast way to check
and see the codes behind any views or procedure. Especially usefule
while you are are reviewing an sql with views.
|
/opt/smenu>
src dba_tables
select u.name, o.name, decode(bitand(t.property,2151678048), 0,
ts.name, null),
decode(bitand(t.property, 1024),
0, null, co.name),
decode((bitand(t.property,
512)+bitand(t.flags, 536870912)),
0, null, co.name),
decode(bitand(t.property, 32+64),
0, mod(t.pctfree$, 100), 64, 0, null),
decode(bitand(ts.flags, 32), 32,
to_number(NULL),
decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0, null)),
decode(bitand(t.property, 32), 0,
t.initrans, null),
decode(bitand(t.property, 32), 0,
t.maxtrans, null),
s.iniexts * ts.blocksize,
decode(bitand(ts.flags, 3), 1,
to_number(NULL),
s.extsize * ts.blocksize),
s.minexts, s.maxexts,
decode(bitand(ts.flags, 3), 1,
to_number(NULL),
s.extpct),
decode(bitand(ts.flags, 32), 32,
to_number(NULL),
decode(bitand(o.flags,
2), 2, 1, decode(s.lists, 0, 1, s.lists))),
decode(bitand(ts.flags, 32), 32,
to_number(NULL),
decode(bitand(o.flags,
2), 2, 1, decode(s.groups, 0, 1, s.groups))),
decode(bitand(t.property, 32+64),
0,
decode(bitand(t.flags, 32), 0, 'YES', 'NO'), null),
decode(bitand(t.flags,1), 0, 'Y',
1, 'N', '?'),
t.rowcnt,
decode(bitand(t.property, 64), 0,
t.blkcnt, null),
decode(bitand(t.property, 64), 0,
t.empcnt, null),
t.avgspc, t.chncnt, t.avgrln,
t.avgspc_flb,
decode(bitand(t.property, 64), 0,
t.flbcnt, null),
lpad(decode(t.degree, 32767,
'DEFAULT', nvl(t.degree,1)),10),
lpad(decode(t.instances, 32767,
'DEFAULT', nvl(t.instances,1)),10),
lpad(decode(bitand(t.flags, 8), 8,
'Y', 'N'),5),
decode(bitand(t.flags, 6), 0,
'ENABLED', 'DISABLED'),
t.samplesize, t.analyzetime,
decode(bitand(t.property, 32), 32,
'YES', 'NO'),
decode(bitand(t.property, 64), 64,
'IOT',
decode(bitand(t.property, 512), 512, 'IOT_OVERFLOW',
decode(bitand(t.flags, 536870912), 536870912, 'IOT_MAPPING', null))),
decode(bitand(o.flags, 2), 0, 'N',
2, 'Y', 'N'),
decode(bitand(o.flags, 16), 0,
'N', 16, 'Y', 'N'),
decode(bitand(t.property, 8192),
8192, 'YES',
decode(bitand(t.property, 1), 0, 'NO', 'YES')),
decode(bitand(o.flags, 2), 2,
'DEFAULT',
decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)),
decode(bitand(t.flags, 131072),
131072, 'ENABLED', 'DISABLED'),
decode(bitand(t.flags, 512), 0,
'NO', 'YES'),
decode(bitand(t.flags, 256), 0,
'NO', 'YES'),
decode(bitand(o.flags, 2), 0, NULL,
decode(bitand(t.property, 8388608), 8388608,
'SYS$SESSION', 'SYS$TRANSACTION')),
decode(bitand(t.flags, 1024),
1024, 'ENABLED', 'DISABLED'),
decode(bitand(o.flags, 2), 2, 'NO',
decode(bitand(t.property, 2147483648), 2147483648, 'NO',
decode(ksppcv.ksppstvl, 'TRUE', 'YES', 'NO'))),
decode(bitand(t.property, 1024),
0, null, cu.name),
decode(bitand(t.flags, 8388608),
8388608, 'ENABLED', 'DISABLED'),
decode(bitand(t.property, 32), 32,
null,
decode(bitand(s.spare1, 2048), 2048, 'ENABLED', 'DISABLED')),
decode(bitand(o.flags, 128), 128,
'YES', 'NO')
from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t,
sys.obj$ o,
sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv,
x$ksppi ksppi
where o.owner# = u.user#
and o.obj# = t.obj#
and bitand(t.property, 1) = 0
and bitand(o.flags, 128) = 0
and t.bobj# = co.obj# (+)
and t.ts# = ts.ts#
and t.file# = s.file# (+)
and t.block# = s.block# (+)
and t.ts# = s.ts# (+)
and t.dataobj# = cx.obj# (+)
and cx.owner# = cu.user# (+)
and ksppi.indx = ksppcv.indx
and ksppi.ksppinm = '_dml_monitoring_enabled'
|
|
| Top |
Transaction
|
/opt/smenu> tx
Date - Friday 28th April 2006 19:31:59
Username - SYS
Report - Show transaction info
Type tx -h for help
Running Session Transact
SEGMENT_NAME Start time time(s) SID Status XID Status LOG_IO PHY_IO CR_GET CR_CHANGE
------------ -------------- ------- ------ -------- ---------------- -------- ---------- ---------- ---------- ----------
_SYSSMU3$ 04-28 07:10:19 0 405 SNIPED 0003002F001F308A IDLE 5 35797 38446 0
_SYSSMU17$ 04-28 08:13:33 0 377 INACTIVE 001100040009383D ACTIVE 7 24767 131392 0
_SYSSMU31$ 04-28 08:16:20 19919 332 ACTIVE 001F002B00003DEB ACTIVE 26031585 254181 1773102 75972
_SYSSMU38$ 04-28 09:31:29 2819 418 ACTIVE 002600310000D42A ACTIVE 55 2721 111840 0
_SYSSMU2$ 04-28 09:31:59 4319 388 ACTIVE 00020036001C2186 ACTIVE 537 46 84 0
_SYSSMU1$ 04-28 08:41:47 0 336 SNIPED 00010020002040AA INACTIVE 3 4388 5332 0
_SYSSMU39$ 04-28 09:31:14 1040 401 ACTIVE 002700220000A2B1 ACTIVE 34826 5033 47325 0
_SYSSMU42$ 04-27 18:05:48 0 339 SNIPED 002A0022000042EA INACTIVE 3 1577 68532 0
/opt/smenu> tx -h
tx -s -p -n -h -purge <TRANS_ID>
-s : List SCN instead of xid
-n : Lists all incoming (from remote client) and outgoing (to remote server)
in-doubt distributed transactions(DBA_2PC_NEIGHBORS)
-h : This help
-p : Lists all in-doubt distributed transactions (DBA_2PC_PENDING)
You get the TRANS_ID for purge this option -p
-purge : To manually remove an entry from the data dictionary
|
|
|
|
|
|
|
|
|
| Top |
|
|
|
|
|
|
|
Response time breakdown
|
|
|
|
|
|
|
|
/opt/oracle > cpu
MACHINE
beefsam18 - ORACLE_SID :
POLDEV
Page: 1
Date
- Wednesday 03rd May
2006 21:50:49
Username
- SYS
Response time
breakdown
MAJOR
MINOR
WAIT_EVENT
SECONDS PCT
--------
------------- ---------------------------------------- ---------- ------
CPU time
parsing
n/a
0 .00%
reloads
n/a
0 .00%
execution
n/a
15 .00%
disk I/O normal
I/O db file sequential
read
243456 9.64%
full scans db file scattered
read
23875 .95%
direct I/O direct path read
temp
756 .03%
direct path write
temp
297 .01%
direct path
read
68 .00%
direct path
write
0 .00%
other I/O control file sequential
read
408 .02%
db file parallel
read
184 .01%
db file single
write
11 .00%
control file parallel
write
9 .00%
control file single
write
7 .00%
control file
heartbeat
4 .00%
log file sequential
read
0 .00%
waits
DBWn writes rdbms ipc
reply
22 .00%
local write
wait
11 .00%
log file switch (checkpoint
incomplete)
0 .00%
LGWR writes log buffer
space
1059 .04%
log file switch
completion
315 .01%
ARCn writes log file switch (archiving
needed)
0 .00%
other locks PX Deq Credit: send
blkd
238 .01%
PX Deq Credit: need
buffer
29 .00%
buffer busy
waits
16 .00%
library cache
pin
11 .00%
latch
free
7 .00%
sort segment
request
5 .00%
library cache load
lock
4 .00%
PX qref
latch
2 .00%
row cache
lock
1 .00%
library cache
lock
1 .00%
undo segment
extension
0 .00%
buffer
deadlock
0 .00%
latency
commits log file
sync
475 .02%
network SQL*Net more data from
dblink
8542 .34%
SQL*Net more data to
client
43 .00%
SQL*Net more data from
client
23 .00%
SQL*Net break/reset to
client
9 .00%
SQL*Net message to
dblink
6 .00%
SQL*Net message to
client
2 .00%
SQL*Net more data to
dblink
0 .00%
SQL*Net break/reset to
dblink
0 .00%
process ctl inactive
session
59 .00%
process
startup
16 .00%
PX Deq: Signal
ACK
8 .00%
PX Deq: Msg
Fragment
5 .00%
PX Deq: Join
ACK
1 .00%
misc i/o slave
wait
1592069 63.03%
pmon
timer
322666 12.78%
wakeup time
manager
276705 10.96%
|
|
|
|
|
|
|
|
|
| Last
update : 03-May-2006
bpolarsk@yahoo.com |
|
|