Vinod Siram – Page 4

Datapump export and import from Source PDB to Target PDB

2 years ago by Vinod SiramOracle DB Admin

Datapump export and import from Source PDB to Target PDB.

Source :
SQL> alter session set container=OGGDB1PDB;
Session altered.

SQL> select username from dba_users where oracle_maintained='N';
USERNAME
--------------------------------------------------------------------------------
PDBADMIN
HR

SQL> select table_name from dba_tables where owner='HR';
TABLE_NAME
--------------------------------------------------------------------------------
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
COUNTRIES

7 rows selected.

SQL> select table_name,num_rows from dba_tables where owner='HR';
TABLE_NAME          NUM_ROWS
------------------- ----------
REGIONS             4
LOCATIONS           23
DEPARTMENTS         27
JOBS                19
EMPLOYEES           107
JOB_HISTORY         10
COUNTRIES           25

7 rows selected.

SQL> select directory_name from dba_directories;
DIRECTORY_NAME
--------------------------------------------------------------------------------
XMLDIR
XSDDIR
ORA_DBMS_FCP_LOGDIR
ORA_DBMS_FCP_ADMINDIR
OPATCH_INST_DIR
ORACLE_OCM_CONFIG_DIR
DATA_PUMP_DIR
ORACLE_OCM_CONFIG_DIR2
OPATCH_SCRIPT_DIR
OPATCH_LOG_DIR
ORACLE_BASE
ORACLE_HOME

1* select directory_name,directory_path from dba_directories
SQL> /
DIRECTORY_NAME         DIRECTORY_PATH
-----------------      --------------------------------------------------------------------------------- 
XMLDIR                 /u01/app/oracle/product/12.2.0.1/rdbms/xml
XSDDIR                 /u01/app/oracle/product/12.2.0.1/rdbms/xml/schema
ORA_DBMS_FCP_LOGDIR    /u01/app/oracle/product/12.2.0.1/cfgtoollogs
ORA_DBMS_FCP_ADMINDIR  /u01/app/oracle/product/12.2.0.1/rdbms/admin
OPATCH_INST_DIR        /u01/app/oracle/product/12.2.0.1/OPatch
ORACLE_OCM_CONFIG_DIR  /u01/app/oracle/product/12.2.0.1/ccr/state
DATA_PUMP_DIR          /u01/app/oracle/admin/oggdb1/dpdump/EE0955F9C4AB14D0E0536638
ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/12.2.0.1/ccr/state
OPATCH_SCRIPT_DIR      /u01/app/oracle/product/12.2.0.1/QOpatch
OPATCH_LOG_DIR         /u01/app/oracle/product/12.2.0.1/rdbms/log
ORACLE_BASE            /u01/app/oracle
ORACLE_HOME            /u01/app/oracle/product/12.2.0.1

OGGDB1PDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ggate1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oggdb1pdb)
)
)
expdp sys/oracle@OGGDB1PDB directory=DATA_PUMP_DIR dumpfile=hr_employees.dmp tables=hr.employees logfile=employees.log

Export: Release 12.2.0.1.0 - Production on Fri Dec 2 00:02:19 2022
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

UDE-28009: operation generated ORACLE error 28009
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

Username: sys/oracle@OGGDB1PDB as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_TABLE_01": sys/********@OGGDB1PDB AS SYSDBA directory=DATA_PUMP_DIR dumpfile=hr_employees.dmp tables=hr.employees logfile=employees.log
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES" 17.08 KB 107 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/oggdb1/dpdump/EE0955F9C4AB14D0E0536638A8C0EB50/hr_employees.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Fri Dec 2 00:03:28 2022 elapsed 0 00:00:37

Target :
[oracle@ggate1 ~]$ . oraenv
ORACLE_SID = [oggdb1] ? tgtcdb
The Oracle base remains unchanged with value /u01/app/oracle

SQL> show pdbs
CON_ID     CON_NAME               OPEN MODE  RESTRICTED
---------- ---------------------- ---------- ----------
2          PDB$SEED               READ ONLY  NO
3          TARGETPDB              READ WRITE NO
SQL> alter session set container=TARGETPDB;
Session altered.

1* select directory_name,directory_path from dba_directories
SQL> /
DIRECTORY_NAME                          DIRECTORY_PATH
--------------------------------------- -------------------------------------------------------------------------
XMLDIR                                  /u01/app/oracle/product/12.2.0.1/rdbms/xml
XSDDIR                                  /u01/app/oracle/product/12.2.0.1/rdbms/xml/schema
ORA_DBMS_FCP_LOGDIR                     /u01/app/oracle/product/12.2.0.1/cfgtoollogs
ORA_DBMS_FCP_ADMINDIR                   /u01/app/oracle/product/12.2.0.1/rdbms/admin
OPATCH_INST_DIR                         /u01/app/oracle/product/12.2.0.1/OPatch
ORACLE_OCM_CONFIG_DIR                   /u01/app/oracle/product/12.2.0.1/ccr/state
DATA_PUMP_DIR                           /u01/app/oracle/admin/tgtcdb/dpdump/EE1DA335F2064F71E0536638A8C034E1
ORACLE_OCM_CONFIG_DIR2                  /u01/app/oracle/product/12.2.0.1/ccr/state
OPATCH_SCRIPT_DIR                       /u01/app/oracle/product/12.2.0.1/QOpatch
OPATCH_LOG_DIR                          /u01/app/oracle/product/12.2.0.1/rdbms/log
ORACLE_BASE                             /u01/app/oracle
ORACLE_HOME                             /u01/app/oracle/product/12.2.0.1

SQL> select table_name,num_rows from dba_tables where owner='HR';
TABLE_NAME        NUM_ROWS
----------------- -----------------
REGIONS           4
LOCATIONS         23
DEPARTMENTS       27
JOBS              19
EMPLOYEES         107
JOB_HISTORY       10
COUNTRIES         25

7 rows selected.
SQL> show pdbs
CON_ID     CON_NAME                   OPEN MODE  RESTRICTED
---------- -------------------------- ---------- ----------
3          TARGETPDB                  READ WRITE NO
SQL>
TARGETPDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ggate1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TARGETPDB)
)
)
impdp sys/oracle@TARGETPDB directory=DATA_PUMP_DIR dumpfile=hr_employees.dmp tables=hr.employees logfile=employees_imp.log table_exists_action=replace

[oracle@ggate1 admin]$ impdp sys/oracle@TARGETPDB directory=DATA_PUMP_DIR dumpfile=hr_employees.dmp tables=hr.employees logfile=employees_imp.log table_exists_action=replace
Import: Release 12.2.0.1.0 - Production on Fri Dec 2 00:09:15 2022
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

UDI-28009: operation generated ORACLE error 28009
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

Username: sys/oracle@TARGETPDB as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": sys/********@TARGETPDB AS SYSDBA directory=DATA_PUMP_DIR dumpfile=hr_employees.dmp tables=hr.employees logfile=employees_imp.log table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."EMPLOYEES" 17.08 KB 107 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Fri Dec 2 00:10:14 2022 elapsed 0 00:00:32

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

Source :

SQL> alter session set container=OGGDB1PDB;

Session altered.

SQL> select username from dba_users where oracle_maintained='N';

USERNAME

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

PDBADMIN

SQL> select table_name from dba_tables where owner='HR';

TABLE_NAME

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

REGIONS

LOCATIONS

DEPARTMENTS

JOBS

EMPLOYEES

JOB_HISTORY

COUNTRIES

7 rows selected.

SQL> select table_name,num_rows from dba_tables where owner='HR';

TABLE_NAME NUM_ROWS

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

REGIONS 4

LOCATIONS 23

DEPARTMENTS 27

JOBS 19

EMPLOYEES 107

JOB_HISTORY 10

COUNTRIES 25

7 rows selected.

SQL> select directory_name from dba_directories;

DIRECTORY_NAME

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

XMLDIR

XSDDIR

ORA_DBMS_FCP_LOGDIR

ORA_DBMS_FCP_ADMINDIR

OPATCH_INST_DIR

ORACLE_OCM_CONFIG_DIR

DATA_PUMP_DIR

ORACLE_OCM_CONFIG_DIR2

OPATCH_SCRIPT_DIR

OPATCH_LOG_DIR

ORACLE_BASE

ORACLE_HOME

1* select directory_name,directory_path from dba_directories

SQL> /

DIRECTORY_NAME DIRECTORY_PATH

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

XMLDIR /u01/app/oracle/product/12.2.0.1/rdbms/xml

XSDDIR /u01/app/oracle/product/12.2.0.1/rdbms/xml/schema

ORA_DBMS_FCP_LOGDIR /u01/app/oracle/product/12.2.0.1/cfgtoollogs

ORA_DBMS_FCP_ADMINDIR /u01/app/oracle/product/12.2.0.1/rdbms/admin

OPATCH_INST_DIR /u01/app/oracle/product/12.2.0.1/OPatch

ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/12.2.0.1/ccr/state

DATA_PUMP_DIR /u01/app/oracle/admin/oggdb1/dpdump/EE0955F9C4AB14D0E0536638

ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/12.2.0.1/ccr/state

OPATCH_SCRIPT_DIR /u01/app/oracle/product/12.2.0.1/QOpatch

OPATCH_LOG_DIR /u01/app/oracle/product/12.2.0.1/rdbms/log

ORACLE_BASE /u01/app/oracle

ORACLE_HOME /u01/app/oracle/product/12.2.0.1

OGGDB1PDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ggate1)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = oggdb1pdb)

)

expdp sys/oracle@OGGDB1PDB directory=DATA_PUMP_DIR dumpfile=hr_employees.dmp tables=hr.employees logfile=employees.log

Export: Release 12.2.0.1.0 - Production on Fri Dec 2 00:02:19 2022

UDE-28009: operation generated ORACLE error 28009

ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

Username: sys/oracle@OGGDB1PDB as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Starting "SYS"."SYS_EXPORT_TABLE_01": sys/********@OGGDB1PDB AS SYSDBA directory=DATA_PUMP_DIR dumpfile=hr_employees.dmp tables=hr.employees logfile=employees.log

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/COMMENT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/TRIGGER

. . exported "HR"."EMPLOYEES" 17.08 KB 107 rows

Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_TABLE_01 is:

/u01/app/oracle/admin/oggdb1/dpdump/EE0955F9C4AB14D0E0536638A8C0EB50/hr_employees.dmp

Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Fri Dec 2 00:03:28 2022 elapsed 0 00:00:37

Target :

[oracle@ggate1 ~]$ . oraenv

ORACLE_SID = [oggdb1] ? tgtcdb

The Oracle base remains unchanged with value /u01/app/oracle

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 TARGETPDB READ WRITE NO

SQL> alter session set container=TARGETPDB;

Session altered.

1* select directory_name,directory_path from dba_directories

SQL> /

DIRECTORY_NAME DIRECTORY_PATH

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

XMLDIR /u01/app/oracle/product/12.2.0.1/rdbms/xml

XSDDIR /u01/app/oracle/product/12.2.0.1/rdbms/xml/schema

ORA_DBMS_FCP_LOGDIR /u01/app/oracle/product/12.2.0.1/cfgtoollogs

ORA_DBMS_FCP_ADMINDIR /u01/app/oracle/product/12.2.0.1/rdbms/admin

OPATCH_INST_DIR /u01/app/oracle/product/12.2.0.1/OPatch

ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/12.2.0.1/ccr/state

DATA_PUMP_DIR /u01/app/oracle/admin/tgtcdb/dpdump/EE1DA335F2064F71E0536638A8C034E1

ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/12.2.0.1/ccr/state

OPATCH_SCRIPT_DIR /u01/app/oracle/product/12.2.0.1/QOpatch

OPATCH_LOG_DIR /u01/app/oracle/product/12.2.0.1/rdbms/log

ORACLE_BASE /u01/app/oracle

ORACLE_HOME /u01/app/oracle/product/12.2.0.1

SQL> select table_name,num_rows from dba_tables where owner='HR';

TABLE_NAME NUM_ROWS

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

REGIONS 4

LOCATIONS 23

DEPARTMENTS 27

JOBS 19

EMPLOYEES 107

JOB_HISTORY 10

COUNTRIES 25

7 rows selected.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

3 TARGETPDB READ WRITE NO

SQL>

TARGETPDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ggate1)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = TARGETPDB)

)

impdp sys/oracle@TARGETPDB directory=DATA_PUMP_DIR dumpfile=hr_employees.dmp tables=hr.employees logfile=employees_imp.log table_exists_action=replace

[oracle@ggate1 admin]$ impdp sys/oracle@TARGETPDB directory=DATA_PUMP_DIR dumpfile=hr_employees.dmp tables=hr.employees logfile=employees_imp.log table_exists_action=replace

Import: Release 12.2.0.1.0 - Production on Fri Dec 2 00:09:15 2022

UDI-28009: operation generated ORACLE error 28009

ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

Username: sys/oracle@TARGETPDB as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_TABLE_01": sys/********@TARGETPDB AS SYSDBA directory=DATA_PUMP_DIR dumpfile=hr_employees.dmp tables=hr.employees logfile=employees_imp.log table_exists_action=replace

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "HR"."EMPLOYEES" 17.08 KB 107 rows

Processing object type TABLE_EXPORT/TABLE/COMMENT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/TRIGGER

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Fri Dec 2 00:10:14 2022 elapsed 0 00:00:32

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates KTExperts is always active on social media platforms. Facebook : https://www.facebook.com/ktexperts/ LinkedIn : https://www.linkedin.com/company/ktexperts/ Twitter : https://twitter.com/ktexpertsadmin YouTube … Read More

Pluggable database save state

2 years ago by Vinod SiramOracle DB Admin

Pluggable database save state Automate Pluggable DB opening during Container instance startup PDB Saved State is a feature in 12.1.0.2 for saving the OPEN state of all PDBS when CDB is restarted. This is for preserving the state of a Pluggable database proir to CDB… Read More

Different types of creating Pluggable Database manually.

2 years ago by Vinod SiramOracle DB Admin

Oracle,Pluggable Different ortypes of creating Pluggable Database manually There are multiple ways to create a pluggable database, in this article we would go through the process of creating PDB manually as in certain cases where GUI model is not feasible as creating a PDB using… Read More

Oracle : Shell script for Crontab Monitoring alert

4 years ago by Vinod SiramOracle DB Admin

Hi Readers, In this article, we will see the Oracle : Shell script for Crontab Monitoring alert THIS SCRIPT WILL IDENTIFY changes with the crontab file made comparing with original file.

[oracle@nposqa1 ~]$ cat /home/oracle/DBA/bin/crontab_compare.bash
#!/bin/bash
# set -x
# Get the current crontab entries
# Compare with the expected crontab entries
# Notify if any changes found
#########################################################################################
# Control file locations separately.
# create following variables in the file
# ORIGFILE = Original file to compare the current crontab entries
# EMAILRECIPIENTS='example@example.com'
# EMAILBODY=Location of email body file.

VARIABLES=$1
. $VARIABLES

HOST=$(hostname)
CURRFILE='/tmp/currcron.file'
DIFFILE='/tmp/crondif.file'

crontab -l > $CURRFILE
diff $ORIGFILE $CURRFILE > $DIFFILE

#Function to push email to users on alert
function pushmail
{
SUBJ=$1
MAILBODY=$2
RECIPIENTS=$3
mail -s "$SUBJ" $RECIPIENTS < $MAILBODY
}

if [ -s $DIFFILE ]
then
echo "" > $EMAILBODY
echo "Attention: Found changes in the current crontab entry" >>$EMAILBODY
echo "" >> $EMAILBODY
echo "---- Below are the changes from DIFF command ----" >> $EMAILBODY
echo "" >> $EMAILBODY
cat $DIFFILE >> $EMAILBODY
pushmail "ALERT: Found changes in crontab entry on server $HOST" $EMAILBODY $EMAILRECIPIENTS
fi

[oracle@nposqa1 ~]$ cat /home/oracle/DBA/bin/crontab_compare.bash

#!/bin/bash

# set -x

# Get the current crontab entries

# Compare with the expected crontab entries

# Notify if any changes found

#########################################################################################

# Control file locations separately.

# create following variables in the file

# ORIGFILE = Original file to compare the current crontab entries

# EMAILRECIPIENTS='example@example.com'

# EMAILBODY=Location of email body file.

VARIABLES=$1

. $VARIABLES

HOST=$(hostname)

CURRFILE='/tmp/currcron.file'

DIFFILE='/tmp/crondif.file'

crontab -l > $CURRFILE

diff $ORIGFILE $CURRFILE > $DIFFILE

#Function to push email to users on alert

function pushmail

{

SUBJ=$1

MAILBODY=$2

RECIPIENTS=$3

mail -s "$SUBJ" $RECIPIENTS < $MAILBODY

}

if [ -s $DIFFILE ]

then

echo "" > $EMAILBODY

echo "Attention: Found changes in the current crontab entry" >>$EMAILBODY

echo "" >> $EMAILBODY

echo "---- Below are the changes from DIFF command ----" >> $EMAILBODY

echo "" >> $EMAILBODY

cat $DIFFILE >> $EMAILBODY

pushmail "ALERT: Found changes in crontab entry on server $HOST" $EMAILBODY $EMAILRECIPIENTS

Config file :

[oracle@nposqa1 ~]$ cat /home/oracle/DBA/cfg/crontab_compare.cfg
# Control file locations separately.
# create following variables in the file
# ORIGFILE = Original file to compare the current crontab entries
# EMAILRECIPIENTS='example@example.com'
# EMAILBODY=Location of email body file.

export DIR='/home/oracle/DBA'
export DBA_BIN=${DIR}/bin
export DBA_LOG=${DIR}/log
export DBA_CFG=${DIR}/cfg
export DBA_SQL=${DIR}/sql
export ORIGFILE="${DBA_CFG}/crontab_orig.cron"
export EMAILRECIPIENTS="admin@ktexperts.com"
export EMAILBODY="${DBA_CFG}/crontab_emailbody.file"

[oracle@nposqa1 ~]$ cat /home/oracle/DBA/cfg/crontab_compare.cfg

# Control file locations separately.

# create following variables in the file

# ORIGFILE = Original file to compare the current crontab entries

# EMAILRECIPIENTS='example@example.com'

# EMAILBODY=Location of email body file.

export DIR='/home/oracle/DBA'

export DBA_BIN=${DIR}/bin

export DBA_LOG=${DIR}/log

export DBA_CFG=${DIR}/cfg

export DBA_SQL=${DIR}/sql

export ORIGFILE="${DBA_CFG}/crontab_orig.cron"

export EMAILRECIPIENTS="admin@ktexperts.com"

export EMAILBODY="${DBA_CFG}/crontab_emailbody.file"

Create original crontab file :

cd /home/oracle/DBA/cfg\

1	cd /home/oracle/DBA/cfg\

crontab -l >crontab_orig.cron

1	crontab -l >crontab_orig.cron

Add below… Read More

Oracle : RAC Important Commands (SecureCRT)

4 years ago by Vinod SiramRAC

RAC Important Commands (SecureCRT) Dear Readers, In this article, we will see the Oracle : Important Commands for RAC (SecureCRT) Menu Bar : RAC Button : VD_Location

crsctl query css votedisk

1	crsctl query css votedisk

## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 970d29b0fdca4fb3bfa2210f71ebf828 (VIN:SSD_E0_S00_XXYZ41896P1) [DATA]
2. ONLINE 1422365a8a334f2fbfda2a8e9a348d78 (VIN:SSD_E0_S02_XXYZ41868P1) [DATA]
3. ONLINE 2752bc1b291a4f97bf904ff37b43c19b (VIN:SSD_E0_S04_XXYZ41948P1) [DATA]

## STATE File Universal Id File Name Disk group

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

1. ONLINE 970d29b0fdca4fb3bfa2210f71ebf828 (VIN:SSD_E0_S00_XXYZ41896P1) [DATA]

2. ONLINE 1422365a8a334f2fbfda2a8e9a348d78 (VIN:SSD_E0_S02_XXYZ41868P1) [DATA]

3. ONLINE 2752bc1b291a4f97bf904ff37b43c19b (VIN:SSD_E0_S04_XXYZ41948P1) [DATA]

Button : ocrcheck

ocrcheck

ocrcheck

Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 491684
Used space (kbytes) : 85328
Available space (kbytes) : 406356
ID : 496404425
Device/File Name : +DATA
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check bypassed due to non-privileged user

Status of Oracle Cluster Registry is as follows :

Version : 4

Total space (kbytes) : 491684

Used space (kbytes) : 85328

Available space (kbytes) : 406356

ID : 496404425

Device/File Name : +DATA

Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check bypassed due to non-privileged user

Button : ConfigDB

srvctl config database -d ORCL

1	srvctl config database -d ORCL

Database unique name: ORCL
Database name: ORCL
Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_1
Oracle user: oracle
Spfile: +DATA/ORCL/PARAMETERFILE/spfile.266.1005927959
Password file: +DATA/ORCL/PASSWORD/pwdorcl.260.1005927685
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA,REDO
Mount point paths:
Services: orcl_app_svc,orcl_int_svc
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dbaoper
Database instances: orcl11,orcl12
Configured nodes: server03,server04
Database is administrator managed

Database unique name: ORCL

Database name: ORCL

Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_1

Oracle user: oracle

Spfile: +DATA/ORCL/PARAMETERFILE/spfile.266.1005927959

Password file: +DATA/ORCL/PASSWORD/pwdorcl.260.1005927685

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA,REDO

Mount point paths:

Services: orcl_app_svc,orcl_int_svc

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: dba

OSOPER group: dbaoper

Database instances: orcl11,orcl12

Configured nodes: server03,server04

Database is administrator managed

Button : config service

srvctl config service -d ORCL

1	srvctl config service -d ORCL

Service name: orcl_app_svc
Server pool:
Cardinality: 2
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type:
Failover method:
TAF failover retries:
TAF failover delay:
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Session State Consistency:
GSM Flags: 0
Service is enabled
Preferred instances: orcl1,orcl2
Available instances:

Service name: orcl_int_svc
Server pool:
Cardinality: 2
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type:
Failover method:
TAF failover retries:
TAF failover delay:
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Session State Consistency:
GSM Flags: 0
Service is enabled
Preferred instances: orcl1,orcl2
Available instances:

Service name: orcl_app_svc

Server pool:

Cardinality: 2

Disconnect: false

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Global: false

Commit Outcome: false

Failover type:

Failover method:

TAF failover retries:

TAF failover delay:

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: NONE

TAF policy specification: NONE

Edition:

Pluggable database name:

Maximum lag time: ANY

SQL Translation Profile:

Retention: 86400 seconds

Replay Initiation Time: 300 seconds

Session State Consistency:

GSM Flags: 0

Service is enabled

Preferred instances: orcl1,orcl2

Available instances:

Service name: orcl_int_svc

Server pool:

Cardinality: 2

Disconnect: false

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Global: false

Commit Outcome: false

Failover type:

Failover method:

TAF failover retries:

TAF failover delay:

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: NONE

TAF policy specification: NONE

Edition:

Pluggable database name:

Maximum lag time: ANY

SQL Translation Profile:

Retention: 86400 seconds

Replay Initiation Time: 300 seconds

Session State Consistency:

GSM Flags: 0

Service is enabled

Preferred instances: orcl1,orcl2

Available instances:

Button… Read More

Important Oracle v$parameters (SecureCRT)

4 years ago by Vinod SiramSecure CRT

Hi Dears, In this article, we will see Important Oracle v$parameters (SecureCRT) Tab Name : v$parameter Button Name : SGA

col name for a50 
set linesize 300 
col value for a40  
select name,value from v$parameter where name like '%sga%'; 
select &sga/1024/1024/1024 GB from dual;

col name for a50

set linesize 300

col value for a40

select name,value from v$parameter where name like '%sga%';

select &sga/1024/1024/1024 GB from dual;

Output :

NAME                                               VALUE
-------------------------------------------------- ----------------------------------------
sga_max_size                                       4294967296
pre_page_sga                                       TRUE
lock_sga                                           FALSE
allow_group_access_to_sga                          FALSE
sga_target                                         4294967296
unified_audit_sga_queue_size                       1048576

NAME VALUE

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

sga_max_size 4294967296

pre_page_sga TRUE

lock_sga FALSE

allow_group_access_to_sga FALSE

sga_target 4294967296

unified_audit_sga_queue_size 1048576

select &sga/1024/1024/1024 GB from dual; 
Enter value for sga: 4294967296

        GB
----------
         4

select &sga/1024/1024/1024 GB from dual;

Enter value for sga: 4294967296

----------

Button Name : SGA_TARGET

col name for a50 
set linesize 300 
col value for a40  
select name,value from v$parameter where name like '%sga_target%'; 
select &sga_target/1024/1024/1024 GB from dual;

col name for a50

set linesize 300

col value for a40

select name,value from v$parameter where name like '%sga_target%';

select &sga_target/1024/1024/1024 GB from dual;

Output :

NAME                                               VALUE
-------------------------------------------------- ----------------------------------------
sga_target                                         4294967296

select &sga_target/1024/1024/1024 GB from dual; 
Enter value for sga_target: 4294967296

        GB
----------
         4

NAME VALUE

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

sga_target 4294967296

select &sga_target/1024/1024/1024 GB from dual;

Enter value for sga_target: 4294967296

----------

Button Name : PGA_Aggregate_target

SQL>   col name for a50 
SQL> set linesize 300 
SQL> col value for a40  
select name,value from v$parameter where name like '%pga_aggregate_target%'; 
select &pga_aggregate_target/1024/1024/1024 GB from dual;

SQL> col name for a50

SQL> set linesize 300

SQL> col value for a40

select name,value from v$parameter where name like '%pga_aggregate_target%';

select &pga_aggregate_target/1024/1024/1024 GB from dual;

Output :

SQL> select &pga_aggregate_target/1024/1024/1024 GB from dual; 

NAME                                               VALUE
-------------------------------------------------- ----------------------------------------
pga_aggregate_target                               2147483648

SQL> select &pga_aggregate_target/1024/1024/1024 GB from dual;

NAME VALUE

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

pga_aggregate_target 2147483648

SQL> Enter value for pga_aggregate_target: 2147483648

        GB
----------
         2

SQL> Enter value for pga_aggregate_target: 2147483648

----------

Button Name :… Read More

Oracle : Important Commands for Dataguard (SecureCRT)

4 years ago by Vinod SiramSecure CRT

Dear Readers, In this article, we will see the Oracle : Important Commands for Dataguard (SecureCRT) Menubar Name : Dataguard Tab Name : Instance_info

set linesize 300 
col machine for a60 
select name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role from v$database, v$instance; 

NAME||'-'||INSTANCE_NAME||'@'||HOST_NAME||'-'||DB_UNIQUE_NAME||'-'||VERSION||'-'||OPEN_MODE||'-'||TO_CHAR(STARTUP_TIME,'DD-MON-YYYYHH24:MI:SS')||'-'||DATABASE_ROLE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORCLTEST - ORCLTEST1@SERVER1234.sephoraus.com - MSTORCLTEST - 12.1.0.2.0 - READ ONLY WITH APPLY - 04-FEB-2021 00:59:59 - PHYSICAL STANDBY

set linesize 300

col machine for a60

select name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role from v$database, v$instance;

NAME||'-'||INSTANCE_NAME||'@'||HOST_NAME||'-'||DB_UNIQUE_NAME||'-'||VERSION||'-'||OPEN_MODE||'-'||TO_CHAR(STARTUP_TIME,'DD-MON-YYYYHH24:MI:SS')||'-'||DATABASE_ROLE

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

ORCLTEST - ORCLTEST1@SERVER1234.sephoraus.com - MSTORCLTEST - 12.1.0.2.0 - READ ONLY WITH APPLY - 04-FEB-2021 00:59:59 - PHYSICAL STANDBY

Tab Name : DG_GAP

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", 
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" 
FROM 
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)  
FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, 
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)  
FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL 
WHERE 
ARCH.THREAD# = APPL.THREAD# 
ORDER BY 1; 

select thread#,applied,max(sequence#) from gv$archived_log where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#,applied order by thread#,applied; 

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                  44791                 44791          0
         2                  42354                 42354          0
         3                  40223                 40223          0

         

SQL> 
   THREAD# APPLIED   MAX(SEQUENCE#)
---------- --------- --------------
         1 IN-MEMORY          44791
         1 NO                 39800
         1 YES                44790
         2 NO                 38782
         2 YES                42354
         3 NO                 38673
         3 YES                40223

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",

(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)

FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)

FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE

ARCH.THREAD# = APPL.THREAD#

ORDER BY 1;

select thread#,applied,max(sequence#) from gv$archived_log where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#,applied order by thread#,applied;

Thread Last Sequence Received Last Sequence Applied Difference

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

1 44791 44791 0

2 42354 42354 0

3 40223 40223 0

SQL>

THREAD# APPLIED MAX(SEQUENCE#)

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

1 IN-MEMORY 44791

1 NO 39800

1 YES 44790

2 NO 38782

2 YES 42354

3 NO 38673

3 YES 40223

Tab Name : ERR_DEST

set linesize 300 
col dest_name for a30 
col error for a70 
select dest_id,dest_name,status,error from v$archive_dest where dest_id in (1,2,3,4); 
select inst_id,dest_name,status,error from gv$archive_dest where dest_id in (1,2,3,4) and status='VALID' order by 1; 

   DEST_ID DEST_NAME                      STATUS    ERROR
---------- ------------------------------ --------- --------------
         1 LOG_ARCHIVE_DEST_1             VALID
         2 LOG_ARCHIVE_DEST_2             INACTIVE
         3 LOG_ARCHIVE_DEST_3             INACTIVE
         4 LOG_ARCHIVE_DEST_4             DEFERRED

SQL> 
   INST_ID DEST_NAME                      STATUS    ERROR
---------- ------------------------------ --------- --------------
         1 LOG_ARCHIVE_DEST_1             VALID

set linesize 300

col dest_name for a30

col error for a70

select dest_id,dest_name,status,error from v$archive_dest where dest_id in (1,2,3,4);

select inst_id,dest_name,status,error from gv$archive_dest where dest_id in (1,2,3,4) and status='VALID' order by 1;

DEST_ID DEST_NAME STATUS ERROR

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

1 LOG_ARCHIVE_DEST_1 VALID

2 LOG_ARCHIVE_DEST_2 INACTIVE

3 LOG_ARCHIVE_DEST_3 INACTIVE

4 LOG_ARCHIVE_DEST_4 DEFERRED

SQL>

INST_ID DEST_NAME STATUS ERROR

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

1 LOG_ARCHIVE_DEST_1 VALID

Tab Name : Date Format

alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';

1	alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';

Tab Name :… Read More

Oracle : Important Queries for Parallel Feature (SecureCRT)

4 years ago by Vinod SiramSecure CRT

Dear Readers, In this article, we will see the Oracle : Important Queries for Parallel Feature (SecureCRT). Menubar Name : Parallel Tab Name : Create table show parameter parallel_max_servers

create table owner.tablename_bkp as select /*+ parallel(tablename,64) */ * from owner.tablename ;

1	create table owner.tablename_bkp as select /+ parallel(tablename,64) / * from owner.tablename ;

create table vinod.tablename_bkp as select /*+ parallel(EMP,4) */ * from SCOTT.EMP;

1	create table vinod.tablename_bkp as select /+ parallel(EMP,4) / * from SCOTT.EMP;

Tab Name : Finding_Parallel_Queries

set linesize 300  
col username for a19  
col terminal for a15  
col osuser for a15  
col program for a45 
col time for a25

set linesize 300

col username for a19

col terminal for a15

col osuser for a15

col program for a45

col time for a25

select inst_id,sid,sql_id,program,username,to_char(LOGON_TIME,'DD-MON-YYYY HH24:MI:SS') time,TERMINAL,OSUSER   
from gv$session where program like '%P0%' and sql_id is not null;   

   INST_ID        SID SQL_ID        PROGRAM                                       USERNAME            TIME                      TERMINAL        OSUSER
---------- ---------- ------------- --------------------------------------------- ------------------- ------------------------- --------------- ---------------
         1         58 29d991q9rq5kn oracle@awsserver003 (P000)                    SYS                 27-FEB-2021 21:08:38      pts/1           oracle
         1         59 29d991q9rq5kn oracle@awsserver003 (P001)                    SYS                 27-FEB-2021 21:08:38      pts/1           oracle
         2         37 29d991q9rq5kn oracle@awsserver004 (P001)                    SYS                 27-FEB-2021 21:08:38      pts/1           oracle
         2        207 29d991q9rq5kn oracle@awsserver004 (P000)                    SYS                 27-FEB-2021 21:08:38      pts/1           oracle

select inst_id,sid,sql_id,program,username,to_char(LOGON_TIME,'DD-MON-YYYY HH24:MI:SS') time,TERMINAL,OSUSER

from gv$session where program like '%P0%' and sql_id is not null;

INST_ID SID SQL_ID PROGRAM USERNAME TIME TERMINAL OSUSER

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

1 58 29d991q9rq5kn oracle@awsserver003 (P000) SYS 27-FEB-2021 21:08:38 pts/1 oracle

1 59 29d991q9rq5kn oracle@awsserver003 (P001) SYS 27-FEB-2021 21:08:38 pts/1 oracle

2 37 29d991q9rq5kn oracle@awsserver004 (P001) SYS 27-FEB-2021 21:08:38 pts/1 oracle

2 207 29d991q9rq5kn oracle@awsserver004 (P000) SYS 27-FEB-2021 21:08:38 pts/1 oracle

Tab Name : Select Query with Parallel Option… Read More

Oracle: Important commands for Lock/Unlock Stats and Backup/Restore Old Stats (SecureCRT)

4 years ago by Vinod SiramSecure CRT

Dear Readers, In this article, we will see the Oracle: Important commands for Lock/Unlock Stats and Backup/Restore Old Stats (SecureCRT). MenuBar Name : Lock/Unlock Stats and Backup/Restore Old Stats Tab Name : Check Stats for Table

--example : 'DEPT','EMP','SALGRADE' 
set linesize 300 
set pagesize 100  
col owner for a15  
col LAST_ANALYZED for a30 
col table_name for a40 
select OWNER,TABLE_NAME ,  NUM_ROWS , BLOCKS , EMPTY_BLOCKS , 
CHAIN_CNT, AVG_ROW_LEN 
CACHE, to_char(LAST_ANALYZED,'DD-MON-YYYY HH24:MI:SS') LAST_ANALYZED 
from dba_tables where table_name in (&Selected_Tables_Check_Example) and owner='&OWNER'; 

Enter value for selected_tables_check_example: 'TEST'
Enter value for owner: VINOD
old   4: from dba_tables where table_name in (&Selected_Tables_Check_Example) and owner='&OWNER'
new   4: from dba_tables where table_name in ('TEST') and owner='VINOD'

OWNER           TABLE_NAME                                 NUM_ROWS     BLOCKS EMPTY_BLOCKS  CHAIN_CNT      CACHE LAST_ANALYZED
--------------- ---------------------------------------- ---------- ---------- ------------ ---------- ---------- ------------------------------
VINOD           TEST                                              2          5            0          0          3 18-FEB-2021 22:00:21

--example : 'DEPT','EMP','SALGRADE'

set linesize 300

set pagesize 100

col owner for a15

col LAST_ANALYZED for a30

col table_name for a40

select OWNER,TABLE_NAME , NUM_ROWS , BLOCKS , EMPTY_BLOCKS ,

CHAIN_CNT, AVG_ROW_LEN

CACHE, to_char(LAST_ANALYZED,'DD-MON-YYYY HH24:MI:SS') LAST_ANALYZED

from dba_tables where table_name in (&Selected_Tables_Check_Example) and owner='&OWNER';

Enter value for selected_tables_check_example: 'TEST'

Enter value for owner: VINOD

old 4: from dba_tables where table_name in (&Selected_Tables_Check_Example) and owner='&OWNER'

new 4: from dba_tables where table_name in ('TEST') and owner='VINOD'

OWNER TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS CHAIN_CNT CACHE LAST_ANALYZED

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

VINOD TEST 2 5 0 0 3 18-FEB-2021 22:00:21

Tab Name : Backup Existing Stats

exec dbms_stats.create_stat_table ( ownname => 'VINOD',stattab => 'TEST_STAT');
exec dbms_stats.export_table_stats ( ownname => 'VINOD',stattab => 'TEST_STAT', tabname => 'TEST',statid => 'stats04252016');
select count(*) from vinod.TEST_STAT;

SQL> exec dbms_stats.create_stat_table ( ownname => 'VINOD',stattab => 'TEST_STAT');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.export_table_stats ( ownname => 'VINOD',stattab => 'TEST_STAT', tabname => 'TEST',statid => 'stats04252016');

PL/SQL procedure successfully completed.

SQL> select count(*) from vinod.TEST_STAT;

  COUNT(*)
----------
         2

exec dbms_stats.create_stat_table ( ownname => 'VINOD',stattab => 'TEST_STAT');

exec dbms_stats.export_table_stats ( ownname => 'VINOD',stattab => 'TEST_STAT', tabname => 'TEST',statid => 'stats04252016');

select count(*) from vinod.TEST_STAT;

SQL> exec dbms_stats.create_stat_table ( ownname => 'VINOD',stattab => 'TEST_STAT');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.export_table_stats ( ownname => 'VINOD',stattab => 'TEST_STAT', tabname => 'TEST',statid => 'stats04252016');

PL/SQL procedure successfully completed.

SQL> select count(*) from vinod.TEST_STAT;

COUNT(*)

----------

Tab… Read More

Oracle : Important Queries to check Table Fragmentation (SecureCRT)

4 years ago by Vinod SiramSecure CRT

Dear Readers, In this article, we will see the Oracle : Important Queries to check Table Fragmentation (SecureCRT). Menubar Name : Fragmentation Tab name : Fragmented_tablesList in HTML

col spoolname new_value spoolname 
select 'Fragmented_tables'||to_char(sysdate, 'YYYYMONDD')||'.html' spoolname from dual; 
spool '&spoolname' 
set pagesize 200 
set markup html on 
WITH STAT_SPACE AS( 
  SELECT OWNER, TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, NUM_ROWS*AVG_ROW_LEN/1024/1024/1024 tbl_STAT_SPACE 
  FROM   DBA_TABLES  
  WHERE  OWNER='SCOTT'), 
SEG_SPACE AS ( 
  SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BLOCKS, BYTES/1024/1024/1024 SEGSIZE_GB  
  FROM DBA_SEGMENTS  
  WHERE 1=1 
  AND OWNER='SCOTT' 
  AND SEGMENT_TYPE='TABLE') 
SELECT sesp.tablespace_name, StSp.TABLE_NAME,  sesp.blocks, StSp.NUM_ROWS, StSp.AVG_ROW_LEN, StSp.TBL_STAT_SPACE, sesp.SEGSIZE_GB, abs(STSP.TBL_STAT_SPACE - sesp.SEGSIZE_GB) claim_gb 
FROM   STAT_SPACE STSP, SEG_SPACE SESP 
WHERE  abs(STSP.TBL_STAT_SPACE - sesp.SEGSIZE_GB) > 1 
AND    STSP.TABLE_NAME = SESP.SEGMENT_NAME 
AND    STSP.OWNER = SESP.OWNER 
ORDER BY CLAIM_GB desc; 
spool off 
exit

col spoolname new_value spoolname

select 'Fragmented_tables'||to_char(sysdate, 'YYYYMONDD')||'.html' spoolname from dual;

spool '&spoolname'

set pagesize 200

set markup html on

WITH STAT_SPACE AS(

SELECT OWNER, TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, NUM_ROWS*AVG_ROW_LEN/1024/1024/1024 tbl_STAT_SPACE

FROM DBA_TABLES

WHERE OWNER='SCOTT'),

SEG_SPACE AS (

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BLOCKS, BYTES/1024/1024/1024 SEGSIZE_GB

FROM DBA_SEGMENTS

WHERE 1=1

AND OWNER='SCOTT'

AND SEGMENT_TYPE='TABLE')

SELECT sesp.tablespace_name, StSp.TABLE_NAME, sesp.blocks, StSp.NUM_ROWS, StSp.AVG_ROW_LEN, StSp.TBL_STAT_SPACE, sesp.SEGSIZE_GB, abs(STSP.TBL_STAT_SPACE - sesp.SEGSIZE_GB) claim_gb

FROM STAT_SPACE STSP, SEG_SPACE SESP

WHERE abs(STSP.TBL_STAT_SPACE - sesp.SEGSIZE_GB) > 1

AND STSP.TABLE_NAME = SESP.SEGMENT_NAME

AND STSP.OWNER = SESP.OWNER

ORDER BY CLAIM_GB desc;

spool off

exit

Output : Tab name : BLOCKS Size

set linesize 300 
col table_name for a30 
col "Blocks Size" for a30 
select table_name,round((blocks*8),2)||' KB' "Blocks Size",(round((blocks*8),2))/1024/1024 ||' GB' "Blocks Size" 
from dba_tables 
where owner='&OWNER' and table_name = upper('&table_name'); 


TABLE_NAME                     Blocks Size                    Blocks Size
------------------------------ ------------------------------ ------------------------------
EMPTEST123                     1231264 KB                     1.174224853515625 GB

set linesize 300

col table_name for a30

col "Blocks Size" for a30

select table_name,round((blocks*8),2)||' KB' "Blocks Size",(round((blocks*8),2))/1024/1024 ||' GB' "Blocks Size"

from dba_tables

where owner='&OWNER' and table_name = upper('&table_name');

TABLE_NAME Blocks Size Blocks Size

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

EMPTEST123 1231264 KB 1.174224853515625 GB

Tab name : Rows Size

set linesize 300 
select table_name,round((num_rows*avg_row_len/1024),2)||' KB' "Rows Size",(round((num_rows*avg_row_len/1024),2))/1024/1024||' GB' "Rows Size" 
from dba_tables 
where owner='&OWNER' and table_name = upper('&table_name'); 

TABLE_NAME                     Rows Size                                                                Rows Size
------------------------------ ------------------------------------------------------------------------ ----------------------------
EMPTEST123                     10597.63 KB                                                              .0101066875457763671875 GB

set linesize 300

select table_name,round((num_rows*avg_row_len/1024),2)||' KB' "Rows Size",(round((num_rows*avg_row_len/1024),2))/1024/1024||' GB' "Rows Size"

from dba_tables

where owner='&OWNER' and table_name = upper('&table_name');

TABLE_NAME Rows Size Rows Size

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

EMPTEST123 10597.63 KB .0101066875457763671875 GB

Tab name… Read More