Home » RDBMS Server » Server Utilities » ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM (Oracle 9.2.0.8.0 , windows)
ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM [message #614827] Tue, 27 May 2014 11:55 Go to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member
Hi,

When i try to do schema import using imp utility i get following error.

Quote:
IMP-00058: ORACLE error 2002 encountered
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM
IMP-00018: partial import of previous table completed: 815598 rows impor
IMP-00003: ORACLE error 2002 encountered
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM
IMP-00003: ORACLE error 2002 encountered
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM
IMP-00003: ORACLE error 2002 encountered
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM


Please let me know how to fix this issue.

With Regards,
Mohan

Report message to a moderator

Re: ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM [message #614828 is a reply to message #614827] Tue, 27 May 2014 11:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
01653, 00000, "unable to extend table %s.%s by %s in tablespace %s"
// *Cause: Failed to allocate an extent of the required number of blocks for
// a table segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.

Report message to a moderator

Re: ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM [message #614829 is a reply to message #614828] Tue, 27 May 2014 12:07 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member
Blackswan,

Should i add space to SYSTEM tablespace to run out of this error ?

Thanks

Report message to a moderator

Re: ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM [message #614830 is a reply to message #614829] Tue, 27 May 2014 12:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Yes or purge audit trail.

Report message to a moderator

Re: ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM [message #614888 is a reply to message #614830] Wed, 28 May 2014 05:00 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member
Thanks Michel,

Since import got stopped in the middle due to lack of space in SYSTEM tablespace , should i rerun the import again after
adding space to SYSTEM tablespace ? out of 5000 tables only 2000 tables got imported successfully.

1.When i try to rerun the import , it is again loading the same tables from beginning which i dont want.
2.Is there any issue in reruninng the import i means would there be any conflict to data if i rerun the import becuase import is again loading the data
to same table which is present in database?

I want to continue from where the import stopped due to lack of tablespace .Please advice me how to do that ?

Thanks,
Mohan

[Updated on: Wed, 28 May 2014 05:27]

Report message to a moderator

Re: ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM [message #614889 is a reply to message #614888] Wed, 28 May 2014 05:39 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member
I also want to know how SYSTEM tablespace is afffected during this import since schema's default tablespace is different tablespace ?

Is it something because of SYSTEM user i am using to import tables into the database ?

Following is the import command i am using to import schema objects

Quote:
imp system@DB file=C:\orcl\exp_schema_28may.dmp log=C:\orcl\exp_schema_28may.log fromuser=robo touser=robo commit=y ignore=y grants=n indexes=n,constraints=n


Thanks,
Mohan

Report message to a moderator

Re: ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM [message #614891 is a reply to message #614888] Wed, 28 May 2014 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
I want to continue from where the import stopped due to lack of tablespace .Please advice me how to do that ?


You can't do that automatically, you have to list all what have been done and what has not be done and import using TABLES parameter to just import the table you want and use INDEXFILE to create a SQL file to all other objects you want to import.

Quote:
I also want to know how SYSTEM tablespace is afffected during this import since schema's default tablespace is different tablespace ?


It is auditing that implies the SYSTEM tablespace.
Check and remove all audit on the tables you import and recreate them afterwards.

Report message to a moderator

Re: ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM [message #614893 is a reply to message #614891] Wed, 28 May 2014 06:47 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member
Thanks Michel,

1.What will happen to table data if i rerun the import again ? so far the table import is going on but im worried
if there would be conflict to data by running the import again since some of the tables already exist in database?

Quote:
It is auditing that implies the SYSTEM tablespace.

Could you elaborate the above sentence in some other way.I couldnt understand this sentence.

Thanks

[Updated on: Wed, 28 May 2014 06:48]

Report message to a moderator

Re: ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM [message #614894 is a reply to message #614893] Wed, 28 May 2014 07:23 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
You've got auditing switched on. The audit tables are in the system tablespace, so any audited action causes a record to be written there.

Report message to a moderator

Re: ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM [message #614895 is a reply to message #614894] Wed, 28 May 2014 07:40 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member
Cookiemaster

Thanks for your explanation.

1.If i import a schema using sys user or any other user, will it happen same ?

2..What will happen to existing table data if i rerun the schema import again ? I have started the import and so far the table import is going on
without any errors but im worried if there could be conflict to data by running the import again since some of the tables already exist in database?

Regds,
Mohan

[Updated on: Wed, 28 May 2014 07:42]

Report message to a moderator

Re: ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM [message #614896 is a reply to message #614895] Wed, 28 May 2014 07:42 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
I believe so, what's generally relevant is the objects being audited, not the user performing the actions.

Report message to a moderator

Re: ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM [message #614899 is a reply to message #614896] Wed, 28 May 2014 08:31 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member
Cookiemaster,

Thanks,

Can you answer to below question ?

What will happen to existing table data if i rerun the schema import again ? I have started the import and so far the table import is going on
without any errors but im worried if there could be conflict to data by running the import again since some of the tables already exist in database?

Regds
Mohan

Report message to a moderator

Re: ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM [message #614901 is a reply to message #614899] Wed, 28 May 2014 09:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Yes, there will be as you will import a second time unless you do what I said.
But instead on waiting almost one day to get this answer why didn't you make a little test by yourself? You will have anyway to make this test as you can't rely on answers given in a forum, from anyone.

Report message to a moderator

Re: ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM [message #614902 is a reply to message #614901] Wed, 28 May 2014 09:10 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member
ok Thanks Michel.

Quote:
Check and remove all audit on the tables you import and recreate them afterwards.


How to Check and remove all audit on the tables that i import ?

Regds,
Mohan

Report message to a moderator

Re: ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM [message #614906 is a reply to message #614902] Wed, 28 May 2014 09:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Query dba_obj_audit_opts, dba_priv_audit_opts and dba_stmt_audit_opts.
For all records relating to your objects, execute the matching NOAUDIT statement.

Report message to a moderator

Re: ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM [message #614912 is a reply to message #614906] Wed, 28 May 2014 11:03 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member
Thanks Michel

Report message to a moderator

Re: ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM [message #614967 is a reply to message #614912] Thu, 29 May 2014 04:01 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member
Michel,

Import got terminated with error .Most of the objects got imported only few objects like 1 package and package body was not imported .

Quote:
IMP-00003: ORACLE error 1031 encountered
ORA-01031: insufficient privileges
IMP-00017: following statement failed with ORACLE error 1031:


Now when i try to import only indexes and constraints with rows=N , i get following error.

Quote:

IMP-00030: failed to create file F:\imp_prod\imp_schema.log for write
IMP-00000: Import terminated unsuccessfully


The second import command i used to import without data is as below

Quote:
imp system@DB file=C:\orcl\exp_schema_28may.dmp log=C:\orcl\exp_schema_28may.log fromuser=robo touser=robo grants=y indexes=y constraints=y rows=N


Note --I am using 2 import commands one is to load data without indexes and constraints and another is to create indexes and constraints without data.

Could you tell me how to fix this error?

Thanks

[Updated on: Thu, 29 May 2014 04:42]

Report message to a moderator

Re: ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM [message #614975 is a reply to message #614967] Thu, 29 May 2014 04:43 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member
I have fixed below error. can you help me other one ?

Quote:
IMP-00030: failed to create file F:\imp_prod\imp_schema.log for write
IMP-00000: Import terminated unsuccessfully

Report message to a moderator

Re: ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM [message #614979 is a reply to message #614975] Thu, 29 May 2014 04:48 Go to previous message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member
I am getting following error when trying to create indexes and constraints without table data using import utility
but paralleley when checked in database indexes are created. Could you clarify this?

Quote:
IMP-00017: following statement failed with ORACLE error 1452:
"CREATE UNIQUE INDEX "SNAP_IDX" ON "SNAP" ("M_ROW$$" ) PCTFRE"
"E 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GR"
"OUPS 1) TABLESPACE "DEMO_DATA" LOGGING"
IMP-00003: ORACLE error 1452 encountered
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
IMP-00017: following statement failed with ORACLE error 20000:
"BEGIN DBMS_STATS.SET_INDEX_STATS(NULL,'"I_SNAP$_CRM_RM_MV"',NULL,NULL,NULL"
",2719,10,2719,1,1,186,1,6); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: Unable to set values for index I_SNAP$_SNAP_IDX: does not exist or insufficient privileg
ORA-06512: at "SYS.DBMS_STATS", line 3908
ORA-06512: at "SYS.DBMS_STATS", line 4017
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 1452:

[Updated on: Thu, 29 May 2014 05:58]

Report message to a moderator

Previous Topic: how to execute the CATPROC.SQL
Next Topic: question on datapump
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun May 18 05:28:24 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.