The experiences, Test cases, views, and opinions etc expressed in this website are my own and does not reflect the views or opinions of my employer. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.Product and company names mentioned in this website may be the trademarks of their respective owners.
Oracle Database Express Edition (XE) is a community supported edition of the Oracle Database family. It can be Installed in Linux, Windows, Dockers, and Virtual Machine as well.
Oracle Database Express Edition does not restrict in which environment it can be deployed. However, Oracle Database Express Edition is not supported and does not receive any patches, including security patches.
Oracle Database XE supports up to:
2 CPUs for foreground processes
2GB of RAM (SGA and PGA combined)
12GB of user data on disk (irrespective of compression factor)
wget https://download.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm
--2022-12-22 06:35:24-- https://download.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm
Resolving download.oracle.com (download.oracle.com)... 23.216.84.89
Connecting to download.oracle.com (download.oracle.com)|23.216.84.89|:443... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://edelivery.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm [following]
--2022-12-22 06:35:24-- https://edelivery.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm
Resolving edelivery.oracle.com (edelivery.oracle.com)... 23.218.130.184, 2600:1408:9000:690::366, 2600:1408:9000:684::366
Connecting to edelivery.oracle.com (edelivery.oracle.com)|23.218.130.184|:443... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://download.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm?AuthParam=1671709045_2ea5c9ceb6bfd5f59e6722739e5109ef [following]
--2022-12-22 06:35:25-- https://download.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm?AuthParam=1671709045_2ea5c9ceb6bfd5f59e6722739e5109ef
Connecting to download.oracle.com (download.oracle.com)|23.216.84.89|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 2339651768 (2.2G) [application/x-redhat-package-manager]
Saving to: ‘oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm’
oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm 54%[=========================================================================> ] 1.18G 2.14MB/s eta 2m 41s
oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm 67%[===========================================================================================> ] 1.47G 2.80MB/s eta 2m 27s
oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm 80%[=============================================================================================================> ] 1.76G 2.85MB/s eta 98s
oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm 80%[=============================================================================================================> ] 1.76G 2.76MB/s eta 98s
oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm 100%[========================================================================================================================================>] 2.18G 6.60MB/s in 8m 57s
2022-12-22 06:44:22 (4.16 MB/s) - ‘oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm’ saved [2339651768/2339651768]
Downloading PreInstall RPM
curl -o oracle-database-preinstall-21c-1.0-1.el8.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL8/appstream/x86_64/getPackage/oracle-database-preinstall-21c-1.0-1.el8.x86_64.rpm
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 30772 100 30772 0 0 132k 0 --:--:-- --:--:-- --:--:-- 132k
dnf -y localinstall oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm
Last metadata expiration check: 3:43:34 ago on Thu 22 Dec 2022 03:25:41 AM EST.
Dependencies resolved.
=============================================================================================================================================================================================================================================
Package Architecture Version Repository Size
=============================================================================================================================================================================================================================================
Installing:
oracle-database-xe-21c x86_64 1.0-1 @commandline 2.2 G
Transaction Summary
=============================================================================================================================================================================================================================================
Install 1 Package
Total size: 2.2 G
Installed size: 5.8 G
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Running scriptlet: oracle-database-xe-21c-1.0-1.x86_64 1/1
Installing : oracle-database-xe-21c-1.0-1.x86_64 1/1
Running scriptlet: oracle-database-xe-21c-1.0-1.x86_64 1/1
[INFO] Executing post installation scripts...
[INFO] Oracle home installed successfully and ready to be configured.
To configure Oracle Database XE, optionally modify the parameters in '/etc/sysconfig/oracle-xe-21c.conf' and then execute '/etc/init.d/oracle-xe-21c configure' as root.
Verifying : oracle-database-xe-21c-1.0-1.x86_64 1/1
Installed:
oracle-database-xe-21c-1.0-1.x86_64
Complete!
This Completes the Required RPM and Database Installation
Configuring the Database
# /etc/init.d/oracle-xe-21c configure
Oracle Net Listener configured.
Specify a password to be used for database accounts. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. Note that the same password will be used for SYS, SYSTEM and PDBADMIN accounts:
Confirm the password:
Configuring Oracle Listener.
Listener configuration succeeded.
Configuring Oracle Database XE.
Enter SYS user password:
************
Enter SYSTEM user password:
**************
Enter PDBADMIN User Password:
**************
[WARNING] [INS-08109] Unexpected error occurred while validating inputs at state 'DBCreationOptions'.
CAUSE: No additional information available.
ACTION: Contact Oracle Support Services or refer to the software manual.
SUMMARY:
- java.lang.NullPointerException
Database configuration failed. Check logs under '/opt/oracle/cfgtoollogs/dbca'.
Set the CV_ASSUME_DISTID variable as its coming as NULL, to proceed further.
export CV_ASSUME_DISTID=OEL8.4
# /etc/init.d/oracle-xe-21c configure
Oracle Net Listener configured.
Specify a password to be used for database accounts. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. Note that the same password will be used for SYS, SYSTEM and PDBADMIN accounts:
Confirm the password:
Configuring Oracle Listener.
Listener configuration succeeded.
Configuring Oracle Database XE.
Enter SYS user password:
*************
Enter SYSTEM user password:
*************
Enter PDBADMIN User Password:
************
Prepare for db operation
7% complete
Copying database files
29% complete
Creating and starting Oracle instance
30% complete
33% complete
37% complete
40% complete
43% complete
Completing Database Creation
47% complete
50% complete
Creating Pluggable Databases
54% complete
71% complete
Executing Post Configuration Actions
93% complete
Running Custom Scripts
100% complete
Database creation complete. For details check the logfiles at:
/opt/oracle/cfgtoollogs/dbca/XE.
Database Information:
Global Database Name:XE
System Identifier(SID):XE
Look at the log file "/opt/oracle/cfgtoollogs/dbca/XE/XE.log" for further details.
Connect to Oracle Database using one of the connect strings:
Pluggable database: masked_FQDN:1523/XEPDB1
Multitenant container database: masked_FQDN:1523
Use https://localhost:5500/em to access Oracle Enterprise Manager for Oracle Database XE
This Completes the Oracle Database 21c Express Edition Installation on Oracle Linux 9
$ sqlplus /nolog
SQL*Plus: Release 21.0.0.0.0 - Production on Thu Dec 22 08:03:44 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
SQL> exit
Disconnected from Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
Today, I had an Issue with My Oracle DB V 11.2.0.1 and It was Corrupted due to unexpected power failures More than 10 times 😉 !.
As Its a Test DB, I don`t have regular backups. It was an Internal Error with ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr] As I don`t have complete backup to recover , I can recover using ICR ( Incomplete recovery)
Thanks for your time , Happy reading & Have a Good day 😉
Today ,While Installing Oracle V 10g Using DBCA On a 32 bit Linux machine with 16 GB of RAM,(allocated 5GB as SGA ) We were encountered with this Error and unable to go further.
[oracle@db ~]$ oerr ORA 27102 27102, 00000, “out of memory” // *Cause: Out of memory // *Action: Consult the trace file for details [oracle@db ~]$
This is Because of On 32 Bit version,It cannot allocate more than 4 GB of Memory to a process Directly.For this either we need to upgrade to 64 bit and start the Installation or decrease the Memory total value.
So we decreased the total memory size to 2.5 GB and It went successful without any issues.
Read Oracle Meta link Document ID 430492.1 to get More Information on this.
Recently we upgraded Our Development Database from Oracle V10.2.0.1 to V10.2.0.5.
Today While I was Compiling an Invalid procedure,
ORA-06544: PL/SQL: internal error, arguments: [ph2_cly out of
bounds in PH2POP.], [0], [1240], [], [], [], [], [] Encountered.
No clues available On “alert-log” file.
Verified with Oracle Support about it and found that its a Bug{Bug 10400244} on 10.2.0.5.
Oracle Meta-link Document [ID 1358114.1] Will give some clues about it.
Fixing errors other than this Internal error will resolved this issue.
Some good Experience for Me today.Early in the Morning ……
One of Our RAC node filled up with 100% CPU usage.As part of Investigation We found that this was because of racgmain Process.
It was filled-up with more than 250 racgmain process which are consuming CPU a lot.Due this Our System got Very slow.
To avoid this, we should apply latest patch which is > 10.2.0.5 and CRS patch too.There was a temporary workaround for this ..you can see that in Metalink Doc ID 732086.1 and ID 6196746.8
Let `s discuss about The Changes Of DBA_USERS Especially In PASSWORD Column In Oracle 11g and 10g.
DBA_USERS Gives Us Information about all users of the database.
And it contains password hash value In its PASSWORD Column
ind> col TABLE_NAME format a10
ind> col COLUMN_NAME format a10
ind> col COMMENTS format a25
ind> select TABLE_NAME,COLUMN_NAME, COMMENTS
2 from dict_columns
3 where TABLE_NAME='DBA_USERS' and COLUMN_NAME='PASSWORD';
TABLE_NAME COLUMN_NAM COMMENTS
---------- ---------- -------------------------
DBA_USERS PASSWORD Encrypted password
1 row selected.
Yes..It contains the encrypted value Based On Concatenation of Username and Password
This Is How/why two Users with same password can have different Encrypted hash values.
ind> create user satya identified by satya;
User created.
ind> create user kalyani identified by satya;
User created.
ind> select username,password
2 from dba_users
3 where USERNAME in ('KALYANI','SATYA');
USERNAME PASSWORD
------------------------------ ------------------------------
KALYANI E81F7CB996A56BA9
SATYA 218ED5615AAE5F6B
2 rows selected.
ind> drop user satya;
User dropped.
ind> drop user satyab;
drop user satyab
*
ERROR at line 1:
ORA-01918: user 'SATYAB' does not exist
ind> create user satya identified by backpain;
User created.
ind> create user satyab identified by ackpain;
User created.
ind> select username,password
2 from dba_users
3 where username like 'SATYA%';
USERNAME PASSWORD
------------------------------ ------------------------------
SATYA 458070F68E74206E
SATYAB 458070F68E74206E
2 rows selected.
Here You can Observe that User SATYA and SATYAB with same hash value,
as the Concatenation of USERNAME and PASSWORD are Same.
But In Oracle 11g Oracle Wont store any password hash values in DBA_USERS.PASSWORD column.As Per the Document
Password Column is deprecated in favor of the AUTHENTICATION_TYPE column
But You can get the password from “User$“.
SQL> create User a identified by "a";
User created.
SQL> create User b identified by "a";
User created.
SQL> select dbms_metadata.get_ddl('USER','A') from dual;
DBMS_METADATA.GET_DDL('USER','A')
--------------------------------------------------------------------------------
CREATE USER "A" IDENTIFIED BY VALUES 'S:298EDEE1721E71B950D55CCB9ABA7EE5C596E
A6B0CCFF098E88889B98BD5;AFCC9478DFBF9029'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
SQL> select dbms_metadata.get_ddl('USER','B') from dual;
DBMS_METADATA.GET_DDL('USER','B')
--------------------------------------------------------------------------------
CREATE USER "B" IDENTIFIED BY VALUES 'S:25E85C1466288EE377681D131DF1920B33448
CD4108F8DDFC580A3315A39;9017AAA5BF2D9732'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
SQL> select name, password, spare4 from sys.user$ where name ='A'
2 ;
NAME PASSWORD
------------------------------ ------------------------------
SPARE4
--------------------------------------------------------------------------------
A AFCC9478DFBF9029
S:298EDEE1721E71B950D55CCB9ABA7EE5C596EA6B0CCFF098E88889B98BD5
SQL> select password,username from dba_users
2 where length(username)=1;
PASSWORD USERNAME
------------------------------ ------------------------------
A
B
SQL>
This is all about the Relation B/W MAXDATAFILES and DB_FILES In Oralce Database.
–This will Give Us db_files value. max no of data files that we can add.
select value from v$parameter where name = ‘db_files’;
This will give the MAXDATAFILES specified at Control file level while creating Database.
select records_total from v$controlfile_record_section where type = ‘DATAFILE’;
Lets Start with a small case. I have Created My database with DB_FILES as 10.
C:\Windows\System32>sqlplus system/tejajun20
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 27 20:20:19 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select value from v$parameter where name = 'db_files';
VALUE
--------------------------------------------------------------------------------
10
SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';
RECORDS_TOTAL
-------------
100
Let us have a look at the no of data files I have.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User2.dbf' size 100m ;
Tablespace altered.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User3.dbf' size 100m ;
Tablespace altered.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User4.dbf' size 100m ;
Tablespace altered.
SQL> select count(*) from v$datafile;
COUNT(*)
----------
8
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User5.dbf' size 100m ;
Tablespace altered.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User6.dbf' size 100m ;
Tablespace altered.
SQL> select count(*) from v$datafile;
COUNT(*)
----------
10
SQL> select value from v$parameter where name = 'db_files';
VALUE
--------------------------------------------------------------------------------
10
SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';
RECORDS_TOTAL
-------------
100
As I have already reached the max limit 10,Adding one more file will raise an Error.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User7.dbf' size 100m ;
alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User7.dbf' size 100m
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded
ORA-00059:
maximum number of DB_FILES exceeded
Cause:
The value of the DB_FILES initialization parameter was exceeded.
Action:
Increase the value of the DB_FILES parameter and warm start.
Lets Try to increase the value to a small number.
SQL> alter system set db_files=20 scope=spfile;
System altered.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 401743872 bytes
Fixed Size 1374892 bytes
Variable Size 268436820 bytes
Database Buffers 125829120 bytes
Redo Buffers 6103040 bytes
Database mounted.
Database opened.
SQL> select value from v$parameter where name = 'db_files';
VALUE
--------------------------------------------------------------------------------
20
SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';
RECORDS_TOTAL
-------------
100
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User7.dbf' size 100m ;
Tablespace altered.
SQL>
See now it allowing us to add datafiles.what about Exceeding MAXDATAFILES? (i.e more than 100 data files).
Lets do that And verify what `ll happen.
SQL> alter system set db_files=150 scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 401743872 bytes
Fixed Size 1374892 bytes
Variable Size 268436820 bytes
Database Buffers 125829120 bytes
Redo Buffers 6103040 bytes
Database mounted.
Database opened.
SQL> select value from v$parameter where name = 'db_files';
VALUE
--------------------------------------------------------------------------------
150
SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';
RECORDS_TOTAL
-------------
100
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User8.dbf' size 1m ;
Tablespace altered.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User9.dbf' size 1m ;
Tablespace altered.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User10.dbf' size 1m ;
Tablespace altered.
SQL> select count(*) from v$datafile;
COUNT(*)
----------
14
--- In another window I am adding datafiles upto the limit "100"
SQL> /
COUNT(*)
----------
48
SQL> /
COUNT(*)
----------
55
SQL> /
COUNT(*)
----------
82
SQL> /
COUNT(*)
----------
98
SQL> /
COUNT(*)
----------
98
SQL> /
COUNT(*)
----------
98
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User95.dbf' size 1m ;
Tablespace altered.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User96.dbf' size 1m ;
Tablespace altered.
SQL>
SQL> select value from v$parameter where name = 'db_files';
VALUE
--------------------------------------------------------------------------------
150
SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';
RECORDS_TOTAL
-------------
100
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User97.dbf' size 1m ;
Tablespace altered.
SQL> select value from v$parameter where name = 'db_files';
VALUE
--------------------------------------------------------------------------------
150
SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';
RECORDS_TOTAL
-------------
200
----Observe it Automatically changed..
SQL>
As Per Oracle Documents :
Consider Possible Limitations When Adding Datafiles to a Tablespace:
You can add datafiles to traditional smallfile tablespaces, subject to the following limitations:
Operating systems often impose a limit on the number of files a process can open simultaneously. More datafiles cannot be created when the operating system limit of open files is reached.
Operating systems impose limits on the number and size of datafiles.
The database imposes a maximum limit on the number of datafiles for any Oracle Database opened by any instance. This limit is operating system specific.
You cannot exceed the number of datafiles specified by the DB_FILES initialization parameter.
When you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an initial size of the datafile portion of the control file. However,if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the control file will expand automatically so that the datafiles section can accommodate more files.
DB_FILES specifies the maximum number of database files that can be opened for this database.The maximum valid value is the maximum number of files, subject to operating system constraint,that will ever be specified for the database, including files to be added by ADD DATAFILE statements.If you increase the value of DB_FILES, then you must shut down and restart all instances accessing the database before the new value can take effect. If you have a primary and standby database,then they should have the same value for this parameter.
The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer.A statement’s execution plan is the sequence of operations Oracle performs to run the statement.execution plans are read inside-out.if there are two statements at the same level, the first statement is executed first.
You can use “Explain plan statement for this.” which should be explained first.
The basic syntax to get explain plan is explain plan set statement_id= for
For Ex:
ind> explain plan set statement_id=’ORAFAQ’ for
2 select
3 a.empno,
4 a.ename,
5 b.dname
6 from
7 emp_sriram a,
8 dept b
9 where
10 a.deptno=b.deptno
11 /
Explained.
Now the sequential steps of this sql statement is stored in a Table called PLAN_TABLE.
Query Plan
———————————————————————————————–
1.4 SELECT STATEMENT ORAFAQ Cost = 44
2.1 NESTED LOOPS 4
3.1 TABLE ACCESS FULL EMP_SRIRAM TABLE 3
3.2 TABLE ACCESS BY INDEX ROWID DEPT TABLE 1
4.1 INDEX UNIQUE SCAN PK_DEPT INDEX (UNIQUE) 0
5 rows selected.
You can Also Use DBMS_XPLAN.DISPLAY.
ind> SELECT *
2 FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’,’ORAFAQ’,’BASIC’));
PLAN_TABLE_OUTPUT
———————————————————————————-
Plan hash value: 2868360194
—————————————————
| Id | Operation | Name |
—————————————————
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | TABLE ACCESS FULL | EMP_SRIRAM |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT |
| 4 | INDEX UNIQUE SCAN | PK_DEPT |
—————————————————
11 rows selected.
The basic structure of this package is
dbms_xplan.display(
table_name IN VARCHAR2 DEFAULT ‘PLAN_TABLE’,
statement_id IN VARCHAR2 DEFAULT NULL,
format IN VARCHAR2 DEFAULT ‘TYPICAL’);
Format choices are
BASIC ….. displays minimum information
TYPICAL … displays most relevant information
SERIAL …. like TYPICAL but without parallel information
ALL ……. displays all information
SQL plus autotrace also produce the explain plan …..
set autotrace off
set autotrace on
set autotrace traceonly
set autotrace on explain
set autotrace on statistics
set autotrace on explain statistics
set autotrace traceonly explain
set autotrace traceonly statistics
set autotrace traceonly explain statistics
set autotrace off explain
set autotrace off statistics
set autotrace off explain statistics
Setting autotrace allows to display some statistics and/or an query execution plan for DML statements.
set autotrace on: Shows the execution plan as well as statistics of the statement.
set autotrace on explain: Displays the execution plan only.
set autotrace on statistics: Displays the statistics only.
set autotrace traceonly: Displays the execution plan and the statistics (as set autotrace on does), but doesn't print a query's result.
set autotrace off: Disables all autotrace
Now lets have a look at execution plan order ….
ind> select LPAD(‘ ‘,2*(LEVEL-1))||operation “OPERATION”, options “OPTIONS”,
2 DECODE(TO_CHAR(id),’0’,’COST = ‘ || NVL(TO_CHAR(position),’n/a’),
3 object_name) “OBJECTNAME”, id ||’-‘|| NVL(parent_id, 0)||’-‘||
4 NVL(position, 0) “ORDER”, SUBSTR(optimizer,1,6) “OPT”
5 from plan_table
6 start with id = 0
7 and statement_id=’ORAFAQ’
8 connect by prior id = parent_id
9 and statement_id=’ORAFAQ’;
OPERATION OPTIONS OBJECTNAME ORDER OPT
—————————— —————————— —————————— ——-
SELECT STATEMENT COST = 4 0-0-4 ALL_RO
NESTED LOOPS 1-0-1
TABLE ACCESS FULL EMP_SRIRAM 2-1-1 ANALYZ
TABLE ACCESS BY INDEX ROWID DEPT 3-1-2 ANALYZ
INDEX UNIQUE SCAN PK_DEPT 4-3-1 ANALYZ
3 object_name) “OBJECTNAME”, id ||’-‘|| NVL(parent_id, 0)||’-‘||
4 NVL(position, 0) “ORDER”
5 from plan_table
6 start with id = 0
7 and statement_id=’ORAFAQ’
8 connect by prior id = parent_id
9 and statement_id=’ORAFAQ’;
OPERATION OPTIONS OBJECTNAME ORDER
—————————— —————————— —————————— ——-
SELECT STATEMENT COST = 4 0-0-4
NESTED LOOPS 1-0-1
TABLE ACCESS FULL EMP_SRIRAM 2-1-1
TABLE ACCESS BY INDEX ROWID DEPT 3-1-2
INDEX UNIQUE SCAN PK_DEPT 4-3-1
5 rows selected.
On 0-0-4 it is in an order like ID and parent id nad its position.
Like wise if we can order them
00
10
21
31
43
here Level 1 has 2 childs(2,3) and 3 has 1 child(4).
So the execution steps sequential order will be 2,4,3,1
i.e
TABLE ACCESS FULL EMP_SRIRAM
INDEX UNIQUE SCAN PK_DEPT
TABLE ACCESS BY INDEX ROWID DEPT
Produce the result
SELECT STATEMENT Indicates that its a "SELECT STATEMENT"
Here First it will read data from emp_sriram then a unique Index scan on dept (step 4)
which gives the rowids and based the rowid input it goes to the step 1
(we can say for understanding) and produce the result.
For More Information Please have a look at Oracle documents.
My Experiences with Databases 