dbv for ASM disk

Oracle disk verification Utility dbv On ASM file system

set feedback off  head off echo off linesize 200 pagesize 1000
spool /tmp/dbvchk.txt
select 'dbv file=' || name || ' blocksize='|| block_size || ' USERID=system/SYS logfile=' ||substr(name, instr(name, '/', -1, 1) +1) ||'.' || file# || '.log' from v$datafile
/ 

Output

dbv file=+DATA/rac/datafile/system.259.716288417 blocksize=8192 USERID=system/SYS logfile=system.259.716288417.1.log
dbv file=+DATA/rac/datafile/undotbs1.260.716288467 blocksize=8192 USERID=system/SYS logfile=undotbs1.260.716288467.2.log
dbv file=+DATA/rac/datafile/sysaux.261.716288483 blocksize=8192 USERID=system/SYS logfile=sysaux.261.716288483.3.log
dbv file=+DATA/rac/datafile/undotbs2.263.716288533 blocksize=8192 USERID=system/SYS logfile=undotbs2.263.716288533.4.log
dbv file=+DATA/rac/datafile/users.264.716288559 blocksize=8192 USERID=system/SYS logfile=users.264.716288559.5.log

without password

set feedback off  head off echo off linesize 200 pagesize 1000
spool /tmp/dbvchk.txt
select 'dbv file=' || name || ' blocksize='|| block_size || ' USERID=\''/ as sysdba\'' logfile=' ||substr(name, instr(name, '/', -1, 1) +1) ||'.' || file# || '.log' from v$datafile
/ 

=======

corrupt the datafile 

dd if=/dev/zero of=/u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6 bs=8k conv=notrunc seek=10 count=1

dbv file=/u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6 blocksize=8192 USERID=\'/ as sysdba\' logfile=data_D-ORCL_TS-USERS_FNO-6.6.log

 cat data_D-ORCL_TS-USERS_FNO-6.6.log

DBVERIFY: Release 12.2.0.1.0 - Production on Fri Dec 30 06:30:04 2022

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6

Page 10 is marked corrupt

Corrupt block relative dba: 0x0180000a (file 6, block 10)

Completely zero block found during dbv:

DBVERIFY - Verification complete

Total Pages Examined         : 640

Total Pages Processed (Data) : 30

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 5

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 576

Total Pages Processed (Seg)  : 11

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 17

Total Pages Marked Corrupt   : 1

Total Pages Influx           : 0

Total Pages Encrypted        : 0

Highest block SCN            : 1839277 (0.1839277)

[oracle@wcp12cr2 Datafile]$

select * from v$database_block_corruption ;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO     CON_ID

---------- ---------- ---------- ------------------ --------- ----------

         6         10          1                  0 ALL ZERO           0

repair failure preview;

repair failure noprompt;

RMAN> list failure;

Database Role: PRIMARY

List of Database Failures

=========================

Failure ID Priority Status    Time Detected       Summary

---------- -------- --------- ------------------- -------

1761       HIGH     OPEN      30-12-2022 06:42:46 Datafile 6: '/u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6' contains one or more corrupt blocks

==========

RMAN> advise failure;

Database Role: PRIMARY

List of Database Failures

=========================

Failure ID Priority Status    Time Detected       Summary

---------- -------- --------- ------------------- -------

1761       HIGH     OPEN      30-12-2022 06:42:46 Datafile 6: '/u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6' contains one or more corrupt blocks

analyzing automatic repair options; this may take some time

using channel ORA_DISK_1

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

no manual actions available

Automated Repair Options

========================

Option Repair Description

------ ------------------

1      Perform block media recovery of block 10 in file 6

  Strategy: The repair includes complete media recovery with no data loss

  Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_1375281163.hm  <<<< check this file 

RMAN>

[oracle@wcp12cr2 Datafile]$ cat /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_1375281163.hm

   # block media recovery

   recover datafile 6 block 10;

=====

define file_no=6

define block_value=8

set linesize 300

col SEGMENT_NAME for a20

col TABLESPACE_NAME for a20

SELECT segment_name, TABLESPACE_NAME,segment_type,file_id, block_id, blocks

        FROM   dba_extents

        WHERE file_id = &file_no 

--AND ( &block_value BETWEEN block_id   AND ( block_id + blocks -1 ) )

;

define file_id=6

define block_id=144

col OWNER for a15

col SEGMENT_NAME for a20

col TABLESPACE_NAME for a20

SELECT relative_fno, owner, segment_name, segment_type ,file_id, block_id, blocks

 FROM dba_extents 

 WHERE file_id = &file_id

 and block_id=&block_id

;

RELATIVE_FNO OWNER           SEGMENT_NAME         SEGMENT_TYPE                FILE_ID   BLOCK_ID     BLOCKS

------------ --------------- -------------------- ------------------------ ---------- ---------- ----------

           6 OJVMSYS         OJDS$BINDINGS$       TABLE                             6        144          8

define file_number=6

define BLOCK_NUMBER=144

SELECT relative_fno, owner, segment_name, segment_type ,file_id, block_id, blocks FROM DBA_EXTENTS WHERE FILE_ID= &file_number 

 AND &BLOCK_NUMBER BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS-1;

RELATIVE_FNO OWNER           SEGMENT_NAME         SEGMENT_TYPE                FILE_ID   BLOCK_ID     BLOCKS

------------ --------------- -------------------- ------------------------ ---------- ---------- ----------

           6 OJVMSYS         OJDS$BINDINGS$       TABLE                             6        144          8

Oracle 11g alert log change to old alert log

sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Apr 28 09:13:18 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> alter system set background_dump_dest='/opt/app/oracle/admin/vihaan/bdump' scope=spfile;

System altered.

SQL> alter system set "_diag_adr_enabled"=false scope=spfile;

System altered.

SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup force ;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2146112 bytes
Variable Size 150995136 bytes
Database Buffers 377487360 bytes
Redo Buffers 3833856 bytes
Database mounted.
Database opened.




SQL> show parameter back

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /opt/app/oracle/admin/vihaan/b
dump
backup_tape_io_slaves boolean FALSE
db_flashback_retention_target integer 1440
fast_start_parallel_rollback string LOW
rollback_segments string
transactions_per_rollback_segment integer 5




set pagesize 200
col KSPPINM format a50
col KSPPSTVL format a20
1* select i.ksppinm, v.ksppstvl from x$ksppi i, x$ksppcv v where i.ksppinm like '_diag_adr_enabled'
SQL> select i.ksppinm, v.ksppstvl from x$ksppi i, x$ksppcv v
where i.ksppinm like '_diag_adr_enabled%'
-- where i.ksppinm like '_%'
and i.indx=v.indx
and v.ksppstvl!='TRUE';

KSPPINM KSPPSTVL
-------------------------------------------------- --------------------
_diag_adr_enabled FALSE

oracle 10g and 11g database Enabling ARCHIVELOG Mode

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2146112 bytes
Variable Size 150995136 bytes
Database Buffers 377487360 bytes
Redo Buffers 3833856 bytes
Database mounted.
SQL> alter system set log_archive_dest = '/opt/app/oracle/admin/vihaan/archive' scope=spfile ;

System altered.

SQL> alter system set log_archive_dest = '/opt/app/oracle/admin/vihaan/archive' scope=both;

System altered.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list ;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/app/oracle/admin/vihaan/archive
Oldest online log sequence 63
Next log sequence to archive 65
Current log sequence 65


set linesize 200
col DEST_NAME format a50
col DESTINATION format a30
set pagesize 100
select DEST_NAME,STATUS,DESTINATION from V$ARCHIVE_DEST

DEST_NAME STATUS DESTINATION
------------------------------ --------- --------------------------------------------------
LOG_ARCHIVE_DEST_1 VALID /opt/app/oracle/admin/vihaan/archive
LOG_ARCHIVE_DEST_2 INACTIVE
LOG_ARCHIVE_DEST_3 INACTIVE
LOG_ARCHIVE_DEST_4 INACTIVE
LOG_ARCHIVE_DEST_5 INACTIVE
LOG_ARCHIVE_DEST_6 INACTIVE
LOG_ARCHIVE_DEST_7 INACTIVE
LOG_ARCHIVE_DEST_8 INACTIVE
LOG_ARCHIVE_DEST_9 INACTIVE
LOG_ARCHIVE_DEST_10 INACTIVE


select log_mode from v$database;


LOG_MODE
------------
ARCHIVELOG



log file status


COL GROUP# FORMAT 999999 HEAD 'Groupe'
COL THREAD# FORMAT 999999 HEAD 'Thread'
COL SEQUENCE# FORMAT 99999999 HEAD 'Sequence'
COL TAI FORMAT A6 HEAD 'Taille|Mo'
COL STATUS FORMAT A10 HEAD 'Statut'
COL MEMBER FORMAT A40 HEAD 'Nom fichier'
COL HR FORMAT A20 HEAD 'Date ouverture'
COL ARCHIVED FORMAT A7 HEAD 'Archive'
BREAK ON THREAD# NODUP ON GROUP# NODUP SKIP 1 ON TAI NODUP ON HR NODUP
COMPUTE NUMBER LABEL 'Nombre:' OF SEQUENCE# ON GROUP#
SELECT L.GROUP#, L.THREAD#, L.SEQUENCE#, LPAD(TRUNC(BYTES/1024/1024),5) TAI, L.STATUS,
MEMBER, TO_CHAR(FIRST_TIME, 'YYYY-MM-DD HH24:MI:SS') HR,
DECODE(ARCHIVED,'YES','Oui','Non') ARCHIVED
FROM V$LOG L, V$LOGFILE F
WHERE L.GROUP# = F.GROUP#
ORDER BY 1,3,6;


in Oracle 11gr2


Oracle 11R2 archive log file

SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

System altered.


SQL> alter system set log_archive_dest_1='LOCATION=/opt/app/oracle/admin/vihaan/archive';







For RAC Perform:
ALTER SYSTEM set db_recovery_file_dest_size=60G scope=both sid='*' ;
ALTER SYSTEM SET db_recovery_file_dest='+FLASH' sid='*';
In a RAC database, all instances must have the same values for these parameters. Even though there are multiple nodes they all share the same controlfiles.

To disable FRA you can use:

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '' scope=both;
Note: even after you disable the flash recovery area, the RMAN will continue to access the files located in the flash recovery area for backup and recovery purposes.

Upgrade Oracle Database 10g to 11g Manual Upgrade

We are going to use Manual Upgrade

Install 11g database software in different ORACLE_HOME from source Database



oracle@apt-amd-02:/opt/app/oracle/product/11.1/rdbms/admin> pwd
/opt/app/oracle/product/11.1/rdbms/admin
oracle@apt-amd-02:/opt/app/oracle/product/11.1/rdbms/admin> ls -lt utlu111i.sql
-rw-r--r-- 1 oracle oinstall 138636 2007-07-11 09:01 utlu111i.sql
oracle@apt-amd-02:/opt/app/oracle/product/11.1/rdbms/admin> pwd
/opt/app/oracle/product/11.1/rdbms/admin
oracle@apt-amd-02:/opt/app/oracle/product/11.1/rdbms/admin> sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Apr 27 09:28:04 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

SQL> spool anujupgrade.lst

SQL> @utlu111i.sql
Oracle Database 11.1 Pre-Upgrade Information Tool 04-27-2010 09:29:25
.
**********************************************************************
Database:
**********************************************************************
--> name: VIHAAN
--> version: 10.2.0.4.0
--> compatible: 10.2.0.3.0
--> blocksize: 8192
--> platform: Linux x86 64-bit
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 727 MB
.... AUTOEXTEND additional space required: 247 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 473 MB
.... AUTOEXTEND additional space required: 443 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 425 MB
.... AUTOEXTEND additional space required: 175 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.... AUTOEXTEND additional space required: 41 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 69 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
WARNING: --> "sga_target" needs to be increased to at least 672 MB
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
--> "background_dump_dest" replaced by "diagnostic_dest"
--> "user_dump_dest" replaced by "diagnostic_dest"
--> "core_dump_dest" replaced by "diagnostic_dest"
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Data Mining [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database contains stale optimizer statistics.
.... Refer to the 11g Upgrade Guide for instructions to update
.... statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS
.... OLAPSYS
.... SYSMAN
.... CTXSYS
.... XDB
.... MDSYS
WARNING: --> Database contains INVALID objects prior to upgrade.
.... USER PUBLIC has 1 INVALID objects.
.... USER SYS has 2 INVALID objects.
WARNING: --> Database contains schemas with objects dependent on network
packages.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... 11g Upgrade Guide for instructions to save the EM data prior to upgrade.
.

PL/SQL procedure successfully completed.

SQL> select * from v$timezone_file;
If time zone file version is less than 4 then apply time zone patch 5632264 manually

select object_name, owner, object_type from all_objects where status like 'INVALID';


SQL> select object_name, owner, object_type from all_objects where status like 'INVALID';

OBJECT_NAME OWNER
------------------------------ ------------------------------
OBJECT_TYPE
-------------------
DBMS_REGISTRY SYS
PACKAGE BODY

DBA_REGISTRY_DATABASE SYS
VIEW

DBA_REGISTRY_DATABASE PUBLIC
SYNONYM

col COMP_NAME format a50
set linesize 200
set pagesize 200
SQL> r
1* select comp_name,version, status from dba_registry

COMP_NAME VERSION STATUS
-------------------------------------------------- ------------------------------ --------------------------------------------
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and Types 10.2.0.4.0 VALID
Oracle Workspace Manager 10.2.0.4.3 VALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID
Oracle Expression Filter 10.2.0.4.0 VALID
Oracle Data Mining 10.2.0.4.0 VALID
Oracle Text 10.2.0.4.0 VALID
Oracle XML Database 10.2.0.4.0 VALID
Oracle Rules Manager 10.2.0.4.0 VALID
Oracle interMedia 10.2.0.4.0 VALID
OLAP Analytic Workspace 10.2.0.4.0 VALID
Oracle OLAP API 10.2.0.4.0 VALID
OLAP Catalog 10.2.0.4.0 VALID
Spatial 10.2.0.4.0 VALID
Oracle Enterprise Manager 10.2.0.4.0 VALID

17 rows selected.



1.7 If you are using spfile, create pfile
SQL> create pfile from spfile ;

This will create pfile in 10g $ORACLE_HOME/dbs/init[SID].ora





a) Remove *.background_dump_dest, *.core_dump_dest, *.user_dump_dest and add
*.diagnostic_dest=’/11g_base’ (11g Base Directory)
b) Change
*.compatible='10.2.0.1.0'
to
*.compatible=’11.1.0′




vihaan.__db_cache_size=377487360
vihaan.__java_pool_size=4194304
vihaan.__large_pool_size=4194304
vihaan.__shared_pool_size=142606336
vihaan.__streams_pool_size=0
*.control_files='/opt/app/oracle/datafile/vihaan/control01.ctl','/opt/app/oracle/datafile/vihaan/control02.ctl','/opt/app/oracle/datafile/vihaan/control03.ctl'
*.db_block_size=8192
*.db_domain='apt-amd-02'
*.db_file_multiblock_read_count=16
*.db_name='vihaan'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=vihaanXDB)'
*.job_queue_processes=10
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.pga_aggregate_target=199229440
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=536870912
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.compatible='11.1.0'
*.diagnostic_dest='/opt/app/oracle/admin/vihaan/diagnostic'



oracle@apt-amd-02:/opt/app/oracle/product/10.2/dbs> mkdir -p /opt/app/oracle/admin/vihaan/diagnostic
oracle@apt-amd-02:/opt/app/oracle/product/10.2/dbs> cd /opt/app/oracle/admin/vihaan/
oracle@apt-amd-02:/opt/app/oracle/admin/vihaan> ls -lt
total 8
drwxr-xr-x 2 oracle oinstall 6 2010-04-27 09:43 diagnostic
drwxr-x--- 2 oracle oinstall 4096 2010-04-27 09:29 adump
drwxr-x--- 2 oracle oinstall 4096 2010-04-27 09:20 udump
drwxr-x--- 2 oracle oinstall 141 2010-04-27 09:20 bdump
drwxr-x--- 2 oracle oinstall 35 2010-04-26 13:26 pfile
drwxr-x--- 2 oracle oinstall 6 2010-04-26 13:20 cdump
drwxr-x--- 2 oracle oinstall 6 2010-04-26 13:20 dpdump


2. Upgrade Database

Shut down source database (10g) - Your downtime starts here

SQL> connect sys/sys as sysdba
Connected.
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.



2.2 Set your environment variables to Oracle Database 11g Release 1 (11.1) :

export ORACLE_HOME=/u01/oracle/11gbase/11.1.0
export ORACLE_SID=TEST
export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_BASE=/opt/app/oracle
export ORACLE_HOME=/opt/app/oracle/product/10.2
#export ORACLE_HOME=/opt/app/oracle/product/11.1
export ORACLE_SID=vihaan
export PATH=$ORACLE_HOME/bin:$PATH:.
export LIBXCB_ALLOW_SLOPPY_LOCK=1
export TZ=GMT

comment the oracle 10g home and uncomment 11g




2.3 Start Upgrade
oracle@apt-amd-02:~> pwd
/home/oracle
oracle@apt-amd-02:~> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Apr 27 09:50:25 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup upgrade pfile='/tmp/initvihaan.ora' ;
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2146112 bytes
Variable Size 150995136 bytes
Database Buffers 377487360 bytes
Redo Buffers 3833856 bytes
Database mounted.
Database opened.


Check shared_pool & java_pool size, to set new values



SQL> spool anujupgrade.log
SQL> @?/rdbms/admin/catupgrd.sql

after catupgrd.sql database will be shutdown .


SQL> /*****************************************************************************/
SQL> /* Step 10 - SHUTDOWN THE DATABASE..!!!!!
SQL> */
SQL> /*****************************************************************************/
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The above sql script is the final step of the upgrade. Please
DOC> review any errors in the spool log file. If there are any errors in
DOC> the spool file, consult the Oracle Database Upgrade Guide for
DOC> troubleshooting recommendations.
DOC>
DOC> Next restart for normal operation, and then run utlrp.sql to
DOC> recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> Rem Set errorlogging off
SQL> SET ERRORLOGGING OFF;
SQL>
SQL> Rem *********************************************************************
SQL> Rem END catupgrd.sql
SQL> Rem *********************************************************************
SQL>

=====


SQL> startup pfile='/tmp/initvihaan.ora' ;
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2146112 bytes
Variable Size 150995136 bytes
Database Buffers 377487360 bytes
Redo Buffers 3833856 bytes
Database mounted.
Database opened.

SQL> @?/rdbms/admin/utlu111s.sql




Oracle Database 11.1 Post-Upgrade Status Tool 04-27-2010 12:11:48
.
Component Status Version HH:MM:SS
.
Oracle Server
. VALID 11.1.0.6.0 00:42:49
JServer JAVA Virtual Machine
. VALID 11.1.0.6.0 00:20:34
Oracle Workspace Manager
. VALID 10.2.0.4.3 00:00:01
OLAP Analytic Workspace
. VALID 11.1.0.6.0 00:00:55
OLAP Catalog
. VALID 11.1.0.6.0 00:01:40
Oracle OLAP API
. VALID 11.1.0.6.0 00:00:26
Oracle Enterprise Manager
. ORA-06550: line 5, column 35:
. PL/SQL: ORA-00942: table or view does not exist
. ORA-06550: line 5, column 1:
. PL/SQL: SQL Statement ignored
. ORA-00001: unique constraint (SYSMAN.PARAMETERS_PRIMARY_KEY) violated
. ORA-06512: at "SYSMAN.MGMT_TIME_SYNC", line 108
. ORA-06512: at "SYSMAN.MGMT_TIME_SYNC", line 166
. ORA-06512: at line 2
. VALID 11.1.0.6.0 00:20:30
Oracle XDK
. VALID 11.1.0.6.0 00:01:37
Oracle Text
. VALID 11.1.0.6.0 00:01:41
Oracle XML Database
. VALID 11.1.0.6.0 00:13:43
Oracle Database Java Packages
. VALID 11.1.0.6.0 00:00:42
Oracle Multimedia
. VALID 11.1.0.6.0 00:09:04
Spatial
. VALID 11.1.0.6.0 00:06:41
Oracle Expression Filter
. VALID 11.1.0.6.0 00:00:15
Oracle Rules Manager
. VALID 11.1.0.6.0 00:00:13
Gathering Statistics
. 00:05:25
Total Upgrade Time: 02:06:28

PL/SQL procedure successfully completed.

SQL>
SQL> SET SERVEROUTPUT OFF
SQL> SET VERIFY ON



Workaround:

These errors do not result in any data loss. Therefore, you can ignore these errors.


===============================
or apply Patch 7449757

oracle@apt-amd-02:~> unzip p7449757_111070_Generic.zip
Archive: p7449757_111070_Generic.zip
creating: 7449757/
creating: 7449757/etc/
creating: 7449757/etc/xml/
inflating: 7449757/etc/xml/ShiphomeDirectoryStructure.xml
inflating: 7449757/etc/xml/GenericActions.xml
creating: 7449757/etc/config/
inflating: 7449757/etc/config/actions.xml
inflating: 7449757/etc/config/inventory.xml
inflating: 7449757/README.txt
creating: 7449757/files/
creating: 7449757/files/rdbms/
creating: 7449757/files/rdbms/admin/
inflating: 7449757/files/rdbms/admin/i1002000.sql

oracle@apt-amd-02:~> cd 7449757/
oracle@apt-amd-02:~/7449757> pwd
/home/oracle/7449757
oracle@apt-amd-02:~/7449757> /opt/app/oracle/product/11.1/OPatch/opatch apply
Invoking OPatch 11.1.0.6.0


====


SQL> @?/rdbms/admin/catuppst.sql
SQL> @?/rdbms/admin/utlrp.sql


SQL> create spfile from pfile='/tmp/initvihaan.ora' ;

File created.


Check invalid objects
SQL> select count(*) from dba_objects where status like 'INVALID';

COUNT(*)
----------
0


Post Upgrade steps

Check status of database components
SQL>select comp_name,version, status from dba_registry;

SQL> col COMP_NAME format a40
SQL> set pagesize 200
SQL> set linesize 200
SQL> col STATUS format a15
SQL> select comp_name,version, status from dba_registry

COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ ---------------
Oracle Enterprise Manager 11.1.0.6.0 VALID
OLAP Catalog 11.1.0.6.0 VALID
Spatial 11.1.0.6.0 VALID
Oracle Multimedia 11.1.0.6.0 VALID
Oracle XML Database 11.1.0.6.0 VALID
Oracle Text 11.1.0.6.0 VALID
Oracle Data Mining 11.1.0.6.0 VALID
Oracle Expression Filter 11.1.0.6.0 VALID
Oracle Rules Manager 11.1.0.6.0 VALID
Oracle Workspace Manager 10.2.0.4.3 VALID
Oracle Database Catalog Views 11.1.0.6.0 VALID
Oracle Database Packages and Types 11.1.0.6.0 VALID
JServer JAVA Virtual Machine 11.1.0.6.0 VALID
Oracle XDK 11.1.0.6.0 VALID
Oracle Database Java Packages 11.1.0.6.0 VALID
OLAP Analytic Workspace 11.1.0.6.0 VALID
Oracle OLAP API 11.1.0.6.0 VALID

17 rows selected.




Copy tnsnames.ora, listener.ora, sqlnet.ora and include file from source (10g)
oracle_home to target (11g) oracle_home


===
useful link

# 429825.1 Complete Checklist for Manual Upgrades to 11gR1
# 744693.1 ORA-00001 For SYS.DIANA_VERSION$ During RDBMS Upgrade From 11.1.0.6 To 11.1.0.7
# 413671.1 Applying version 4 Time Zone Files on an Oracle Database
# 396387.1 Workarounds when Database time zone patches are not available for your patchset
# 396671.1 Usage of utltzuv2.sql before updating time zone files in Oracle 10
# 730057.1 Upgrading to 11g Fails with ORA-01722: invalid number

Check Metalink note: Complete Checklist for Manual Upgrades to 11gR1 - 429825.1

Oracle re-create or recover  parameter file
..

set linesize 300 pagesize 300

column num noprint

col value for a100

col name for a35

select num, '*' "thread#", name, value from v$parameter where num in (select num from v$parameter 

WHERE (isdefault = 'FALSE' or ismodified <> 'FALSE') and name NOT LIKE 'nls%'

MINUS

select num from gv$parameter gvp, gv$instance gvi 

where 1=1

and num in   ( select distinct gvpa.num from gv$parameter gvpa, gv$parameter gvpb where gvpa.num = gvpb.num 

and  gvpa.value <> gvpb.value 

and (gvpa.isdefault = 'FALSE' or gvpa.ismodified <> 'FALSE') AND gvpa.name NOT LIKE 'nls%'

              ) 

and gvi.inst_id = gvp.inst_id  

and (gvp.isdefault = 'FALSE' or gvp.ismodified <> 'FALSE') 

and gvp.name not like 'nls%')

union

select num, to_char(thread#) "thread#", name, value from gv$parameter gvp, gv$instance gvi 

where 1=1

and num in (select distinct gvpa.num from gv$parameter gvpa, gv$parameter gvpb 

where 1=1

and gvpa.num = gvpb.num 

and gvpa.value <> gvpb.value 

and (gvpa.isdefault = 'FALSE' or gvpa.ismodified <> 'FALSE') 

and gvp.name not like 'nls%'

           ) 

and gvi.inst_id = gvp.inst_id  

and (gvp.isdefault = 'FALSE' or gvp.ismodified <> 'FALSE') 

and gvp.name NOT LIKE 'nls%' 

order by 1, 2;

=============

Oracle re-create or recover parameter file

SELECT

initcap(p2.value) ||' startup with '|| DECODE(p1.value, NULL,'pfile', 'spfile') || ' on '|| to_char(startup_time,'dd/mon/yyyy hh24:mi:ss') "Instance startup info"

FROM v$parameter p1, v$parameter p2, v$instance

WHERE p1.name = 'spfile'

and p2.name='db_name';

-- NUMB

col name format a25

col value format a20

-- type is_defaul is_se is_system is_modified is_ad

col description format a20

set linesize 200  pagesize 200

select

nam.indx+1 numb,

nam.ksppinm name,

val.ksppstvl value,

nam.ksppity type,

val.ksppstdf is_default,

decode(bitand(nam.ksppiflg/256,1),1,'True','False') is_session_modifiable,

decode(bitand(nam.ksppiflg/65536,3),1,'Immediate',2,'Deferred' ,3,'Immediate','False') is_system_modifiable,

decode(bitand(val.ksppstvf,7),1,'Modified',4,'System Modified','False') is_modified,

decode(bitand(val.ksppstvf,2),2,'True','False') is_adjusted,

nam.ksppdesc description

from x$ksppi nam,

x$ksppsv val

where nam.indx = val.indx

/

select

nam.indx+1 numb,

nam.ksppinm name,

val.ksppstvl value,

nam.ksppity type,

val.ksppstdf is_default,

decode(bitand(nam.ksppiflg/256,1),1,'True','False') is_session_modifiable,

decode(bitand(nam.ksppiflg/65536,3),1,'Immediate',2,'Deferred' ,3,'Immediate','False') is_system_modifiable,

decode(bitand(val.ksppstvf,7),1,'Modified',4,'System Modified','False') is_modified,

decode(bitand(val.ksppstvf,2),2,'True','False') is_adjusted,nam.ksppdesc description

from x$ksppi nam,x$ksppcv val

where nam.indx = val.indx

;

/

===

set head off

set feed off

set pages 0

--spool $ORACLE_ADMIN/pfile/initPROD920.ora.sav

SELECT

'######################################' ||CHR(10)||

'# file : init'||upper(value)||'.ora #' ||CHR(10) ||

'# Date : '||to_char(sysdate,'DD/MM/YYYY HH24:MI:SS')|| ' #' ||CHR(10)||

'######################################' ||CHR(10) ||CHR(10)

FROM v$parameter

WHERE name = 'db_name';

SELECT '# '|| DESCRIPTION ||CHR(10) ,NAME||'='|| DECODE(VALUE,'TRUE',VALUE,'FALSE',VALUE,

DECODE(SIGN(INSTR(VALUE,' ')),1,'( "'||REPLACE(VALUE,', ','",'||CHR(10)||' "')||'" )', '"'||VALUE||'"'))||DECODE(upper(ISDEFAULT),'TRUE',' # (Default value) - ',NULL)|| CHR(10)

FROM v$parameter

WHERE ISDEFAULT = 'FALSE'

ORDER BY num;

SELECT '############### END ################' FROM DUAL;

--spool off;

======

col param_name format a40 heading "Parameter Name"

col param_value format a38 heading "Parameter Value" word_wrap

col isdefault format a7 heading "Default|Value"

col isses_modifiable format a10 heading "Session|Modifiable"

col issys_modifiable format a10 heading "System|Modifiable"

col ismod                          heading "Is|Modified"

-- Get database name and store in variable

column name new_value s_dbname

SELECT rtrim(name) name from v$database;

-- Get today's date

column today new_value s_curDate

SELECT to_char(sysdate, 'Month DD, YYYY') today from dual;

-- Get host name and store in variable

column host_name new_value s_machine

SELECT host_name from v$instance;

set termout on

set feedback off

set head on

select name param_name,value param_value,isdefault,ismodified ismod,isses_modifiable,issys_modifiable from v$parameter

order by param_name;

spool off

ttitle off

btitle off

clear columns

clear breaks

set feedback on

set termout on

set verify on

===

VARIABLE BgnSnap NUMBER

VARIABLE EndSnap NUMBER

VARIABLE DID NUMBER

VARIABLE INST_NUMBER number

exec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ;

exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;

exec select DBID into :DID from v$database;

exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;

set linesize 300 pagesiz 300

col NAME for a45

col BVAL for a20

col EVAL for a20

select e.parameter_name             name

         , b.value                      bval

         , decode(b.value, e.value, NULL, e.value) eval

      from dba_hist_parameter b

         , dba_hist_parameter e

     where b.snap_id(+)         = :BgnSnap

       and e.snap_id            = :EndSnap

       and b.dbid(+)            = :DID 

       and e.dbid               = :DID 

       and b.instance_number(+) = 1

       and e.instance_number    = 1

       and b.parameter_hash(+)  = e.parameter_hash

       and (   nvl(b.isdefault, 'X')   = 'FALSE'

            or nvl(b.ismodified,'X')  != 'FALSE'

            or     e.ismodified       != 'FALSE'

            or nvl(e.value,0)         != nvl(b.value,0)

           )

       and e.parameter_name not like '\_\_%' escape '\'

     order by e.parameter_name;

 

----

 

set linesize 600 pagesize 300

col sid for a20

col name for a20

col value for a70

col host_name  for a15

select sid,SYS_CONTEXT('USERENV', 'HOST', 15) host_name ,name,value from v$spparameter where isspecified='TRUE' 

--and name='event' 

 order by 2;

==========

set linesize 300 pagesize 20

col NAME for a20

col VALUE for a20

col DESCRIPTION for a70

select name, value, isdefault, isses_modifiable, issys_modifiable,isinstance_modifiable, isdeprecated, description from v$parameter where upper(name) = 'PARALLEL_MAX_SERVERS';

set linesize 400 pagesize 300

col name for a30

SELECT name,

       CASE

         WHEN type = 1 THEN 'Boolean'

         WHEN type = 2 THEN 'String'

         WHEN type = 3 THEN 'Integer'

         WHEN type = 4 THEN 'Parameter file'

         WHEN type = 5 THEN 'Reserved'

         WHEN type = 6 THEN 'Big integer'

       END AS parameter_type,

       default_value

 FROM v$parameter

  WHERE ispdb_modifiable = 'TRUE' 

AND isdeprecated = 'FALSE'

   ORDER BY name;

=================

SELECT name,

           CASE

             WHEN type = 1 THEN 'Boolean'

             WHEN type = 2 THEN 'String'

             WHEN type = 3 THEN 'Integer'

             WHEN type = 4 THEN 'Parameter file'

             WHEN type = 5 THEN 'Reserved'

             WHEN type = 6 THEN 'Big integer'

           END AS parameter_type,

          default_value

    FROM v$parameter

     WHERE ispdb_modifiable = 'TRUE' AND isdeprecated = 'FALSE'

      ORDER BY name;

 

Oracle script to monitor for the PGA per session

 
 
 ORACLE PGA


The PGA (Program or Process Global Area) is a memory area (RAM) that stores data and control information for a single process. For example, it typically contains a sort area, hash area, session cursor cache, etc.


prompt
prompt PGA Usage Summary Report
prompt ------------------------

col alloc_sum format 999,999,990 heading 'Total Allocation'
col free_sum format 999,999,990 heading 'Total Freeable'
col used_sum format 999,999,990 heading 'Total In Use'
col pga_range heading 'PGA Range'
col sessions heading 'No.|Sessions'

compute sum of alloc_sum on report
compute sum of used_sum on report
compute sum of free_sum on report
break on report

select count (*) as sessions
, case
when pga_alloc_mem < (1*1024*1024) then ' < 1Mb'
when pga_alloc_mem < (2*1024*1024) then ' 1Mb-> 2Mb'
when pga_alloc_mem < (3*1024*1024) then ' 2Mb-> 3Mb'
when pga_alloc_mem < (4*1024*1024) then ' 3Mb-> 4Mb'
when pga_alloc_mem < (5*1024*1024) then ' 4Mb-> 5Mb'
when pga_alloc_mem < (10*1024*1024) then ' 5Mb->10Mb'
when pga_alloc_mem < (15*1024*1024) then '10Mb->15Mb'
when pga_alloc_mem < (20*1024*1024) then '15Mb->20Mb'
when pga_alloc_mem < (25*1024*1024) then '20Mb->25Mb'
else '25Mb+ '
end as pga_range
, sum (pga_alloc_mem) as alloc_sum
, sum (pga_used_mem) as used_sum
, sum (pga_freeable_mem) as free_sum
from v$process
group by case
when pga_alloc_mem < (1*1024*1024) then ' < 1Mb'
when pga_alloc_mem < (2*1024*1024) then ' 1Mb-> 2Mb'
when pga_alloc_mem < (3*1024*1024) then ' 2Mb-> 3Mb'
when pga_alloc_mem < (4*1024*1024) then ' 3Mb-> 4Mb'
when pga_alloc_mem < (5*1024*1024) then ' 4Mb-> 5Mb'
when pga_alloc_mem < (10*1024*1024) then ' 5Mb->10Mb'
when pga_alloc_mem < (15*1024*1024) then '10Mb->15Mb'
when pga_alloc_mem < (20*1024*1024) then '15Mb->20Mb'
when pga_alloc_mem < (25*1024*1024) then '20Mb->25Mb'
else '25Mb+ '
end
order by pga_range
/



prompt SQL work area (current)
col sid for 99990
col operation_type for a20
col wsize  for 999,990 heading 'Current|W.Size(k)'
col esize  for 999,990 heading 'Expected|W.Size(k)'
col amsize for 999,999,990 heading 'Current|Mem.(k)'
col mmsize for 999,999,990 heading 'Maximum|Mem.(k)'
col tsize  for 999,999,990 heading 'Tmp. Seg.|Size (k)'
col passes for 999,990 heading 'Passes'
select SID
     , OPERATION_TYPE
     , WORK_AREA_SIZE/1024   as wsize
     , EXPECTED_SIZE/1024    as esize
     , ACTUAL_MEM_USED/1024  as amsize
     , MAX_MEM_USED/1024     as mmsize
     , TEMPSEG_SIZE/1024     as tsize
     , NUMBER_PASSES         as passes
from gv$sql_workarea_active
order by sid
/

--clear breaks
--clear computes

Script to monitor for the 'session pga memory'



col VALUE format 9999999999999
col name format a40
select name,value ,unit from V$PGASTAT ;


SELECT PROGRAM, PGA_USED_MEM, PGA_ALLOC_MEM/(1024*1024) "ALLOC(MB)",
PGA_FREEABLE_MEM FREEABLE, PGA_MAX_MEM
FROM V$PROCESS
order by pga_alloc_mem asc;

Show the maximum PGA usage per process:

select max(pga_used_mem), max(pga_alloc_mem), max(pga_max_mem) from v$process;


 
 set pagesize 100
prompt PER SESSION PGA
col TXT format a30
col MACHINE format a12
col MODULE format a12
col "MEMORY/SESSION" format a10
col EVENT format a12
col program format a12
col unix_pid format a8
col schemaname format a10
col username format a12
col sid_serial format a10
SELECT
a.sid||','|| c.serial# sid_serial, lpad(e.spid,7) unix_pid,
round(sum(a.value)/1024/1024,2) || ' MB ' "MEMORY/SESSION",
c.username, c.schemaname, c.machine, c.status, c.server,
c.program, c.module, c.event,d.sql_id,
round(e.pga_used_mem/1024/1024,2) as pga_used_mem,
round(e.pga_alloc_mem/1024/1024,2) as pga_alloc_mem,
round(e.pga_freeable_mem/1024/1024,2) as pga_freeable_mem,
round(e.pga_max_mem/1024/1024,2) as pga_max_mem
FROM v$sesstat a, v$statname b, v$session c, v$sqlarea d, v$process e
WHERE name = 'session pga memory'
AND a.statistic# = b.statistic#
AND a.sid = c.sid
AND c.sql_address = d.address(+)
AND c.sql_hash_value = d.hash_value(+)
AND e.addr=c.paddr
GROUP BY a.sid, c.serial#, lpad(e.spid,7),
c.username, c.schemaname, c.machine, c.status, c.server, c.program,
c.module, c.event,d.sql_id,e.pga_used_mem/1024/1024, e.pga_alloc_mem/1024/1024,
e.pga_freeable_mem/1024/1024, e.pga_max_mem/1024/1024
ORDER BY round(sum(a.value)/1024/1024,2) DESC;
 
 
 SGA Pools History Report from AWR (include free memory)
 
 https://github.com/carlos-sierra/cscripts/blob/master/cs_sgastat_awr_report.sql
 
 set linesize 300 pagesize 300
 
 VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -2 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;
 
 define cs_instance_number=1
 define cs_dbid=''
 COL snap_id 					FOR 9999999 	HEA 'Snap ID'
COL begin_time 					FOR A19 		HEA 'Begin Time'
COL end_time 					FOR A19 		HEA 'End Time'
COL pga_aggregate_target 		FOR 9,990.000 	HEA 'PGA|Aggregate|Target'
COL sga_target 					FOR 9,990.0 	HEA 'SGA|Target'
COL buffer_cache 				FOR 9,990.000 	HEA 'Buffer|Cache'
COL log_buffer 					FOR 9,990.000 	HEA 'Log|Buffer'
COL shared_io_pool 				FOR 9,990.000 	HEA 'Shared|IO Pool'
COL fixed_sga 					FOR 9,990.000 	HEA 'Fixed|SGA'
COL shared_pool 				FOR 9,990.000 	HEA 'Shared|Pool'
COL shared_pool_free_memory 	FOR 9,990.000 	HEA 'Shared|Pool|Free|Memory'
COL large_pool 					FOR 9,990.000 	HEA 'Large|Pool'
COL large_pool_free_memory 		FOR 9,990.000 	HEA 'Large|Pool|Free|Memory'
COL java_pool 					FOR 9,990.000 	HEA 'Java|Pool'
COL java_pool_free_memory 		FOR 9,990.000 	HEA 'Java|Pool|Free|Memory'
COL streams_pool 				FOR 9,990.000 	HEA 'Streams|Pool'
COL streams_pool_free_memory 	FOR 9,990.000 	HEA 'Streams|Pool|Free|Memory'
--
PRO
PRO Memory Pools (GBs)
PRO ~~~~~~~~~~~~~~~~~~
WITH
sgastat AS (
SELECT /*+ MATERIALIZE NO_MERGE */
       snap_id,
       ROUND(SUM(CASE WHEN name = 'buffer_cache' AND pool IS NULL THEN bytes ELSE 0 END)/POWER(2,30), 3) buffer_cache, -- see bug 18166499
       ROUND(SUM(CASE WHEN name = 'log_buffer' AND pool IS NULL THEN bytes ELSE 0 END)/POWER(2,30), 3) log_buffer,
       ROUND(SUM(CASE WHEN name = 'shared_io_pool' AND pool IS NULL THEN bytes ELSE 0 END)/POWER(2,30), 3) shared_io_pool,
       ROUND(SUM(CASE WHEN name = 'fixed_sga' AND pool IS NULL THEN bytes ELSE 0 END)/POWER(2,30), 3) fixed_sga,
       ROUND(SUM(CASE WHEN pool = 'shared pool' AND name IS NULL THEN bytes ELSE 0 END)/POWER(2,30), 3) shared_pool, -- see bug 18166499
       ROUND(SUM(CASE WHEN pool = 'shared pool' AND name = 'free memory' THEN bytes ELSE 0 END)/POWER(2,30), 3) shared_pool_free_memory,
       ROUND(SUM(CASE WHEN pool = 'large pool'  AND name IS NULL THEN bytes ELSE 0 END)/POWER(2,30), 3) large_pool, -- see bug 18166499
       ROUND(SUM(CASE WHEN pool = 'large pool' AND name = 'free memory' THEN bytes ELSE 0 END)/POWER(2,30), 3) large_pool_free_memory,
       ROUND(SUM(CASE WHEN pool = 'java pool' AND name IS NULL THEN bytes ELSE 0 END)/POWER(2,30), 3) java_pool, -- see bug 18166499
       ROUND(SUM(CASE WHEN pool = 'java pool' AND name = 'free memory' THEN bytes ELSE 0 END)/POWER(2,30), 3) java_pool_free_memory,
       ROUND(SUM(CASE WHEN pool = 'streams pool' AND name IS NULL THEN bytes ELSE 0 END)/POWER(2,30), 3) streams_pool, -- see bug 18166499
       ROUND(SUM(CASE WHEN pool = 'streams pool' AND name = 'free memory' THEN bytes ELSE 0 END)/POWER(2,30), 3) streams_pool_free_memory
  FROM dba_hist_sgastat
 WHERE 1=1
 and dbid = TO_NUMBER(:DID)
   AND instance_number = TO_NUMBER(:INST_NUMBER )
   AND snap_id BETWEEN TO_NUMBER(:BgnSnap) AND TO_NUMBER(:EndSnap)
 GROUP BY
       snap_id
),
param AS (
SELECT /*+ MATERIALIZE NO_MERGE */
       snap_id,
       ROUND(SUM(CASE parameter_name WHEN '__db_cache_size' THEN TO_NUMBER(value) ELSE 0 END)/POWER(2,30), 3) buffer_cache,
       ROUND(SUM(CASE parameter_name WHEN '__shared_io_pool_size' THEN TO_NUMBER(value) ELSE 0 END)/POWER(2,30), 3) shared_io_pool,
       ROUND(SUM(CASE parameter_name WHEN '__shared_pool_size' THEN TO_NUMBER(value) ELSE 0 END)/POWER(2,30), 3) shared_pool,
       ROUND(SUM(CASE parameter_name WHEN '__large_pool_size' THEN TO_NUMBER(value) ELSE 0 END)/POWER(2,30), 3) large_pool,
       ROUND(SUM(CASE parameter_name WHEN '__java_pool_size' THEN TO_NUMBER(value) ELSE 0 END)/POWER(2,30), 3) java_pool,
       ROUND(SUM(CASE parameter_name WHEN '__streams_pool_size' THEN TO_NUMBER(value) ELSE 0 END)/POWER(2,30), 3) streams_pool,
       ROUND(SUM(CASE parameter_name WHEN '__sga_target' THEN TO_NUMBER(value) ELSE 0 END)/POWER(2,30), 3) sga_target,
       ROUND(SUM(CASE parameter_name WHEN '__pga_aggregate_target' THEN TO_NUMBER(value) ELSE 0 END)/POWER(2,30), 3) pga_aggregate_target
  FROM dba_hist_parameter
 WHERE 1=1 
 and dbid = TO_NUMBER(:DID)
   AND instance_number = TO_NUMBER(:INST_NUMBER )
   AND snap_id BETWEEN TO_NUMBER(:BgnSnap) AND TO_NUMBER(:EndSnap)
 --  AND parameter_name IN ('__db_cache_size', '__java_pool_size', '__large_pool_size', '__pga_aggregate_target', '__sga_target', '__shared_io_pool_size', '__shared_pool_size', '__streams_pool_size')
 GROUP BY
       snap_id
),
my_query AS (
SELECT /*+ MATERIALIZE NO_MERGE */
       s.snap_id,
       CAST(s.begin_interval_time AS DATE) begin_time,
       CAST(s.end_interval_time AS DATE) end_time,
       p.pga_aggregate_target,
       p.sga_target,
       p.buffer_cache,
       p.shared_pool,
       p.large_pool,
       p.java_pool,
       p.streams_pool,
       p.shared_io_pool,
       t.shared_pool_free_memory,
       t.large_pool_free_memory,
       t.java_pool_free_memory,
       t.streams_pool_free_memory
  FROM dba_hist_snapshot s,
       sgastat t,
       param p
 WHERE 1=1
 and s.dbid = TO_NUMBER(:DID)
   AND s.instance_number = TO_NUMBER(:INST_NUMBER )
   AND s.snap_id BETWEEN TO_NUMBER(:BgnSnap) AND TO_NUMBER(:EndSnap)
   AND t.snap_id = s.snap_id
   AND p.snap_id = s.snap_id
)
SELECT end_time,
       pga_aggregate_target,
       sga_target,
       buffer_cache,
       shared_pool,
       large_pool,
       java_pool,
       streams_pool,
       shared_io_pool,
       shared_pool_free_memory,
       large_pool_free_memory,
       java_pool_free_memory,
       streams_pool_free_memory
  FROM my_query
 ORDER BY
       snap_id
/



                                                                                                                Shared      Large       Java    Streams
                           PGA                                                                                  Pool       Pool       Pool       Pool
                     Aggregate      SGA     Buffer     Shared      Large       Java    Streams     Shared       Free       Free       Free       Free
End Time                Target   Target      Cache       Pool       Pool       Pool       Pool    IO Pool     Memory     Memory     Memory     Memory
------------------- ---------- -------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
23-SEP-22                0.000      0.0      0.000      0.000      0.000      0.000      0.000      0.000      0.535      0.035      0.016      0.000
23-SEP-22                0.000      0.0      0.000      0.000      0.000      0.000      0.000      0.000      0.535      0.035      0.016      0.000
23-SEP-22                0.000      0.0      0.000      0.000      0.000      0.000      0.000      0.000      0.521      0.035      0.016      0.000





WITH
pgastat_denorm_1 AS (
SELECT /*+ MATERIALIZE NO_MERGE */
       snap_id,
       dbid,
       instance_number,
       SUM(CASE name WHEN 'PGA memory freed back to OS'           THEN value ELSE 0 END) pga_mem_freed_to_os,
       SUM(CASE name WHEN 'aggregate PGA auto target'             THEN value ELSE 0 END) aggr_pga_auto_target,
       SUM(CASE name WHEN 'aggregate PGA target parameter'        THEN value ELSE 0 END) aggr_pga_target_param,
       SUM(CASE name WHEN 'bytes processed'                       THEN value ELSE 0 END) bytes_processed,
       SUM(CASE name WHEN 'extra bytes read/written'              THEN value ELSE 0 END) extra_bytes_rw,
       SUM(CASE name WHEN 'global memory bound'                   THEN value ELSE 0 END) global_memory_bound,
       SUM(CASE name WHEN 'maximum PGA allocated'                 THEN value ELSE 0 END) max_pga_allocated,
       SUM(CASE name WHEN 'maximum PGA used for auto workareas'   THEN value ELSE 0 END) max_pga_used_aut_wa,
       SUM(CASE name WHEN 'maximum PGA used for manual workareas' THEN value ELSE 0 END) max_pga_used_man_wa,
       SUM(CASE name WHEN 'total PGA allocated'                   THEN value ELSE 0 END) tot_pga_allocated,
       SUM(CASE name WHEN 'total PGA inuse'                       THEN value ELSE 0 END) tot_pga_inuse,
       SUM(CASE name WHEN 'total PGA used for auto workareas'     THEN value ELSE 0 END) tot_pga_used_aut_wa,
       SUM(CASE name WHEN 'total PGA used for manual workareas'   THEN value ELSE 0 END) tot_pga_used_man_wa,
       SUM(CASE name WHEN 'total freeable PGA memory'             THEN value ELSE 0 END) tot_freeable_pga_mem
  FROM dba_hist_pgastat
 WHERE name IN
('PGA memory freed back to OS'
,'aggregate PGA auto target'
,'aggregate PGA target parameter'
,'bytes processed'
,'extra bytes read/written'
,'global memory bound'
,'maximum PGA allocated'
,'maximum PGA used for auto workareas'
,'maximum PGA used for manual workareas'
,'total PGA allocated'
,'total PGA inuse'
,'total PGA used for auto workareas'
,'total PGA used for manual workareas'
,'total freeable PGA memory'
)
   AND snap_id in (select snap_id from dba_hist_snapshot where begin_interval_time > sysdate -60)
 GROUP BY
       snap_id,
       dbid,
       instance_number
),
pgastat_denorm_2 AS (
SELECT /*+ MATERIALIZE NO_MERGE */
       h.dbid,
       h.instance_number,
       s.startup_time,
       MIN(h.pga_mem_freed_to_os) pga_mem_freed_to_os,
       MIN(h.bytes_processed) bytes_processed,
       MIN(h.extra_bytes_rw) extra_bytes_rw
  FROM pgastat_denorm_1 h,
       dba_hist_snapshot s
 WHERE s.snap_id = h.snap_id
   AND s.dbid = h.dbid
   AND s.instance_number = h.instance_number
 GROUP BY
       h.dbid,
       h.instance_number,
       s.startup_time
),
pgastat_delta AS (
SELECT /*+ MATERIALIZE NO_MERGE */
       h1.snap_id,
       h1.dbid,
       h1.instance_number,
       s1.begin_interval_time,
       s1.end_interval_time,
       ROUND((CAST(s1.end_interval_time AS DATE) - CAST(s1.begin_interval_time AS DATE)) * 24 * 60 * 60) interval_secs,
       (h1.pga_mem_freed_to_os - h0.pga_mem_freed_to_os) pga_mem_freed_to_os,
       h1.aggr_pga_auto_target,
       h1.aggr_pga_target_param,
       (h1.bytes_processed - h0.bytes_processed) bytes_processed,
       (h1.extra_bytes_rw - h0.extra_bytes_rw) extra_bytes_rw,
       h1.global_memory_bound,
       h1.max_pga_allocated,
       h1.max_pga_used_aut_wa,
       h1.max_pga_used_man_wa,
       h1.tot_pga_allocated,
       h1.tot_pga_inuse,
       h1.tot_pga_used_aut_wa,
       h1.tot_pga_used_man_wa,
       h1.tot_freeable_pga_mem
  FROM pgastat_denorm_1 h0,
       pgastat_denorm_1 h1,
       dba_hist_snapshot s0,
       dba_hist_snapshot s1,
       pgastat_denorm_2 min /* to see cumulative use (replace h0 with min on select list above) */
 WHERE h1.snap_id = h0.snap_id + 1
   AND h1.dbid = h0.dbid
   AND h1.instance_number = h0.instance_number
   AND s0.snap_id = h0.snap_id
   AND s0.dbid = h0.dbid
   AND s0.instance_number = h0.instance_number
   AND s1.snap_id = h1.snap_id
   AND s1.dbid = h1.dbid
   AND s1.instance_number = h1.instance_number
   AND s1.snap_id = s0.snap_id + 1
   AND s1.startup_time = s0.startup_time
   AND s1.begin_interval_time > (s0.begin_interval_time + (1 / (24 * 60))) /* filter out snaps apart < 1 min */
   AND min.dbid = s1.dbid
   AND min.instance_number = s1.instance_number
   AND min.startup_time = s1.startup_time
)
SELECT snap_id,
       TO_CHAR(MIN(begin_interval_time), 'YYYY-MM-DD HH24:MI') begin_time,
       TO_CHAR(MIN(end_interval_time), 'YYYY-MM-DD HH24:MI') end_time,
       ROUND(SUM(pga_mem_freed_to_os) / POWER(2, 30), 3) pga_mem_freed_to_os,
       ROUND(SUM(aggr_pga_auto_target) / POWER(2, 30), 3) aggr_pga_auto_target,
       ROUND(SUM(aggr_pga_target_param) / POWER(2, 30), 3) aggr_pga_target_param,
       ROUND(SUM(bytes_processed) / POWER(2, 30), 3) bytes_processed,
       ROUND(SUM(extra_bytes_rw) / POWER(2, 30), 3) extra_bytes_rw,
       ROUND(SUM(global_memory_bound) / POWER(2, 30), 3) global_memory_bound,
       ROUND(SUM(max_pga_allocated) / POWER(2, 30), 3) max_pga_allocated,
       ROUND(SUM(max_pga_used_aut_wa) / POWER(2, 30), 3) max_pga_used_aut_wa,
       ROUND(SUM(max_pga_used_man_wa) / POWER(2, 30), 3) max_pga_used_man_wa,
       ROUND(SUM(tot_pga_allocated) / POWER(2, 30), 3) tot_pga_allocated,
       ROUND(SUM(tot_pga_inuse) / POWER(2, 30), 3) tot_pga_inuse,
       ROUND(SUM(tot_pga_used_aut_wa) / POWER(2, 30), 3) tot_pga_used_aut_wa,
       ROUND(SUM(tot_pga_used_man_wa) / POWER(2, 30), 3) tot_pga_used_man_wa,
       ROUND(SUM(tot_freeable_pga_mem) / POWER(2, 30), 3) tot_freeable_pga_mem,
       0 dummy_15
  FROM pgastat_delta
 GROUP BY
       snap_id
 ORDER BY
       snap_id;
	  
	

=====	


https://weidongzhou.wordpress.com/2016/02/25/pga_aggregate_target-vs-pga_aggregate_limit/comment-page-1/

set linesize 700 pagesize 300	  
WITH
pgastat_denorm_1 AS (
SELECT /*+ MATERIALIZE NO_MERGE */
       snap_id,
       dbid,
       instance_number,
       SUM(CASE name WHEN 'PGA memory freed back to OS'           THEN value ELSE 0 END) pga_mem_freed_to_os,
       SUM(CASE name WHEN 'aggregate PGA auto target'             THEN value ELSE 0 END) aggr_pga_auto_target,
       SUM(CASE name WHEN 'aggregate PGA target parameter'        THEN value ELSE 0 END) aggr_pga_target_param,
       SUM(CASE name WHEN 'bytes processed'                       THEN value ELSE 0 END) bytes_processed,
       SUM(CASE name WHEN 'extra bytes read/written'              THEN value ELSE 0 END) extra_bytes_rw,
       SUM(CASE name WHEN 'global memory bound'                   THEN value ELSE 0 END) global_memory_bound,
       SUM(CASE name WHEN 'maximum PGA allocated'                 THEN value ELSE 0 END) max_pga_allocated,
       SUM(CASE name WHEN 'maximum PGA used for auto workareas'   THEN value ELSE 0 END) max_pga_used_aut_wa,
       SUM(CASE name WHEN 'maximum PGA used for manual workareas' THEN value ELSE 0 END) max_pga_used_man_wa,
       SUM(CASE name WHEN 'total PGA allocated'                   THEN value ELSE 0 END) tot_pga_allocated,
       SUM(CASE name WHEN 'total PGA inuse'                       THEN value ELSE 0 END) tot_pga_inuse,
       SUM(CASE name WHEN 'total PGA used for auto workareas'     THEN value ELSE 0 END) tot_pga_used_aut_wa,
       SUM(CASE name WHEN 'total PGA used for manual workareas'   THEN value ELSE 0 END) tot_pga_used_man_wa,
       SUM(CASE name WHEN 'total freeable PGA memory'             THEN value ELSE 0 END) tot_freeable_pga_mem
  FROM dba_hist_pgastat
 WHERE name IN
('PGA memory freed back to OS'
,'aggregate PGA auto target'
,'aggregate PGA target parameter'
,'bytes processed'
,'extra bytes read/written'
,'global memory bound'
,'maximum PGA allocated'
,'maximum PGA used for auto workareas'
,'maximum PGA used for manual workareas'
,'total PGA allocated'
,'total PGA inuse'
,'total PGA used for auto workareas'
,'total PGA used for manual workareas'
,'total freeable PGA memory'
)
   AND snap_id in (select snap_id from dba_hist_snapshot where begin_interval_time > sysdate -60)
 GROUP BY
       snap_id,
       dbid,
       instance_number
),
pgastat_denorm_2 AS (
SELECT /*+ MATERIALIZE NO_MERGE */
       h.dbid,
       h.instance_number,
       s.startup_time,
       MIN(h.pga_mem_freed_to_os) pga_mem_freed_to_os,
       MIN(h.bytes_processed) bytes_processed,
       MIN(h.extra_bytes_rw) extra_bytes_rw
  FROM pgastat_denorm_1 h,
       dba_hist_snapshot s
 WHERE s.snap_id = h.snap_id
   AND s.dbid = h.dbid
   AND s.instance_number = h.instance_number
 GROUP BY
       h.dbid,
       h.instance_number,
       s.startup_time
),
pgastat_delta AS (
SELECT /*+ MATERIALIZE NO_MERGE */
       h1.snap_id,
       h1.dbid,
       h1.instance_number,
       s1.begin_interval_time,
       s1.end_interval_time,
       ROUND((CAST(s1.end_interval_time AS DATE) - CAST(s1.begin_interval_time AS DATE)) * 24 * 60 * 60) interval_secs,
       (h1.pga_mem_freed_to_os - h0.pga_mem_freed_to_os) pga_mem_freed_to_os,
       h1.aggr_pga_auto_target,
       h1.aggr_pga_target_param,
       (h1.bytes_processed - h0.bytes_processed) bytes_processed,
       (h1.extra_bytes_rw - h0.extra_bytes_rw) extra_bytes_rw,
       h1.global_memory_bound,
       h1.max_pga_allocated,
       h1.max_pga_used_aut_wa,
       h1.max_pga_used_man_wa,
       h1.tot_pga_allocated,
       h1.tot_pga_inuse,
       h1.tot_pga_used_aut_wa,
       h1.tot_pga_used_man_wa,
       h1.tot_freeable_pga_mem
  FROM pgastat_denorm_1 h0,
       pgastat_denorm_1 h1,
       dba_hist_snapshot s0,
       dba_hist_snapshot s1,
       pgastat_denorm_2 min /* to see cumulative use (replace h0 with min on select list above) */
 WHERE h1.snap_id = h0.snap_id + 1
   AND h1.dbid = h0.dbid
   AND h1.instance_number = h0.instance_number
   AND s0.snap_id = h0.snap_id
   AND s0.dbid = h0.dbid
   AND s0.instance_number = h0.instance_number
   AND s1.snap_id = h1.snap_id
   AND s1.dbid = h1.dbid
   AND s1.instance_number = h1.instance_number
   AND s1.snap_id = s0.snap_id + 1
   AND s1.startup_time = s0.startup_time
   AND s1.begin_interval_time > (s0.begin_interval_time + (1 / (24 * 60))) /* filter out snaps apart < 1 min */
   AND min.dbid = s1.dbid
   AND min.instance_number = s1.instance_number
   AND min.startup_time = s1.startup_time
)
SELECT snap_id,
       TO_CHAR(MIN(begin_interval_time), 'YYYY-MM-DD HH24:MI') begin_time,
       TO_CHAR(MIN(end_interval_time), 'YYYY-MM-DD HH24:MI') end_time,
       ROUND(SUM(pga_mem_freed_to_os) / POWER(2, 30), 3) pga_mem_freed_to_os,
       ROUND(SUM(aggr_pga_auto_target) / POWER(2, 30), 3) aggr_pga_auto_target,
       ROUND(SUM(aggr_pga_target_param) / POWER(2, 30), 3) aggr_pga_target_param,
       ROUND(SUM(bytes_processed) / POWER(2, 30), 3) bytes_processed,
       ROUND(SUM(extra_bytes_rw) / POWER(2, 30), 3) extra_bytes_rw,
       ROUND(SUM(global_memory_bound) / POWER(2, 30), 3) global_memory_bound,
       ROUND(SUM(max_pga_allocated) / POWER(2, 30), 3) max_pga_allocated,
       ROUND(SUM(max_pga_used_aut_wa) / POWER(2, 30), 3) max_pga_used_aut_wa,
       ROUND(SUM(max_pga_used_man_wa) / POWER(2, 30), 3) max_pga_used_man_wa,
       ROUND(SUM(tot_pga_allocated) / POWER(2, 30), 3) tot_pga_allocated,
       ROUND(SUM(tot_pga_inuse) / POWER(2, 30), 3) tot_pga_inuse,
       ROUND(SUM(tot_pga_used_aut_wa) / POWER(2, 30), 3) tot_pga_used_aut_wa,
       ROUND(SUM(tot_pga_used_man_wa) / POWER(2, 30), 3) tot_pga_used_man_wa,
       ROUND(SUM(tot_freeable_pga_mem) / POWER(2, 30), 3) tot_freeable_pga_mem,
       0 dummy_15
  FROM pgastat_delta
 GROUP BY
       snap_id
 ORDER BY
       snap_id;
	   
	Snap ID Begin Time          End Time            PGA_MEM_FREED_TO_OS AGGR_PGA_AUTO_TARGET AGGR_PGA_TARGET_PARAM BYTES_PROCESSED EXTRA_BYTES_RW GLOBAL_MEMORY_BOUND MAX_PGA_ALLOCATED MAX_PGA_USED_AUT_WA MAX_PGA_USED_MAN_WA TOT_PGA_ALLOCATED TOT_PGA_INUSE TOT_PGA_USED_AUT_WA TOT_PGA_USED_MAN_WA TOT_FREEABLE_PGA_MEM   DUMMY_15
-------- ------------------- ------------------- ------------------- -------------------- --------------------- --------------- -------------- ------------------- ----------------- ------------------- ------------------- ----------------- ------------- ------------------- ------------------- -------------------- ----------
    8399 2022-09-14 21:00    2022-09-14 22:00                   .009                 .644                 2.666           1.233              0                .267             2.528                .182       .028              2.257         1.954                .006                   0                 .035          0
    8400 2022-09-14 22:00    2022-09-14 23:00                   .005                 .717                 2.666            3.32              0                .267             2.537                .182       .028               2.13         1.869                   0                   0                 .035          0
    8403 2022-09-16 06:16    2022-09-16 07:00                   .008                 .706                 2.666            .578              0                .267             2.537                .182       .028              2.126         1.886                   0                   0                 .035          0
    8404 2022-09-16 07:00    2022-09-16 08:00                   .007                 .716                 2.666            .847              0                .267             2.537                .182       .028              2.118         1.879                   0                   0                 .034          0
    8405 2022-09-16 08:00    2022-09-16 09:00                   .009                 .718                 2.666            .865              0                .267             2.537                .182       .028              2.117         1.878                   0                   0                 .034          0
    8406 2022-09-16 09:00    2022-09-16 10:00                    .01                 .709                 2.666            .858              0                .267             2.537                .182       .028              2.128         1.886                   0                   0                 .036          0
    8407 2022-09-16 10:00    2022-09-16 11:00                   .007                 .696                 2.666            .966              0                .267             2.537                .182       .028              2.143         1.901                   0                   0                 .035          0
    8408 2022-09-16 11:00    2022-09-16 12:00                   .009                 .698                 2.666            .864              0                .267             2.537                .182       .028          
	
	
	
	col NAME for a40
SELECT NAME, VALUE, UNIT FROM V$PGASTAT 
where
NAME in ( 'aggregate PGA target parameter', 'aggregate PGA auto target', 'total PGA inuse ', 
'total PGA allocated', 'maximum PGA used for auto workareas', 'cache hit percentage', 'over allocation count');

NAME                                          VALUE UNIT
---------------------------------------- ---------- ------------
aggregate PGA target parameter           1431306240 bytes
aggregate PGA auto target                 348337152 bytes
total PGA allocated                      1173649408 bytes
maximum PGA used for auto workareas        98097152 bytes
over allocation count                             0
cache hit percentage                          99.99 percent

6 rows selected.




SELECT * FROM (
  SELECT 
         count(*) AS count,
         user_id, program, module, machine, sql_id
  FROM SYS.DBA_HIST_ACTIVE_SESS_HISTORY
  WHERE sample_time > sysdate-1
  AND sample_time < sysdate
 -- AND pga_allocated > 1024*1024*1024
  GROUP BY user_id, program, module, machine, sql_id
  ORDER BY count(*) DESC
)
WHERE rownum <= 20



define sql_id='8cnh50qfgwg73'



SELECT    sql_id, 
       TO_CHAR(sample_time,'DD-MON-YYYY HH24:MI:SS') AS sample_time, 
       pga_allocated
FROM SYS.DBA_HIST_ACTIVE_SESS_HISTORY
WHERE sample_time > sysdate-1
AND sample_time < sysdate
AND sql_id = '&SQL_ID'
ORDER BY sample_time;



set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 9,999,999,999.9
col avg_pio for 9,999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(executions_delta,0),0,1,executions_delta)) avg_lio,
(disk_reads_delta/decode(nvl(executions_delta,0),0,1,executions_delta)) avg_pio,
rows_processed_delta total_rows
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','8cnh50qfgwg73')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and s.instance_number like nvl('&instance_number',s.instance_number)
order by 1, 2, 3
/



col sid for 99999 
col operation for a20
col start_time for a20
col expected_size_MB for 99,999.9 
col Act_Used_MB for 99,999.9 
col Max_Used_MB for 99,999.9
col TEMP_Seg_MB for 999,999
select to_char(sql_exec_start, 'yyyy-mm-dd hh24:mi:ss') start_time, sql_id,
       to_number(decode(sid, 65535, NULL, sid)) sid,
       operation_type operation, trunc(expected_size/1024/1024) expected_size_MB,
       trunc(actual_mem_used/1024/1024) Act_Used_MB, trunc(max_mem_used/1024/1204) Max_Used_MB,
       number_passes pass, trunc(tempseg_size/1024/1024) TEMP_Seg_MB
FROM gv$sql_workarea_active
where 1=1
and SQL_ID='&SQL_ID'
ORDER BY 1,2;

	   
define top=50	
col STAR for a50		   
select SQL_ID,round(PGA_MB,1) PGA_MB,percent,rpad('*',percent*10/100,'*') star
from
(
select SQL_ID,sum(DELTA_PGA_MB) PGA_MB ,(ratio_to_report(sum(DELTA_PGA_MB)) over ())*100 percent,rank() over(order by sum(DELTA_PGA_MB) desc) rank
from
(
select SESSION_ID,SESSION_SERIAL#,sample_id,SQL_ID,SAMPLE_TIME,IS_SQLID_CURRENT,SQL_CHILD_NUMBER,PGA_ALLOCATED,
greatest(PGA_ALLOCATED - first_value(PGA_ALLOCATED) over (partition by SESSION_ID,SESSION_SERIAL# order by sample_time rows 1 preceding),0)/power(1024,2) "DELTA_PGA_MB"
from
gv$active_session_history
where
IS_SQLID_CURRENT='Y'
and sample_time > sysdate - interval '30' minute
order by 1,2,3,4
)
group by sql_id
having sum(DELTA_PGA_MB) > 0
)
where rank < (&top+1)
order by rank