pdb queries

set lines 333
col tablespace_name for a30
col contents for a25
col status for a15
col con_name for a20
select c.con_id, c.name  con_name, t.tablespace_name, t.contents, t.status from v$containers c, cdb_tablespaces t
where c.con_id=t.con_id order by 1,2;

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

set lines 400
col con_name for a20
col username for a25
col account_status for a28
-- col LOCK_DATE
-- col EXPIRY_DATE
-- col LAST_LOGIN
col DEFAULT_TABLESPACE for a25
col TEMPORARY_TABLESPACE for a15
--col LOCAL_TEMP_TABLESPACE for a15
col PROFILE for a25
select c.con_id, c.name  con_name, u.username,u.account_status,u.common,u.default_tablespace,u.temporary_tablespace,u.profile from v$containers c, cdb_users u
where c.con_id=u.con_id order by 1,2;

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

set lines 400
col file_name for a66
col TABLESPACE_NAME for a22
col con_name for a20
col STATUS for a10
select c.con_id, c.name  con_name,t.file_name,t.tablespace_name,t.bytes/1024/1024 "size",t.status from  v$containers c, cdb_data_files t
where c.con_id=t.con_id order by 1,2;
===============================================
In expdp/impdp also you have remap_schema for the same purpose, but you dont need to create the target schema there.
IMPDP itself creates target schema for you but the schema will be locked and you have to set the password before you start using it.


create ts in OMF

CREATE TABLESPACE APPS_DATA01 DATAFILE SIZE 150M;


create user devuser identified by devuser default tablespace APPS_DATA01;
create user hr2 identified by devuser default tablespace APPS_DATA01;

alter session set container=orclapdb;

conn devuser/devuser@orclapdb

conn hr/hr@orclapdb

conn c##mahidba/mahidba@orclapdb



You may need to re-add object grants to the new martin schema eg
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SCOTT') FROM DUAL
before you remove the SCOTT schema of course.
========================================================================================

taking backup of entire hr schema and import into same database but into different schema
devuser with different default tablespace


expdp c##mahidba/mahidba@orclapdb directory=dump schemas=hr dumpfile=hr.dmp

impdp c##mahidba/mahidba@orclapdb directory=dump remap_schema=hr:devuser dumpfile=hr.dmp remap_tablespace=users:APPS_DATA01

========================================================================================


taking backup of few tables from hr schema and import into same database but into different schema
hr_new (new schenma that will create as part of impdp)with different default tablespace


expdp c##mahidba/mahidba@orclapdb directory=dump tables=hr.region,hr.countries,hr.jobs dumpfile=hr1.dmp

impdp c##mahidba/mahidba@orclapdb directory=dump remap_schema=hr:hr_new dumpfile=hr1.dmp remap_tablespace=users:APPS_DATA01

above output throws error

ORA-01918: user 'HR_NEW' does not exist


as druing the expdp execution it does not do the following


Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE


ie  the users & grants were not exported in the first place, and so were not created when importing.

so create user and then import


create user hr_new identified by hr_new default tablespace APPS_DATA01;

grant dba to hr_new;

then the import ran successfully]