KTEXPERTS – Page 12 – KNOWLEDGE SHARING PLATFORM

Meetup (Get Together)

2 years ago by KTEXPERTS TEAMUncategorized

Hi Guys, Recently we had a very good time in friendly Get Together. Looking forward to many more to come…! Chandler, Arizon Hyderabad

Python – Datatypes 👇👇👇👇 Please check the below link for the full document 👇👇👇👇 https://www.ktexperts.com/wp-content/uploads/2022/12/datatypes.pdf Author : Teja LinkedIn : https://www.linkedin.com/in/teja-sai-nadh-reddy-tatireddy-048882201 Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates KTExperts is… Read More

PYTHON INTRODUCTION

2 years ago by TejaPython

PYTHON INTRODUCTION WHAT IS PYTHON Python is a high-level, interpreted, interactive and object-oriented scripting language developed by Guido Van Rossum. Python is a powerful, flexible and easy to use scripting language. FEATURES OF PYTHON: Free & Open Source Python language is freely available at the… Read More

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

CLONE ORACLE 12c PLUGGABLE DATABASE INTO ANOTHER CDB

2 years ago by Shoaib AnsariOracle DB Admin

CLONE ORACLE 12c PLUGGABLE DATABASE INTO ANOTHER CDB In this article we will learn how to clone oracle 12c pluggable database (SHOPDB1) from one container database (SHOCDB1) to another container database (SHOCDB2), using the DBLINK The 3 main steps that we will do to clone… Read More

CLONE ORACLE 12c PLUGGABLE DATABASE IN SAME CDB

2 years ago by Shoaib AnsariOracle DB Admin

CLONE ORACLE 12c PLUGGABLE DATABASE IN SAME CDB In This article we will learn how to clone Oracle 12c pluggable database [SHOPDB2] from another oracle 12c pluggable database [SHOPDB1] in the same container database [SHOCDB1] using FILE_NAME_CONVERT parameter. 👇👇👇👇 Please check the below link… Read More

CREATE ORACLE 12c PLUGGABLE DATABASE MANUALLY

2 years ago by Shoaib AnsariOracle DB Admin

CREATE ORACLE 12c PLUGGABLE DATABASE MANUALLY In this article, we will learn how to create oracle 12c pluggable database (SHOPDB1) from seed pluggable database (PDB$SEED) in the same container database (SHOCDB1) using the manual command 👇👇👇👇 Please check the below link for the… Read More

CREATE & DROP ORACLE 12c PLUGGABLE DATABASE WITH DBCA

2 years ago by Shoaib AnsariOracle DB Admin

CREATE & DROP ORACLE 12c PLUGGABLE DATABASE WITH DBCA In this article, we will learn how to create and drop oracle 12c pluggable database PDB (SHOPDB1) from seed pluggable database PDB (PDB$SEED) in the same container database (SHOCDB1) with oracle database configuration assistant… Read More