Drop empty tablespace fails with ORA-14404 or ORA-144047 partitioned table contains partitions/subpartitions in a different tablespace

Recently I was performing database reorg operation on one of pre-prod environment with traditional way of table/lob movement followed by index rebuild. After moving all segments from current tablespace to new, I was ready to drop original one.


SQL> select count(0) from dba_segments where tablespace_name='MY_TABLESPACE';
  COUNT(0)
----------         
0

And it errored out!!!


SQL> DROP TABLESPACE MY_TABLESPACE INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE MY_TABLESPACE INCLUDING CONTENTS AND DATAFILES
*ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace

As first step, I tried purging dba_recyclebin, but it didn’t help.

After searching on metalink, I found following note with similar issue: ORA-14404 or ORA-14407 When Trying to Drop a Tablespace (Doc ID 1674989.1)

which talks about two causes of this issue:

1. An attempt was made to drop a tablespace which contains tables whose partitions or subpartitions are not completely contained in this tablespace. 

OR

2. This can happen due to deferred segment creation introduced in 11.2.

First cause was matching my case:

I tried finding out tables whose partitions were spread across other partitions along with tablespace MY_TABLESPACE & moved to it to new tablespace as well:


SELECT    'alter table '|| table_owner|| '.'|| table_name|| ' move partition '|| partition_name|| '   tablespace MY_TABLESPACE_NEW;'  FROM (SELECT DISTINCT table_name, partition_name          FROM dba_tab_partitions         WHERE tablespace_name = 'MY_TABLESPACE') a,       dba_tab_partitions b WHERE b.tablespace_name <> 'MY_TABLESPACE'       AND a.table_name = b.table_name;

After moving all affected table partitions, I once again tried dropping tablespace: MY_TABLESPACE. It once again failed with same error: ORA-14404.
Then I found same issue with some of index partitions as well, for which I used following command:


select distinct 'alter index ' || index_owner || '.' || a.index_name || ' rebuild partition ' || a.partition_name || ' tablespace MY_TABLESPACE_NEW;'from ( select distinct index_name,partition_namefrom dba_ind_partitionswhere tablespace_name = 'MY_TABLESPACE' ) a, dba_ind_partitions bwhere b.tablespace_name <> 'MY_TABLESPACE' and a.index_name = b.index_name;

Once indexes were rebuild, I was able to drop tablespace MY_TABLESPACE without any issues 🙂

Note: You may also get ORA-14407 is some cases, wherein sub-partitions are the issue, for which you will use above commands just with sub-partitions views.

Just to elaborate seconds possible cause of this error: deferred segment creation:


The Oracle Database 11.2 includes a new space allocation method. When you create a non-partitioned heap table, the table segment creation is deferred to the first row insert. This functionality is enabled by default with the initialization parameter DEFERRED_SEGMENT_CREATION  set to TRUE.So in simple words, if you have set this parameter to value TRUE, particular table will be populated in dba_tables once created, but will not be populated in dba_segments unless we insert a row to it. So in the case of defered segments, you will not be able to drop tablespace.


Hope so u will find this post very useful:-)

Cheers

Regards,
Adityanath

Advertisements

2015 in review

The WordPress.com stats helper monkeys prepared a 2015 annual report for this blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 22,000 times in 2015. If it were a concert at Sydney Opera House, it would take about 8 sold-out performances for that many people to see it.

Click here to see the complete report.

SQL Developer providing incorrect query results than TOAD/sql*plus

Some days before I came across very weird issue with SQL Developer. One of the developer in my company was complaining that, she is getting “no rows selected” after executing some select query. According to her it was an unexpected behaviour & expecting some rows from that query.

After having some chat got to know that she was using SQL Developer as tool for running that query.

I tried executing same query by logging with her credentials on my PC but through TOAD (not SQL developer). Unexpectdely I got 300 rows with same query.

Means When I run the one query directly on SQLPLUS or TOAD then it displays complete results, while I run the same query with SQL Developer then it does not provide me any result. 😦

After searching on metalink I found : SQL Developer Returns Zero Rows on Table while SQL*Plus Returns Positive Count (Doc ID 415920.1) 

Which states this issue happens when the query is dependent on the results of USERENV(‘LANG’).

I checked USERENV(‘LANG’) settings in both the tools & found that :

TOAD : SELECT USERENV (‘LANG’) FROM dual; => US
SQL Developer : SELECT USERENV (‘LANG’) FROM dual; => GB

After following below steps for changing LANG settings on sql deveoper issue resolved 🙂


1. Start sqldeveloper.
2. Go to Tools-->preferences-->database-->NLS parameters.
3. Change LANGUAGE to AMERICAN.
4. Restart sql developer.

For putting some light on this behaviour, Enviornment in our case was Oracle eBusiness Suite which has lots of language specific views (only returns records where userenv(‘LANG’) matches an installed language in eBusiness).

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath