DBA Blogs

Change all procedures in a schema

Tom Kyte - 12 hours 3 min ago
Is it possible to change a string to another string in all procedures in a schema? Use case: We have a function, say F_A. We want to place it within a package, say PG_B. Now we would like to change all calls to F_A in all procedures with PG_B.F...
Categories: DBA Blogs

Privilege required to execute a stored procedure

Tom Kyte - 12 hours 3 min ago
Hi, There is a procedure named 'Update_emp_details' in our database which actually update the employees table present in the same database. I needs to give execute permission for a another user to this procedure 'Update_emp_details'. Is giving...
Categories: DBA Blogs

Locking issues on Table A with FKs on table X while a procedure inserts records in table B with FKs on table X too

Tom Kyte - 12 hours 3 min ago
Hello, we use oracle 11.2.0.3 and following topic i would like to discuss here: Setup like this: Table A (partitioned) with an article column and FK defined to masterdata (article) keytable X Table B also with article column and FK define...
Categories: DBA Blogs

Spool file hide SQL statement

Tom Kyte - 12 hours 3 min ago
I would like to know what Statement makes it, that the the SQL Statement is not printet in the spool file? The column names still should be printed. I'm calling the script from the SQL developer. I have the following script: <code>set CO...
Categories: DBA Blogs

Unused indexes Identification

Tom Kyte - 12 hours 3 min ago
Hello Tom, i was looking for options available in oracle 12c by which i can find out Unused indexes in database. I have used v$object_usage. but it only gives me index is used? it does not display how many times it has been used. also, if i would ...
Categories: DBA Blogs

Grant Compile Packaged to Other User

Tom Kyte - 12 hours 3 min ago
<b>Hi, I have a package in schema A. I want schema B to be able to compile the code for just this package and not any other packages that schema A owns. Can this be achieved? </b>
Categories: DBA Blogs

Self full outer join with additional predicateds

Tom Kyte - 12 hours 3 min ago
Hi Tom, I was asked to create a query, counting the number of the columns with different names in 2 tables. Say in "Employees" and "Departments" we have DEPARTMENT_ID and MANAGER_ID columns with equal names, and the rest of the columns differs in na...
Categories: DBA Blogs

rouning inconsistency in oracle db

Tom Kyte - 12 hours 3 min ago
Hi Tom, We use the below rounding logic in our applications but unfortunately the first query was rounding incorrectly. This we can see only when this code was executed from the application. But when I execute it in sql*plus it giving me correct o...
Categories: DBA Blogs

Help Me Help You!

VitalSoftTech - 13 hours 24 min ago
Hello everyone, I hope the holidays went well for you, best of wishes from my family to yours! It is a new year and we all have new year resolutions! After years of creating blogs for you all, I have decided to turn it up a notch! As many of you already know, I am […]
Categories: DBA Blogs

Links for 2017-01-15 [del.icio.us]

Categories: DBA Blogs

12cR1 RAC Posts -- 3 : Convert PolicyManaged DB back to AdminManaged

Hemant K Chitale - Sun, 2017-01-15 20:35
In the previous post on 12cR1 RAC, I had converted my AdminManaged Database to PolicyManaged.

Here, I convert it back to AdminManaged.

First, I verify that the database is shutdown (note that I have only 1 node of the cluster currently up and running, I don't need both nodes and instances up).

[oracle@collabn1 ~]$ srvctl status database -d RAC
Instance RAC_1 is running on node collabn1
[oracle@collabn1 ~]$ srvctl stop database -d RAC
[oracle@collabn1 ~]$ ps -fuoracle |grep smon
oracle 3422 1 0 09:49 ? 00:00:00 asm_smon_+ASM1
oracle 4882 1 0 09:50 ? 00:00:00 mdb_smon_-MGMTDB
oracle 16889 9821 0 10:08 pts/0 00:00:00 grep smon
[oracle@collabn1 ~]$


Next, I remove the database from the Cluster Registry.

[oracle@collabn1 ~]$ srvctl config database -d RAC
Database unique name: RAC
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile:
Password file:
Domain: racattack
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: MyPool
Disk Groups: DATA,FRA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances:
Configured nodes:
Database is policy managed
[oracle@collabn1 ~]$ srvctl remove database -d RAC
Remove the database RAC? (y/[n]) y
[oracle@collabn1 ~]$ srvctl config database -d RAC
PRCD-1120 : The resource for database RAC could not be found.
PRCR-1001 : Resource ora.rac.db does not exist
[oracle@collabn1 ~]$


I then remove the defined Server Pool that I used for this database.

[oracle@collabn1 ~]$ srvctl config srvpool
Server pool name: Free
Importance: 0, Min: 0, Max: -1
Category:
Candidate server names:
Server pool name: Generic
Importance: 0, Min: 0, Max: -1
Category:
Candidate server names:
Server pool name: MyPool
Importance: 100, Min: 1, Max: 2
Category:
Candidate server names: collabn1,collabn2
[oracle@collabn1 ~]$ srvctl remove srvpool -serverpool MyPool
[oracle@collabn1 ~]$ srvctl config srvpool
Server pool name: Free
Importance: 0, Min: 0, Max: -1
Category:
Candidate server names:
Server pool name: Generic
Importance: 0, Min: 0, Max: -1
Category:
Candidate server names:
[oracle@collabn1 ~]$


I then add the database back into the Cluster Registry.

[oracle@collabn1 ~]$ srvctl add database -d RAC \
> -oraclehome /u01/app/oracle/product/12.1.0/dbhome_1 \
> -pwfile +DATA/RAC/PASSWORD/pwdrac.277.931824933
[oracle@collabn1 ~]$ srvctl config database -d RAC
Database unique name: RAC
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile:
Password file: +DATA/RAC/PASSWORD/pwdrac.277.931824933
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances:
Configured nodes:
Database is administrator managed
[oracle@collabn1 ~]$


I start the second node of the cluster before I configure the instances (Note : I have the $ORACLE_HOME/dbs pfiles created in advance).

[oracle@collabn1 ~]$ srvctl add instance -d RAC -i RAC1 -n collabn1
[oracle@collabn1 ~]$ srvctl add instance -d RAC -i RAC2 -n collabn2
[oracle@collabn1 ~]$ srvctl config database -d RAC
Database unique name: RAC
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile:
Password file: +DATA/RAC/PASSWORD/pwdrac.277.931824933
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances: RAC1,RAC2
Configured nodes: collabn1,collabn2
Database is administrator managed
[oracle@collabn1 ~]$


I am now ready to start the database (instances).

[oracle@collabn1 ~]$ srvctl start database -d RAC
[oracle@collabn1 ~]$ srvctl status database -d RAC
Instance RAC1 is running on node collabn1
Instance RAC2 is running on node collabn2
[oracle@collabn1 ~]$ ps -fuoracle |grep smon
oracle 3422 1 0 09:49 ? 00:00:00 asm_smon_+ASM1
oracle 4882 1 0 09:50 ? 00:00:00 mdb_smon_-MGMTDB
oracle 25431 1 0 10:30 ? 00:00:00 ora_smon_RAC1
oracle 27533 9821 0 10:33 pts/0 00:00:00 grep smon
[oracle@collabn1 ~]$
[root@collabn2 ~]# ps -fuoracle |grep smon
oracle 3460 1 0 10:19 ? 00:00:00 asm_smon_+ASM2
oracle 9561 1 0 10:30 ? 00:00:00 ora_smon_RAC2
[root@collabn2 ~]#
[oracle@collabn1 ~]$ env |grep SID
ORACLE_SID=RAC1
[oracle@collabn1 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 16 10:34:15 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select * from v$containers;

CON_ID DBID CON_UID GUID
---------- ---------- ---------- --------------------------------
NAME OPEN_MODE RES
------------------------------ ---------- ---
OPEN_TIME
---------------------------------------------------------------------------
CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID
---------- ---------- ---------- -------- ----------------------
1 2519807290 1 FD9AC20F64D344D7E043B6A9E80A2F2F
CDB$ROOT READ WRITE NO
16-JAN-17 10.31.34.014 AM +08:00
0 0 8192 ENABLED 0


CON_ID DBID CON_UID GUID
---------- ---------- ---------- --------------------------------
NAME OPEN_MODE RES
------------------------------ ---------- ---
OPEN_TIME
---------------------------------------------------------------------------
CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID
---------- ---------- ---------- -------- ----------------------
2 2061548092 2061548092 44BB5E17F41A2618E053334EA8C006B9
PDB$SEED READ ONLY NO
16-JAN-17 10.31.34.859 AM +08:00
1594413 859832320 8192 ENABLED 0


CON_ID DBID CON_UID GUID
---------- ---------- ---------- --------------------------------
NAME OPEN_MODE RES
------------------------------ ---------- ---
OPEN_TIME
---------------------------------------------------------------------------
CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID
---------- ---------- ---------- -------- ----------------------
3 1857084550 1857084550 44BBC69CE8F552AEE053334EA8C07365
PDB MOUNTED

1755977 0 8192 ENABLED 0


SQL> alter pluggable database PDB open;

Pluggable database altered.

SQL>
SQL> select con_id, name, open_mode from v$pdbs;

CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 PDB READ WRITE

SQL>


Thus, I converted my PolicyManaged database to AdministratorManaged.
.
.
.

Categories: DBA Blogs

Procedure accepting more than 32K text as a parameter and inserting to CLOB coumn

Tom Kyte - Sun, 2017-01-15 16:46
Hi Team, We have one requirement where: 1) we have to write one proceudre which will insert into a clob column meant for email body. This procedure should accept text more than 32K. 2) the application or routine will execute this procedure by p...
Categories: DBA Blogs

Space required in UNDO tablespace for adding a new column with Default value not null.

Tom Kyte - Sun, 2017-01-15 16:46
Hi Tom, Recently we needed to add a new field in a table with a default value and a not null constraint, since the table was 45 million rows and 7GB, we ran out of space in UNDO tablespace. In order to avoid the issue in the future, we did some...
Categories: DBA Blogs

Hybridized Database after an upgrade of a retail Point of Sale application.

Tom Kyte - Sun, 2017-01-15 16:46
Background: Point of Sale product running on retail stores. Each store has a store primary database and there is a centralized database. The data from the stores get replicated real time to the central Database. The Front end application and the DB i...
Categories: DBA Blogs

Migrate a Oracle Database from a client server to an internal server through an intermediate server like EFT

Tom Kyte - Sun, 2017-01-15 16:46
Hello. I need to copy a customer database from their server to my server. I will get a copy using the Oracle Data Pump. But the client network is different that my network, so I need to use an intermediate server like an EFT. How can I do this? Thank...
Categories: DBA Blogs

Connnect by level

Tom Kyte - Sun, 2017-01-15 16:46
HI TOM, I need a small short and brief explanation of the below query. create table t as select dbms_random.string('a', 10) y from dual connect by level <= 10; ---Here can i know what does "connect by level" can you explain a bit ...
Categories: DBA Blogs

Oracle Index Internals

Tom Kyte - Sun, 2017-01-15 16:46
Team, Generally talking about the Indexes, it is considered as an object which assists in fetching the data faster or quickly. But i would like to know the internals of index, like how index is stored and index leaf node,branch node and index r...
Categories: DBA Blogs

Kill Oracle Session when closing client on browser (Forms 11g)

Tom Kyte - Sun, 2017-01-15 16:46
Hey guys, I am facing a problem, at the company I work for, we've noticed there were >60 active sessions without users "using" it. Then, we've noticed it was being caused because of the way the user was closing the application. The user was clic...
Categories: DBA Blogs

Best way to change the datatype of a column in a Table with 20 million

Tom Kyte - Sun, 2017-01-15 16:46
Hi Team, Wish you a happy new Year. Welcome to Maria Colgan. I have a requirement to change the datatype of a column of number type to Varchar2(35) and then rename the table. The table has huge data around 20 millions. Since already data is the...
Categories: DBA Blogs

Copying a Tablespace from NonCDB to a PDB (using TTS)

Hemant K Chitale - Sun, 2017-01-15 02:54
A Tablespace can be "transported"  from a NonCDB to a PDB as a way of copying the Tablespace.  Here I work with ASM as well.

First in the NonCDB :

[oracle@ora12102 Desktop]$ . oraenv
ORACLE_SID = [oracle] ? NONCDB
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora12102 Desktop]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:03:43 2017

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 402653184 bytes
Fixed Size 2924928 bytes
Variable Size 260050560 bytes
Database Buffers 134217728 bytes
Redo Buffers 5459968 bytes
Database mounted.
Database opened.
SQL> select file_name, bytes/1048576
2 from dba_data_files
3 where tablespace_name = 'EXAMPLE'
4 /

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1048576
-------------
+DATA/NONCDB/DATAFILE/example.266.896482777
1243.75


SQL>
[oracle@ora12102 Desktop]$ expdp hemant/hemant \
> directory=data_pump_dir dumpfile=EXAMPLE_TTS.dmp \
> transport_tablespaces=EXAMPLE transport_full_check=Y

Export: Release 12.1.0.2.0 - Production on Sun Jan 15 16:08:27 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "HEMANT"."SYS_EXPORT_TRANSPORTABLE_01": hemant/******** directory=data_pump_dir dumpfile=EXAMPLE_TTS.dmp transport_tablespaces=EXAMPLE transport_full_check=Y
ORA-39123: Data Pump transportable tablespace job aborted
ORA-39185: The transportable tablespace failure list is

ORA-29335: tablespace 'EXAMPLE' is not read only
Job "HEMANT"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at Sun Jan 15 16:08:41 2017 elapsed 0 00:00:06

[oracle@ora12102 Desktop]$


The tablespace has to be set READ ONLY before we can use export to transport it (also, it should be READ ONLY while the data files are being copied.

[oracle@ora12102 Desktop]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:09:10 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> alter tablespace example read only;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$ expdp hemant/hemant \
> directory=data_pump_dir dumpfile=EXAMPLE_TTS.dmp \
> transport_tablespaces=EXAMPLE transport_full_check=Y

Export: Release 12.1.0.2.0 - Production on Sun Jan 15 16:09:58 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "HEMANT"."SYS_EXPORT_TRANSPORTABLE_01": hemant/******** directory=data_pump_dir dumpfile=EXAMPLE_TTS.dmp transport_tablespaces=EXAMPLE transport_full_check=Y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Processing object type TRANSPORTABLE_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/XMLSCHEMA/XMLSCHEMA
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX/BITMAP_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/TABLE
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/INDEX
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "HEMANT"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HEMANT.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/app/oracle/admin/NONCDB/dpdump/EXAMPLE_TTS.dmp
******************************************************************************
Datafiles required for transportable tablespace EXAMPLE:
+DATA/NONCDB/DATAFILE/example.266.896482777
Job "HEMANT"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Sun Jan 15 16:12:48 2017 elapsed 0 00:02:46

[oracle@ora12102 Desktop]$


Now, I need to copy the datafile (while the tablespace is READ ONLY).

[oracle@ora12102 Desktop]$ su - grid
Password:
[grid@ora12102 ~]$ asmcmd
ASMCMD> cp +DATA/NONCDB/DATAFILE/example.266.896482777 /tmp/example.dbf
copying +DATA/NONCDB/DATAFILE/example.266.896482777 -> /tmp/example.dbf
ASMCMD>
ASMCMD> exit
[grid@ora12102 ~]$ exit
logout
[oracle@ora12102 Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:16:24 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> alter tablespace example read write ;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$


I must now identify the target location for the datafile in the CDB database.

[oracle@ora12102 Desktop]$ . oraenv
ORACLE_SID = [NONCDB] ? CDB1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora12102 Desktop]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:17:44 2017

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size 2925024 bytes
Variable Size 973082144 bytes
Database Buffers 654311424 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL>
SQL> alter session set container=PDB1;

Session altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/system.284.914408541
+DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/sysaux.285.914408541
+DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/users.287.914408663
+DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/hemant.288.914713623

SQL>


Now that I have identiied the default location for all PDB1 files, I need to use ASMCMD to copy the datafile.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$ su - grid
Password:
[grid@ora12102 ~]$
[grid@ora12102 ~]$ asmcmd
ASMCMD> cp /tmp/example.dbf +DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/
copying /tmp/example.dbf -> +DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/example.dbf
ASMCMD> cd +DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE
ASMCMD> ls
HEMANT.288.914713623
SYSAUX.285.914408541
SYSTEM.284.914408541
USERS.287.914408663
example.dbf
ASMCMD> exit
[grid@ora12102 ~]$


Now, I need to import the tablespace with the datafile. Before that, I need to setup the user that will do the import and all the users of the target tablespace.

[grid@ora12102 ~]$ exit
logout
[oracle@ora12102 Desktop]$
[oracle@ora12102 Desktop]$ sqlplus system/oracle@PDB1

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:28:16 2017

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

ERROR:
ORA-28002: the password will expire within 7 days


Last Successful login time: Sun Jan 15 2017 16:27:18 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> grant select_catalog_role, imp_full_database to hemant;

Grant succeeded.

SQL> select privilege from dba_sys_privs where grantee = 'HEMANT';

PRIVILEGE
----------------------------------------
CREATE TABLE
CREATE SESSION

SQL> select granted_role from dba_role_privs where grantee = 'HEMANT';

GRANTED_ROLE
--------------------------------------------------------------------------------
SELECT_CATALOG_ROLE
IMP_FULL_DATABASE

SQL>


As with the Export, I am using a non-DBA user for the import.  I also have to setup the users and their grants.

[oracle@ora12102 Desktop]$ sqlplus sys/oracle@PDB1 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:33:17 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> create directory imp_from_noncdb as '/u01/app/oracle/admin/NONCDB/dpdump';

Directory created.

SQL> grant read, write on directory imp_from_noncdb to hemant;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$
[oracle@ora12102 Desktop]$ sqlplus system/oracle@PDB1

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:41:36 2017

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

ERROR:
ORA-28002: the password will expire within 7 days


Last Successful login time: Sun Jan 15 2017 16:28:16 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> create user HR identified by HR ;
create user IX identified by IX ;
create user OE identified by OE ;
create user PM identified by PM ;
create user SH identified by SH ;

User created.

SQL>
User created.

SQL>
User created.

SQL>
User created.

SQL>
User created.

SQL> SQL>
SQL> @grants_to_EXAMPLE
SQL> spool grants_to_EXAMPLE
SQL>
SQL> grant ALTER SESSION to HR ;

Grant succeeded.

SQL> grant ALTER SESSION to IX ;

Grant succeeded.

SQL> grant ALTER SESSION to SH ;

Grant succeeded.

SQL> grant CREATE CLUSTER to IX ;

Grant succeeded.

SQL> grant CREATE CLUSTER to SH ;

Grant succeeded.

SQL> grant CREATE DATABASE LINK to HR ;

Grant succeeded.

SQL> grant CREATE DATABASE LINK to IX ;

Grant succeeded.

SQL> grant CREATE DATABASE LINK to OE ;

Grant succeeded.

SQL> grant CREATE DATABASE LINK to SH ;

Grant succeeded.

SQL> grant CREATE DIMENSION to SH ;

Grant succeeded.

SQL> grant CREATE INDEXTYPE to IX ;

Grant succeeded.

SQL> grant CREATE MATERIALIZED VIEW to OE ;

Grant succeeded.

SQL> grant CREATE MATERIALIZED VIEW to SH ;

Grant succeeded.

SQL> grant CREATE OPERATOR to IX ;

Grant succeeded.

SQL> grant CREATE PROCEDURE to HR ;

Grant succeeded.

SQL> grant CREATE PROCEDURE to IX ;

Grant succeeded.

SQL> grant CREATE RULE to IX ;

Grant succeeded.

SQL> grant CREATE RULE SET to IX ;

Grant succeeded.

SQL> grant CREATE SEQUENCE to HR ;

Grant succeeded.

SQL> grant CREATE SEQUENCE to IX ;

Grant succeeded.

SQL> grant CREATE SEQUENCE to SH ;

Grant succeeded.

SQL> grant CREATE SESSION to HR ;

Grant succeeded.

SQL> grant CREATE SESSION to IX ;

Grant succeeded.

SQL> grant CREATE SESSION to OE ;

Grant succeeded.

SQL> grant CREATE SESSION to SH ;

Grant succeeded.

SQL> grant CREATE SYNONYM to HR ;

Grant succeeded.

SQL> grant CREATE SYNONYM to IX ;

Grant succeeded.

SQL> grant CREATE SYNONYM to OE ;

Grant succeeded.

SQL> grant CREATE SYNONYM to SH ;

Grant succeeded.

SQL> grant CREATE TABLE to IX ;

Grant succeeded.

SQL> grant CREATE TABLE to SH ;

Grant succeeded.

SQL> grant CREATE TRIGGER to IX ;

Grant succeeded.

SQL> grant CREATE TYPE to IX ;

Grant succeeded.

SQL> grant CREATE VIEW to HR ;

Grant succeeded.

SQL> grant CREATE VIEW to IX ;

Grant succeeded.

SQL> grant CREATE VIEW to OE ;

Grant succeeded.

SQL> grant CREATE VIEW to SH ;

Grant succeeded.

SQL> grant QUERY REWRITE to OE ;

Grant succeeded.

SQL> grant QUERY REWRITE to SH ;

Grant succeeded.

SQL> grant SELECT ANY DICTIONARY to IX ;

Grant succeeded.

SQL> grant UNLIMITED TABLESPACE to HR ;

Grant succeeded.

SQL> grant UNLIMITED TABLESPACE to IX ;

Grant succeeded.

SQL> grant UNLIMITED TABLESPACE to OE ;

Grant succeeded.

SQL> grant UNLIMITED TABLESPACE to PM ;

Grant succeeded.

SQL> grant UNLIMITED TABLESPACE to SH ;

Grant succeeded.

SQL>
SQL> spool off
SQL> @roles_to_EXAMPLE
SQL> set echo on
SQL> spool roles_to_EXAMPLE
SQL>
SQL> grant AQ_ADMINISTRATOR_ROLE to IX ;

Grant succeeded.

SQL> grant AQ_USER_ROLE to IX ;

Grant succeeded.

SQL> grant CONNECT to IX ;

Grant succeeded.

SQL> grant CONNECT to PM ;

Grant succeeded.

SQL> grant RESOURCE to HR ;

Grant succeeded.

SQL> grant RESOURCE to IX ;

Grant succeeded.

SQL> grant RESOURCE to OE ;

Grant succeeded.

SQL> grant RESOURCE to PM ;

Grant succeeded.

SQL> grant RESOURCE to SH ;

Grant succeeded.

SQL> grant SELECT_CATALOG_ROLE to IX ;

Grant succeeded.

SQL> grant SELECT_CATALOG_ROLE to SH ;

Grant succeeded.

SQL> grant XDBADMIN to OE ;

Grant succeeded.

SQL>
SQL> spool off
SQL>


I am now ready to import the tablespace and datafile.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$ impdp hemant/hemant@PDB1 \
> dumpfile=EXAMPLE_TTS.dmp directory=imp_from_noncdb \
> transport_datafiles=+DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/example.dbf

Import: Release 12.1.0.2.0 - Production on Sun Jan 15 16:50:16 2017

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

UDI-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Master table "HEMANT"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Source time zone is +00:00 and target time zone is -07:00.
Starting "HEMANT"."SYS_IMPORT_TRANSPORTABLE_01": hemant/********@PDB1 dumpfile=EXAMPLE_TTS.dmp directory=imp_from_noncdb transport_datafiles=+DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/example.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Processing object type TRANSPORTABLE_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/XMLSCHEMA/XMLSCHEMA
Processing object type TRANSPORTABLE_EXPORT/TABLE
ORA-39360: Table "OE"."ORDERS" was skipped due to transportable import and TSLTZ issues resulting from time zone mismatch.
ORA-39151: Table "OE"."PURCHASEORDER" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."PROMOTIONS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."PRODUCT_DESCRIPTIONS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."PRODUCT_INFORMATION" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."INVENTORIES" TO "BI"
ORA-39112: Dependent object type OBJECT_GRANT:"OE" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"OE" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."ORDER_ITEMS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."WAREHOUSES" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."CUSTOMERS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."COSTS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."SALES" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."COUNTRIES" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."CUSTOMERS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."PROMOTIONS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."CHANNELS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."PRODUCTS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."TIMES" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."FWEEK_PSCAT_SALES_MV" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."CAL_MONTH_SALES_MV" TO "BI"
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
ORA-39112: Dependent object type INDEX:"OE"."ORD_SALES_REP_IX" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type INDEX:"OE"."ORD_ORDER_DATE_IX" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type INDEX:"OE"."ORD_CUSTOMER_IX" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type INDEX:"OE"."ORDER_PK" skipped, base object type TABLE:"OE"."ORDERS" creation failed
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:"OE"."ORDER_MODE_LOV" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type CONSTRAINT:"OE"."ORDER_TOTAL_MIN" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type CONSTRAINT:"OE"."ORDER_PK" skipped, base object type TABLE:"OE"."ORDERS" creation failed
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT:"OE"."ORDER_ITEMS_ORDER_ID_FK" failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "OE"."ORDER_ITEMS" ADD CONSTRAINT "ORDER_ITEMS_ORDER_ID_FK" FOREIGN KEY ("ORDER_ID") REFERENCES "OE"."ORDERS" ("ORDER_ID") ON DELETE CASCADE ENABLE NOVALIDATE
ORA-39112: Dependent object type REF_CONSTRAINT:"OE"."ORDERS_SALES_REP_FK" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type REF_CONSTRAINT:"OE"."ORDERS_CUSTOMER_ID_FK" skipped, base object type TABLE:"OE"."ORDERS" creation failed
Processing object type TRANSPORTABLE_EXPORT/INDEX/BITMAP_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/TABLE
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/INDEX
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
ORA-39082: Object type TRIGGER:"HR"."SECURE_EMPLOYEES" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR"."UPDATE_JOB_HISTORY" created with compilation warnings
Job "HEMANT"."SYS_IMPORT_TRANSPORTABLE_01" completed with 41 error(s) at Sun Jan 15 16:51:40 2017 elapsed 0 00:01:23

[oracle@ora12102 Desktop]$


The key error is the failure on the ORDERS table creation because of a TimeZone mismatch !   So, there is a lesson to be learnt !

.
.
.

Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs