Recovery of Index tablespace

RECOVERY OF DAMAGED/DELETED INDEX DATAFILE

OPEN SVRMGR30
CONNECT AS INTERNAL/ORACLE
>STARTUP MOUNT (HERE SEE THAT THE INIT.ORA FILE POINTS TO THE
REQUIRED DATABASE)
>ALTER DATABASE DATAFILE ‘D:\ORANT\DATABASE\INDX2ORDB.ORA’
OFFLINE DROP; --- INDX2ORDB.ORA IS THE CORRUPT DATA FILE.
>ALTER DATABASE OPEN;
THIS WILL OPEN THE DATABASE BUT THEN THE TABLES WHO’S INDEXES
ARE MAPPED IN THE DAMAGED DATAFILE WILL SHOW ERROR SAYING
ORA- : CURRENTLY OFFLINE’

THE ENTIRE INDEX TABLESPACE WILL HAVE TO BE DROPPED AND RECREATED.
BUT >DROP TABLESPACE INDX INCLUDING CONTENTS; WILL GIVE ERRORS ORA-00604, ORA-02429

FOLLOWING STEPS HAS TO BE CARRIED OUT:

(1) INDENTIFY THE USERS WHICH USE THE INDEX TABLESPACE. USING
STORAGE MANAGER=>INDEXES
(2) CREATE USERS EXACTLY LIKE THESE USERS USING CREATE LIKE
OPTION IN SECURITY MANAGER
(3) EXPORT ENTIRE DATABASE USING EXP80 UTILITY
(4) DROP THE ORIGINAL USERS USING SECURITY MANAGER
(5) DROP TABLESPACE INDX INCLUDING CONTENTS
(6) CREATE THE TABLESPACE IN STORAGE MANAGER
(7) IMPORT THE USERS FROM THE DMP FILE CREATED IN STEP 3


I HAVE ONE MORE QUESTION THOUGH, CAN I CONFIGURE THE DATA FILE TO AUTOEXTENT MODE WITHOUT MAKING THE DATAFILE OFFLINE?
:)

Quote:

---

Originally posted by sfdba
THE ENTIRE INDEX TABLESPACE WILL HAVE TO BE DROPPED AND RECREATED.
BUT >DROP TABLESPACE INDX INCLUDING CONTENTS; WILL GIVE ERRORS ORA-00604, ORA-02429

---

arent you complicating your life...? just disable the primary keys and enable them later

...yeah we tried that initially but then there were too many tables whose primary keys had to be disabled......

...is there a way to diable the primary keys of all tables for a given database in a single command?

Quote:

---

Originally posted by sfdba
...is there a way to diable the primary keys of all tables for a given database in a single command?

---

All tables? Even SYS' tables :-)

Here is something that will generate a script for you (for schema SCOTT):

--
set serveroutput on
set verify off
prompt
--
DECLARE
--
w_owner varchar2(30);
w_table varchar2(30);
w_name varchar2(50);
sql_text varchar2(256);
Dynamic_Cursor integer;
dummy integer;
table_usage pls_integer;
--
cursor trii is
select
TABLE_NAME, CONSTRAINT_NAME
from
DBA_CONSTRAINTS
where
owner = 'SCOTT' and status = 'ENABLED' and constraint_type = 'P'
order by
TABLE_NAME desc;
--
BEGIN
--
dbms_output.enable(60000);
open trii;
fetch trii into w_table, w_name;
while trii%FOUND loop
begin
Dynamic_Cursor := dbms_sql.open_cursor;
sql_text := 'alter table SCOTT.'||w_table||' disable constraint '||w_name||' cascade';
dbms_sql.parse(Dynamic_Cursor, sql_text, dbms_sql.v7);
dummy := dbms_sql.execute(Dynamic_Cursor);
dbms_sql.close_cursor(Dynamic_Cursor);
exception
when others then null;
end;
fetch trii into w_table, w_name;
end loop;
close trii;
--
END;
/

You can try this:


set feedback off
set heading off
set echo off
set pagesize 0
set trimspool on
set linesize 132
spool disable_cons.sql
select 'alter table '||table_name|| ' disable constraint ' || constraint_name || ' cascade;'
from dba_constraints
where owner = 'SCOTT' and status = 'ENABLED' and constraint_type = 'P'
order by TABLE_NAME desc
/
spool off
set feedback on
set heading on
set trimspool off
@disable_cons.sql


Hope this helps.

THANK TO YOU ALL!!!