Oracle Version :11g
OS: Rhel 6.4
#Turn on flashback and archivelog from mount stage
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 318769144 bytes
Database Buffers 88080384 bytes
Redo Buffers 4308992 bytes
Database mounted.
SQL>alter database archivelog;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
#Now create a tablespace to test the scenario
SQL> create tablespace test datafile '/u01/app/oracle/oradata/prim/testtbs.dbf' size 20m;
Tablespace created.
SQL> create user test identified by test;
User created.
SQL> grant connect , reesource to test;
SQL> alter user test default tablespace test;
User altered.
SQL> conn test/test
Connected.
SQL> create table t1 (id number);
SQL> insert into t1 values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
ID
----------
1
SQL> connect sqlplus as sysdba
Enter password:
Connected.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1075681
#Now drop the tablespace:-
SQL> drop tablespace test including contents and datafiles;
Tablespace dropped.
SQL> shutdown immediate
SQL> startup mount
SQL> flashback database to scn 1075681;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/system01.dbf
/u01/app/oracle/oradata/prim/sysaux01.dbf
/u01/app/oracle/oradata/prim/undotbs01.dbf
/u01/app/oracle/oradata/prim/users01.dbf
/u01/app/oracle/oradata/prim/example01.dbf
/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006
SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006' as '/u01/app/oracle/oradata/prim/testtbs.dbf';
Database altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/system01.dbf
/u01/app/oracle/oradata/prim/sysaux01.dbf
/u01/app/oracle/oradata/prim/undotbs01.dbf
/u01/app/oracle/oradata/prim/users01.dbf
/u01/app/oracle/oradata/prim/example01.dbf
/u01/app/oracle/oradata/prim/testtbs.dbf
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
EXAMPLE
TEST
#Login to test schema and verify the data
SQL> conn test/test
Connected.
SQL> select * from t1;
ID
----------
1
OS: Rhel 6.4
#Turn on flashback and archivelog from mount stage
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 318769144 bytes
Database Buffers 88080384 bytes
Redo Buffers 4308992 bytes
Database mounted.
SQL>alter database archivelog;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
#Now create a tablespace to test the scenario
SQL> create tablespace test datafile '/u01/app/oracle/oradata/prim/testtbs.dbf' size 20m;
Tablespace created.
SQL> create user test identified by test;
User created.
SQL> grant connect , reesource to test;
SQL> alter user test default tablespace test;
User altered.
SQL> conn test/test
Connected.
SQL> create table t1 (id number);
SQL> insert into t1 values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
ID
----------
1
SQL> connect sqlplus as sysdba
Enter password:
Connected.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1075681
#Now drop the tablespace:-
SQL> drop tablespace test including contents and datafiles;
Tablespace dropped.
SQL> shutdown immediate
SQL> startup mount
SQL> flashback database to scn 1075681;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/system01.dbf
/u01/app/oracle/oradata/prim/sysaux01.dbf
/u01/app/oracle/oradata/prim/undotbs01.dbf
/u01/app/oracle/oradata/prim/users01.dbf
/u01/app/oracle/oradata/prim/example01.dbf
/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006
SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006' as '/u01/app/oracle/oradata/prim/testtbs.dbf';
Database altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/system01.dbf
/u01/app/oracle/oradata/prim/sysaux01.dbf
/u01/app/oracle/oradata/prim/undotbs01.dbf
/u01/app/oracle/oradata/prim/users01.dbf
/u01/app/oracle/oradata/prim/example01.dbf
/u01/app/oracle/oradata/prim/testtbs.dbf
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
EXAMPLE
TEST
#Login to test schema and verify the data
SQL> conn test/test
Connected.
SQL> select * from t1;
ID
----------
1
No comments:
Post a Comment