Vežba CRVENO i CRNO -- Transport

Vežba CRVENO i CRNO -- Tablespace transport iz baze 9i u bazu 10g

Transport Tablespace 9.2_To_10.2

SOURCE 9i crvena

DESTINATION 10g crna

SQL> select * from V$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

SQL> select * from V$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

create user user_ts1 identified by user_ts1 default tablespace users temporary tablespace temp;
User created.

SQL> grant connect,resource to user_ts1;

connect user_ts1/user_ts1

create table table1(col1 varchar2(1));

insert into table1 values ('s');

insert into table1 values ('y');

commit;

connect sys/****

column file_name format a59
column tablespace_name format a40
select file_name,tablespace_name from dba_data_files;

C:\ORACLE\ORADATA\FILEN\SYSTEM01.DBF SYSTEM
C:\ORACLE\ORADATA\FILEN\UNDOTBS01.DBF UNDOTBS1
C:\ORACLE\ORADATA\FILEN\CWMLITE01.DBF CWMLITE
C:\ORACLE\ORADATA\FILEN\DRSYS01.DBF DRSYS
C:\ORACLE\ORADATA\FILEN\EXAMPLE01.DBF EXAMPLE
C:\ORACLE\ORADATA\FILEN\INDX01.DBF INDX
C:\ORACLE\ORADATA\FILEN\ODM01.DBF ODM
C:\ORACLE\ORADATA\FILEN\TOOLS01.DBF TOOLS
C:\ORACLE\ORADATA\FILEN\USERS01.DBF USERS
C:\ORACLE\ORADATA\FILEN\XDB01.DBF XDB
C:\ORACLE\ORADATA\FILEN\USR USR
C:\ORACLE\ORADATA\FILEN\TS1.ORA TS1

ALTER TABLESPACE TS1 READ ONLY;

Tablespace altered.

SQL> exit

set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

exp "'/ as sysdba'" file=export_ts1.dmp log=export_ts1.log transport_tablespace=y tablespaces=TS1

Copy export_ts1.dmp i C:\ORACLE\ORADATA\FILEN\TS1.ORA na odredisnu lokaciju, tj DB server 10.2.x

SQL> ALTER TABLESPACE "TS1" READ WRITE; --vrati TS1 iz moda READ u READ WRITE user sys

SQL> create user user_ts2 identified by user_ts2 default tablespace users temporary tablespace temp2;
User created.

SQL> grant connect,resource to user_ts2;
proveriti da li su dostupni export_ts1.dmp i datoteka TS1.ORA

SQL>exit

set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

D:\oracle\product\10.2.0\oradata\orcl>imp "'/ as sysdba'" file=D:\oracle\product\10.2.0\oradata\orcl\export_export_ts1.d
mp log=imp_ts1.log fromuser=user_ts1 touser=user_ts2 transport_tablespace=y datafiles=D:\ORACLE\PRODUCT\10.2.0\ORADATA\O
RCL\TS1.ORA

Import: Release 10.2.0.1.0 - Production on Mon Oct 2 13:31:50 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V09.02.00 via conventional path
About to import transportable tablespace(s) metadata...
. importing USER_TS1's objects into USER_TS2
. . importing table "TABLE1"
Import terminated successfully without warnings.

SQL>

column file_name format a59
column tablespace_name format a40

select file_name,tablespace_name from dba_data_files
FILE_NAME TABLESPACE_NAME
----------------------------------------------------------- -------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF USERS
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF SYSAUX
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF UNDOTBS1
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF SYSTEM
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF EXAMPLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST_TABLESPACE1.DBF TEST_BIG_TBSPC
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TBSP_SIZE3.DBF TBSP_SIZE3
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATOTEKA1.DBG NO_LOGGING_TABLESPACE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TS1.ORA TS1

SQL>ALTER TABLESPACE "TS1" READ WRITE;

SQL> connect user_ts2/user_ts2
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TABLE1 TABLE

SQL> select * from table1;

C
-
s
y