Test transportable tablespace (5) -- Observe object's grant and public synonym

Objective:
a). To observe object's grant if  it is retained after transportation.
b). Guess public synonym won't be brought over as imp/impdp.



1. On top of earlier experiment, now create a public synonym and grant to a id called liqy.


SYS@TTS> create public synonym t01 for tranp.t01;

Synonym created.


SYS@TTS> select status from dba_tablespaces where tablespace_name='TTS1';

STATUS
---------
READ ONLY

SYS@TTS> create user liqy identified by liqyliqy;

User created.

SYS@TTS> grant select on tranp.t01 to liqy;

Grant succeeded.


SYS@TTS> grant create session to liqy;

Grant succeeded.

SYS@TTS> conn liqy
Connected.


LIQY@TTS> select count(*) from t01;

  COUNT(*)
----------
     65949

LIQY@TTS> select count(*) from tranp.t01;

  COUNT(*)
----------
     65949

2. export the tablespace.

#backup earlier dump files which doesn't contain grant .

orarac2poc:TTS:/home/oracle/ops/exp> mv tts.dmp tts_nogrant.dmp

orarac2poc:TTS:/home/oracle/ops/exp> cat expdp_trans_df.par
DIRECTORY=tranp_dir
DUMPFILE=tts.dmp
TRANSPORT_TABLESPACES=tts1


orarac2poc:TTS:/home/oracle/ops/exp> expdp system parfile=expdp_trans_df.par

Export: Release 11.2.0.2.0 - Production on Fri Mar 22 10:16:41 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** parfile=expdp_trans_df.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /home/oracle/ops/exp/tts.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS1:
  /u02/oradata/TTS/tts1_01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 10:19:13

orarac2poc:TTS:/home/oracle/ops/exp> ls -l tts.dmp tts_nogrant.dmp
-rw-r----- 1 oracle asmadmin 94208 Mar 22 10:19 tts.dmp
-rw-r----- 1 oracle asmadmin 90112 Mar 21 10:27 tts_nogrant.dmp

Note that, the dump file size is different.

3. In target database, preparations for the  import.

SYS@RCAT> drop tablespace tts1 including contents and datafiles;

Tablespace dropped.

SYS@RCAT> select file_name from dba_data_files;

FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
+DGRCAT/rcat/datafile/mgmt_ecm_depot_ts.273.804790907
+DGRCAT/rcat/datafile/mgmt_tablespace.272.804790907
+DGRCAT/rcat/datafile/mgmt_ad4j_ts.271.804790909
+DGRCAT/rcat/datafile/system.261.803153779
+DGRCAT/rcat/datafile/sysaux.262.803153789
+DGRCAT/rcat/datafile/undotbs1.263.803153795
+DGRCAT/rcat/datafile/users.265.803153807
+DGRCAT/rcat/datafile/rman_tbs01.dbf

8 rows selected.

SYS@RCAT> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
orarac2poc:RCAT:/home/oracle/ops/exp> su - grid
Password:
[grid@orarac2poc ~]$ asmcmd
ASMCMD> cp /u02/oradata/TTS/tts1_01.dbf +DGRCAT/rcat/datafile/
copying /u02/oradata/TTS/tts1_01.dbf -> +DGRCAT/rcat/datafile/tts1_01.dbf
ASMCMD> ls -l  +DGRCAT/rcat/datafile/tts1_01.dbf
Type      Redund  Striped  Time             Sys  Name
                                            N    tts1_01.dbf => +DGRCAT/ASM/DATAFILE/tts1_01.dbf.274.810728659
ASMCMD> exit
[grid@orarac2poc ~]$ exit
logout

SYS@RCAT> create user liqy identified by liqyliqy;

User created.

SYS@RCAT> grant create session to liqy;

Grant succeeded.

4. Import the tablespace.

orarac2poc:RCAT:/home/oracle/ops/exp> cat  impdp_trans_df.par
DIRECTORY=tranp_dir
DUMPFILE=tts.dmp
#TRANSPORT_TABLESPACES=tts1
TRANSPORT_DATAFILES='+DGRCAT/ASM/DATAFILE/tts1_01.dbf'


orarac2poc:RCAT:/home/oracle/ops/exp> impdp system parfile=impdp_trans_df.par

Import: Release 11.2.0.2.0 - Production on Fri Mar 22 10:37:25 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** parfile=impdp_trans_df.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 10:37:39

5. Verification.

SYS@RCAT> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
RMAN_TBS                       ONLINE
MGMT_ECM_DEPOT_TS              ONLINE
MGMT_TABLESPACE                ONLINE
MGMT_AD4J_TS                   ONLINE
TTS1                           READ ONLY

10 rows selected.

SYS@RCAT> select count(*) from tranp.t01;

  COUNT(*)
----------
     65949


SYS@RCAT> select * from dba_tab_privs where table_name='T01';

GRANTEE                        OWNER                          TABLE_NAME                     GRANTOR
------------------------------ ------------------------------ ------------------------------ ------------------------------
PRIVILEGE                                GRA HIE
---------------------------------------- --- ---
LIQY                           TRANP                          T01                            TRANP
SELECT                                   NO  NO


SYS@RCAT> select * from dba_synonyms where synonym_name='T01';

no rows selected

SYS@RCAT> conn liqy/liqyliqy
Connected.
LIQY@RCAT> select count(*) from tranp.t01;

  COUNT(*)
----------
     65949


Conclusion
a. No worry about object's grant.
b. Need additional effort public synonym for transported tablespace..