Oracle Ressuscitado

Oracle 9i : 9.0.1.1
My computer with oracle server is not working anymore, but I have the all the files and I'm able to create the same installation structure. Can I recover it? Find out in the end!


LOCAL TEST, real test will be done soon:
0 - installed oracle 9i
1 - Started the services, created the user, set default tablespace, stopped the services
2- backed up the files
3 - Started the services, set temporary tablespace to the user.
4 - created two tables with relationships
5 - created one row for each table and commited
6 - run select statement
7 - stopped the services
8 -copied backed up files to the local files.
9 - restarted all services



well it works but the thing is: al objects become part of the system tablespace.


1 - IDENTIFYING OBJECTS
see if the objects exist, using SYSTEM schema:
select * from all_objects where object_name='EMPRESA' or object_name='PESSOA'


we see owner and created date. TO see the indexes or other objects, try this:
select * from all_objects where created to_date('15-JAN-2010','DD-MON-YYYY')
It doesnt work for me. I have to check it.
For now, try this: select * from all_objects where owner='SYSTEM' order by CREATED DESC


2 - TABLES MUST BE MOVED:


before: see the rownum
select ROWID, a.* from system.EMPRESA a
AAAH2AAABAAAPCDAAA


select ROWID, a.* from system.PESSOA a
AAAH2CAABAAAPCJAAA


alter table system.EMPRESA MOVE TABLESPACE USERS;
alter table system.PESSOA MOVE TABLESPACE USERS;


check results:
select * from all_tables where table_name='PESSOA'


indexes are still in system and now UNUSABLE:
select * from all_indexes where index_name='SYS_C002715'


Moving:
ALTER INDEX system.SYS_C002715 REBUILD TABLESPACE INDX;


Status valid, but one problem:
http://www.dba-oracle.com/t_change_table_schema_owner.htm


The data and objects are in right place, but the owner is not. Unless you import/export. At least, the data and objects are usable!

One thing to remember is that ‘MOVE TABLESPACE’ does not work if table contains column with LONG or LONG RAW data type. You will run into ‘ORA-00997: illegal use of LONG datatype’ error. Such tables can be moved to new tablespace using exp/imp command.

Recovering complete!