Home » SQL & PL/SQL » SQL & PL/SQL » drop a partitioned table oracle11g
drop a partitioned table oracle11g [message #420165] Thu, 27 August 2009 06:12 Go to next message
usuaria11
Messages: 4
Registered: August 2009
Junior Member
i´m using oracle 11..1.0.6.0.
I have a partitioned tabled with three asociated indexes (one is a unique index simulating a PK) also partitioned.
user_tab_partitions & user_ind_partitions are well provisioned.

I drop the partitioned table and the asociated entries in user_tab_partitions & user_ind_partitions are not also dropped. Those entries are renamed and they exist in the tables as "garbage".

I think oracle 9i didn´t work in that way. In oracle 9i the entries in user_tab_partitions & user_ind_partitions where also dropped. ¿is that ok in oracle11? ¿i´m doing something wrong? ¿is a new behaviour in oracle 11?¿can i drop that entries from the tables?

I copy the result of the queries after drop the partitioned tables:

SQL> select table_name, partition_name, tablespace_name from user_tab_partitions order by 2;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
BIN$cgtw6d5JQdrgRAAUT3A81A==$0 HISTORY_20090713 TSP_D_MDU_01
BIN$cgtw6d5NQdrgRAAUT3A81A==$0 HISTORY_20090720 TSP_D_MDU_02
BIN$chud7rkfUCbgRAAUT3A81A==$0 HISTORY_20090727 TSP_D_MDU_03

....

INDEX_NAME PARTITION_NAME STATUS TABLESPACE_NAME
------------------------------ ------------------------------ -------- ------------------------------
BIN$cgtw6d5UQdrgRAAUT3A81A==$0 HISTORY_20090831 USABLE TSP_I_MDU_01
BIN$chud7rkcUCbgRAAUT3A81A==$0 HISTORY_20090720 USABLE TSP_I_MDU_02
BIN$chud7rkcUCbgRAAUT3A81A==$0 HISTORY_20090727 USABLE TSP_I_MDU_03
BIN$chud7rkcUCbgRAAUT3A81A==$0 HISTORY_20090803 USABLE TSP_I_MDU_04


..

and also the status of the indexes is USABLE ¿¿??

The partitioned table and the asociated indexes where created in that way:

create table HISTORY
(
service VARCHAR2(16) not null,
orderid INTEGER not null,
datestamp TIMESTAMP null ,
key1 VARCHAR2(32) null ,
key2 VARCHAR2(32) null ,
tname VARCHAR2(32) null ,
op CHAR(1) null ,
origin VARCHAR2(10) null ,
str VARCHAR2(512) null
) tablespace TSP_D_MDU_01 storage(maxextents unlimited)
partition by range (datestamp)
(partition HISTORY_MAX values less than (MAXVALUE))
/

PROMPT Creating unique index on 'HISTORY.SERVICE-ORDERID'
create unique index IN_PK_HISTORY on History (service, orderid, datestamp)
tablespace TSP_I_MDU_01 storage(maxextents unlimited)
local
(partition HISTORY_MAX tablespace TSP_I_MDU_01)
/

PROMPT Creating index on 'HISTORY.KEY1'
create index IN_HI_KEY1 on History (key1 asc, datestamp)
tablespace TSP_I_MDU_01 storage(maxextents unlimited)
local
/

PROMPT Creating index on 'HISTORY.KEY2'
create index IN_HI_KEY2 on History (key2 asc, datestamp)
tablespace TSP_I_MDU_01 storage(maxextents unlimited)
local
/

After that I create the new partitions by date and i move indexes and tables to the new partition and tablespace.

thanks a lot


Re: drop a partitioned table oracle11g [message #420170 is a reply to message #420165] Thu, 27 August 2009 06:40 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Read about
Flashback features and Recyclebin that were introduced in Oracle 10G.
Re: drop a partitioned table oracle11g [message #420189 is a reply to message #420170] Thu, 27 August 2009 08:30 Go to previous message
usuaria11
Messages: 4
Registered: August 2009
Junior Member
thanks a lot!
Previous Topic: query help
Next Topic: Self Join Using 2 tables
Goto Forum:
  


Current Time: Fri Dec 09 09:32:43 CST 2016

Total time taken to generate the page: 0.08203 seconds