Home » RDBMS Server » Server Administration » transportable tablespace with IOT (11gr1 solaris)
transportable tablespace with IOT [message #331533] Thu, 03 July 2008 18:01 Go to next message
cromagnon
Messages: 3
Registered: June 2008
Junior Member
Can you do RMAN transportable tablespaces with IOT tables with constraints?

Below I create a simple table with FK, then an IOT table with FK and the subsequent ts_pitr_check fails. Is there any way to do this? Disabling constraints? How would you do that with an RMAN generated "recover tablespace"?





#
# create test table, not IOT
#

chris.BKUP_TEST> create table test (id number,constraint pkid primary key (id)) tablespace bkup_test;

Table created.

chris.BKUP_TEST> create table test2 (id number,constraint t2pk primary key (id),constraint fk2 foreign key (id) references test(id)) tablespace bkup_test;

Table created.

#
# Check that transportable okay
#
chris.SYS> select obj1_name,obj2_name,ts1_name,ts2_name,obj1_type,obj2_type,reason from sys.ts_pitr_check where ts1_name='BKUP_TEST' or ts2_name='BKUP_TEST';

no rows selected


#
# Now make table 2 IOT, and it fails
#
chris.BKUP_TEST> drop table test2;

Table dropped.

chris.BKUP_TEST> create table test2 (id number,constraint t2pk primary key (id),constraint fk2 foreign key (id) references test(id)) organization index tablespace bkup_test;

Table created.





chris.SYS> select obj1_name,obj2_name,ts1_name,ts2_name,obj1_type,obj2_type,reason from sys.ts_pitr_check where ts1_name='BKUP_TEST' or ts2_name='BKUP_TEST';

OBJ1_NAME OBJ2_NAME TS1_NAME TS2_NAME OBJ1_TYPE OBJ2_TYPE REASON
---------- ---------- ---------- ---------- ---------------- --------------- ---------------------------------------------------------------------------------
TEST TEST2 BKUP_TEST SYSTEM TABLE TABLE constraint between tables not contained in recovery set
TEST TEST2 BKUP_TEST SYSTEM TABLE TABLE constraint between tables not contained in recovery set

Re: transportable tablespace with IOT [message #331534 is a reply to message #331533] Thu, 03 July 2008 18:07 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

What is the default tablespace for the user doing the SQL?

Do the results change if this user has default tablespace of BKUP_TEST?
Re: transportable tablespace with IOT [message #331536 is a reply to message #331534] Thu, 03 July 2008 18:21 Go to previous message
cromagnon
Messages: 3
Registered: June 2008
Junior Member
the default tablespace is bkup_test (and the username).
Previous Topic: ORA-14450: attempt to access a transactional temp table already in use
Next Topic: Information required: Migration to 10g from Oracle9i
Goto Forum:
  


Current Time: Tue Dec 06 12:17:50 CST 2016

Total time taken to generate the page: 0.15592 seconds