Hello All,
One of my database(oracle 10g, aix server ) has a tablespace called tools for xample. its size is nearly 16g, but current usage is around 170 mb only. So i want to resize the tablespace size to 2G
So what i did is, took export of tools tablespace and full database export backup separately. so in my hand i have, tools tablespace backup(export file) and whole database backup(took using again exp utility)
Now i tried to drop the tools tablespace and got this error
SQL> DROP TABLESPACE TOOLS INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TOOLS INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-29857: domain indexes and/or secondary objects exist in the tablespace.
so i also planned, instead of dropping the tablespace, i thought of removing objects in the tablespace and resize it and import using the backup what i have.
when i query the tablespace, i got 4 object. index, tables, lobindex and lobsegment.
i wrote script to drop indexes and tables.but dint execute yet.
how to remove/move lobindex and lobsegment, so that i can resize the tools tablespace?
anyone has come across this issue? or any other method is there to over come this issue?
Please provide detailed step if you have so.
Regards,
Pradeep. V