| Drop Tablespace [message #233371] |
Wed, 25 April 2007 20:51  |
kmkumar24
Messages: 69
Registered: January 2007
Location: Singapore
|
Member |
|
|
Hi All,
I was trying to drop all the tablespace including the contents and datafiles. I followed with the below said command but it doesn't works.
SQL> @droptablespace.sql
drop tablespace BPL_DAY1 including contents and datafiles
*
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace
Is there is any other command to drop the tablespace including the datafiles.
Thanks in advance
Magesh
|
|
|
|
|
|
| Re: Drop Tablespace [message #233374 is a reply to message #233373] |
Wed, 25 April 2007 21:18  |
kmkumar24
Messages: 69
Registered: January 2007
Location: Singapore
|
Member |
|
|
Hi,
Can you tell me how to delete the partition table. b'cos i inserted this tables new in my development by running the scripts. So i have no idea about which are the tables have the partition in which tablespaces.
Could you give me the query to delete this.
Thanks for reply.
|
|
|
|
| Re: Drop Tablespace [message #233377 is a reply to message #233374] |
Wed, 25 April 2007 21:48 |
kmkumar24
Messages: 69
Registered: January 2007
Location: Singapore
|
Member |
|
|
Hi,
Here are some of my partioned table with the table name and the tablespace name.
TABLE_NAME TABLESPACE_NAME PAR
------------------------------ ------------------------------ ---
LOGMNRT_ATTRCOL$ NO
LOGMNRT_COL$ NO
LOGMNRT_TAB$ NO
LOGMNRT_OBJ$ NO
LOGMNRT_DICTIONARY$ NO
LOGMNRT_MDDL$ NO
LOGMNRT_SEED$ NO
LOGMNR_INDCOMPART$ YES
LOGMNR_INDSUBPART$ YES
LOGMNR_INDPART$ YES
LOGMNR_LOBFRAG$ YES
TABLE_NAME TABLESPACE_NAME PAR
------------------------------ ------------------------------ ---
LOGMNR_ICOL$ YES
LOGMNR_CCOL$ YES
LOGMNR_CDEF$ YES
LOGMNR_LOB$ YES
LOGMNR_ATTRIBUTE$ YES
LOGMNR_COLTYPE$ YES
LOGMNR_TYPE$ YES
LOGMNR_TABCOMPART$ YES
LOGMNR_TABSUBPART$ YES
LOGMNR_TABPART$ YES
LOGMNR_IND$ YES
TABLE_NAME TABLESPACE_NAME PAR
------------------------------ ------------------------------ ---
LOGMNR_TS$ YES
LOGMNR_ATTRCOL$ YES
LOGMNR_COL$ YES
LOGMNR_TAB$ YES
LOGMNRC_GSII YES
LOGMNRC_GTCS YES
LOGMNRC_GTLO YES
LOGMNR_USER$ YES
LOGMNR_OBJ$ YES
LOGMNR_DICTIONARY$ YES
LOGMNR_DICTSTATE$ YES
Now can any one tell me how to delete the tablespace by dropping the partitioned tables.
Thanks in Advance
|
|
|
|
| Re: Drop Tablespace [message #233380 is a reply to message #233371] |
Wed, 25 April 2007 23:07 |
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
|
Senior Member |
|
|
A tablespace cannot be droped if tablepartitions exist in other tablespaces .
Work around is
Drop the table ,then drop the tablespace
or
For each table,drop the partition exist in other tablespace;Then drop desired tablespace.
or
Move the table partitions exist in other tabelspace to the one you want to drop;then drop the tablespace.
Hope this helps
|
|
|
|
|
|
| Re: Drop Tablespace [message #233422 is a reply to message #233419] |
Thu, 26 April 2007 02:00 |
kmkumar24
Messages: 69
Registered: January 2007
Location: Singapore
|
Member |
|
|
Hi,
Instead of dropping the tablespaces iam adding datafiles to increase the size. Now the problem is i am able to add a datafile but not resize the datafile. The output is given below.
SQL> alter database datafile '/ls10g/tmsprddb/DATA02.dbf' resize 1000M;
Database altered.
SQL> alter database datafile '/ls10g/tmsprddb/DATA02.dbf' resize 2048M;
alter database datafile '/ls10g/tmsprddb/DATA02.dbf' resize 2048M
*
ERROR at line 1:
ORA-01237: cannot extend datafile 8
ORA-01110: data file 8: '/ls10g/tmsprddb/DATA02.dbf'
ORA-27039: create file failed, file size limit reached
Additional information: 2
Please help me with command to resize the datafile instead off adding new datafile to the tablespace.
Thanks for all your support.
Magesh
|
|
|
|
|
|
| Re: Drop Tablespace [message #233467 is a reply to message #233371] |
Thu, 26 April 2007 04:20 |
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
|
Senior Member |
|
|
|
Usualy fileysystems in unix has largefiles disabled which doest allow you to create file larger thar 2GB.Check with your unix admins on this.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
