[oracle@localhost ~]$ export ORACLE_SID=vinay
[oracle@localhost ~]$ sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 24 12:37:22 2010
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
SQL> select name from v$database;
NAME
---------
vinay
SQL> select status from v$instance;
STATUS
------------
OPEN
12:25:33 SQL> select username,default_tablespace from dba_users;
--make sure sid will tablespace name and sid.dbf file will be the same name of tablespace then tablespace can be created only...
12:26:02 SQL> create tablespace sid datafile '/u01/app/oracle/oradata/vinay/sid1.dbf' size 10m;
Tablespace created.
12:27:57 SQL> create user tts_user identified by tts default tablespace sid;
User created.
--now we will check this user has been created in the same tablespace which we have just created..
12:28:32 SQL> select username,default_tablespace from dba_users;
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW SYSTEM
SYS SYSTEM
SYSTEM SYSTEM
DBSNMP SYSAUX
SYSMAN SYSAUX
HR USERS
TTS_USER SID
OUTLN SYSTEM
MDSYS SYSAUX
ORDSYS SYSAUX
EXFSYS SYSAUX
--Now we will check our created tablespace is online or offline of readonly so that....????
12:29:50 SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TBSALERT ONLINE
TTS READ ONLY
SID ONLINE
9 rows selected.
--after checking it online we will make it Read Only Istead of online...
12:30:17 SQL> alter tablespace sid read only;
Tablespace altered.
12:30:33 SQL> exec dbms_tts.transport_set_check('SID');
PL/SQL procedure successfully completed.
--THIS THE PACKAGE WE ARE RUNNING HERE TO verify is it able to transport or not(package)...IF IT IS SUCCESSFULLY COMPLETED THEN WE CAN MOVE TO ANY DATABASE...WE WILL ALSO CHECK THROUGH THIS STATMENT TO CHECK THE VOILATIONS IF ANY IT WILL SHOW OR IT WILL SAY NO ROWS SELECTED MEANS WE CAN GO AHEAD SUSSESSFULLY...
12:31:03 SQL> select * from transport_set_violations;
no rows selected
--it will check is there any object created by sys in current tablespace or not if yes then it wont allow..
12:31:20 SQL> host pwd
/home/oracle
12:31:29 SQL> host exp file=sid.dmp transport_tablespace=Y tablespaces=sid
Export: Release 10.2.0.1.0 - Production on Fri Sep 24 12:32:01 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace SID ...
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
12:32:18 SQL> alter tablespace sid online;
Tablespace altered.
12:32:54 SQL>
[oracle@localhost ~]$ export ORACLE_SID=orcl1
[oracle@localhost ~]$ sqlplus '/ as sydba'
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 24 12:37:15 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL*Plus: Release 10.2.0.1.0 - Production
Copyright (c) 1982, 2005, Oracle. All rights reserved.
[oracle@localhost ~]$ sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 24 12:37:22 2010
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
--THIS IS VERY IMPORTANT STEP TO COPY THE sid1.dbf FILE FROM FIRST DATABSE & PAST IT INTO SECOND DATABASE...
--now go to oradata folder n from first database copy the first datafile sid1.dbf and past into second database .
SQL> select name from v$database;
NAME
---------
ORCL1
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> create user tts_user identified by tts_user; --Here we will create same user which has been created in first db.
User created.
SQL> grant connect,resource to tts_user;
Grant succeeded.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE
6 rows selected.
--we confirmed it no such table space availabe which we created above in current databse...
--Now we r going to import the datafile & tablespace so make sure here we will give orcl1 which is current database....
SQL> host imp file=sid.dmp transport_tablespace=Y tablespaces=sid datafiles='/u01/app/oracle/oradata/orcl1/sid1.dbf'
Import: Release 10.2.0.1.0 - Production on Fri Sep 24 12:42:03 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: / as sysdba
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:V10.02.01 via conventional vinay
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
Import terminated successfully without warnings.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
SID READ ONLY
7 rows selected.
SQL> alter tablespace sid read write;
Tablespace altered.
SQL> conn tts_user/tts_user
Connected.
-----------------------------one more time practice detials given below date was-5-feb-2011-------------
[oracle@localhost ~]$ export ORACLE_SID=orcl2
[oracle@localhost ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Feb 5 19:10:33 2011
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
SQL> conn hr/hr
Connected.
SQL> conn as sysdba
Enter user-name: sys
Enter password:
Connected.
SQL> show user
USER is "SYS"
SQL> select name from v$database;
NAME
---------
ORCL2
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select username,default_tablespace from dba_users;
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
MDSYS SYSAUX
ORDSYS SYSAUX
EXFSYS SYSAUX
DMSYS SYSAUX
DBSNMP SYSAUX
SCOTT USERS
WMSYS SYSAUX
TSMSYS USERS
BI USERS
PM USERS
MDDATA USERS
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
IX USERS
CTXSYS SYSAUX
ANONYMOUS SYSAUX
SH USERS
OUTLN SYSTEM
DIP USERS
OE USERS
HR USERS
SYSMAN SYSAUX
XDB SYSAUX
ORDPLUGINS SYSAUX
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW SYSTEM
SI_INFORMTN_SCHEMA SYSAUX
OLAPSYS SYSAUX
SYS SYSTEM
SYSTEM SYSTEM
27 rows selected.
SQL> Create TableSpace Saba datafile '/u01/app/oracle/oradata/orcl2/saba1.dbf' size 10m;
Tablespace created.
SQL> create user sabau identified by salim default tablespace saba;
User created.
SQL> select username,default_tablespace from dba_users;
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
MDSYS SYSAUX
ORDSYS SYSAUX
EXFSYS SYSAUX
DMSYS SYSAUX
DBSNMP SYSAUX
SCOTT USERS
WMSYS SYSAUX
TSMSYS USERS
SABAU SABA
BI USERS
PM USERS
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
MDDATA USERS
IX USERS
CTXSYS SYSAUX
ANONYMOUS SYSAUX
SH USERS
OUTLN SYSTEM
DIP USERS
OE USERS
HR USERS
SYSMAN SYSAUX
XDB SYSAUX
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
ORDPLUGINS SYSAUX
MGMT_VIEW SYSTEM
SI_INFORMTN_SCHEMA SYSAUX
OLAPSYS SYSAUX
SYS SYSTEM
SYSTEM SYSTEM
28 rows selected.
SQL> host emctl start dbconsole
TZ set to Asia/Calcutta
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://localhost.localdomain:5500/em/console/aboutApplication
- An instance of Oracle Enterprise Manager 10g Database Control is already running.
SQL> alter tablespace saba read only;
Tablespace altered.
SQL> exe dbms_tts.transport_set_check('saba');
SP2-0734: unknown command beginning "exe dbms_t..." - rest of line ignored.
SQL> exec dbms_tts.transport_set_check('saba');
PL/SQL procedure successfully completed.
SQL> select *from transport_set_violations;
no rows selected
SQL> host pwd
/home/oracle
SQL> host exp file=saba.dmp transport_tabaspace=Y tablespaces=saba;
LRM-00101: unknown parameter name 'transport_tabaspace'
EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully
SQL> host exp file=saba.dmp transport_tablespace=Y tablespaces=saba;
Export: Release 10.2.0.1.0 - Production on Sat Feb 5 19:29:08 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace SABA ...
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
SQL> alter tablespace saba online;
Tablespace altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost ~]$ export ORACLE_SID=orcl1
[oracle@localhost ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Feb 5 19:32:58 2011
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
SQL> select name from v$database;
NAME
---------
ORCL1
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> create user sabau identified by salim;
User created.
SQL> Grant connect,resource to sabau;
Grant succeeded.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE
SAMDBA
7 rows selected.
SQL> host imp file=saba.dmp transport_tablespace=Y tablespaces=saba datafiles='/u01/app/oracle/oradata/orcl1/saba1.dbf';
Import: Release 10.2.0.1.0 - Production on Sat Feb 5 19:52:28 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: / as sysdba
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:V10.02.01 via conventional vinay
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
Import terminated successfully without warnings.
SQL> select tablespace_name , status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
SAMDBA ONLINE
SABA READ ONLY
8 rows selected.
SQL> alter tablespace saba read write;
Tablespace altered.
SQL> conn sabau/salim
Connected.
SQL> create table saba(R Number);
Table created.
SQL> Insert Into saba values(1);
1 row created.
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost ~]$
---Run Programm successufully.....
No comments:
Post a Comment