Sunday, June 29, 2014
Oracle Performance Tuning Script - List Of Users Currently Waiting
Oracle Performance Tuning Script - List Of Users Currently Waiting
col username format a12
col sid format 9999
col state format a15
col event format a50
col wait_time format 99999999
set pagesize 100
set linesize 120
select s.sid, s.username, se.event, se.state, se.wait_time
from v$session s, v$session_wait se
where s.sid=se.sid
and se.event not like 'SQL*Net%'
and se.event not like '%rdbms%'
and s.username is not null
order by se.wait_time;
Oracle Performance Tuning Script - Top Wait Events Since Instance Startup
Oracle Performance Tuning Script - Top Wait Events Since Instance Startup
col event format a60
select event, total_waits, time_waited
from v$system_event e, v$event_name n
where n.event_id = e.event_id
and n.wait_class !='Idle'
and n.wait_class = (select wait_class from v$session_wait_class
where wait_class !='Idle'
group by wait_class having
sum(time_waited) = (select max(sum(time_waited)) from v$session_wait_class
where wait_class !='Idle'
group by (wait_class)))
order by 3;
Oracle Performance Tuning Script - Top Recent Wait Events
col EVENT format a60
select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history
where active_session_history.event is not null
group by active_session_history.event
order by 2 desc)
where rownum < 6
/
Friday, June 27, 2014
Tuning script for Oracle Database 1
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
SELECT *
FROM v$database;
PROMPT
DECLARE
v_value NUMBER;
FUNCTION Format(p_value IN NUMBER)
RETURN VARCHAR2 IS
BEGIN
RETURN LPad(To_Char(Round(p_value,2),'990.00') || '%',8,' ') || ' ';
END;
BEGIN
-- --------------------------
-- Dictionary Cache Hit Ratio
-- --------------------------
SELECT (1 - (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100
INTO v_value
FROM v$rowcache;
DBMS_Output.Put('Dictionary Cache Hit Ratio : ' || Format(v_value));
IF v_value < 90 THEN
DBMS_Output.Put_Line('Increase SHARED_POOL_SIZE parameter to bring value above 90%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;
-- -----------------------
-- Library Cache Hit Ratio
-- -----------------------
SELECT (1 -(Sum(reloads)/(Sum(pins) + Sum(reloads)))) * 100
INTO v_value
FROM v$librarycache;
DBMS_Output.Put('Library Cache Hit Ratio : ' || Format(v_value));
IF v_value < 99 THEN
DBMS_Output.Put_Line('Increase SHARED_POOL_SIZE parameter to bring value above 99%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;
-- -------------------------------
-- DB Block Buffer Cache Hit Ratio
-- -------------------------------
SELECT (1 - (phys.value / (db.value + cons.value))) * 100
INTO v_value
FROM v$sysstat phys,
v$sysstat db,
v$sysstat cons
WHERE phys.name = 'physical reads'
AND db.name = 'db block gets'
AND cons.name = 'consistent gets';
DBMS_Output.Put('DB Block Buffer Cache Hit Ratio : ' || Format(v_value));
IF v_value < 89 THEN
DBMS_Output.Put_Line('Increase DB_BLOCK_BUFFERS parameter to bring value above 89%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;
-- ---------------
-- Latch Hit Ratio
-- ---------------
SELECT (1 - (Sum(misses) / Sum(gets))) * 100
INTO v_value
FROM v$latch;
DBMS_Output.Put('Latch Hit Ratio : ' || Format(v_value));
IF v_value < 98 THEN
DBMS_Output.Put_Line('Increase number of latches to bring the value above 98%');
ELSE
DBMS_Output.Put_Line('Value acceptable.');
END IF;
-- -----------------------
-- Disk Sort Ratio
-- -----------------------
SELECT (disk.value/mem.value) * 100
INTO v_value
FROM v$sysstat disk,
v$sysstat mem
WHERE disk.name = 'sorts (disk)'
AND mem.name = 'sorts (memory)';
DBMS_Output.Put('Disk Sort Ratio : ' || Format(v_value));
IF v_value > 5 THEN
DBMS_Output.Put_Line('Increase SORT_AREA_SIZE parameter to bring value below 5%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;
-- ----------------------
-- Rollback Segment Waits
-- ----------------------
SELECT (Sum(waits) / Sum(gets)) * 100
INTO v_value
FROM v$rollstat;
DBMS_Output.Put('Rollback Segment Waits : ' || Format(v_value));
IF v_value > 5 THEN
DBMS_Output.Put_Line('Increase number of Rollback Segments to bring the value below 5%');
ELSE
DBMS_Output.Put_Line('Value acceptable.');
END IF;
-- -------------------
-- Dispatcher Workload
-- -------------------
SELECT NVL((Sum(busy) / (Sum(busy) + Sum(idle))) * 100,0)
INTO v_value
FROM v$dispatcher;
DBMS_Output.Put('Dispatcher Workload : ' || Format(v_value));
IF v_value > 50 THEN
DBMS_Output.Put_Line('Increase MTS_DISPATCHERS to bring the value below 50%');
ELSE
DBMS_Output.Put_Line('Value acceptable.');
END IF;
END;
/
PROMPT
SET FEEDBACK ON
Monday, June 16, 2014
Golden Gate step by step Implementation notes
Step By Step Golden Gate Configuration
Step By Step Golden Gate Configuration:
GoldenGate enables us to extract and replicate data across a variety of topologies as shown the diagram below as well as the exchange and manipulation of data at the transactional level between a variety of database platforms like Oracle, DB2, SQL Server, Ingres, MySQL etc.
It can support a number of different business requirements like:
Business Continuity and High Availability
Data migrations and upgrades
Decision Support Systems and Data Warehousing
Data integration and consolidation
Manager
The Manager process must be running on both the source as well as target systems before the Extract or Replicat process can be started and performs a number of functions including monitoring and starting other GoldenGate processes, managing the trail files and also reporting.
Extract
The Extract process runs on the source system and is the data capture mechanism of GoldenGate. It can be configured both for initial loading of the source data as well as to synchronize the changed data on the source with the target. This can be configured to also propagate any DDL changes on those databases where DDL change support is available.
Replicat
The Replicat process runs on the target system and reads transactional data changes as well as DDL changes and replicates then to the target database. Like the Extract process, the Replicat process can also be configured for Initial Load as well as Change Synchronization.
Collector
The Collector is a background process which runs on the target system and is started automatically by the Manager (Dynamic Collector) or it can be configured to stsrt manually (Static Collector). It receives extracted data changes that are sent via TCP/IP and writes then to the trail files from where they are processed by the Replicat process.
Trails
Trails are series of files that GoldenGate temporarily stores on disks and these files are written to and read from by the Extract and Replicat processes as the case may be. Depending on the configuration chosen, these trail files can exist on the source as well as on the target systems. If it exists on the local system, it will be known an Extract Trail or as an Remote Trail if it exists on the target system.
Data Pumps
Data Pumps are secondary extract mechanisms which exist in the source configuration. This is optional component and if Data Pump is not used then Extract sends data via TCP/IP to the remote trail on the target. When Data Pump is configured, the Primary Extract process will write to the Local Trail and then this trail is read by the Data Pump and data is sent over the network to Remote Trails on the target system.
In the absence of Data Pump, the data that the Extract process extracts resides in memory alone and there is no storage of this data anywhere on the source system. In case of network of target failures, there could be cases where the primary extract process can abort or abend. Data Pump can also be useful in those cases where we are doing complex filtering and transformation of data as well as when we are consolidating data from many sources to a central target.
Data source
When processing transactional data changes, the Extract process can obtain data directly from the database transaction logs (Oracle, DB2, SQL Server, MySQL etc) or from a GoldenGate Vendor Access Module (VAM) where the database vendor (for example Teradata) will provide the required components that will be used by Extract to extract the data changes.
Groups
To differentiate between the number of different Extract and Replicat groups which can potentially co-exist on a system, we can define processing groups. For instance, if we want to replicate different sets of data in parallel, we can create two Replicat groups.
A processing group consists of a process which could be either a Extract or Replicat process, a corresponding parameter file, checkpoint file or checkpoint table (for Replicat) and other files which could be associated with the process.
Steps :-
1. Golden Gate Setup at Source and Target
2. Configure Source :-
A. Configure Schema
B. Configure Manager
C. Configure Extract
D. Add Trandata
E. Configure Pump
3. Configure Target
A. Configure Manager
B. Create Check point table
C. Configure Replicat
Database And Machine Info
SOURCE Database
Oracle Release: Oracle11g Release 2 – (11.2.0.1.0)
Machine Name: PRODDB
Operating System: Red Hat Linux 5
Oracle SID: PROD
Replication Schema GGS_OWNER
TARGET Database
Oracle Release: Oracle11g Release 2 – (11.2.0.1.0)
Machine Name: GOLDDB
Operating System: Red Hat Linux 5
Oracle SID: TARGET
Replication Schema GGS_OWNER
¨ Golden Gate Setup at Source and Target
1. mkdir /mount1/stfolder/
1. $unzip ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip
Archive: ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip
inflating: fbo_ggs_Linux_x86_ora11g_32bit.tar
1. $ tar -xf fbo_ggs_Linux_x86_ora11g_32bit.tar
1. export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/oradata/gg
1. [oracle@PRODDB soft]$ ls
bcpfmt.tpl ddl_ora10upCommon.sql defgen freeBSD.txt libxml2.txt role_setup.sql
bcrypt.txt ddl_ora11.sql demo_more_ora_create.sql ggcmd logdump sequence.sql
cfg ddl_ora9.sql demo_more_ora_insert.sql ggMessage.dat marker_remove.sql server
chkpt_ora_create.sql ddl_pin.sql demo_ora_create.sql ggsci marker_setup.sql sqlldr.tpl
cobgen ddl_purgeRecyclebin.sql demo_ora_insert.sql help.txt marker_status.sql tcperrs
convchk ddl_remove.sql demo_ora_lob_create.sql jagent.sh mgr ucharset.h
db2cntl.tpl ddl_session1.sql demo_ora_misc.sql keygen notices.txt ulg.sql
ddl_cleartrace.sql ddl_session.sql demo_ora_pk_befores_create.sql libantlr3c.so oggerr UserExitExamples
ddlcob ddl_setup.sql demo_ora_pk_befores_insert.sql libdb-5.2.so params.sql usrdecs.h
ddl_ddl2file.sql ddl_status.sql demo_ora_pk_befores_updates.sql libgglog.so prvtclkm.plb zlib.txt
ddl_disable.sql ddl_staymetadata_off.sql dirjar libggrepo.so pw_agent_util.sh
ddl_enable.sql ddl_staymetadata_on.sql dirprm libicudata.so.38 remove_seq.sql
ddl_filter.sql ddl_tracelevel.sql emsclnt libicui18n.so.38 replicat
ddl_nopurgeRecyclebin.sql ddl_trace_off.sql extract libicuuc.so.38 retrace
ddl_ora10.sql ddl_trace_on.sql fbo_ggs_Linux_x86_ora11g_32bit.tar libxerces-c.so.28 reverse
1. [oracle@PRODDB soft]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
— YOU WILL GET GG COMMAND PROMPT
1. GGSCI (PRODDB) 1> create subdirs
Creating subdirectories under current directory /mount1/stfolder
Parameter files /mount1/stfolder/dirprm: already exists
Report files /mount1/stfolder/dirrpt: created
Checkpoint files /mount1/stfolder/dirchk: created
Process status files /mount1/stfolder/dirpcs: created
SQL script files /mount1/stfolder/dirsql: created
Database definitions files /mount1/stfolder/dirdef: created
Extract data files /mount1/stfolder/dirdat: created
Temporary files /mount1/stfolder/dirtmp: created
Stdout files /mount1/stfolder/dirout: created
1. Issue the following command to exit GGSCI.
GGSCI (PRODDB) 2> exit
Configure Schema
create tablespace
create user
Give Grants
grant connect, resource to ggs_owner;
grant select any dictionary, select any table to ggs_owner;
grant create table to ggs_owner;
grant flashback any table to ggs_owner;
grant execute on dbms_flashback to ggs_owner;
grant execute on utl_file to ggs_owner;
grant create any table to ggs_owner;
grant insert any table to ggs_owner;
grant update any table to ggs_owner;
grant delete any table to ggs_owner;
grant drop any table to ggs_owner;
Change Parameter as per requirement
UNDO_MANAGEMENT=AUTO
UNDO_RETENTION=86400
Script :
SQL> startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 247466368 bytes
Database Buffers 167772160 bytes
Redo Buffers 6094848 bytes
Database mounted.
Database opened.
SQL>
SQL> alter system set recyclebin=off scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 247466368 bytes
Database Buffers 167772160 bytes
Redo Buffers 6094848 bytes
SQL> alter database archivelog;
Database altered.
SQL> alter database Open;
Database altered.
SQL> create tablespace ggs_data datafile ‘/u01/ggs_data01.dbf’ size 200m;
tablespace created
SQL> create user ggs_owner identified by ggs_owner default tablespace ggs_data temporary tablespace temp;
User created.
SQL> grant connect, resource to ggs_owner;
grant select any dictionary, select any table to ggs_owner;
grant create table to ggs_owner;
grant flashback any table to ggs_owner;
grant execute on dbms_flashback to ggs_owner;
grant execute on utl_file to ggs_owner;
grant create any table to ggs_owner;
grant insert any table to ggs_owner;
grant update any table to ggs_owner;
grant delete any table to ggs_owner;
grant drop any table to ggs_owner;
grant drop any table to ggs_owner;
SQL> show parameter undo
NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> alter system set UNDO_RETENTION=86400 scope=both;
System altered.
sql> alter database add supplemental log data;
Database altered.
– bounce the database
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 251660672 bytes
Database Buffers 163577856 bytes
Redo Buffers 6094848 bytes
Database mounted.
Database opened.
INITIAL DATALOAD :-
EXPORT: @ SOURCE
$ expdp directory=db_dir dumpfile=schema_gg.dmp logfile=schema_gg.log schemas=ggtest Scp from SOURCE and TARGET
$scp –p schema_gg.dmp 172.168.10.108:/oradata
IMPORT: @ TARGET
$impdp directory=db_dir dumpfile=schema_gg.dmp logfile=schema_imp_gg.log schemas=ggtest
Execute following script at Source by connecting SYS user which will create required object for Golden Gate.
Run scripts for creating all necessary objects for support DDL replication
[oracle@PRODDB soft]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 26 05:42:29 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @marker_setup.sql
Marker setup script
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:GGS_OWNER
Marker setup table script complete, running verification script…
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGS_OWNER
MARKER TABLE
——————————-
OK
MARKER SEQUENCE
——————————-
OK
Script complete.
SQL> @ddl_setup.sql
Oracle GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication…
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:GGS_OWNER
Working, please wait …
Spooling to file ddl_setup_spool.txt
Checking for sessions that are holding locks on Oracle Golden Gate metadata tables …
Check complete.
Using GGS_OWNER as a Oracle GoldenGate schema name.
Working, please wait …
DDL replication setup script complete, running verification script…
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGS_OWNER
CLEAR_TRACE STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
CREATE_TRACE STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
TRACE_PUT_LINE STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
INITIAL_SETUP STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
DDLVERSIONSPECIFIC PACKAGE STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
DDLREPLICATION PACKAGE STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
DDLREPLICATION PACKAGE BODY STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
DDL IGNORE TABLE
———————————–
OK
DDL IGNORE LOG TABLE
———————————–
OK
DDLAUX PACKAGE STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
DDLAUX PACKAGE BODY STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
SYS.DDLCTXINFO PACKAGE STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
SYS.DDLCTXINFO PACKAGE BODY STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
DDL HISTORY TABLE
———————————–
OK
DDL HISTORY TABLE(1)
———————————–
OK
DDL DUMP TABLES
———————————–
OK
DDL DUMP COLUMNS
———————————–
OK
DDL DUMP LOG GROUPS
———————————–
OK
DDL DUMP PARTITIONS
———————————–
OK
DDL DUMP PRIMARY KEYS
———————————–
OK
DDL SEQUENCE
———————————–
OK
GGS_TEMP_COLS
———————————–
OK
GGS_TEMP_UK
———————————–
OK
DDL TRIGGER CODE STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
DDL TRIGGER INSTALL STATUS
———————————–
OK
DDL TRIGGER RUNNING STATUS
———————————–
ENABLED
STAYMETADATA IN TRIGGER
———————————–
OFF
DDL TRIGGER SQL TRACING
———————————–
0
DDL TRIGGER TRACE LEVEL
———————————–
0
LOCATION OF DDL TRACE FILE
————————————————————————————————————————
/u01/app/oracle/diag/rdbms/PROD/PROD/trace/ggs_ddl_trace.log
Analyzing installation status…
STATUS OF DDL REPLICATION
————————————————————————————————————————
SUCCESSFUL installation of DDL Replication software components
Script complete.
SQL> @role_setup.sql
GGS Role setup script
This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:GGS_OWNER
Wrote file role_setup_set.txt
PL/SQL procedure successfully completed.
Role setup script complete
Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO
where is the user assigned to the GoldenGate processes.
SQL>
SQL> grant ggs_ggsuser_role to ggs_owner;
Grant succeeded.
SQL> @ddl_enable
Trigger altered.
SQL> @ddl_pin GGS_OWNER
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Configure Source :-
1. Add Trandata
i. Create trandata statement and execute it on gg prompt
SQL> select ‘add trandata ‘||owner||’.’||object_name||’;’ from dba_objects where owner=’GGTEST’ and object_type=’TABLE’;
‘ADDTRANDATA’||OWNER||’.’||OBJECT_NAME||’;’
——————————————————————————–
add trandata GGS_OWNER.SCOTT;
add trandata GGS_OWNER.DEPT;
ii. Syntax for reference add trandata .
b. Configure Manager
i. DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
ii. info all
iii. edit params mgr
IT Jobs in Dubai
– Script :- Parameter file for MANAGER
– Manager Parameter file
PORT 7809
–Automatically start extract and pump as soon as manager starts
–AUTOSTART EXTRACT GGEC01G1
–If an abend occurs, try to restart with maximum of 20 times delaying 2 minutes between start attempts
AUTOSTART EXTRACT GGEC01G1
–,RETRIES 20 ,WAITMINUTES 2
–Delete GG trails if it is older than 3 days and there are no GG process with a checkpoint to it
PURGEOLDEXTRACTS ./DIRDAT/* USECHECKPOINTS MINKEEPHOURS 3
–Report any lag every 60 Minutes. anytimes lag exceeds 20 Minutes immediately generate a critical message
LAGREPORTMINUTES 60
LAGCRITICALMINUTES 20
iv. info all
v. dblogin USERID ggs_owner, PASSWORD ggs_owner vi. start manager
c. Configure Extract
i. Create Obey file if required and put below code into that or directly execute it on gg prompt.
IT Jobs in Dubai
– Script :- To Add Extract
–Add extract for EMP
– TARGET DATABASE
DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
DELETE GGEC01G1
ADD EXTRACT GGEC01G1, TRANLOG, BEGIN NOW
ADD EXTTRAIL /mount1/stfolder/dirdat/g1, EXTRACT GGEC01G1
–, MEGABYTES 5
ii. Create parameter file by “edit params” command and put below code in the parameter file.
1. Edit params GGEC01G1
IT Jobs in Dubai
– Script :- Parameter file for Extract
– Parameter Syntax check ( Remove after syntax Validation )
– CHECKPARAMS
EXTRACT GGEC01G1
–Source Database
USERID ggs_owner, PASSWORD ggs_owner
–Generate SQL output
–FORMATSQL
–Discard file parameters
DISCARDFILE /mount1/stfolder/dirrpt/GGEC01G1.dsc, PURGE
–Transaction log parameter
–TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT
– Local Trail File Parameters
EXTTRAIL /mount1/stfolder/dirdat/g1
–Output in SQL format
–To check the parameter syntax, uncomment the below 2 lines and comment out “WILDCARDRESOLVE DYNAMIC” parameter. start the group
–CHECKPARAMS
–NODYNAMICRESOLUTION
– Update and delete operation parameters capture full ops image
–NOCOMPRESSDELETES
–NOCOMPRESSUPDATES
–IGNORE DELETE, UPDATE, INSERT
– IGNOREDELETES
–IGNOREUPDATES
–IGNOREINSERTS
–Check parameter syntax also check the table syntax
–after correcting the syntax, remove these 2 parameters
–CHECKPARAMS
–NODYNAMICRESOLUTION
– Runtime parameters
STATOPTIONS RESETREPORTSTATS
REPORT AT 00:01
REPORTROLLOVER AT 00:01
REPORTCOUNT EVERY 1 HOUR, RATE
–Wildcard table processing parameters
WILDCARDRESOLVE DYNAMIC
DDL INCLUDE MAPPED
– GG data capture table list: EMP
TABLE GGS_OWNER.EMP;
d. Configure Pump
i. Create Obey file if required and put below code into that or directly execute it on gg prompt.
IT Jobs in Dubai
– Script :- To Add Extract
–Add extract for EMP
– TARGET DATABASE
DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
– EXTRACT PUMP
DELETE GGPC01G1
ADD EXTRACT GGPC01G1, EXTTRAILSOURCE /mount1/stfolder/dirdat/g1
ADD RMTTRAIL /mount1/stfolder/dirdat/1g, EXTRACT GGPC01G1
ii. Create parameter file by “edit params” command and put below code in the parameter file.
1. Edit params GGPC01G1
IT Jobs in Dubai
– Script :- Parameter file for Pump
– Parameter Syntax check ( Remove after syntax Validation )
– CHECKPARAMS
EXTRACT GGPC01G1
–Data Pump mode (This should be commented)
PASSTHRU
– Control Parameters
RMTHOST 10.184.93.74, MGRPORT 7809, COMPRESS
–Remote Trail File parameter
RMTTRAIL /mount1/stfolder/dirdat/1g
–To check the parameter syntax, uncomment the below 2 lines and comment out “WILDCARDRESOLVE DYNAMIC” parameter. start the group
–CHECKPARAMS
–NODYNAMICRESOLUTION
–Wildcard table processing parameters
WILDCARDRESOLVE DYNAMIC
– GG data pump table list: EMP
TABLE GGS_OWNER.EMP;
–TABLE EMP FILTER(ORANGE(1,2));
Configure Target :-
1. Edit GLOBAL parameter file by connecting gg prompt
i. edit params ./GLOBAL
GGSCHEMA ggs_owner
CHECKPOINTTABLE ggs_owner. Checkpoint
Configure Manager
i. DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
ii. info all
iii. edit params mgr
IT Jobs in Dubai
– Script :- Parameter file for MANAGER
– Manager Parameter file
PORT 7809
–Automatically start extract and pump as soon as manager starts
–AUTOSTART EXTRACT GGPC01G1
–If an abend occurs, try to restart with maximum of 20 times delaying 2 minutes between start attempts
AUTOSTART EXTRACT GGPC01G1
–,RETRIES 20 ,WAITMINUTES 2
–Delete GG trails if it is older than 3 days and there are no GG process with a checkpoint to it
PURGEOLDEXTRACTS ./DIRDAT/* USECHECKPOINTS MINKEEPHOURS 3
–Report any lag every 60 Minutes. anytimes lag exceeds 20 Minutes immediately generate a critical message
LAGREPORTMINUTES 60
LAGCRITICALMINUTES 20
iv. info all
v. dblogin USERID ggs_owner, PASSWORD ggs_owner
vi. start manager
1. Create Check point table
i. dblogin userid ggs_owner password ggs_owner
ii. add checkpointtable ggs_owner.checkpoint
Configure Replicat
i. Create Obey file if required and put below code into that or directly execute it on gg prompt.
IT Jobs in Dubai
– Script :- Replicat Obey File
– login to the database
DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
– Add replicat
DELETE GGRC01G1
ADD REPLICAT GGRC01G1, EXTTRAIL /mount1/stfolder/dirdat/1g, checkpointtable ggs_owner.checkpoint
ii. Create parameter file by “edit params” command and put below code in the parameter file.
IT Jobs in Dubai
– Script :- Parameter file for Replicat
–Following 3 options are to display SQL statements comment out BATCHSQL
–SHOWSYNTAX
–NODYNSQL
–NOBINARYCHARS
–
–NOLIST will not expand the macros in the report file
–INCLUDE the macro library
–LIST is end of NOLIST
–
REPLICAT GGRC01G1
– Handle the collisions after the initial load
HANDLECOLLISIONS
– TARGET DATABASE
–#DB_Connect()
USERID ggs_owner, PASSWORD ggs_owner
–Discard file parameters
DISCARDFILE /mount1/stfolder/dirrpt/GGEC01G1.dsc, PURGE
–
–Source and Target table parameters
ASSUMETARGETDEFS
–SOURCEDEFS C:\GG\DIRSQL\MYTABLES.SQL
–
–To check the parameter syntax, uncomment the below 2 lines and comment out “WILDCARDRESOLVE DYNAMIC” parameter.
–CHECKPARAMS
–NODYNAMICRESOLUTION
– Performance parameters for <5k bytes per row data change
– Replaced “BATCHSQL” with “BATCHSQL BATCHTRANSOPS … “
BATCHSQL BATCHTRANSOPS 10000
–Running reports parameters
–#generate_stats()
REPORTCOUNT EVERY 1 HOUR, RATE
STATOPTIONS RESETREPORTSTATS
REPORT AT 00:01
REPORTROLLOVER AT 00:01
– Wildcard table processing parameter
WILDCARDRESOLVE DYNAMIC
TRANSACTIONTIMEOUT 5 S
–ddl support
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP
–Specify table mapping —
MAP GGS_OWNER.*, TARGET GGS_OWNER.*;
MAP GGS_OWNER.EMP, TARGET GGS_OWNER.EMP;
START EXTRACT :- NEED TO BE CARRIED OUT AT SOURCE
1. START MGR
2. START EXTRACT GG*
START REPLICAT :- NEED TO BE CARRIED OUT AT TARGET
1. START MGR
2. START replicat GG*
Some more scripts
Golden Gate script
Saturday, June 14, 2014
ORA-00600: internal error code Oracle Database Crashed
ERROR:
======
Errors in file c:\oracle\click11g\diag\rdbms\click11g\click11g\trace\click11g_ora_1992.trc (incident=51767):
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [107], [57549], [57552], [], [], [], [], [], [], []
Incident details in: c:\oracle\click11g\diag\rdbms\click11g\click11g\incident\incdir_51767\click11g_ora_1992_i51767.trc
Aborting crash recovery due to error 600
Errors in file c:\oracle\click11g\diag\rdbms\click11g\click11g\trace\click11g_ora_1992.trc:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [107], [57549], [57552], [], [], [], [], [], [], []
Errors in file c:\oracle\click11g\diag\rdbms\click11g\click11g\trace\click11g_ora_1992.trc:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [107], [57549], [57552], [], [], [], [], [], [], []
ORA-600 signalled during: ALTER DATABASE OPEN...
Trace dumping is performing id=[cdmp_20100621003740]
SOLUTION:
=========
*>connect sys as sysdba*
*> startup mount*
*> alter database recover until cancel using backup controlfile; (i have ignored promtings).*
*> alter database recover continue default; (until it says “no such file or directory” or there are no more suggestions.)*
*> alter database recover cancel;*
*> alter database open resetlogs;*
Wednesday, June 11, 2014
Wednesday, June 4, 2014
obj_lock script to find in oracle 10g
SELECT a.type,
Substr(a.owner,1,30) owner,
a.sid,
Substr(a.object,1,30) object
FROM v$access a
WHERE a.owner NOT IN ('SYS','PUBLIC')
ORDER BY 1,2,3,4
/
Substr(a.owner,1,30) owner,
a.sid,
Substr(a.object,1,30) object
FROM v$access a
WHERE a.owner NOT IN ('SYS','PUBLIC')
ORDER BY 1,2,3,4
/
non_indexed_fks script to find in oracle 10g
SET SERVEROUTPUT ON
SET PAGESIZE 1000
SET LINESIZE 255
SET FEEDBACK OFF
SELECT t.table_name,
c.constraint_name,
c.table_name table2,
acc.column_name
FROM all_constraints t,
all_constraints c,
all_cons_columns acc
WHERE c.r_constraint_name = t.constraint_name
AND c.table_name = acc.table_name
AND c.constraint_name = acc.constraint_name
AND NOT EXISTS (SELECT '1'
FROM all_ind_columns aid
WHERE aid.table_name = acc.table_name
AND aid.column_name = acc.column_name)
ORDER BY c.table_name;
PROMPT
SET FEEDBACK ON
SET PAGESIZE 18
SET PAGESIZE 1000
SET LINESIZE 255
SET FEEDBACK OFF
SELECT t.table_name,
c.constraint_name,
c.table_name table2,
acc.column_name
FROM all_constraints t,
all_constraints c,
all_cons_columns acc
WHERE c.r_constraint_name = t.constraint_name
AND c.table_name = acc.table_name
AND c.constraint_name = acc.constraint_name
AND NOT EXISTS (SELECT '1'
FROM all_ind_columns aid
WHERE aid.table_name = acc.table_name
AND aid.column_name = acc.column_name)
ORDER BY c.table_name;
PROMPT
SET FEEDBACK ON
SET PAGESIZE 18
monitoring_status script to find in oracle 10g
SET VERIFY OFF
SELECT table_name, monitoring
FROM dba_tables
WHERE owner = UPPER('&1')
AND table_name = DECODE(UPPER('&2'), 'ALL', table_name, UPPER('&2'));
SELECT table_name, monitoring
FROM dba_tables
WHERE owner = UPPER('&1')
AND table_name = DECODE(UPPER('&2'), 'ALL', table_name, UPPER('&2'));
monitor_memory script to find in oracle 10g
SET LINESIZE 200
COLUMN username FORMAT A20
COLUMN module FORMAT A20
SELECT NVL(a.username,'(oracle)') AS username,
a.module,
a.program,
Trunc(b.value/1024) AS memory_kb
FROM v$session a,
v$sesstat b,
v$statname c
WHERE a.sid = b.sid
AND b.statistic# = c.statistic#
AND c.name = 'session pga memory'
AND a.program IS NOT NULL
ORDER BY b.value DESC;
COLUMN username FORMAT A20
COLUMN module FORMAT A20
SELECT NVL(a.username,'(oracle)') AS username,
a.module,
a.program,
Trunc(b.value/1024) AS memory_kb
FROM v$session a,
v$sesstat b,
v$statname c
WHERE a.sid = b.sid
AND b.statistic# = c.statistic#
AND c.name = 'session pga memory'
AND a.program IS NOT NULL
ORDER BY b.value DESC;
monitor script to find in oracle 10g
SET VERIFY OFF
SET LINESIZE 255
COL SID FORMAT 999
COL STATUS FORMAT A8
COL PROCESS FORMAT A10
COL SCHEMANAME FORMAT A16
COL OSUSER FORMAT A16
COL SQL_TEXT FORMAT A120 HEADING 'SQL QUERY'
COL PROGRAM FORMAT A30
SELECT s.sid,
s.status,
s.process,
s.schemaname,
s.osuser,
a.sql_text,
p.program
FROM v$session s,
v$sqlarea a,
v$process p
WHERE s.SQL_HASH_VALUE = a.HASH_VALUE
AND s.SQL_ADDRESS = a.ADDRESS
AND s.PADDR = p.ADDR
/
SET VERIFY ON
SET LINESIZE 255
SET LINESIZE 255
COL SID FORMAT 999
COL STATUS FORMAT A8
COL PROCESS FORMAT A10
COL SCHEMANAME FORMAT A16
COL OSUSER FORMAT A16
COL SQL_TEXT FORMAT A120 HEADING 'SQL QUERY'
COL PROGRAM FORMAT A30
SELECT s.sid,
s.status,
s.process,
s.schemaname,
s.osuser,
a.sql_text,
p.program
FROM v$session s,
v$sqlarea a,
v$process p
WHERE s.SQL_HASH_VALUE = a.HASH_VALUE
AND s.SQL_ADDRESS = a.ADDRESS
AND s.PADDR = p.ADDR
/
SET VERIFY ON
SET LINESIZE 255
max_extents script to find in oracle 10g
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
PROMPT
PROMPT Tables and Indexes nearing MAX_EXTENTS
PROMPT **************************************
SELECT e.owner,
e.segment_type,
Substr(e.segment_name, 1, 30) segment_name,
Trunc(s.initial_extent/1024) "INITIAL K",
Trunc(s.next_extent/1024) "NEXT K",
s.max_extents,
Count(*) as extents
FROM dba_extents e,
dba_segments s
WHERE e.owner = s.owner
AND e.segment_name = s.segment_name
AND e.owner NOT IN ('SYS', 'SYSTEM')
GROUP BY e.owner, e.segment_type, e.segment_name, s.initial_extent, s.next_extent, s.max_extents
HAVING Count(*) > s.max_extents - 10
ORDER BY e.owner, e.segment_type, Count(*) DESC;
SET PAGESIZE 1000
SET VERIFY OFF
PROMPT
PROMPT Tables and Indexes nearing MAX_EXTENTS
PROMPT **************************************
SELECT e.owner,
e.segment_type,
Substr(e.segment_name, 1, 30) segment_name,
Trunc(s.initial_extent/1024) "INITIAL K",
Trunc(s.next_extent/1024) "NEXT K",
s.max_extents,
Count(*) as extents
FROM dba_extents e,
dba_segments s
WHERE e.owner = s.owner
AND e.segment_name = s.segment_name
AND e.owner NOT IN ('SYS', 'SYSTEM')
GROUP BY e.owner, e.segment_type, e.segment_name, s.initial_extent, s.next_extent, s.max_extents
HAVING Count(*) > s.max_extents - 10
ORDER BY e.owner, e.segment_type, Count(*) DESC;
lru_latch_ratio script to find in oracle 10g
SET LINESIZE 500
SET PAGESIZE 1000
COLUMN "Ratio %" FORMAT 990.00
PROMPT
PROMPT Values greater than 3% indicate contention.
SELECT a.child#,
(a.SLEEPS / a.GETS) * 100 "Ratio %"
FROM v$latch_children a
WHERE a.name = 'cache buffers lru chain'
ORDER BY 1;
SET PAGESIZE 14
SET PAGESIZE 1000
COLUMN "Ratio %" FORMAT 990.00
PROMPT
PROMPT Values greater than 3% indicate contention.
SELECT a.child#,
(a.SLEEPS / a.GETS) * 100 "Ratio %"
FROM v$latch_children a
WHERE a.name = 'cache buffers lru chain'
ORDER BY 1;
SET PAGESIZE 14
longops script to find in oracle 10g
COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
SELECT s.sid,
s.serial#,
s.machine,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM v$session s,
v$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial#;
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
SELECT s.sid,
s.serial#,
s.machine,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM v$session s,
v$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial#;
locked_objects script to find in oracle 10g
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15
SELECT b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
v$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;
SET PAGESIZE 14
SET VERIFY ON
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15
SELECT b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
v$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;
SET PAGESIZE 14
SET VERIFY ON
Tuesday, June 3, 2014
latch_hit_ratios script in oracle 10g
SET LINESIZE 200
COLUMN latch_hit_ratio FORMAT 990.00
SELECT l.name,
l.gets,
l.misses,
((1 - (l.misses / l.gets)) * 100) AS latch_hit_ratio
FROM v$latch l
WHERE l.gets != 0
UNION
SELECT l.name,
l.gets,
l.misses,
100 AS latch_hit_ratio
FROM v$latch l
WHERE l.gets = 0
ORDER BY 4 DESC;
COLUMN latch_hit_ratio FORMAT 990.00
SELECT l.name,
l.gets,
l.misses,
((1 - (l.misses / l.gets)) * 100) AS latch_hit_ratio
FROM v$latch l
WHERE l.gets != 0
UNION
SELECT l.name,
l.gets,
l.misses,
100 AS latch_hit_ratio
FROM v$latch l
WHERE l.gets = 0
ORDER BY 4 DESC;
invalid_objects script in oracle 10g
-- -----------------------------------------------------------------------------------
COLUMN object_name FORMAT A30
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;
COLUMN object_name FORMAT A30
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;
index_usage script in oracle 10g
SET VERIFY OFF
SET LINESIZE 200
SELECT table_name,
index_name,
used,
start_monitoring,
end_monitoring
FROM v$object_usage
WHERE table_name = UPPER('&1')
AND index_name = DECODE(UPPER('&2'), 'ALL', index_name, UPPER('&2'));
SET LINESIZE 200
SELECT table_name,
index_name,
used,
start_monitoring,
end_monitoring
FROM v$object_usage
WHERE table_name = UPPER('&1')
AND index_name = DECODE(UPPER('&2'), 'ALL', index_name, UPPER('&2'));
index_partitions script in oracle 10g
SET LINESIZE 500
SET PAGESIZE 1000
SET FEEDBACK OFF
SET VERIFY OFF
SELECT a.index_name,
a.partition_name,
a.tablespace_name,
a.initial_extent,
a.next_extent,
a.pct_increase,
a.num_rows
FROM dba_ind_partitions a
WHERE a.index_name = Decode(Upper('&&1'),'ALL',a.index_name,Upper('&&1'))
AND a.index_owner = Upper('&&2')
ORDER BY a.index_name, a.partition_name
/
PROMPT
SET PAGESIZE 14
SET FEEDBACK ON
SET PAGESIZE 1000
SET FEEDBACK OFF
SET VERIFY OFF
SELECT a.index_name,
a.partition_name,
a.tablespace_name,
a.initial_extent,
a.next_extent,
a.pct_increase,
a.num_rows
FROM dba_ind_partitions a
WHERE a.index_name = Decode(Upper('&&1'),'ALL',a.index_name,Upper('&&1'))
AND a.index_owner = Upper('&&2')
ORDER BY a.index_name, a.partition_name
/
PROMPT
SET PAGESIZE 14
SET FEEDBACK ON
index_monitoring_status script in oracle 10g
SET VERIFY OFF
SELECT table_name,
index_name,
monitoring
FROM v$object_usage
WHERE table_name = UPPER('&1')
AND index_name = DECODE(UPPER('&2'), 'ALL', index_name, UPPER('&2'));
SELECT table_name,
index_name,
monitoring
FROM v$object_usage
WHERE table_name = UPPER('&1')
AND index_name = DECODE(UPPER('&2'), 'ALL', index_name, UPPER('&2'));
index_extents script in oracle 10g
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
SELECT i.index_name,
Count(e.segment_name) extents,
i.max_extents,
t.num_rows "ROWS",
Trunc(i.initial_extent/1024) "INITIAL K",
Trunc(i.next_extent/1024) "NEXT K",
t.table_name
FROM all_tables t,
all_indexes i,
dba_extents e
WHERE i.table_name = t.table_name
AND i.owner = t.owner
AND e.segment_name = i.index_name
AND e.owner = i.owner
AND i.table_name = Decode(Upper('&&1'),'ALL',i.table_name,Upper('&&1'))
AND i.owner = Upper('&&2')
GROUP BY t.table_name,
i.index_name,
i.max_extents,
t.num_rows,
i.initial_extent,
i.next_extent
HAVING Count(e.segment_name) > 5
ORDER BY Count(e.segment_name) DESC;
SET PAGESIZE 18
SET VERIFY ON
SET PAGESIZE 1000
SET VERIFY OFF
SELECT i.index_name,
Count(e.segment_name) extents,
i.max_extents,
t.num_rows "ROWS",
Trunc(i.initial_extent/1024) "INITIAL K",
Trunc(i.next_extent/1024) "NEXT K",
t.table_name
FROM all_tables t,
all_indexes i,
dba_extents e
WHERE i.table_name = t.table_name
AND i.owner = t.owner
AND e.segment_name = i.index_name
AND e.owner = i.owner
AND i.table_name = Decode(Upper('&&1'),'ALL',i.table_name,Upper('&&1'))
AND i.owner = Upper('&&2')
GROUP BY t.table_name,
i.index_name,
i.max_extents,
t.num_rows,
i.initial_extent,
i.next_extent
HAVING Count(e.segment_name) > 5
ORDER BY Count(e.segment_name) DESC;
SET PAGESIZE 18
SET VERIFY ON
identify_trace_file script in oracle 10g
SET LINESIZE 100
COLUMN trace_file FORMAT A60
SELECT s.sid,
s.serial#,
pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
'_ora_' || p.spid || '.trc' AS trace_file
FROM v$session s,
v$process p,
v$parameter pa
WHERE pa.name = 'user_dump_dest'
AND s.paddr = p.addr
AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
COLUMN trace_file FORMAT A60
SELECT s.sid,
s.serial#,
pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
'_ora_' || p.spid || '.trc' AS trace_file
FROM v$session s,
v$process p,
v$parameter pa
WHERE pa.name = 'user_dump_dest'
AND s.paddr = p.addr
AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
hot_blocks script in oracle 10g
SET LINESIZE 200
SET VERIFY OFF
SELECT *
FROM (SELECT name,
addr,
gets,
misses,
sleeps
FROM v$latch_children
WHERE name = 'cache buffers chains'
AND misses > 0
ORDER BY misses DESC)
WHERE rownum < 11;
ACCEPT address PROMPT "Enter ADDR: "
COLUMN owner FORMAT A15
COLUMN object_name FORMAT A30
COLUMN subobject_name FORMAT A20
SELECT *
FROM (SELECT o.owner,
o.object_name,
o.subobject_name,
bh.tch,
bh.obj,
bh.file#,
bh.dbablk,
bh.class,
bh.state
FROM x$bh bh,
dba_objects o
WHERE o.data_object_id = bh.obj
AND hladdr = '&address'
ORDER BY tch DESC)
WHERE rownum < 11;
SET VERIFY OFF
SELECT *
FROM (SELECT name,
addr,
gets,
misses,
sleeps
FROM v$latch_children
WHERE name = 'cache buffers chains'
AND misses > 0
ORDER BY misses DESC)
WHERE rownum < 11;
ACCEPT address PROMPT "Enter ADDR: "
COLUMN owner FORMAT A15
COLUMN object_name FORMAT A30
COLUMN subobject_name FORMAT A20
SELECT *
FROM (SELECT o.owner,
o.object_name,
o.subobject_name,
bh.tch,
bh.obj,
bh.file#,
bh.dbablk,
bh.class,
bh.state
FROM x$bh bh,
dba_objects o
WHERE o.data_object_id = bh.obj
AND hladdr = '&address'
ORDER BY tch DESC)
WHERE rownum < 11;
high_water_mark script in oracle 10g
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
CURSOR cu_tables IS
SELECT a.owner,
a.table_name
FROM all_tables a
WHERE a.table_name = Decode(Upper('&&1'),'ALL',a.table_name,Upper('&&1'))
AND a.owner = Upper('&&2');
op1 NUMBER;
op2 NUMBER;
op3 NUMBER;
op4 NUMBER;
op5 NUMBER;
op6 NUMBER;
op7 NUMBER;
BEGIN
Dbms_Output.Disable;
Dbms_Output.Enable(1000000);
Dbms_Output.Put_Line('TABLE UNUSED BLOCKS TOTAL BLOCKS HIGH WATER MARK');
Dbms_Output.Put_Line('------------------------------ --------------- --------------- ---------------');
FOR cur_rec IN cu_tables LOOP
Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,'TABLE',op1,op2,op3,op4,op5,op6,op7);
Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,' ') ||
LPad(op3,15,' ') ||
LPad(op1,15,' ') ||
LPad(Trunc(op1-op3-1),15,' '));
END LOOP;
END;
/
SET VERIFY ON
SET VERIFY OFF
DECLARE
CURSOR cu_tables IS
SELECT a.owner,
a.table_name
FROM all_tables a
WHERE a.table_name = Decode(Upper('&&1'),'ALL',a.table_name,Upper('&&1'))
AND a.owner = Upper('&&2');
op1 NUMBER;
op2 NUMBER;
op3 NUMBER;
op4 NUMBER;
op5 NUMBER;
op6 NUMBER;
op7 NUMBER;
BEGIN
Dbms_Output.Disable;
Dbms_Output.Enable(1000000);
Dbms_Output.Put_Line('TABLE UNUSED BLOCKS TOTAL BLOCKS HIGH WATER MARK');
Dbms_Output.Put_Line('------------------------------ --------------- --------------- ---------------');
FOR cur_rec IN cu_tables LOOP
Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,'TABLE',op1,op2,op3,op4,op5,op6,op7);
Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,' ') ||
LPad(op3,15,' ') ||
LPad(op1,15,' ') ||
LPad(Trunc(op1-op3-1),15,' '));
END LOOP;
END;
/
SET VERIFY ON
free_space script in oracle 10g
SET SERVEROUTPUT ON
SET PAGESIZE 1000
SET LINESIZE 255
SET FEEDBACK OFF
SELECT Substr(df.tablespace_name,1,20) "Tablespace Name",
Substr(df.file_name,1,40) "File Name",
Round(df.bytes/1024/1024,2) "Size (M)",
Round(e.used_bytes/1024/1024,2) "Used (M)",
Round(f.free_bytes/1024/1024,2) "Free (M)",
Rpad(' '|| Rpad ('X',Round(e.used_bytes*10/df.bytes,0), 'X'),11,'-') "% Used"
FROM DBA_DATA_FILES DF,
(SELECT file_id,
Sum(Decode(bytes,NULL,0,bytes)) used_bytes
FROM dba_extents
GROUP by file_id) E,
(SELECT Max(bytes) free_bytes,
file_id
FROM dba_free_space
GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id
AND df.file_id = f.file_id (+)
ORDER BY df.tablespace_name,
df.file_name;
PROMPT
SET FEEDBACK ON
SET PAGESIZE 18
SET PAGESIZE 1000
SET LINESIZE 255
SET FEEDBACK OFF
SELECT Substr(df.tablespace_name,1,20) "Tablespace Name",
Substr(df.file_name,1,40) "File Name",
Round(df.bytes/1024/1024,2) "Size (M)",
Round(e.used_bytes/1024/1024,2) "Used (M)",
Round(f.free_bytes/1024/1024,2) "Free (M)",
Rpad(' '|| Rpad ('X',Round(e.used_bytes*10/df.bytes,0), 'X'),11,'-') "% Used"
FROM DBA_DATA_FILES DF,
(SELECT file_id,
Sum(Decode(bytes,NULL,0,bytes)) used_bytes
FROM dba_extents
GROUP by file_id) E,
(SELECT Max(bytes) free_bytes,
file_id
FROM dba_free_space
GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id
AND df.file_id = f.file_id (+)
ORDER BY df.tablespace_name,
df.file_name;
PROMPT
SET FEEDBACK ON
SET PAGESIZE 18
fks script in oracle 10g
PROMPT
SET VERIFY OFF
SET FEEDBACK OFF
SET LINESIZE 255
SET PAGESIZE 1000
PROMPT
PROMPT Constraints Owned By Table
PROMPT ==========================
SELECT c.constraint_name "Constraint",
Decode(c.constraint_type,'P','Primary Key',
'U','Unique Key',
'C','Check',
'R','Foreign Key',
c.constraint_type) "Type",
c.r_owner "Ref Table",
c.r_constraint_name "Ref Constraint"
FROM all_constraints c
WHERE c.table_name = Upper('&&1')
AND c.owner = Upper('&&2');
PROMPT
PROMPT Constraints Referencing Table
PROMPT =============================
SELECT c1.table_name "Table",
c1.constraint_name "Foreign Key",
c1.r_constraint_name "References"
FROM all_constraints c1
WHERE c1.owner = Upper('&&2')
AND c1.r_constraint_name IN (SELECT c2.constraint_name
FROM all_constraints c2
WHERE c2.table_name = Upper('&&1')
AND c2.owner = Upper('&&2')
AND c2.constraint_type IN ('P','U'));
SET VERIFY ON
SET FEEDBACK ON
SET PAGESIZE 1000
PROMPT
SET VERIFY OFF
SET FEEDBACK OFF
SET LINESIZE 255
SET PAGESIZE 1000
PROMPT
PROMPT Constraints Owned By Table
PROMPT ==========================
SELECT c.constraint_name "Constraint",
Decode(c.constraint_type,'P','Primary Key',
'U','Unique Key',
'C','Check',
'R','Foreign Key',
c.constraint_type) "Type",
c.r_owner "Ref Table",
c.r_constraint_name "Ref Constraint"
FROM all_constraints c
WHERE c.table_name = Upper('&&1')
AND c.owner = Upper('&&2');
PROMPT
PROMPT Constraints Referencing Table
PROMPT =============================
SELECT c1.table_name "Table",
c1.constraint_name "Foreign Key",
c1.r_constraint_name "References"
FROM all_constraints c1
WHERE c1.owner = Upper('&&2')
AND c1.r_constraint_name IN (SELECT c2.constraint_name
FROM all_constraints c2
WHERE c2.table_name = Upper('&&1')
AND c2.owner = Upper('&&2')
AND c2.constraint_type IN ('P','U'));
SET VERIFY ON
SET FEEDBACK ON
SET PAGESIZE 1000
PROMPT
file_io script in oracle 10g
SET PAGESIZE 1000
SELECT Substr(d.name,1,50) "File Name",
f.phyblkrd "Blocks Read",
f.phyblkwrt "Blocks Writen",
f.phyblkrd + f.phyblkwrt "Total I/O"
FROM v$filestat f,
v$datafile d
WHERE d.file# = f.file#
ORDER BY f.phyblkrd + f.phyblkwrt DESC;
SET PAGESIZE 18
SELECT Substr(d.name,1,50) "File Name",
f.phyblkrd "Blocks Read",
f.phyblkwrt "Blocks Writen",
f.phyblkrd + f.phyblkwrt "Total I/O"
FROM v$filestat f,
v$datafile d
WHERE d.file# = f.file#
ORDER BY f.phyblkrd + f.phyblkwrt DESC;
SET PAGESIZE 18
explain script in oracle 10g
SET PAGESIZE 100
SET LINESIZE 200
SET VERIFY OFF
COLUMN plan FORMAT A50
COLUMN object_name FORMAT A30
COLUMN object_type FORMAT A15
COLUMN bytes FORMAT 9999999999
COLUMN cost FORMAT 9999999
COLUMN partition_start FORMAT A20
COLUMN partition_stop FORMAT A20
SELECT LPAD(' ', 2 * (level - 1)) ||
DECODE (level,1,NULL,level-1 || '.' || pt.position || ' ') ||
INITCAP(pt.operation) ||
DECODE(pt.options,NULL,'',' (' || INITCAP(pt.options) || ')') plan,
pt.object_name,
pt.object_type,
pt.bytes,
pt.cost,
pt.partition_start,
pt.partition_stop
FROM plan_table pt
START WITH pt.id = 0
AND pt.statement_id = '&1'
SET LINESIZE 200
SET VERIFY OFF
COLUMN plan FORMAT A50
COLUMN object_name FORMAT A30
COLUMN object_type FORMAT A15
COLUMN bytes FORMAT 9999999999
COLUMN cost FORMAT 9999999
COLUMN partition_start FORMAT A20
COLUMN partition_stop FORMAT A20
SELECT LPAD(' ', 2 * (level - 1)) ||
DECODE (level,1,NULL,level-1 || '.' || pt.position || ' ') ||
INITCAP(pt.operation) ||
DECODE(pt.options,NULL,'',' (' || INITCAP(pt.options) || ')') plan,
pt.object_name,
pt.object_type,
pt.bytes,
pt.cost,
pt.partition_start,
pt.partition_stop
FROM plan_table pt
START WITH pt.id = 0
AND pt.statement_id = '&1'
error script in oracle 10g
SELECT To_Char(a.line) || ' - ' || a.text error
FROM user_source a,
user_errors b
WHERE a.name = Upper('&&1')
AND a.name = b.name
AND a.type = b.type
AND a.line = b.line
ORDER BY a.name, a.line;
FROM user_source a,
user_errors b
WHERE a.name = Upper('&&1')
AND a.name = b.name
AND a.type = b.type
AND a.line = b.line
ORDER BY a.name, a.line;
error_stack script in oracle 10g
SET SERVEROUTPUT ON
DECLARE
v_stack VARCHAR2(2000);
BEGIN
v_stack := Dbms_Utility.Format_Error_Stack;
Dbms_Output.Put_Line(v_stack);
END;
/
DECLARE
v_stack VARCHAR2(2000);
BEGIN
v_stack := Dbms_Utility.Format_Error_Stack;
Dbms_Output.Put_Line(v_stack);
END;
/
dispatchers script in oracle 10g
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
SELECT a.name "Name",
a.status "Status",
a.accept "Accept",
a.messages "Total Mesgs",
a.bytes "Total Bytes",
a.owned "Circs Owned",
a.idle "Total Idle Time",
a.busy "Total Busy Time",
Round(a.busy/(a.busy + a.idle),2) "Load"
FROM v$dispatcher a
ORDER BY 1;
SET PAGESIZE 14
SET VERIFY ON
SET PAGESIZE 1000
SET VERIFY OFF
SELECT a.name "Name",
a.status "Status",
a.accept "Accept",
a.messages "Total Mesgs",
a.bytes "Total Bytes",
a.owned "Circs Owned",
a.idle "Total Idle Time",
a.busy "Total Busy Time",
Round(a.busy/(a.busy + a.idle),2) "Load"
FROM v$dispatcher a
ORDER BY 1;
SET PAGESIZE 14
SET VERIFY ON
db_info script in oracle 10g
SET PAGESIZE 1000
SET LINESIZE 100
SET FEEDBACK OFF
SELECT *
FROM v$database;
SELECT *
FROM v$instance;
SELECT *
FROM v$version;
SELECT a.name,
a.value
FROM v$sga a;
SELECT Substr(c.name,1,60) "Controlfile",
NVL(c.status,'UNKNOWN') "Status"
FROM v$controlfile c
ORDER BY 1;
SELECT Substr(d.name,1,60) "Datafile",
NVL(d.status,'UNKNOWN') "Status",
d.enabled "Enabled",
LPad(To_Char(Round(d.bytes/1024000,2),'9999990.00'),10,' ') "Size (M)"
FROM v$datafile d
ORDER BY 1;
SELECT l.group# "Group",
Substr(l.member,1,60) "Logfile",
NVL(l.status,'UNKNOWN') "Status"
FROM v$logfile l
ORDER BY 1,2;
PROMPT
SET PAGESIZE 14
SET FEEDBACK ON
SET LINESIZE 100
SET FEEDBACK OFF
SELECT *
FROM v$database;
SELECT *
FROM v$instance;
SELECT *
FROM v$version;
SELECT a.name,
a.value
FROM v$sga a;
SELECT Substr(c.name,1,60) "Controlfile",
NVL(c.status,'UNKNOWN') "Status"
FROM v$controlfile c
ORDER BY 1;
SELECT Substr(d.name,1,60) "Datafile",
NVL(d.status,'UNKNOWN') "Status",
d.enabled "Enabled",
LPad(To_Char(Round(d.bytes/1024000,2),'9999990.00'),10,' ') "Size (M)"
FROM v$datafile d
ORDER BY 1;
SELECT l.group# "Group",
Substr(l.member,1,60) "Logfile",
NVL(l.status,'UNKNOWN') "Status"
FROM v$logfile l
ORDER BY 1,2;
PROMPT
SET PAGESIZE 14
SET FEEDBACK ON
db_cache_advice script in oracle 10g
COLUMN size_for_estimate FORMAT 999,999,999,999 heading 'Cache Size (MB)'
COLUMN buffers_for_estimate FORMAT 999,999,999 heading 'Buffers'
COLUMN estd_physical_read_factor FORMAT 999.90 heading 'Estd Phys|Read Factor'
COLUMN estd_physical_reads FORMAT 999,999,999 heading 'Estd Phys| Reads'
SELECT size_for_estimate,
buffers_for_estimate,
estd_physical_read_factor,
estd_physical_reads
FROM v$db_cache_advice
WHERE name = 'DEFAULT'
AND block_size = (SELECT value
FROM v$parameter
WHERE name = 'db_block_size')
AND advice_status = 'ON';
COLUMN buffers_for_estimate FORMAT 999,999,999 heading 'Buffers'
COLUMN estd_physical_read_factor FORMAT 999.90 heading 'Estd Phys|Read Factor'
COLUMN estd_physical_reads FORMAT 999,999,999 heading 'Estd Phys| Reads'
SELECT size_for_estimate,
buffers_for_estimate,
estd_physical_read_factor,
estd_physical_reads
FROM v$db_cache_advice
WHERE name = 'DEFAULT'
AND block_size = (SELECT value
FROM v$parameter
WHERE name = 'db_block_size')
AND advice_status = 'ON';
column_defaults script in oracle 10g
SET LINESIZE 100
SET VERIFY OFF
SELECT a.column_name "Column",
a.data_default "Default"
FROM all_tab_columns a
WHERE a.table_name = Upper('&1')
AND a.data_default IS NOT NULL
/
SET VERIFY OFF
SELECT a.column_name "Column",
a.data_default "Default"
FROM all_tab_columns a
WHERE a.table_name = Upper('&1')
AND a.data_default IS NOT NULL
/
script to find in oracle 10g - code_dep_on
SET VERIFY OFF
SET FEEDBACK OFF
SET LINESIZE 255
SET PAGESIZE 1000
BREAK ON type SKIP 1
PROMPT
SELECT a.type,
SUBSTR(a.owner,1,10) AS owner,
a.name
FROM all_dependencies a
WHERE a.referenced_name = UPPER('&1')
AND a.referenced_owner = DECODE(UPPER('&2'), 'ALL', a.referenced_owner, UPPER('&2'))
ORDER BY 1,2,3;
SET VERIFY ON
SET FEEDBACK ON
SET PAGESIZE 22
PROMPT
SET FEEDBACK OFF
SET LINESIZE 255
SET PAGESIZE 1000
BREAK ON type SKIP 1
PROMPT
SELECT a.type,
SUBSTR(a.owner,1,10) AS owner,
a.name
FROM all_dependencies a
WHERE a.referenced_name = UPPER('&1')
AND a.referenced_owner = DECODE(UPPER('&2'), 'ALL', a.referenced_owner, UPPER('&2'))
ORDER BY 1,2,3;
SET VERIFY ON
SET FEEDBACK ON
SET PAGESIZE 22
PROMPT
Script to find in oracle 10g - code_dep
SET VERIFY OFF
SET FEEDBACK OFF
SET LINESIZE 255
SET PAGESIZE 1000
BREAK ON type SKIP 1
PROMPT
SELECT a.referenced_type AS type,
SUBSTR(a.referenced_owner,1,10) AS ref_owner,
a.referenced_name AS ref_name,
SUBSTR(a.referenced_link_name,1,20) AS ref_link_name
FROM all_dependencies a
WHERE a.name = Upper('&1')
AND a.owner = DECODE(UPPER('&2'), 'ALL', a.referenced_owner, UPPER('&2'))
ORDER BY 1,2,3;
SET VERIFY ON
SET FEEDBACK ON
SET PAGESIZE 22
PROMPT
SET FEEDBACK OFF
SET LINESIZE 255
SET PAGESIZE 1000
BREAK ON type SKIP 1
PROMPT
SELECT a.referenced_type AS type,
SUBSTR(a.referenced_owner,1,10) AS ref_owner,
a.referenced_name AS ref_name,
SUBSTR(a.referenced_link_name,1,20) AS ref_link_name
FROM all_dependencies a
WHERE a.name = Upper('&1')
AND a.owner = DECODE(UPPER('&2'), 'ALL', a.referenced_owner, UPPER('&2'))
ORDER BY 1,2,3;
SET VERIFY ON
SET FEEDBACK ON
SET PAGESIZE 22
PROMPT
Script to find in oracle 10g - call_stack
SET SERVEROUTPUT ON
DECLARE
v_stack VARCHAR2(2000);
BEGIN
v_stack := Dbms_Utility.Format_Call_Stack;
Dbms_Output.Put_Line(v_stack);
END;
/
DECLARE
v_stack VARCHAR2(2000);
BEGIN
v_stack := Dbms_Utility.Format_Call_Stack;
Dbms_Output.Put_Line(v_stack);
END;
/
Script to find in oracle 10g - cache_hit_ratio
PROMPT
PROMPT Hit ratio should exceed 89%
SELECT Sum(Decode(a.name, 'consistent gets', a.value, 0)) "Consistent Gets",
Sum(Decode(a.name, 'db block gets', a.value, 0)) "DB Block Gets",
Sum(Decode(a.name, 'physical reads', a.value, 0)) "Physical Reads",
Round(((Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
Sum(Decode(a.name, 'db block gets', a.value, 0)) -
Sum(Decode(a.name, 'physical reads', a.value, 0)) )/
(Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
Sum(Decode(a.name, 'db block gets', a.value, 0))))
*100,2) "Hit Ratio %"
FROM v$sysstat a;
PROMPT Hit ratio should exceed 89%
SELECT Sum(Decode(a.name, 'consistent gets', a.value, 0)) "Consistent Gets",
Sum(Decode(a.name, 'db block gets', a.value, 0)) "DB Block Gets",
Sum(Decode(a.name, 'physical reads', a.value, 0)) "Physical Reads",
Round(((Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
Sum(Decode(a.name, 'db block gets', a.value, 0)) -
Sum(Decode(a.name, 'physical reads', a.value, 0)) )/
(Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
Sum(Decode(a.name, 'db block gets', a.value, 0))))
*100,2) "Hit Ratio %"
FROM v$sysstat a;
Script to find in oracle 10g - active_sessions
SET LINESIZE 500
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20
SELECT NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
p.spid,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s,
v$process p
WHERE s.paddr = p.addr
AND s.status = 'ACTIVE'
ORDER BY s.username, s.osuser;
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20
SELECT NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
p.spid,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s,
v$process p
WHERE s.paddr = p.addr
AND s.status = 'ACTIVE'
ORDER BY s.username, s.osuser;
Script to find in oracle 10g - access
SET SERVEROUTPUT ON
SET PAGESIZE 1000
SET LINESIZE 255
SET VERIFY OFF
SELECT Substr(a.object,1,30) object,
a.type,
a.sid,
b.username,
b.osuser,
b.program
FROM v$access a,
v$session b
WHERE a.sid = b.sid
AND a.owner = Upper('&1');
PROMPT
SET PAGESIZE 18
SET PAGESIZE 1000
SET LINESIZE 255
SET VERIFY OFF
SELECT Substr(a.object,1,30) object,
a.type,
a.sid,
b.username,
b.osuser,
b.program
FROM v$access a,
v$session b
WHERE a.sid = b.sid
AND a.owner = Upper('&1');
PROMPT
SET PAGESIZE 18
Script to find in oracle 10g - windows
SET LINESIZE 250
COLUMN comments FORMAT A40
SELECT window_name,
resource_plan,
enabled,
active,
comments
FROM dba_scheduler_windows
ORDER BY window_name;
COLUMN comments FORMAT A40
SELECT window_name,
resource_plan,
enabled,
active,
comments
FROM dba_scheduler_windows
ORDER BY window_name;
Script to find in oracle 10g - window_groups
SET LINESIZE 250
COLUMN comments FORMAT A40
SELECT window_group_name,
enabled,
number_of_windows,
comments
FROM dba_scheduler_window_groups
ORDER BY window_group_name;
SELECT window_group_name,
window_name
FROM dba_scheduler_wingroup_members
ORDER BY window_group_name, window_name;
COLUMN comments FORMAT A40
SELECT window_group_name,
enabled,
number_of_windows,
comments
FROM dba_scheduler_window_groups
ORDER BY window_group_name;
SELECT window_group_name,
window_name
FROM dba_scheduler_wingroup_members
ORDER BY window_group_name, window_name;
Script to find in oracle 10g - session_waits
SET LINESIZE 200
SET PAGESIZE 1000
COLUMN username FORMAT A20
COLUMN event FORMAT A30
COLUMN wait_class FORMAT A15
SELECT NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_class,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM v$session_wait sw,
v$session s
WHERE s.sid = sw.sid
ORDER BY sw.seconds_in_wait DESC;
SET PAGESIZE 1000
COLUMN username FORMAT A20
COLUMN event FORMAT A30
COLUMN wait_class FORMAT A15
SELECT NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_class,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM v$session_wait sw,
v$session s
WHERE s.sid = sw.sid
ORDER BY sw.seconds_in_wait DESC;
Script to find in oracle 10g - services
SET LINESIZE 200
COLUMN name FORMAT A30
COLUMN network_name FORMAT A50
SELECT name,
network_name
FROM dba_services
ORDER BY name;
COLUMN name FORMAT A30
COLUMN network_name FORMAT A50
SELECT name,
network_name
FROM dba_services
ORDER BY name;
Script to find in oracle 10g - segment_advisor
SET SERVEROUTPUT ON SIZE 1000000
SET LINESIZE 200
SET VERIFY OFF
DECLARE
l_object_id NUMBER;
l_task_name VARCHAR2(32767) := 'SEGMENT_ADVISOR_TASK';
l_object_type VARCHAR2(32767) := UPPER('&1');
l_attr1 VARCHAR2(32767) := UPPER('&2');
l_attr2 VARCHAR2(32767) := UPPER('&3');
BEGIN
IF l_attr2 = 'NULL' THEN
l_attr2 := NULL;
END IF;
DBMS_ADVISOR.create_task (
advisor_name => 'Segment Advisor',
task_name => l_task_name);
DBMS_ADVISOR.create_object (
task_name => l_task_name,
object_type => l_object_type,
attr1 => l_attr1,
attr2 => l_attr2,
attr3 => NULL,
attr4 => 'null',
attr5 => NULL,
object_id => l_object_id);
DBMS_ADVISOR.set_task_parameter (
task_name => l_task_name,
parameter => 'RECOMMEND_ALL',
value => 'TRUE');
DBMS_ADVISOR.execute_task(task_name => l_task_name);
FOR cur_rec IN (SELECT f.impact,
o.type,
o.attr1,
o.attr2,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE f.task_name = l_task_name
ORDER BY f.impact DESC)
LOOP
DBMS_OUTPUT.put_line('..');
DBMS_OUTPUT.put_line('Type : ' || cur_rec.type);
DBMS_OUTPUT.put_line('Attr1 : ' || cur_rec.attr1);
DBMS_OUTPUT.put_line('Attr2 : ' || cur_rec.attr2);
DBMS_OUTPUT.put_line('Message : ' || cur_rec.message);
DBMS_OUTPUT.put_line('More info : ' || cur_rec.more_info);
END LOOP;
DBMS_ADVISOR.delete_task(task_name => l_task_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error : ' || DBMS_UTILITY.format_error_backtrace);
DBMS_ADVISOR.delete_task(task_name => l_task_name);
END;
/
SET LINESIZE 200
SET VERIFY OFF
DECLARE
l_object_id NUMBER;
l_task_name VARCHAR2(32767) := 'SEGMENT_ADVISOR_TASK';
l_object_type VARCHAR2(32767) := UPPER('&1');
l_attr1 VARCHAR2(32767) := UPPER('&2');
l_attr2 VARCHAR2(32767) := UPPER('&3');
BEGIN
IF l_attr2 = 'NULL' THEN
l_attr2 := NULL;
END IF;
DBMS_ADVISOR.create_task (
advisor_name => 'Segment Advisor',
task_name => l_task_name);
DBMS_ADVISOR.create_object (
task_name => l_task_name,
object_type => l_object_type,
attr1 => l_attr1,
attr2 => l_attr2,
attr3 => NULL,
attr4 => 'null',
attr5 => NULL,
object_id => l_object_id);
DBMS_ADVISOR.set_task_parameter (
task_name => l_task_name,
parameter => 'RECOMMEND_ALL',
value => 'TRUE');
DBMS_ADVISOR.execute_task(task_name => l_task_name);
FOR cur_rec IN (SELECT f.impact,
o.type,
o.attr1,
o.attr2,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE f.task_name = l_task_name
ORDER BY f.impact DESC)
LOOP
DBMS_OUTPUT.put_line('..');
DBMS_OUTPUT.put_line('Type : ' || cur_rec.type);
DBMS_OUTPUT.put_line('Attr1 : ' || cur_rec.attr1);
DBMS_OUTPUT.put_line('Attr2 : ' || cur_rec.attr2);
DBMS_OUTPUT.put_line('Message : ' || cur_rec.message);
DBMS_OUTPUT.put_line('More info : ' || cur_rec.more_info);
END LOOP;
DBMS_ADVISOR.delete_task(task_name => l_task_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error : ' || DBMS_UTILITY.format_error_backtrace);
DBMS_ADVISOR.delete_task(task_name => l_task_name);
END;
/
Script to find in oracle 10g - lock_tree
SET LINESIZE 500
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20
SELECT LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL;
SET PAGESIZE 14
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20
SELECT LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL;
SET PAGESIZE 14
Script to find in oracle 10g jobs_running
SET LINESIZE 200
COLUMN owner FORMAT A20
SELECT owner,
job_name,
running_instance,
elapsed_time
FROM dba_scheduler_running_jobs
ORDER BY owner, job_name;
COLUMN owner FORMAT A20
SELECT owner,
job_name,
running_instance,
elapsed_time
FROM dba_scheduler_running_jobs
ORDER BY owner, job_name;
Script to find in oracle 10g to Displays scheduler job information.
SET LINESIZE 200
COLUMN owner FORMAT A20
COLUMN next_run_date FORMAT A35
SELECT owner,
job_name,
enabled,
job_class,
next_run_date
FROM dba_scheduler_jobs
ORDER BY owner, job_name;
COLUMN owner FORMAT A20
COLUMN next_run_date FORMAT A35
SELECT owner,
job_name,
enabled,
job_class,
next_run_date
FROM dba_scheduler_jobs
ORDER BY owner, job_name;
Monday, June 2, 2014
Script to find in oracle 10g for displays scheduler information about job schedules.
SET LINESIZE 250
COLUMN owner FORMAT A20
COLUMN schedule_name FORMAT A30
COLUMN start_date FORMAT A35
COLUMN repeat_interval FORMAT A50
COLUMN end_date FORMAT A35
COLUMN comments FORMAT A40
SELECT owner,
schedule_name,
start_date,
repeat_interval,
end_date,
comments
FROM dba_scheduler_schedules
ORDER BY owner, schedule_name;
COLUMN owner FORMAT A20
COLUMN schedule_name FORMAT A30
COLUMN start_date FORMAT A35
COLUMN repeat_interval FORMAT A50
COLUMN end_date FORMAT A35
COLUMN comments FORMAT A40
SELECT owner,
schedule_name,
start_date,
repeat_interval,
end_date,
comments
FROM dba_scheduler_schedules
ORDER BY owner, schedule_name;
Script to find in oracle 10g for Displays scheduler information about job programs.
SET LINESIZE 250
COLUMN owner FORMAT A20
COLUMN program_name FORMAT A30
COLUMN program_action FORMAT A50
COLUMN comments FORMAT A40
SELECT owner,
program_name,
program_type,
program_action,
number_of_arguments,
enabled,
comments
FROM dba_scheduler_programs
ORDER BY owner, program_name;
COLUMN owner FORMAT A20
COLUMN program_name FORMAT A30
COLUMN program_action FORMAT A50
COLUMN comments FORMAT A40
SELECT owner,
program_name,
program_type,
program_action,
number_of_arguments,
enabled,
comments
FROM dba_scheduler_programs
ORDER BY owner, program_name;
Script to find in oracle 10g for job_classes
SET LINESIZE 200
COLUMN service FORMAT A20
COLUMN comments FORMAT A40
SELECT job_class_name,
resource_consumer_group,
service,
logging_level,
log_history,
comments
FROM dba_scheduler_job_classes
ORDER BY job_class_name;
COLUMN service FORMAT A20
COLUMN comments FORMAT A40
SELECT job_class_name,
resource_consumer_group,
service,
logging_level,
log_history,
comments
FROM dba_scheduler_job_classes
ORDER BY job_class_name;
Script to find in oracle 10g for feature_usage
COLUMN name FORMAT A50
COLUMN detected_usages FORMAT 999999999999
SELECT u1.name,
u1.detected_usages
FROM dba_feature_usage_statistics u1
WHERE u1.version = (SELECT MAX(u2.version)
FROM dba_feature_usage_statistics u2
WHERE u2.name = u1.name)
ORDER BY u1.name;
COLUMN FORMAT DEFAULT
COLUMN detected_usages FORMAT 999999999999
SELECT u1.name,
u1.detected_usages
FROM dba_feature_usage_statistics u1
WHERE u1.version = (SELECT MAX(u2.version)
FROM dba_feature_usage_statistics u2
WHERE u2.name = u1.name)
ORDER BY u1.name;
COLUMN FORMAT DEFAULT
Script to find dynamic_memory in Oracle 10g
COLUMN name FORMAT A40
COLUMN value FORMAT A40
SELECT name,
value
FROM v$parameter
WHERE SUBSTR(name, 1, 1) = '_'
ORDER BY name;
COLUMN FORMAT DEFAULT
COLUMN value FORMAT A40
SELECT name,
value
FROM v$parameter
WHERE SUBSTR(name, 1, 1) = '_'
ORDER BY name;
COLUMN FORMAT DEFAULT
script to find db_usage_hwm in oracle 10g
COLUMN name FORMAT A40
COLUMN highwater FORMAT 999999999999
COLUMN last_value FORMAT 999999999999
SET PAGESIZE 24
SELECT hwm1.name,
hwm1.highwater,
hwm1.last_value
FROM dba_high_water_mark_statistics hwm1
WHERE hwm1.version = (SELECT MAX(hwm2.version)
FROM dba_high_water_mark_statistics hwm2
WHERE hwm2.name = hwm1.name)
ORDER BY hwm1.name;
COLUMN FORMAT DEFAULT
COLUMN highwater FORMAT 999999999999
COLUMN last_value FORMAT 999999999999
SET PAGESIZE 24
SELECT hwm1.name,
hwm1.highwater,
hwm1.last_value
FROM dba_high_water_mark_statistics hwm1
WHERE hwm1.version = (SELECT MAX(hwm2.version)
FROM dba_high_water_mark_statistics hwm2
WHERE hwm2.name = hwm1.name)
ORDER BY hwm1.name;
COLUMN FORMAT DEFAULT
Script to find active_session_waits in Oracle 10g
SET LINESIZE 250
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN osuser FORMAT A15
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN wait_class FORMAT A15
COLUMN state FORMAT A19
COLUMN logon_time FORMAT A20
SELECT NVL(a.username, '(oracle)') AS username,
a.osuser,
a.sid,
a.serial#,
d.spid AS process_id,
a.wait_class,
a.seconds_in_wait,
a.state,
a.blocking_session,
a.blocking_session_status,
a.module,
TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session a,
v$process d
WHERE a.paddr = d.addr
AND a.status = 'ACTIVE'
ORDER BY 1,2;
SET PAGESIZE 14
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN osuser FORMAT A15
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN wait_class FORMAT A15
COLUMN state FORMAT A19
COLUMN logon_time FORMAT A20
SELECT NVL(a.username, '(oracle)') AS username,
a.osuser,
a.sid,
a.serial#,
d.spid AS process_id,
a.wait_class,
a.seconds_in_wait,
a.state,
a.blocking_session,
a.blocking_session_status,
a.module,
TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session a,
v$process d
WHERE a.paddr = d.addr
AND a.status = 'ACTIVE'
ORDER BY 1,2;
SET PAGESIZE 14
Subscribe to:
Posts (Atom)