Test(= play) - Duplicate without connection to target/catalog

I saw some mail-lists. How to clone database without connection target database in the same server? On 11gR2, We can duplicate database without connection target database... DUPLICATE Without Connection to Target Database
Actually, I forgot it. I just thought without connection target and catalog databases. I knew this was waste our time. We should connect target database, Except we was using 11gR2 (just connect to catalog).

It was just my play, during I was testing some about RMAN. I used backupset from target database to restore new database (for standby database) then (graceful) failed over it.
On Target Database (ORCL):

SQL> select file#,name from  v$datafile;

     FILE# NAME
---------- ----------------------------------------------------------------------
         1 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_7kzfbnwy_.dbf
         2 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_7kzfbot8_.dbf
         3 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_7kzfboym_.dbf
         4 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_7kzfbp8p_.dbf
         5 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_7kzfpwwg_.dbf
         6 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_apex_7l310vnk_.dbf
         7 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_apex_7l367j3l_.dbf
         8 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_demo_7lods738_.dbf
         9 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_rcat_ts_7lrgnll6_.dbf

9 rows selected.

SQL> select name from v$tempfile;

NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_7m5969gl_.tmp

SQL> select member from v$logfile;

MEMBER
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_7m596119_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_7m5963pb_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_7m595wts_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_7m595yyc_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_7m595ps4_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_7m595t1m_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_4_7m2z44ts_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_4_7m2z45x3_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_5_7m2z4b5w_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_5_7m2z4cwv_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_6_7m2z4hop_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_6_7m2z4js9_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_7_7m2z4nnh_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_7_7m2z4p1t_.log

[oracle@chart01 ~]$ ps -aef | grep pmon
oracle    4589     1  0 Feb08 ?        00:00:09 ora_pmon_orcl

SQL> select name from v$database;

NAME
---------
ORCL

SQL>  ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dupdb.ctl' reuse;

Database altered.

For Duplicate Database (DUPDB): make sure all paths no affect with target database !!!

[oracle@chart01 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initdupdb.ora
*.audit_file_dest='/u01/app/oracle/admin/dupdb/adump'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dupdb.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_domain=''
*.db_name='orcl'
*.db_unique_name='dupdb'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=21474836480
*.diagnostic_dest='/u01/app/oracle'
*.job_queue_processes=1000
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_trace=1
*.sga_target=700M
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_retention=3600
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='orcl','dupdb','ORCL','DUPDB'
*.log_file_name_convert='orcl','dupdb','ORCL','DUPDB'

[oracle@chart01 ~]$ export ORACLE_SID=dupdb
[oracle@chart01 ~]$ sqlplus  / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 9 00:21:48 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  732352512 bytes
Fixed Size                  1347456 bytes
Variable Size             205521024 bytes
Database Buffers          520093696 bytes
Redo Buffers                5390336 bytes
Database mounted.
SQL>  select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PHYSICAL STANDBY

SQL> select file#,name from  v$datafile;

     FILE# NAME
---------- ----------------------------------------------------------------------
         1 /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_system_7kzfbnwy_.dbf
         2 /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_sysaux_7kzfbot8_.dbf
         3 /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_undotbs1_7kzfboym_.dbf
         4 /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_users_7kzfbp8p_.dbf
         5 /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_example_7kzfpwwg_.dbf
         6 /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_tbs_apex_7l310vnk_.dbf
         7 /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_tbs_apex_7l367j3l_.dbf
         8 /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_tbs_demo_7lods738_.dbf
         9 /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_rcat_ts_7lrgnll6_.dbf

9 rows selected.

SQL> select name from v$tempfile;

NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_temp_7m5969gl_.tmp

SQL> select member from v$logfile;

MEMBER
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/DUPDB/onlinelog/o1_mf_3_7m596119_.log
/u01/app/oracle/fast_recovery_area/DUPDB/onlinelog/o1_mf_3_7m5963pb_.log
/u01/app/oracle/oradata/DUPDB/onlinelog/o1_mf_2_7m595wts_.log
/u01/app/oracle/fast_recovery_area/DUPDB/onlinelog/o1_mf_2_7m595yyc_.log
/u01/app/oracle/oradata/DUPDB/onlinelog/o1_mf_1_7m595ps4_.log
/u01/app/oracle/fast_recovery_area/DUPDB/onlinelog/o1_mf_1_7m595t1m_.log
/u01/app/oracle/oradata/DUPDB/onlinelog/o1_mf_4_7m2z44ts_.log
/u01/app/oracle/fast_recovery_area/DUPDB/onlinelog/o1_mf_4_7m2z45x3_.log
/u01/app/oracle/oradata/DUPDB/onlinelog/o1_mf_5_7m2z4b5w_.log
/u01/app/oracle/fast_recovery_area/DUPDB/onlinelog/o1_mf_5_7m2z4cwv_.log
/u01/app/oracle/oradata/DUPDB/onlinelog/o1_mf_6_7m2z4hop_.log
/u01/app/oracle/fast_recovery_area/DUPDB/onlinelog/o1_mf_6_7m2z4js9_.log
/u01/app/oracle/oradata/DUPDB/onlinelog/o1_mf_7_7m2z4nnh_.log
/u01/app/oracle/fast_recovery_area/DUPDB/onlinelog/o1_mf_7_7m2z4p1t_.log

[oracle@chart01 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Feb 9 01:45:12 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1301287541, not open)

RMAN> run{
restore database;
recover database;
}

Starting restore at 09-FEB-12
Starting implicit crosscheck backup at 09-FEB-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
Crosschecked 5 objects
Finished implicit crosscheck backup at 09-FEB-12

Starting implicit crosscheck copy at 09-FEB-12
using channel ORA_DISK_1
Crosschecked 10 objects
Finished implicit crosscheck copy at 09-FEB-12

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/DUPDB/autobackup/2012_02_08/o1_mf_n_774745439_7m577k3l_.bkp

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_system_7kzfbnwy_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_sysaux_7kzfbot8_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_undotbs1_7kzfboym_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_users_7kzfbp8p_.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_example_7kzfpwwg_.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_tbs_apex_7l310vnk_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_tbs_apex_7l367j3l_.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_tbs_demo_7lods738_.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_rcat_ts_7lrgnll6_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2012_02_09/o1_mf_nnnd0_TAG20120209T014117_7m5jgg70_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2012_02_09/o1_mf_nnnd0_TAG20120209T014117_7m5jgg70_.bkp tag=TAG20120209T014117
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:45
Finished restore at 09-FEB-12

Starting recover at 09-FEB-12
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=24
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2012_02_09/o1_mf_annnn_TAG20120209T014323_7m5jlcv5_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2012_02_09/o1_mf_annnn_TAG20120209T014323_7m5jlcv5_.bkp tag=TAG20120209T014323
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fast_recovery_area/DUPDB/archivelog/2012_02_09/o1_mf_1_24_7m5jvfqo_.arc thread=1 sequence=24
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/DUPDB/archivelog/2012_02_09/o1_mf_1_24_7m5jvfqo_.arc RECID=1 STAMP=774755293
unable to find archived log
archived log thread=1 sequence=25
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/09/2012 01:48:15
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 25 and starting SCN of 2045943

RMAN> exit

Recovery Manager complete.

[oracle@chart01 ~]$ sqlplus  / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 9 01:48:27 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

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

SQL>  select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PHYSICAL STANDBY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH
*
ERROR at line 1:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1:
'/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_system_7m5jp64v_.dbf'

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

Database altered.

SQL> select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PRIMARY

SQL> alter database open;

Database altered.

SQL> select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ WRITE           PRIMARY

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DUPDB/onlinelog/o1_mf_3_7m5jy095_.log
/u01/app/oracle/fast_recovery_area/DUPDB/onlinelog/o1_mf_3_7m5jy1fm_.log
/u01/app/oracle/oradata/DUPDB/onlinelog/o1_mf_2_7m5jxtrl_.log
/u01/app/oracle/fast_recovery_area/DUPDB/onlinelog/o1_mf_2_7m5jxwhx_.log
/u01/app/oracle/oradata/DUPDB/onlinelog/o1_mf_1_7m5jxo0w_.log
/u01/app/oracle/fast_recovery_area/DUPDB/onlinelog/o1_mf_1_7m5jxprl_.log
/u01/app/oracle/fast_recovery_area/DUPDB/archivelog/2012_02_09/o1_mf_1_0_7m5jwzjs_.arc

7 rows selected.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_system_7m5jp64v_.dbf
/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_sysaux_7m5jp65h_.dbf
/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_undotbs1_7m5jp674_.dbf
/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_users_7m5jpcmm_.dbf
/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_example_7m5jp662_.dbf
/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_tbs_apex_7m5jp67p_.dbf
/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_tbs_apex_7m5jp6b2_.dbf
/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_tbs_demo_7m5jp6bn_.dbf
/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_rcat_ts_7m5jp6cn_.dbf

9 rows selected.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_temp_7m5k1lvf_.tmp

[oracle@chart01 ~]$ ps -aef | grep pmon
oracle    4589     1  0 Feb08 ?        00:00:09 ora_pmon_orcl
oracle    8920     1  0 02:01 ?        00:00:00 ora_pmon_dupdb

Why I didn't restore from backupset? I tested this idea as well. But I had to rename redo log files. That was the bad idea to do on the same server with target database. How do you guarantee all commands no affect to target database?
So, this was just my play and no affect with target database in the same server also. Maybe, my steps was wrong. But just fun and it worked for my play.

***By the way, We should use "duplicate" command (just connect target database and catalog databases), It's safe***

Related Posts:
DUPLICATE Without Connection to Target Database (11gR2)

Sit around and learn some from Average Active Sessions chart

Nothing to do much for me. I just test for the practical idea. A "Average Active Sessions" chart on EM(from picture). Each block of color on the chart will sort from Waited Classes catagories(Point D). CPU (green) -> CPU Wait (light green) -> ... Other (purple).

How to practice for this idea?
A. Read on Oracle Documents
B. Just practice for fun :)

I tested Blocking Session. So, I found Application(red) waited class (Point A). During Database blocking session time (red), I tesed INSERT and SELECT. I found CPU, CPU Wait, Scheduler, USER I/O, Application and Configuration waites classes(Point B). After INSERT and SELECT sessions finished, I still see Application(red) waited class(Point C). That mean database has still Blocking Sessions. Anyway, that's not important. I just make sure about the sort each block color (Point B).

*** If Database no waited events for some waited classes, which waited class won't appear on Active Sessions page. ***

For new DBA, when you see the graph. You should know this graph colors are sorted by Waited Classes catagories (Point D).
Example Point B: that doesn't mean! TOP waited class is either "Configuration" or "Other". From chart, You have to identify each waited class on the chart. It's simple on EM, just click on each block color of waited class.

On other charts, just read the category on left chart before.

How To? Recreate CTXSYS after dropped it

Nothing special, Someone asked me How to recreate CTXSYS after dropped it. Good !!! it's only system development. Actually i don't know much about Oracle Text. However I just tested and wrote .

SQL> show user;
USER is "DEMO"

SQL> SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'France', 1) > 0;

SCORE(1) ID TEXT
---------- ---------- ----------------------------------------
4 2 <HTML>Paris is a city in France.</HTML>
4 3 <HTML>France is in Europe.</HTML>

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
mklob CLOB;
amt NUMBER := 40;
line VARCHAR2(80);
BEGIN
CTX_DOC.MARKUP('idx_docs','3','France', mklob);
DBMS_LOB.READ(mklob, amt, 1, line);
DBMS_OUTPUT.PUT_LINE('FIRST 40 CHARS ARE:'||line);
DBMS_LOB.FREETEMPORARY(mklob);
END;
/

FIRST 40 CHARS ARE:<HTML><<<France>>> is in Europe.</HTML>

PL/SQL procedure successfully completed.

- Drop CTXSYS schema.

SQL> connect / as sysdba
Connected.
SQL> drop user CTXSYS cascade;

User dropped.

- Go to "demo" user and try again

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
mklob CLOB;
amt NUMBER := 40;
line VARCHAR2(80);
BEGIN
CTX_DOC.MARKUP('idx_docs','3','France', mklob);
DBMS_LOB.READ(mklob, amt, 1, line);
DBMS_OUTPUT.PUT_LINE('FIRST 40 CHARS ARE:'||line);
DBMS_LOB.FREETEMPORARY(mklob);
END; /
CTX_DOC.MARKUP('idx_docs','3','France', mklob);
*
ERROR at line 6:
ORA-06550: line 6, column 5:
PLS-00201: identifier 'CTX_DOC.MARKUP' must be declared
ORA-06550: line 6, column 5:
PL/SQL: Statement ignored

Oops!!! Idea to recreate CTXSYS schema again by catctx.sql script.

SQL> connect / as sysdba
Connected.
SQL> @?/ctx/admin/catctx.sql password sysaux temp NOLOCK
SQL> connect CTXSYS/password
Connected.
SQL> @?/ctx/admin/defaults/drdefus.sql
SQL> connect / as sysdba
Connected.
SQL> alter user CTXSYS account lock;

User altered.

SQL> @?/rdbms/admin/utlrp

SQL> select STATUS from dba_registry where COMP_ID='CONTEXT' and SCHEMA='CTXSYS';

STATUS
--------------------------------------------
VALID

and granted:
GRANT CTXAPP to demo;
GRANT EXECUTE ON CTXSYS.CTX_CLS TO demo;
GRANT EXECUTE ON CTXSYS.CTX_DDL TO demo;
GRANT EXECUTE ON CTXSYS.CTX_DOC TO demo;
GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO demo;
GRANT EXECUTE ON CTXSYS.CTX_QUERY TO demo;
GRANT EXECUTE ON CTXSYS.CTX_REPORT TO demo;
GRANT EXECUTE ON CTXSYS.CTX_THES TO demo;
GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO demo;

- Go to "demo" user, check what's happened? and fix!!!

SQL> show user;
USER is "DEMO"

SQL> DECLARE
mklob CLOB;
amt NUMBER := 40;
line VARCHAR2(80);
BEGIN
CTX_DOC.MARKUP('idx_docs','3','France', mklob);
DBMS_LOB.READ(mklob, amt, 1, line);
DBMS_OUTPUT.PUT_LINE('FIRST 40 CHARS ARE:'||line);
DBMS_LOB.FREETEMPORARY(mklob);
END;
/
DECLARE
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "CTXSYS.CTX_DOC" has been invalidated
ORA-04065: not executed, altered or dropped package "CTXSYS.CTX_DOC"
ORA-06508: PL/SQL: could not find program unit being called: "CTXSYS.CTX_DOC"
ORA-06512: at line 6

SQL> SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'France', 1) > 0;
SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'France', 1) > 0
*
ERROR at line 1:
ORA-20000: Oracle Text error: DRG-10599: column is not indexed

*** check data, index status and etc ... ***

SQL> SELECT id, text FROM docs;

ID TEXT
---------- ----------------------------------------
1 <HTML>California is a state in the US.</
HTML>
2 <HTML>Paris is a city in France.</HTML>
3 <HTML>France is in Europe.</HTML>

SQL> select object_name, status from user_objects where object_type='INDEX' and status='INVALID';

OBJECT_NAME STATUS
------------------------------ -------
IDX_DOCS INVALID

SQL> ALTER INDEXTYPE IDX_DOCS compile;
ALTER INDEXTYPE IDX_DOCS compile
*
ERROR at line 1:
ORA-29833: indextype does not exist

SQL> select dbms_metadata.get_ddl('INDEX','IDX_DOCS') from dual;

DBMS_METADATA.GET_DDL('INDEX','IDX_DOCS')
--------------------------------------------------------------------------------

CREATE INDEX "DEMO"."IDX_DOCS" ON "DEMO"."DOCS" ("TEXT")
INDEXTYPE IS ""."" PARAMETERS ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXS
YS.HTML_SECTION_GROUP')

I can get ddl, but INDEXTYPE IS ""."" PARAMETERS...
check more....

SQL> CREATE INDEX "DEMO"."IDX_DOCS" ON "DEMO"."DOCS" ("TEXT") INDEXTYPE IS ctxsys.context PARAMETERS ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP');
CREATE INDEX "DEMO"."IDX_DOCS" ON "DEMO"."DOCS" ("TEXT") INDEXTYPE IS ctxsys.context PARAMETERS ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP')
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> drop index "DEMO"."IDX_DOCS";

Index dropped.

SQL> CREATE INDEX "DEMO"."IDX_DOCS" ON "DEMO"."DOCS" ("TEXT") INDEXTYPE IS ctxsys.context PARAMETERS ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP');
CREATE INDEX "DEMO"."IDX_DOCS" ON "DEMO"."DOCS" ("TEXT") INDEXTYPE IS ctxsys.context PARAMETERS ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP')
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "CTXSYS.DRIPARSE" has been invalidated
ORA-04065: not executed, altered or dropped package "CTXSYS.DRIPARSE"
ORA-06508: PL/SQL: could not find program unit being called: "CTXSYS.DRIPARSE"
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 81

SQL> drop index "DEMO"."IDX_DOCS";

Index dropped.

SQL> CREATE INDEX "DEMO"."IDX_DOCS" ON "DEMO"."DOCS" ("TEXT") INDEXTYPE IS ctxsys.context PARAMETERS ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP');
CREATE INDEX "DEMO"."IDX_DOCS" ON "DEMO"."DOCS" ("TEXT") INDEXTYPE IS ctxsys.context PARAMETERS ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP')
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvxtab.create_index_tables
ORA-00955: name is already used by an existing object
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366

SQL> drop index "DEMO"."IDX_DOCS";

Index dropped.

SQL> CREATE INDEX "DEMO"."IDX_DOCS" ON "DEMO"."DOCS" ("TEXT") INDEXTYPE IS ctxsys.context PARAMETERS ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP');

Index created.

- Check again ...

SQL> SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'France', 1) > 0;

SCORE(1) ID TEXT
---------- ---------- ----------------------------------------
4 2 <HTML>Paris is a city in France.</HTML>
4 3 <HTML>France is in Europe.</HTML>

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
mklob CLOB;
amt NUMBER := 40;
line VARCHAR2(80);
BEGIN
CTX_DOC.MARKUP('idx_docs','3','France', mklob);
DBMS_LOB.READ(mklob, amt, 1, line);
DBMS_OUTPUT.PUT_LINE('FIRST 40 CHARS ARE:'||line);
DBMS_LOB.FREETEMPORARY(mklob);
END;
/

FIRST 40 CHARS ARE:<HTML><<<France>>> is in Europe.</HTML>

PL/SQL procedure successfully completed.

Reference (example)
Not sure IDEA - How about your idea?

Upgrade - Oracle Database 11.2.0.2 to 11.2.0.3

I saw some tweet...#Oracle 11.2.0.3 patchset(10404530) is available.
Wow!!! time to patch database again. It has only for Linux x86 and x86-64 now. You can download, install and upgrade your database. You can review Oracle Database 11g Release 2 (11.2.0.3) New Features as well.

I just downloaded, installed and upgraded (dbua) my database on virtualbox. (Linux x86)

$ ls p10404530_112030_LINUX_*
p10404530_112030_LINUX_1of7.zip p10404530_112030_LINUX_2of7.zip

Oracle Database software uses 2 files (1 and 2). then install and upgraded database

SQL> select name from v$database;

NAME
---------
ORCL

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

$ cat /etc/oratab
orcl:/u01/app/oracle/product/11.2.0/dbhome_1:N

- Installation (silent mode)

$ ./runInstaller -silent -ignoreSysPrereqs -ignorePrereq -ignoreInternalDriverError -responseFile /u01/SRC/database/response/db_install.rsp ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_2 ORACLE_HOME_NAME=OraDbHome2 oracle.install.option=INSTALL_DB_SWONLY oracle.install.db.InstallEdition=EE oracle.install.db.DBA_GROUP=dba oracle.install.db.OPER_GROUP=oper DECLINE_SECURITY_UPDATES=true -ignoreInternalDriverError
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB. Actual 7684 MB Passed
Checking swap space: must be greater than 150 MB. Actual 3095 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2011-09-23_09-22-33PM. Please wait ...
/u01/app/oraInventory/logs/installActions2011-09-23_09-22-33PM.log

As a root user, execute the following script(s):
1. /u01/app/oracle/product/11.2.0/dbhome_2/root.sh

$ su -
Password:
# /u01/app/oracle/product/11.2.0/dbhome_2/root.sh
Check /u01/app/oracle/product/11.2.0/dbhome_2/install/root_mytest_2011-09-23_21-39-25.log for the output of root script

# cat /u01/app/oracle/product/11.2.0/dbhome_2/install/root_mytest_2011-09-23_21-39-25.log
Performing root user operation for Oracle 11g
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/11.2.0/dbhome_2
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Finished product-specific root actions.

- Upgraded Oracle Database (silent mode)

$ id
uid=1000(oracle) gid=1000(oinstall) groups=1000(oinstall),1001(dba),1002(oper)

$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_2
$ export PATH=$ORACLE_HOME/bin:$PATH
$ type dbua
dbua is /u01/app/oracle/product/11.2.0/dbhome_2/bin/dbua
$ dbua -silent -sid orcl
Log files for the upgrade operation are located at: /u01/app/oracle/cfgtoollogs/dbua/orcl/upgrade1
Performing Pre Upgrade
1% complete
7% complete
Upgrading Oracle Server
8% complete
9% complete
10% complete
10% complete
11% complete
12% complete
13% complete
13% complete
14% complete
15% complete
16% complete
16% complete
17% complete
18% complete
19% complete
20% complete
20% complete
21% complete
22% complete
23% complete
23% complete
24% complete
25% complete
26% complete
Upgrading JServer JAVA Virtual Machine
27% complete
28% complete
29% complete
30% complete
Upgrading Oracle XDK for Java
31% complete
32% complete
Upgrading OLAP Analytic Workspace
33% complete
Upgrading OLAP Catalog
35% complete
36% complete
36% complete
37% complete
38% complete
Upgrading EM Repository
40% complete
40% complete
41% complete
42% complete
43% complete
43% complete
44% complete
45% complete
46% complete
46% complete
47% complete
48% complete
49% complete
50% complete
50% complete
51% complete
52% complete
53% complete
53% complete
54% complete
55% complete
56% complete
56% complete
57% complete
58% complete
Upgrading Oracle Text
60% complete
60% complete
61% complete
62% complete
63% complete
63% complete
Upgrading Oracle XML Database
65% complete
66% complete
66% complete
67% complete
Upgrading Oracle Java Packages
69% complete
70% complete
Upgrading Oracle interMedia
71% complete
72% complete
73% complete
73% complete
74% complete
Upgrading Spatial
76% complete
76% complete
77% complete
78% complete
79% complete
80% complete
80% complete
81% complete
82% complete
83% complete
83% complete
84% complete
Upgrading Oracle Workspace Manager
Upgrading Expression Filter
Upgrading Rule Manager
Upgrading Oracle Application Express
Upgrading Oracle OLAP API
Performing Post Upgrade
85% complete
86% complete
92% complete
Generating Summary
Database upgrade has been completed successfully, and the database is ready to use.
100% complete
Check the log file "/u01/app/oracle/cfgtoollogs/dbua/logs/silent.log" for upgrade details.

*** check /u01/app/oracle/cfgtoollogs/dbua/logs/silent.log file ***

- Checked

$ cat /etc/oratab
orcl:/u01/app/oracle/product/11.2.0/dbhome_2:N

SQL> select name from v$database;

NAME
---------
ORCL

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

*** Copy some configures from old Oracle Home ***

$ cp /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora /u01/app/oracle/product/11.2.0/dbhome_2/network/admin/
$ lsnrctl start

*** Make sure no application opens files at old Oracle Home ***

# lsof | grep "/oracle/product/11.2.0/dbhome_1"

Great!!! simple and easy for upgraded. Plan to test more ...

Related Post:
Upgrade database 11.2.0.1.0 to 11.2.0.2.0 : dbua -silent
struggle to Upgrade Oracle Database 11.2.0.1 to 11.2.0.2
Just upgrade Oracle Database 10gR2 to 11gR2 by command-line
Just Step to upgrade (RAC) 10.2.0.4 to 11.1.0.6
Creating Database 11gR1 on ASM 11gR2

Test - Create table with Upstream (Local) Capture

I tested Oracle Stream on 11.1 (Upstream) with DEMO schema, then created new table. I just tested to solve it(error). It's simple and fun.

On SRC:

SQL> show user;
USER is "STRMADMIN"

SQL> SELECT TABLE_OWNER, TABLE_NAME, SCN from DBA_CAPTURE_PREPARED_TABLES;

TABLE_OWNER TABLE_NAME SCN
------------------------------ ------------------------------ ----------
DEMO TEST01 1759366
DEMO TEST02 1761703

SQL> create table demo.tb_test (id number);

Table created.

SQL> insert into demo.tb_test values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from demo.tb_test;

ID
----------
1

SQL> SELECT TABLE_OWNER, TABLE_NAME, SCN from DBA_CAPTURE_PREPARED_TABLES;

TABLE_OWNER TABLE_NAME SCN
------------------------------ ------------------------------ ----------
DEMO TB_TEST 1766632
DEMO TEST01 1759366
DEMO TEST02 1761703

On DEST:

SQL> select * from demo.tb_test;
select * from demo.tb_test
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> show user;
USER is "STRMADMIN"
SQL> select APPLY_NAME, STATUS from dba_apply;

APPLY_NAME STATUS
------------------------------ --------
DEMO_APPLY ABORTED

SQL> select APPLY_NAME, LOCAL_TRANSACTION_ID, SOURCE_DATABASE, ERROR_MESSAGE from dba_apply_error;

APPLY_NAME LOCAL_TRANSACTION_ID SOURC ERROR_MESSAGE
---------- ---------------------- ----- --------------------------------------------------------------------------------
DEMO_APPLY 2.32.1124 SRC ORA-26687: no instantiation SCN provided for "DEMO"."" in source database "SRC"

Oops!!! no "TB_TEST" table. So.. exp/imp!!! (metadata only)
On SRC: Test,Check and etc

SQL> insert into demo.tb_test values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT TABLE_OWNER, TABLE_NAME, SCN from DBA_CAPTURE_PREPARED_TABLES;

TABLE_OWNER TABLE_NAME SCN
------------------------------ ------------------------------ ----------
DEMO TB_TEST 1766632
DEMO TEST01 1759366
DEMO TEST02 1761703

SQL> alter table DEMO.TB_TEST read only;

Table altered.

On DEST: use DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN on database (destination), SRC = global name of the source site

SQL> select * from demo.tb_test;

no rows selected

SQL> select APPLY_NAME, STATUS from dba_apply;

APPLY_NAME STATUS
---------- --------
DEMO_APPLY ABORTED

SQL> BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name=> 'DEMO.TB_TEST',
source_database_name=>'SRC',
instantiation_SCN=> 1766632);
END;
/

PL/SQL procedure successfully completed.

SQL> exec dbms_apply_adm.stop_apply(apply_name => 'DEMO_APPLY');

PL/SQL procedure successfully completed.

SQL> exec dbms_apply_adm.start_apply(apply_name => 'DEMO_APPLY');

PL/SQL procedure successfully completed.

SQL> select APPLY_NAME, STATUS from dba_apply;

APPLY_NAME STATUS
---------- --------
DEMO_APPLY ENABLED

SQL> select * from demo.tb_test;

no rows selected

On SRC:

SQL> alter table DEMO.TB_TEST read write;

Table altered.

SQL> select * from demo.tb_test;

ID
----------
1
2

On DEST:

SQL> select * from demo.tb_test;

ID
----------
1
2

OK. It showed data at destination. Another Test!!!
On SRC:

SQL> alter table demo.tb_test add (id2 number);

Table altered.

SQL> select * from demo.tb_test;

ID ID2
---------- ----------
1
2

On DEST:

SQL> select * from demo.tb_test;

ID ID2
---------- ----------
1
2

Maybe, I don't know other idea. I just tested, tested and fixed :)

Remark:
ORA-26687: no instantiation SCN provided for tablename.
After, You use DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN, You should use DBMS_APPLY_ADM.EXECUTE_ERROR(‘localtransactionid’) procedure.
but this case, ORA-26687: no instantiation SCN provided for "DEMO".""

read more DBA_APPLY_INSTANTIATED_OBJECTS and ORA-26687 [ID 783815.1]

Upgrade database 11.2.0.1.0 to 11.2.0.2.0 : dbua -silent

Nothing today - just need to tested "dbua -silent" - Upgrading Oracle Database from 11.2.0.1.0 to 11.2.0.2.0 -)
/u01/app/oracle/11.2.0/dbhome_old Home for 11.2.0.1.0
/u01/app/oracle/11.2.0/dbhome_1 Home for 11.2.0.2.0

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

$ cat /etc/oratab
+ASM:/u01/app/oracle/11.2.0/grid:N # line added by Agent
orcl:/u01/app/oracle/11.2.0/dbhome_old:N

$ export ORACLE_HOME=/u01/app/oracle/11.2.0/dbhome_1
$ export PATH=$ORACLE_HOME/bin:$PATH
$ type dbua
dbua is /u01/app/oracle/11.2.0/dbhome_1/bin/dbua
$ dbua -silent -sid orcl
Log files for the upgrade operation are located at: /u01/app/oracle/cfgtoollogs/dbua/ORCL/upgrade1
Performing Pre Upgrade
1% complete
7% complete
Upgrading Oracle Server
8% complete
9% complete
10% complete
10% complete
11% complete
12% complete
13% complete
13% complete
14% complete
15% complete
16% complete
16% complete
17% complete
18% complete
19% complete
20% complete
20% complete
21% complete
22% complete
23% complete
23% complete
24% complete
25% complete
26% complete
26% complete
27% complete
Upgrading JServer JAVA Virtual Machine
29% complete
30% complete
30% complete
31% complete
Upgrading Oracle XDK for Java
33% complete
Upgrading OLAP Analytic Workspace
34% complete
35% complete
Upgrading OLAP Catalog
36% complete
37% complete
38% complete
39% complete
40% complete
40% complete
Upgrading EM Repository
42% complete
43% complete
43% complete
44% complete
45% complete
46% complete
46% complete
47% complete
48% complete
49% complete
50% complete
50% complete
51% complete
52% complete
53% complete
53% complete
54% complete
55% complete
56% complete
56% complete
Upgrading Oracle Text
58% complete
59% complete
60% complete
60% complete
61% complete
62% complete
63% complete
63% complete
64% complete
Upgrading Oracle XML Database
66% complete
66% complete
67% complete
68% complete
Upgrading Oracle Java Packages
70% complete
70% complete
Upgrading Oracle interMedia
72% complete
73% complete
73% complete
74% complete
75% complete
Upgrading Spatial
76% complete
77% complete
78% complete
Upgrading Oracle Workspace Manager
80% complete
Upgrading Expression Filter
81% complete
Upgrading Rule Manager
83% complete
83% complete
Upgrading Oracle Application Express
84% complete
Upgrading Oracle OLAP API
Performing Post Upgrade
85% complete
86% complete
92% complete
Generating Summary
Database upgrade has been completed successfully, and the database is ready to use.
100% complete
Check the log file "/u01/app/oracle/cfgtoollogs/dbua/logs/silent.log" for upgrade details.

During "dbua", You can check "Oracle_Server.log" file in /u01/app/oracle/cfgtoollogs/dbua/ORCL/upgrade1 PATH. After upgraded!!! Reviewed in /u01/app/oracle/cfgtoollogs/dbua/logs/silent.log file.

- Checked

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

$ cat /etc/oratab
+ASM:/u01/app/oracle/11.2.0/grid:N # line added by Agent
orcl:/u01/app/oracle/11.2.0/dbhome_1:N # line added by Agent

How in Grid Infrastructure???

$ crsctl stat res ora.orcl.db -p
NAME=ora.orcl.db
TYPE=ora.database.type
ACL=owner:grid:--x,pgrp:asmdba:--x,other::r--,group:oinstall:r-x,user:oracle:rwx
ACTION_FAILURE_TEMPLATE=
ACTION_SCRIPT=
ACTIVE_PLACEMENT=1
AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%
AUTO_START=restore
CARDINALITY=1
CHECK_INTERVAL=1
CHECK_TIMEOUT=600
CLUSTER_DATABASE=false
DATABASE_TYPE=SINGLE
DB_UNIQUE_NAME=orcl
DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=database) PROPERTY(DB_UNIQUE_NAME= CONCAT(PARSE(%NAME%, ., 2), %USR_ORA_DOMAIN%, .)) ELEMENT(INSTANCE_NAME= %GEN_USR_ORA_INST_NAME%) ELEMENT(DATABASE_TYPE= %DATABASE_TYPE%)
DEGREE=1
DESCRIPTION=Oracle Database resource
ENABLED=1
FAILOVER_DELAY=0
FAILURE_INTERVAL=60
FAILURE_THRESHOLD=1
GEN_AUDIT_FILE_DEST=
GEN_START_OPTIONS=
GEN_USR_ORA_INST_NAME=
HOSTING_MEMBERS=
INSTANCE_FAILOVER=0
LOAD=1
LOGGING_LEVEL=1
MANAGEMENT_POLICY=AUTOMATIC
NLS_LANG=
NOT_RESTARTING_TEMPLATE=
OFFLINE_CHECK_INTERVAL=0
ONLINE_RELOCATION_TIMEOUT=0
ORACLE_HOME=/u01/app/oracle/11.2.0/dbhome_1
PLACEMENT=balanced
PROFILE_CHANGE_TEMPLATE=
RESTART_ATTEMPTS=2
ROLE=PRIMARY
SCRIPT_TIMEOUT=60
SERVER_POOLS=
SPFILE=+DATA/orclold/spfileorclold.ora
START_DEPENDENCIES=hard(ora.DATA.dg) weak(type:ora.listener.type,uniform:ora.ons) pullup(ora.DATA.dg)
START_TIMEOUT=600
STATE_CHANGE_TEMPLATE=
STOP_DEPENDENCIES=hard(intermediate:ora.asm,shutdown:ora.DATA.dg)
STOP_TIMEOUT=600
TYPE_VERSION=2.2
UPTIME_THRESHOLD=1h
USR_ORA_DB_NAME=
USR_ORA_DOMAIN=
USR_ORA_ENV=
USR_ORA_FLAGS=
USR_ORA_INST_NAME=orcl
USR_ORA_OPEN_MODE=open
USR_ORA_OPI=false
USR_ORA_STOP_MODE=immediate
VERSION=11.2.0.2.0

Related Posts:
struggle to Upgrade Oracle Database 11.2.0.1 to 11.2.0.2
Just upgrade Oracle Database 10gR2 to 11gR2 by command-line
Just Step to upgrade (RAC) 10.2.0.4 to 11.1.0.6
Creating Database 11gR1 on ASM 11gR2

Just learned - Real-Time SQL Monitoring

I interest some topic in "Oracle Database 11g Performance Tuning Recipes A Problem-Solution Approach" book. Real-Time SQL Monitoring. How? This is 11g feature.

SQL monitoring is automatically started when a SQL statement runs parallel or when it has consumed at least 5 seconds of CPU or I/O time.

Before you will use this feature, your database have to have "statistics_level" = TYPICAL or ALL, "control_management_pack_access"=DIAGNOSTIC+TUNING

SQL> show parameter statistics_level

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL

SQL> show parameter control_management_pack_access

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string DIAGNOSTIC+TUNING

When you need to test Real-Time SQL Monitoring, you can use HINTS:
MONITOR : To force real-time SQL monitoring
Or if
NO_MONITOR : To prevent the query from being monitored

How to monitor?
You can use V$SQL_MONITOR and V$SQL_PLAN_MONITOR views to monitor the statistics.
The V$SQL_MONITOR view contains a subset of the statistics available in V$SQL.
The V$SQL_PLAN_MONITOR view contains plan level monitoring statistics for each SQL statement in V$SQL_MONITOR.

What should you know from both views?
KEY NUMBER : Artificial join key to efficiently join V$SQL_MONITOR with its corresponding plan level monitoring statistics stored in V$SQL_PLAN_MONITOR.

STATUS : SQL execution status. Values are below :
■ EXECUTING - SQL statement is still executing
■ DONE (ERROR) - Execution terminated with an error
■ DONE (FIRST N ROWS) - Execution terminated by the application before all rows were fetched
■ DONE (ALL ROWS) - Execution terminated and all rows were fetched
■ DONE - Execution terminated (parallel execution)

Tested!!!
- On some session:

SQL> select /*+ MONITOR */ * from TB_TEST;

During SQL statement is still executing
- Find information from SQL statement

select key, status, username, module ,service_name, sql_text, cpu_time, buffer_gets from v$sql_monitor where status = 'EXECUTING'
/

- Find Execution Plan

select plan_line_id id, lpad (' ', plan_depth) || plan_operation operation , plan_options , plan_object_name , plan_cardinality card, plan_cost from v$sql_monitor q, v$sql_plan_monitor p where q.key = p.key and q.key = &KEY order by id
/

KEY= 158913789964
However, If You checked by "dbms_sqltune.report_sql_monitor"

SQL> select dbms_sqltune.report_sql_monitor from dual;
REPORT_SQL_MONITOR
---------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report

SQL Text
------------------------------
select /*+ MONITOR */ * from TB_TEST

Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
Session : DEMO (36:327)
SQL ID : 45fs1021jz1dg
SQL Execution ID : 16777219
Execution Started : 08/27/2011 17:38:05
First Refresh Time : 08/27/2011 17:38:05
Last Refresh Time : 08/27/2011 17:38:06
Duration : 2s
Module/Action : SQL*Plus/-
Service : SYS$USERS
Program : sqlplus@linuxtest01 (TNS V1-V3)
Fetch Calls : 244

Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.08 | 0.06 | 0.02 | 244 | 290 |
=================================================

SQL Plan Monitoring Details (Plan Hash Value=1092599453)
==============================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
==============================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | |
| -> 1 | TABLE ACCESS FULL | TB_TEST | 162K | 597 | 2 | +0 | 1 | 0 | | |
==============================================================================================================================

This is just sample for manually tuning SQL idea. If you need to monitor your SQL. Use it MONITOR hint -)

TEST!!! - _optimizer_cartesian_enabled and _optimizer_mjc_enabled

After I posted Just learn more from V$LOCK and Excessive waited 'SQL*Net message from client' - when query V$LOCK. I found "MERGE JOIN CARTESIAN" in Explain Plan, that made excessive response time from V$LOCK.
I learned some idea from My Oracle Support to eliminate the merge join cartesian Rule. Set both "_optimizer_cartesian_enabled" and "_optimizer_mjc_enabled" hidden parameters.

SQL> SELECT ksppinm name,
2 ksppstvl value,
3 ksppdesc description
4 FROM x$ksppi x, x$ksppcv y
5 WHERE (x.indx = y.indx)
6 AND x.inst_id=userenv('instance')
7 AND x.inst_id=y.inst_id
8 AND ksppinm LIKE '\_optimizer\_cartesian\_enabled' ESCAPE '\'
9 /

NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------------------------------------
_optimizer_cartesian_enabled TRUE optimizer cartesian join enabled

SQL> SELECT ksppinm name,
2 ksppstvl value,
3 ksppdesc description
4 FROM x$ksppi x, x$ksppcv y
5 WHERE (x.indx = y.indx)
6 AND x.inst_id=userenv('instance')
7 AND x.inst_id=y.inst_id
8 AND ksppinm LIKE '\_optimizer\_mjc\_enabled' ESCAPE '\'
9* ORDER BY name
SQL> /

NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------------------------------------
_optimizer_mjc_enabled TRUE enable merge join cartesian

Test it... more
Case1: No change anything

SQL> show parameter "_optimizer_cartesian_enabled"
SQL> show parameter "_optimizer_mjc_enabled"
SQL> explain plan for select * from v$lock;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 554400005
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 144 | 1 (100)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 144 | 1 (100)| 00:00:01 |
| 2 | VIEW | GV$_LOCK | 10 | 680 | 0 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | FILTER | | | | | |
| 5 | VIEW | GV$_LOCK1 | 2 | 136 | 0 (0)| 00:00:01 |
| 6 | UNION-ALL | | | | | |
|* 7 | FIXED TABLE FULL| X$KDNSSF | 1 | 94 | 0 (0)| 00:00:01 |
|* 8 | FIXED TABLE FULL| X$KSQEQ | 1 | 94 | 0 (0)| 00:00:01 |
|* 9 | FIXED TABLE FULL | X$KTADM | 1 | 94 | 0 (0)| 00:00:01 |
|* 10 | FIXED TABLE FULL | X$KTATRFIL | 1 | 94 | 0 (0)| 00:00:01 |
|* 11 | FIXED TABLE FULL | X$KTATRFSL | 1 | 94 | 0 (0)| 00:00:01 |
|* 12 | FIXED TABLE FULL | X$KTATL | 1 | 94 | 0 (0)| 00:00:01 |
|* 13 | FIXED TABLE FULL | X$KTSTUSC | 1 | 94 | 0 (0)| 00:00:01 |
|* 14 | FIXED TABLE FULL | X$KTSTUSS | 1 | 94 | 0 (0)| 00:00:01 |
|* 15 | FIXED TABLE FULL | X$KTSTUSG | 1 | 94 | 0 (0)| 00:00:01 |
|* 16 | FIXED TABLE FULL | X$KTCXB | 1 | 94 | 0 (0)| 00:00:01 |
| 17 | MERGE JOIN CARTESIAN | | 100 | 7600 | 0 (0)| 00:00:01 |
|* 18 | FIXED TABLE FULL | X$KSUSE | 1 | 30 | 0 (0)| 00:00:01 |
| 19 | BUFFER SORT | | 100 | 4600 | 0 (0)| 00:00:01 |
| 20 | FIXED TABLE FULL | X$KSQRS | 100 | 4600 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

SQL> select * from v$lock;
.
.
.
Elapsed: 00:00:00.94

Case 2: Disabled merge join cartesian

SQL> alter system set "_optimizer_cartesian_enabled"=FALSE;

System altered.

SQL> alter system set "_optimizer_mjc_enabled" = FALSE;

System altered.

SQL> explain plan for select * from v$lock;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3634456900
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 144 | 1 (100)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 144 | 1 (100)| 00:00:01 |
| 2 | VIEW | GV$_LOCK | 10 | 680 | 0 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | FILTER | | | | | |
| 5 | VIEW | GV$_LOCK1 | 2 | 136 | 0 (0)| 00:00:01 |
| 6 | UNION-ALL | | | | | |
|* 7 | FIXED TABLE FULL| X$KDNSSF | 1 | 94 | 0 (0)| 00:00:01 |
|* 8 | FIXED TABLE FULL| X$KSQEQ | 1 | 94 | 0 (0)| 00:00:01 |
|* 9 | FIXED TABLE FULL | X$KTADM | 1 | 94 | 0 (0)| 00:00:01 |
|* 10 | FIXED TABLE FULL | X$KTATRFIL | 1 | 94 | 0 (0)| 00:00:01 |
|* 11 | FIXED TABLE FULL | X$KTATRFSL | 1 | 94 | 0 (0)| 00:00:01 |
|* 12 | FIXED TABLE FULL | X$KTATL | 1 | 94 | 0 (0)| 00:00:01 |
|* 13 | FIXED TABLE FULL | X$KTSTUSC | 1 | 94 | 0 (0)| 00:00:01 |
|* 14 | FIXED TABLE FULL | X$KTSTUSS | 1 | 94 | 0 (0)| 00:00:01 |
|* 15 | FIXED TABLE FULL | X$KTSTUSG | 1 | 94 | 0 (0)| 00:00:01 |
|* 16 | FIXED TABLE FULL | X$KTCXB | 1 | 94 | 0 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 100 | 7600 | 0 (0)| 00:00:01 |
|* 18 | FIXED TABLE FULL | X$KSUSE | 1 | 30 | 0 (0)| 00:00:01 |
| 19 | FIXED TABLE FULL | X$KSQRS | 100 | 4600 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

SQL> select * from v$lock;
.
.
.
Elapsed: 00:00:01.26

Response time from both cases, not different. see "NESTED LOOPS" from both "FIXED TABLE FULL" (X$KSUSE) and "FIXED TABLE FULL" (X$KSQRS). It's bad!!! So, back to using ORDERED hint again.

SQL> explain plan for select /*+ ordered */ * from v$lock;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3524752130
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 144 | 1 (100)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 144 | 1 (100)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 98 | 1 (100)| 00:00:01 |
| 3 | VIEW | GV$_LOCK | 10 | 680 | 0 (0)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
|* 5 | FILTER | | | | | |
| 6 | VIEW | GV$_LOCK1 | 2 | 136 | 0 (0)| 00:00:01 |
| 7 | UNION-ALL | | | | | |
|* 8 | FIXED TABLE FULL| X$KDNSSF | 1 | 94 | 0 (0)| 00:00:01 |
|* 9 | FIXED TABLE FULL| X$KSQEQ | 1 | 94 | 0 (0)| 00:00:01 |
|* 10 | FIXED TABLE FULL | X$KTADM | 1 | 94 | 0 (0)| 00:00:01 |
|* 11 | FIXED TABLE FULL | X$KTATRFIL | 1 | 94 | 0 (0)| 00:00:01 |
|* 12 | FIXED TABLE FULL | X$KTATRFSL | 1 | 94 | 0 (0)| 00:00:01 |
|* 13 | FIXED TABLE FULL | X$KTATL | 1 | 94 | 0 (0)| 00:00:01 |
|* 14 | FIXED TABLE FULL | X$KTSTUSC | 1 | 94 | 0 (0)| 00:00:01 |
|* 15 | FIXED TABLE FULL | X$KTSTUSS | 1 | 94 | 0 (0)| 00:00:01 |
|* 16 | FIXED TABLE FULL | X$KTSTUSG | 1 | 94 | 0 (0)| 00:00:01 |
|* 17 | FIXED TABLE FULL | X$KTCXB | 1 | 94 | 0 (0)| 00:00:01 |
|* 18 | FIXED TABLE FULL | X$KSUSE | 1 | 30 | 0 (0)| 00:00:01 |
| 19 | FIXED TABLE FULL | X$KSQRS | 100 | 4600 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

SQL> select /*+ ordered */ * from v$lock;
.
.
.
Elapsed: 00:00:00.06

So, Don't need to use both hidden parameters. just know both _optimizer_cartesian_enabled and _optimizer_mjc_enabled hidden parameter be able to eliminate the merge join cartesian Rule. & ORDERED hint still work -)

Related Posts:
Just learn more from V$LOCK
Excessive waited 'SQL*Net message from client' - when query V$LOCK

Just learn more from V$LOCK

I posted Excessive waited 'SQL*Net message from client' - when query V$LOCK. Thank You for first comment (Marcus). He figured me out about STATE and reviewed EXPLAIN PLAN!!! I want to test it more...

CASE 1: SELECT * FROM V$LOCK

SQL> EXPLAIN PLAN FOR SELECT * FROM V$LOCK;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 554400005
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 156 | 1 (100)| 00:00:04 |
|* 1 | HASH JOIN | | 1 | 156 | 1 (100)| 00:00:04 |
| 2 | VIEW | GV$_LOCK | 10 | 760 | 0 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | FILTER | | | | | |
| 5 | VIEW | GV$_LOCK1 | 2 | 152 | 0 (0)| 00:00:01 |
| 6 | UNION-ALL | | | | | |
|* 7 | FIXED TABLE FULL| X$KDNSSF | 1 | 102 | 0 (0)| 00:00:01 |
|* 8 | FIXED TABLE FULL| X$KSQEQ | 1 | 102 | 0 (0)| 00:00:01 |
|* 9 | FIXED TABLE FULL | X$KTADM | 1 | 102 | 0 (0)| 00:00:01 |
|* 10 | FIXED TABLE FULL | X$KTATRFIL | 1 | 102 | 0 (0)| 00:00:01 |
|* 11 | FIXED TABLE FULL | X$KTATRFSL | 1 | 102 | 0 (0)| 00:00:01 |
|* 12 | FIXED TABLE FULL | X$KTATL | 1 | 102 | 0 (0)| 00:00:01 |
|* 13 | FIXED TABLE FULL | X$KTSTUSC | 1 | 102 | 0 (0)| 00:00:01 |
|* 14 | FIXED TABLE FULL | X$KTSTUSS | 1 | 102 | 0 (0)| 00:00:01 |
|* 15 | FIXED TABLE FULL | X$KTSTUSG | 1 | 102 | 0 (0)| 00:00:01 |
|* 16 | FIXED TABLE FULL | X$KTCXB | 1 | 102 | 0 (0)| 00:00:01 |
| 17 | MERGE JOIN CARTESIAN | | 100 | 8000 | 0 (0)| 00:00:01 |
|* 18 | FIXED TABLE FULL | X$KSUSE | 1 | 32 | 0 (0)| 00:00:01 |
| 19 | BUFFER SORT | | 100 | 4800 | 0 (0)| 00:00:01 |
| 20 | FIXED TABLE FULL | X$KSQRS | 100 | 4800 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

CASE 2: SELECT /*+ PARALLEL */ * FROM V$LOCK

SQL> EXPLAIN PLAN FOR SELECT /*+ PARALLEL */ * FROM V$LOCK;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1453144240
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 156 | 1 (100)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 156 | 1 (100)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 108 | 1 (100)| 00:00:01 |
|* 3 | FIXED TABLE FULL | X$KSUSE | 1 | 32 | 0 (0)| 00:00:01 |
| 4 | VIEW | GV$_LOCK | 10 | 760 | 0 (0)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
|* 6 | FILTER | | | | | |
| 7 | VIEW | GV$_LOCK1 | 2 | 152 | 0 (0)| 00:00:01 |
| 8 | UNION-ALL | | | | | |
|* 9 | FIXED TABLE FULL| X$KDNSSF | 1 | 102 | 0 (0)| 00:00:01 |
|* 10 | FIXED TABLE FULL| X$KSQEQ | 1 | 102 | 0 (0)| 00:00:01 |
|* 11 | FIXED TABLE FULL | X$KTADM | 1 | 102 | 0 (0)| 00:00:01 |
|* 12 | FIXED TABLE FULL | X$KTATRFIL | 1 | 102 | 0 (0)| 00:00:01 |
|* 13 | FIXED TABLE FULL | X$KTATRFSL | 1 | 102 | 0 (0)| 00:00:01 |
|* 14 | FIXED TABLE FULL | X$KTATL | 1 | 102 | 0 (0)| 00:00:01 |
|* 15 | FIXED TABLE FULL | X$KTSTUSC | 1 | 102 | 0 (0)| 00:00:01 |
|* 16 | FIXED TABLE FULL | X$KTSTUSS | 1 | 102 | 0 (0)| 00:00:01 |
|* 17 | FIXED TABLE FULL | X$KTSTUSG | 1 | 102 | 0 (0)| 00:00:01 |
|* 18 | FIXED TABLE FULL | X$KTCXB | 1 | 102 | 0 (0)| 00:00:01 |
| 19 | FIXED TABLE FULL | X$KSQRS | 100 | 4800 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

He told me review "MERGE JOIN CARTESIAN", it can make Excessive fetch data. OK test test test it. then I found out V$LOCK!!!

SQL> SELECT VIEW_DEFINITION FROM V$FIXED_VIEW_DEFINITION WHERE VIEW_NAME='V$LOCK';

VIEW_DEFINITION
-----------------------------------------------------------------------------------------------------------------------------------------------
select ADDR , KADDR , SID , TYPE , ID1 , ID2 , LMODE , REQUEST , CTIME , BLOCK from GV$LOCK where inst_id = USERENV('Instance')

SQL> SELECT VIEW_DEFINITION FROM V$FIXED_VIEW_DEFINITION WHERE VIEW_NAME='GV$LOCK';

VIEW_DEFINITION
-----------------------------------------------------------------------------------------------------------------------------------------------
select s.inst_id,l.laddr,l.kaddr,s.ksusenum,r.ksqrsidt,r.ksqrsid1,r.ksqrsid2,l.lmode,l.request,l.ctime,decode(l.lmode,0,0,l.block) from v$_lock l, x$ksuse s, x$ksqrs r where l.saddr=s.addr and concat(USERENV('Instance'),l.raddr)=concat(r.inst_id,r.addr)

So, SQL statement for V$LOCK!!!
select s.inst_id,l.laddr,l.kaddr,s.ksusenum,r.ksqrsidt,r.ksqrsid1, r.ksqrsid2,l.lmode,l.request,l.ctime,decode(l.lmode,0,0,l.block) from v$_lock l, x$ksuse s, x$ksqrs r where l.saddr=s.addr and concat(USERENV('Instance'),l.raddr)=concat(r.inst_id,r.addr) and s.inst_id = USERENV('Instance');

and tested more ... with this sql statement.

SQL> explain plan for select s.inst_id,l.laddr,l.kaddr,s.ksusenum,r.ksqrsidt,r.ksqrsid1, r.ksqrsid2,l.lmode,l.request,l.ctime,decode(l.lmode,0,0,l.block) from v$_lock l, x$ksuse s, x$ksqrs r where l.saddr=s.addr and concat(USERENV('Instance'),l.raddr)=concat(r.inst_id,r.addr) and s.inst_id = USERENV('Instance');

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 554400005
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 156 | 1 (100)| 00:00:04 |
|* 1 | HASH JOIN | | 1 | 156 | 1 (100)| 00:00:04 |
| 2 | VIEW | GV$_LOCK | 10 | 760 | 0 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | FILTER | | | | | |
| 5 | VIEW | GV$_LOCK1 | 2 | 152 | 0 (0)| 00:00:01 |
| 6 | UNION-ALL | | | | | |
|* 7 | FIXED TABLE FULL| X$KDNSSF | 1 | 102 | 0 (0)| 00:00:01 |
|* 8 | FIXED TABLE FULL| X$KSQEQ | 1 | 102 | 0 (0)| 00:00:01 |
|* 9 | FIXED TABLE FULL | X$KTADM | 1 | 102 | 0 (0)| 00:00:01 |
|* 10 | FIXED TABLE FULL | X$KTATRFIL | 1 | 102 | 0 (0)| 00:00:01 |
|* 11 | FIXED TABLE FULL | X$KTATRFSL | 1 | 102 | 0 (0)| 00:00:01 |
|* 12 | FIXED TABLE FULL | X$KTATL | 1 | 102 | 0 (0)| 00:00:01 |
|* 13 | FIXED TABLE FULL | X$KTSTUSC | 1 | 102 | 0 (0)| 00:00:01 |
|* 14 | FIXED TABLE FULL | X$KTSTUSS | 1 | 102 | 0 (0)| 00:00:01 |
|* 15 | FIXED TABLE FULL | X$KTSTUSG | 1 | 102 | 0 (0)| 00:00:01 |
|* 16 | FIXED TABLE FULL | X$KTCXB | 1 | 102 | 0 (0)| 00:00:01 |
| 17 | MERGE JOIN CARTESIAN | | 100 | 8000 | 0 (0)| 00:00:01 |
|* 18 | FIXED TABLE FULL | X$KSUSE | 1 | 32 | 0 (0)| 00:00:01 |
| 19 | BUFFER SORT | | 100 | 4800 | 0 (0)| 00:00:01 |
| 20 | FIXED TABLE FULL | X$KSQRS | 100 | 4800 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

How to avoid "MERGE JOIN CARTESIAN", tried to use ORDERED hint - The ORDERED hint instructs Oracle to join tables in the order in which they appear in the FROM clause.
When you omit the ORDERED hint from a SQL statement requiring a join, the optimizer chooses the order in which to join the tables. You might want to use the ORDERED hint to specify a join order if you know something that the optimizer does not know about the number of rows selected from each table. Such information lets you choose an inner and outer table better than the optimizer could.

SQL> explain plan for select /*+ ordered */ s.inst_id,l.laddr,l.kaddr,s.ksusenum,r.ksqrsidt,r.ksqrsid1, r.ksqrsid2,l.lmode,l.request,l.ctime,decode(l.lmode,0,0,l.block) from v$_lock l, x$ksuse s, x$ksqrs r where l.saddr=s.addr and concat(USERENV('Instance'),l.raddr)=concat(r.inst_id,r.addr) and s.inst_id = USERENV('Instance');

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3524752130
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 156 | 1 (100)| 00:00:07 |
|* 1 | HASH JOIN | | 1 | 156 | 1 (100)| 00:00:07 |
|* 2 | HASH JOIN | | 1 | 108 | 1 (100)| 00:00:04 |
| 3 | VIEW | GV$_LOCK | 10 | 760 | 0 (0)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
|* 5 | FILTER | | | | | |
| 6 | VIEW | GV$_LOCK1 | 2 | 152 | 0 (0)| 00:00:01 |
| 7 | UNION-ALL | | | | | |
|* 8 | FIXED TABLE FULL| X$KDNSSF | 1 | 102 | 0 (0)| 00:00:01 |
|* 9 | FIXED TABLE FULL| X$KSQEQ | 1 | 102 | 0 (0)| 00:00:01 |
|* 10 | FIXED TABLE FULL | X$KTADM | 1 | 102 | 0 (0)| 00:00:01 |
|* 11 | FIXED TABLE FULL | X$KTATRFIL | 1 | 102 | 0 (0)| 00:00:01 |
|* 12 | FIXED TABLE FULL | X$KTATRFSL | 1 | 102 | 0 (0)| 00:00:01 |
|* 13 | FIXED TABLE FULL | X$KTATL | 1 | 102 | 0 (0)| 00:00:01 |
|* 14 | FIXED TABLE FULL | X$KTSTUSC | 1 | 102 | 0 (0)| 00:00:01 |
|* 15 | FIXED TABLE FULL | X$KTSTUSS | 1 | 102 | 0 (0)| 00:00:01 |
|* 16 | FIXED TABLE FULL | X$KTSTUSG | 1 | 102 | 0 (0)| 00:00:01 |
|* 17 | FIXED TABLE FULL | X$KTCXB | 1 | 102 | 0 (0)| 00:00:01 |
|* 18 | FIXED TABLE FULL | X$KSUSE | 1 | 32 | 0 (0)| 00:00:01 |
| 19 | FIXED TABLE FULL | X$KSQRS | 100 | 4800 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Good!!! joining changed!!! Test more...

SQL> select /*+ ordered */ s.inst_id,l.laddr,l.kaddr,s.ksusenum,r.ksqrsidt,r.ksqrsid1, r.ksqrsid2,l.lmode,l.request,l.ctime,decode(l.lmode,0,0,l.block) from v$_lock l, x$ksuse s, x$ksqrs r where l.saddr=s.addr and concat(USERENV('Instance'),l.raddr)=concat(r.inst_id,r.addr) and s.inst_id = USERENV('Instance');
.
.
.
807 rows selected.

Elapsed: 00:00:00.09

It's faster than....
I posted about 'SQL*Net message from client' wrong.... because "MERGE JOIN CARTESIAN" made excessive response time. So, I use : select /*+ ordered */ * from v$lock !!!

select /*+ ordered */ INST_ID, SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK from gv$lock where (ID1,ID2,TYPE) in (select ID1,ID2,TYPE from gv$lock where request>0) ORDER BY id1, request;

This may work for me... but not someone. But I learned "Check More EXPLAIN PLAN!!!, don't just say it's slowly".

Related Post:
Excessive waited 'SQL*Net message from client' - when query V$LOCK

Learn X$KFFXP - fixed table for ASM

I heard about the X$ fixed table (X$KFFXP). It keeps position of striped and mirrored extents... and etc. If You connect to ASM Instance, You can check and retrieve data from it.

$ sqlplus / as sysasm

SQL> desc X$KFFXP

Name Null? Type
----------------------------------------------------------------------------------- -------- ----------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
GROUP_KFFXP NUMBER
NUMBER_KFFXP NUMBER
COMPOUND_KFFXP NUMBER
INCARN_KFFXP NUMBER
PXN_KFFXP NUMBER
XNUM_KFFXP NUMBER
LXN_KFFXP NUMBER
DISK_KFFXP NUMBER
AU_KFFXP NUMBER
FLAGS_KFFXP NUMBER
CHK_KFFXP NUMBER
SIZE_KFFXP NUMBER

What do you learn?
Some columns in X$KFFXP table:
GROUP_KFFXP = V$ASM_DISKGROUP.GROUP_NUMBER
NUMBER_KFFXP = V$ASM_FILE.FILE_NUMBER
COMPOUND_KFFXP = V$ASM_FILE.COMPOUND_INDEX
INCARN_KFFXP = V$ASM_FILE.INCARNATION
XNUM_KFFXP = Extent Number
LXN_KFFXP = Logical Extent Number (0 = primary, 1 = mirrored copy, 2 = mirrored copy[High redundancy])
DISK_KFFXP = V$ASM_DISK.DISK_NUMBER
AU_KFFXP = V$ASM_DISKGROUP.ALLOCATION_UNIT_SIZE

So, I used X$KFFXP table for testing (just tested)

select dg.name, x.NUMBER_KFFXP "FILE NUMBER", x.XNUM_KFFXP "EXTENT NUMBER", decode(x.LXN_KFFXP,0,'P',1,'M','MM') "EXTENT TYPE", d.path from x$kffxp x, v$asm_disk d, v$asm_diskgroup dg where x.GROUP_KFFXP = d.GROUP_NUMBER and x.DISK_KFFXP = d.DISK_NUMBER and x.GROUP_KFFXP=dg.GROUP_NUMBER and x.NUMBER_KFFXP in (select file_number from v$asm_alias where name like 'USERS%') order by x.NUMBER_KFFXP, x.XNUM_KFFXP, x.LXN_KFFXP
/

P = primary
M = mirrored copy
MM = mirrored copy[High redundancy]

- On ASM, that uses External redundancy

SQL> select dg.name, x.NUMBER_KFFXP "FILE NUMBER", x.XNUM_KFFXP "EXTENT NUMBER", decode(x.LXN_KFFXP,0,'P',1,'M','MM') "EXTENT TYPE", d.path from x$kffxp x, v$asm_disk d, v$asm_diskgroup dg where x.GROUP_KFFXP = d.GROUP_NUMBER and x.DISK_KFFXP = d.DISK_NUMBER and x.GROUP_KFFXP=dg.GROUP_NUMBER and x.NUMBER_KFFXP in (select file_number from v$asm_alias where name like 'USERS%') order by x.NUMBER_KFFXP, x.XNUM_KFFXP, x.LXN_KFFXP
/

NAME FILE NUMBER EXTENT NUMBER EXTENT TYPE PATH
----- ----------- ------------- --------------- ---------------
DATA 316 0 P /dev/sdb
DATA 316 1 P /dev/sdb
DATA 316 2 P /dev/sdb
DATA 316 3 P /dev/sdb
DATA 316 4 P /dev/sdb
DATA 316 5 P /dev/sdb

- On ASM, that uses Normal redundancy

SQL> select dg.name, x.NUMBER_KFFXP "FILE NUMBER", x.XNUM_KFFXP "EXTENT NUMBER", decode(x.LXN_KFFXP,0,'P',1,'M','MM') "EXTENT TYPE", d.path from x$kffxp x, v$asm_disk d, v$asm_diskgroup dg where x.GROUP_KFFXP = d.GROUP_NUMBER and x.DISK_KFFXP = d.DISK_NUMBER and x.GROUP_KFFXP=dg.GROUP_NUMBER and x.NUMBER_KFFXP in (select file_number from v$asm_alias where name like 'USERS%') order by x.NUMBER_KFFXP, x.XNUM_KFFXP, x.LXN_KFFXP
/

NAME FILE NUMBER EXTENT NUMBER EXTENT TYPE PATH
------------------------------ ----------- ------------- --------------- ------------------------------
DATA 261 0 P ORCL:DATA10
DATA 261 0 M ORCL:DATA02
DATA 261 1 P ORCL:DATA03
DATA 261 1 M ORCL:DATA07
DATA 261 2 P ORCL:DATA09
DATA 261 2 M ORCL:DATA05
DATA 261 3 P ORCL:DATA05
DATA 261 3 M ORCL:DATA07
DATA 261 4 P ORCL:DATA08
DATA 261 4 M ORCL:DATA04
DATA 261 5 P ORCL:DATA04
DATA 261 5 M ORCL:DATA06

- On ASM, that uses High redundancy

SQL> select dg.name, x.NUMBER_KFFXP "FILE NUMBER", x.XNUM_KFFXP "EXTENT NUMBER", decode(x.LXN_KFFXP,0,'P',1,'M','MM') "EXTENT TYPE", d.path from x$kffxp x, v$asm_disk d, v$asm_diskgroup dg where x.GROUP_KFFXP = d.GROUP_NUMBER and x.DISK_KFFXP = d.DISK_NUMBER and x.GROUP_KFFXP=dg.GROUP_NUMBER and x.NUMBER_KFFXP in (select file_number from v$asm_alias where name like 'USERS%') order by x.NUMBER_KFFXP, x.XNUM_KFFXP, x.LXN_KFFXP
/

NAME FILE NUMBER EXTENT NUMBER EXTENT TYPE PATH
------------------------------ ----------- ------------- --------------- --------------------------------------------------
DATA 917 0 P o/192.168.99.6/DATA_EXA_CD_11_exacel02
DATA 917 0 M o/192.168.99.11/DATA_EXA_CD_02_exacel07
DATA 917 0 MM o/192.168.99.7/DATA_EXA_CD_00_exacel03
DATA 917 1 P o/192.168.99.9/DATA_EXA_CD_01_exacel05
DATA 917 1 M o/192.168.99.10/DATA_EXA_CD_11_exacel06
DATA 917 1 MM o/192.168.99.7/DATA_EXA_CD_06_exacel03
DATA 917 2 P o/192.168.99.9/DATA_EXA_CD_11_exacel05
DATA 917 2 M o/192.168.99.10/DATA_EXA_CD_01_exacel06
DATA 917 2 MM o/192.168.99.8/DATA_EXA_CD_02_exacel04
DATA 917 3 P o/192.168.99.6/DATA_EXA_CD_04_exacel02
DATA 917 3 M o/192.168.99.5/DATA_EXA_CD_10_exacel01
DATA 917 3 MM o/192.168.99.11/DATA_EXA_CD_10_exacel07
DATA 917 4 P o/192.168.99.8/DATA_EXA_CD_09_exacel04
DATA 917 4 M o/192.168.99.10/DATA_EXA_CD_09_exacel06
DATA 917 4 MM o/192.168.99.9/DATA_EXA_CD_03_exacel05
DATA 917 5 P o/192.168.99.6/DATA_EXA_CD_06_exacel02
DATA 917 5 M o/192.168.99.7/DATA_EXA_CD_10_exacel03
DATA 917 5 MM o/192.168.99.8/DATA_EXA_CD_07_exacel04

That was example to use it. However, you can use X$KFFXP to join with other views(V$ASM_*)