Lost my seq$ system table

[sumit]

Hi Experts

How to recover seq$ system table?

Regards

[ggnanaraj]

Why? How did u arrive at this idea of recovering a system table? Do you get any errors? If so, post them verbatim.

HTHU.

[adewri]

Run this as sys as sysdba.

CREATE TABLE SEQ$
(
  OBJ#        NUMBER                            NOT NULL,
  INCREMENT$  NUMBER                            NOT NULL,
  MINVALUE    NUMBER,
  MAXVALUE    NUMBER,
  CYCLE#      NUMBER                            NOT NULL,
  ORDER$      NUMBER                            NOT NULL,
  CACHE       NUMBER                            NOT NULL,
  HIGHWATER   NUMBER                            NOT NULL,
  AUDIT$      VARCHAR2(38)                      NOT NULL,
  FLAGS       NUMBER
)
TABLESPACE SYSTEM
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCACHE
NOPARALLEL;


CREATE UNIQUE INDEX I_SEQ1 ON SEQ$
(OBJ#)
LOGGING
TABLESPACE SYSTEM
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

[adewri]

This script is for 9iR2 databases.

[pando]

will that work? doubt it really

[sumit]

Hi
I am getting the following error. I shutdown my database as 'shutdown immediate';

SQL> startup
ORACLE instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

my alter log file
=================
Mon Apr 21 15:35:30 2003
Errors in file d:\oracle\admin\buchku\udump\buchku_ora_1576.trc:
ORA-00942: table or view does not exist

Error 942 happened during db open, shutting down database
USER: terminating instance due to error 942
Instance terminated by USER, pid = 1576
ORA-1092 signalled during: ALTER DATABASE OPEN...

I know it is seq$ table, what got deleted

Regards

[sumit]

Hi Adweri

My database is Oracle 9i and release 2, but I can not startup my database presently. So how to execute your stuff?

Pls need help.

Regards

[pando]

you have to do some recovery, it´s not that easy to recover a data dictionary table, we wish huh

[sumit]

Hi Pando,

I tried the same now.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.

SQL> recover automatic database;
Media recovery complete.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

Please help me to come out from this problem. I highly appreciate your time.

Regards

[ggnanaraj]

Originally posted by sumit
Errors in file d:\oracle\admin\buchku\udump\buchku_ora_1576.trc

What is the contents of the above file? BTW, how are u so sure it is seq$ table? Hope u did not delete it yourself?