A reader, March 28, 2001 - 1:14 am UTC
Helena Markova, March 28, 2001 - 2:03 am UTC
Very Useful
Amol, February 28, 2004 - 5:47 am UTC
moving partition
Sophie Wang, December 16, 2004 - 11:46 am UTC
Tom. If I issued this command
alter table x move partition p1 tablespace ts_test NOLOGGING;
What'll happen if the instance crashed in the middle way of moving partition with option NOLOGGING ? Is it possible to lose all records in that partitione? My understanding is moving a partition to another table space just like a DDL, either all records in the partition moved or none of them moved.
December 16, 2004 - 11:56 am UTC
the statement is atomic. the old data will still be there, it will be as if the statement never happened.
you should be asking "what if right after I move that partition, the disk I moved it onto fails". that is the risk with non-logged operations. if in between the time you do the non-logged operation and the time you backup the non-logged tablespaces you suffer media failure -- then you can lose stuff and would be forced to do a point in time recovery to get back as much as you can.
excellent
A reader, March 31, 2005 - 12:57 pm UTC
not true for composite partitioned tables
Ajeet, September 29, 2005 - 8:11 am UTC
Tom,
Seems like we can not move a partition of a composite partitioned tables - why it is so..is there a logic behind it.
I tried the example below :
CREATE TABLE sales_range_hash(
s_productid NUMBER,
s_saledate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
PARTITION BY RANGE (s_saledate)
SUBPARTITION BY HASH (s_productid)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 TABLESPACE tts_ex1,
SUBPARTITION sp2 TABLESPACE tts_ex1,
SUBPARTITION sp3 TABLESPACE tts_ex1,
SUBPARTITION sp4 TABLESPACE tts_ex1,
SUBPARTITION sp5 TABLESPACE tts_ex1,
SUBPARTITION sp6 TABLESPACE tts_ex1,
SUBPARTITION sp7 TABLESPACE tts_ex1,
SUBPARTITION sp8 TABLESPACE tts_ex1)
(PARTITION sal99q1 VALUES LESS THAN (TO_DATE('01-APR-1999', 'DD-MON-YYYY')),
PARTITION sal99q2 VALUES LESS THAN (TO_DATE('01-JUL-1999', 'DD-MON-YYYY')),
PARTITION sal99q3 VALUES LESS THAN (TO_DATE('01-OCT-1999', 'DD-MON-YYYY')),
PARTITION sal99q4 VALUES LESS THAN (TO_DATE('01-JAN-2000', 'DD-MON-YYYY')));
then I tried to move a partition and i got this error.
alter table sales_range_hash move partition sal99q1 compress ;
ngta3zt@EVNODSD2> alter table sales_range_hash move partition sal99q1 compress ;
alter table sales_range_hash move partition sal99q1 compress
*
ERROR at line 1:
ORA-14257: cannot move partition other than a Range or Hash partition
error says that it is not possible.I have not read it in any document/book so far.
thanks
Ajeet
September 30, 2005 - 7:29 am UTC
you cannot move the partition because the partition is a logical entity - it is a collection of other PHYSICAL partitions. You move the segments - here the segments are the SUBPARTITIONS.
ops$tkyte@ORA10G> alter table sales_range_hash move;
alter table sales_range_hash move
*
ERROR at line 1:
ORA-14511: cannot perform operation on a partitioned object
<b>cannot move the table because the table isn't the segment, it doesn't "exist"</b>
ops$tkyte@ORA10G> alter table sales_range_hash move partition sal99q1;
alter table sales_range_hash move partition sal99q1
*
ERROR at line 1:
ORA-14257: cannot move partition other than a Range or Hash partition
<b>cannot move the partition, because the partition isn't the segment, it doesn't exist</b>
ops$tkyte@ORA10G> alter table sales_range_hash move subpartition sal99q1_sp1;
Table altered.
<b>can move the subpartition, it is the segment - it can move</b>
Index Unusable
mAg, November 21, 2005 - 5:22 am UTC
Hi Tom
I experienced this problem, When I am moving partitioned data across tablespace on partition table, I got ORA-01502, their are unique index and primary key in the table... I like to know what alter table tb_name move partition tablespace tb_name; will do?
Can you explian me about this, Thanks a lot.
November 21, 2005 - 8:48 am UTC
when you move a partition, you change the rowids.
This will result in the affected local index partitions and ALL global indexes becoming unusable - they need to be rebuilt
UNLESS you use the options to maintain global indexes (9i and above)
and UNLESS you use the options to maintain local indexes as well (10g and above)
Analyze table
mAg, November 22, 2005 - 7:32 am UTC
Hi Tom
This is regarding the partition table, I have RANGE Partition on table T1 as P1...P15.
Where P15 is new data loaded recently, no changes made on P1-P14...
My question is the table analyzed using DBMS_STAT package before load i.e. P1...P14 data, is there any way to analyze only the newly loaded data? i.e, P15.
Thanx!!!
mAg
November 22, 2005 - 8:43 am UTC
yes, dbms_stats has that ability. when you gather a tables statistics, you may specify the partition to do it on.
but remember, somethings cannot roll up nicely - like certain column statistics.
If you have 100 unique values of X in p1 and 100 in p2, and so on - do you have
a) 100 unique values of X in the table over all partitions
b) 100*15 unique values of X in the table over all partitions
c) some number between A) and B)
for example.
And the answer is...
A reader, November 22, 2005 - 10:19 am UTC
"If you have 100 unique values of X in p1 and 100 in p2, and so on - do you have
a) 100 unique values of X in the table over all partitions
b) 100*15 unique values of X in the table over all partitions
c) some number between A) and B)"
I've seen this multiple choice question on some of your replies. The correct choice is C. The number of unique values in the table MUST be a number between 100 and 100*15 and one way to get the actual number is gathering global stats.
November 22, 2005 - 10:29 am UTC
You answered that alter table move partition updates rowid
NewBlood_Nancy, December 09, 2005 - 1:30 pm UTC
Can we assume then that it is faster than using insert /*APPEND*/ and would now be (9i and above) the fastest way to "reorg" partitioned tables to their own tablespaces.
December 09, 2005 - 1:58 pm UTC
define fastest.
you can alter table t move partition and have it maintain OR NOT maintain the indexes...
a) fastest = minimum downtime. then moving with update indexes will be fastest. The move command will take longer, but no one cares, the data is online.
b) fastest = the command returns as soon as possible. Likely the one that does not maintain indexes, but then you have to rebuild the indexes which could take long too.
eg: there is not any "this is faster answer". just understanding what each does and what each implies.
FASTEST ONLINE - MINIMAL DOWNTIME
NEWBLOOD_NANCY, December 09, 2005 - 2:53 pm UTC
My apologies for not being more succint. Have bitmap indices though not global - all local.
my plan was/is
1)drop indexes
2) move partitons via alter ...move partition to new tablespace
3) recreate indexes
4) run stats
There are about 640million rows in range partitions just want to "reorg" to new tablespaces.
December 10, 2005 - 4:42 am UTC
well, the fastest way will be not to do it (so don't unless you have a compelling reason :)
but what you propose will likely be "fastest from start to finish, but includes downtime while it is happening".
You can use nologging if you schedule a backup right after this takes place and that will skip undo and redo generation (undo is going to be skipped with alter move and create index).
Don't be afraid to perform 2 and 3 (and even 4) in parallel if you have the capability (just remember to reset the default degree of parallelism on your indexes if you rebuild them in parallel)
Nothing like validation from the "man"
NEWBLOOD_NANCY, December 12, 2005 - 8:17 am UTC
Thanks so much for the follwup. It was my first time ergo "new blood" and gee whiz Tom please forward this to whomever -
PLEASE GIVE TOM THE TIME AND RESOURCES TO KEEP DOING ASK TOM - THIS IS AND ALWAYS HAS BEEN THE MOST VALUABLE RESOURCE ON THE INTERNET FOR ORACLE DBAs(all levels)IMHO
Oh and metalink too 8>)
TOM I HOPE YOU STILL ENJOY DOING THIS FOR US -
Is there a way to Copy a partition to the same table
SK, January 12, 2006 - 5:11 pm UTC
Tom,
New year greetings to you.
Needless to say, that yours' is the is the most visited site from my PC. Thanks a ton for all your inputs.
I have a requirement, wherein at the begining of business each day, i have to "copy" the data from the previous day's partition as the data/partition for that day. During the course of the day, the data will get changed and this cycle will go on..
I am wondering if there is an option to copy an existing partition as a new one on the same table. Currently i am using INSERT(NEW PARTITION)..SELECT(OLD PARTITION). But this seem to take a lot of time, especially when the users start using the system.
Is there an elegant and faster way out?
Cheers.
-Sri.
January 13, 2006 - 10:59 am UTC
not sure what the "when the users start using the system" means since when you are copying - they cannot "see this data" yet, so they must not be working on it?
would need more information on the operating environment here. I mean, would it be possible to:
a) use a partitioned table for OLD stuff
b) use a separate table for new stuff
if so, I might
a) alter table partitioned and add yesterdays table to it (meaning the table people work on today disappears for a moment
b) create table as select to copy the data back out of the partitioned table into this new table
c) index it/constrain it
d) grant it
operation resumes.
Copying a partition
SK, January 14, 2006 - 6:17 am UTC
Hi Tom,
Thanks for reverting back.
I should say it would NOT be possible to use a new table for today's stuff.
The same table will have to be used, but users will be querying on today's partition, which will have to be created with yesterday's partition data initially.
Ours is a 24X5.5 system used globally and we have a small window within which we need to copy the data onto new partition before the users from the other part of the globe start using the system.
Hope this information is sufficient.
Thanks in advance.
-Sri
January 15, 2006 - 3:27 pm UTC
I outlined what is likely to be the most efficient way to do this.
but short of that, not sure what you want to optimize here. I don't know what a 24 x 5.5 system is.
You have a small window, I suggest
a) create a new table that is copy of data (create table as select)
b) index it (parallel, no logging, whatever)
c) alter existing table to add new EMPTY partition at end
d) exchange empty partition with full table
very much like what I described above.
Moving default tablespace
Yoav, June 30, 2006 - 8:36 am UTC
Hi tom,
I added a default tablespace (USERS) to your example from the top of this page :
CREATE TABLE partitioned
( x int,
a int default to_char(sysdate,'d')
)
TABLESPACE USERS
PARTITION BY RANGE (a)
(
PARTITION part_1 VALUES LESS THAN(2),
PARTITION part_2 VALUES LESS THAN(3),
PARTITION part_3 VALUES LESS THAN(4),
PARTITION part_4 VALUES LESS THAN(5),
PARTITION part_5 VALUES LESS THAN(6),
PARTITION part_6 VALUES LESS THAN(7),
PARTITION part_7 VALUES LESS THAN(8)
)
I moved each of this partition to new tablespace : PART_10.
Now i want to move tablespace USERS to this new tablespace.
Could you please demonstrate how to move it ?
Thanks
June 30, 2006 - 12:32 pm UTC
does not make sense - not sure what you are asking. You don't move "a tablespace" into "a new tablespace"?
How to change defualt tablespace
yoav, June 30, 2006 - 2:13 pm UTC
Hi Tom,
You right. i didnt made myself clear.
I would like to use transportable tablespace.
I have a table in this structure :
CREATE TABLE INVOICES
(
INVOICE_ID NUMBER(15),
INV_DATE DATE,
INV_MSN NUMBER(5)
)
TABLESPACE USERS
PARTITION BY HASH (INV_MSN)
( PARTITION INVOICE_P01 TABLESPACE DATA01,
PARTITION INVOICE_P02 TABLESPACE DATA02
);
Insted of moving 3 tablespaces (users,data01,data02)
i built a new tablespace named TEST and moved the table into it:
CREATE TABLESPACE TEST
'/test/test_01.dbf' SIZE 2001M AUTOEXTEND OFF,
'/test/test_02.dbf' SIZE 2001M AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
ALTER TABLE INVOICES MOVE PARTITION INVOICE_P01 TABLESPACE TEST;
ALTER TABLE INVOICES MOVE PARTITION INVOICE_P02 TABLESPACE TEST;
This means that partitions of table INVOICES are moved to the new tablespace TEST, but it still has its default tablespace which is: USERS , while i wanted it to be TEST.
So actually I would like to know how to change the
defualt tablespace from USERS to TEST for this table.
Thanks again.
June 30, 2006 - 4:22 pm UTC
say you moves the two partitions to "MANUAL" and wanted to have the default tablespace be manual as well to avoid:
ops$tkyte@ORA10GR2> exec sys.dbms_tts.transport_set_check('MANUAL', true, true );
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR2> SELECT * FROM sys.TRANSPORT_SET_VIOLATIONS;
VIOLATIONS
-------------------------------------------------------------------------------
Default Partition (Table) Tablespace USERS for INVOICES not contained in transp
ortable set
Default Partition (Table) Tablespace USERS for INVOICES not contained in transp
ortable set
Default Partition (Table) Tablespace MANUAL for INVOICES not contained in trans
portable set
Default Partition (Table) Tablespace MANUAL for INVOICES not contained in trans
portable set
<b>You would modify the default attributes of the table:</b>
ops$tkyte@ORA10GR2> alter table invoices modify default attributes tablespace manual;
Table altered.
ops$tkyte@ORA10GR2> exec sys.dbms_tts.transport_set_check('MANUAL', true, true );
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR2> SELECT * FROM sys.TRANSPORT_SET_VIOLATIONS;
no rows selected
One more question
Yoav, July 01, 2006 - 4:57 am UTC
Hi Tom,
Thanks for your help, its work to me.
I would like to ask one more question.
As I said before i would like to use transportable tablespace.
I want to transfer only one tablespace named: TEST
The following table structure contain 2 tablespaces(TEST,DATA1) :
CREATE TABLE PAY_CUST_DIM
(
PAY_CUST_ID NUMBER(10),
PAYING_CUST_NO NUMBER(10),
CUST_ID NUMBER(10),
NAME VARCHAR2(50),
AGG_LEVEL NUMBER(10)
)
TABLESPACE TEST
PARTITION BY RANGE (AGG_LEVEL)
SUBPARTITION BY HASH (PAYING_CUST_NO)
(
PARTITION PAY_CUST_DIM_1 VALUES LESS THAN (2)
TABLESPACE DATA1
( SUBPARTITION PAY_CUST_DIM_1_1 TABLESPACE TEST,
SUBPARTITION PAY_CUST_DIM_1_2 TABLESPACE TEST,
SUBPARTITION PAY_CUST_DIM_1_3 TABLESPACE TEST,
SUBPARTITION PAY_CUST_DIM_1_4 TABLESPACE TEST ),
PARTITION PAY_CUST_DIM_2 VALUES LESS THAN (3)
TABLESPACE DATA1
( SUBPARTITION PAY_CUST_DIM_2_1 TABLESPACE TEST,
SUBPARTITION PAY_CUST_DIM_2_2 TABLESPACE TEST,
SUBPARTITION PAY_CUST_DIM_2_3 TABLESPACE TEST ),
PARTITION PAY_CUST_DIM_3 VALUES LESS THAN (MAXVALUE)
TABLESPACE DATA1
( SUBPARTITION PAY_CUST_DIM_3_1 TABLESPACE TEST )
)
You wrote at the top of this page that:
"cannot move the partition, because the partition isn't the segment, it doesn't exist"
What should be done to replace tablespace DATA1 with TEST ?
Thanks alot
July 01, 2006 - 8:02 am UTC
variation on a theme (did you check out the docs once you got the pointer to the syntax for the table???)
ops$tkyte@ORA10GR2> exec sys.dbms_tts.transport_set_check('TEST', true, true );
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR2> SELECT * FROM sys.TRANSPORT_SET_VIOLATIONS;
VIOLATIONS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Default Composite Partition (Table) Tablespace DATA1 for PAY_CUST_DIM not contained in transportable set
Default Composite Partition (Table) Tablespace DATA1 for PAY_CUST_DIM not contained in transportable set
Default Composite Partition (Table) Tablespace DATA1 for PAY_CUST_DIM not contained in transportable set
Default Composite Partition (Table) Tablespace DATA1 for PAY_CUST_DIM not contained in transportable set
Default Composite Partition (Table) Tablespace DATA1 for PAY_CUST_DIM not contained in transportable set
Default Composite Partition (Table) Tablespace DATA1 for PAY_CUST_DIM not contained in transportable set
Default Composite Partition (Table) Tablespace DATA1 for PAY_CUST_DIM not contained in transportable set
Default Composite Partition (Table) Tablespace DATA1 for PAY_CUST_DIM not contained in transportable set
Default Partition (Table) Tablespace TEST for PAY_CUST_DIM not contained in transportable set
Default Partition (Table) Tablespace TEST for PAY_CUST_DIM not contained in transportable set
Default Partition (Table) Tablespace TEST for PAY_CUST_DIM not contained in transportable set
Default Composite Partition (Table) Tablespace TEST for PAY_CUST_DIM not contained in transportable set
Default Partition (Table) Tablespace DATA1 for PAY_CUST_DIM not contained in transportable set
Default Composite Partition (Table) Tablespace TEST for PAY_CUST_DIM not contained in transportable set
Default Composite Partition (Table) Tablespace TEST for PAY_CUST_DIM not contained in transportable set
Default Composite Partition (Table) Tablespace TEST for PAY_CUST_DIM not contained in transportable set
Default Composite Partition (Table) Tablespace TEST for PAY_CUST_DIM not contained in transportable set
Default Composite Partition (Table) Tablespace TEST for PAY_CUST_DIM not contained in transportable set
Default Partition (Table) Tablespace DATA1 for PAY_CUST_DIM not contained in transportable set
Default Composite Partition (Table) Tablespace TEST for PAY_CUST_DIM not contained in transportable set
Default Composite Partition (Table) Tablespace TEST for PAY_CUST_DIM not contained in transportable set
Default Partition (Table) Tablespace DATA1 for PAY_CUST_DIM not contained in transportable set
22 rows selected.
ops$tkyte@ORA10GR2> alter table pay_cust_dim
2 modify default attributes
3 for partition PAY_CUST_DIM_1
4 tablespace test;
Table altered.
ops$tkyte@ORA10GR2> alter table pay_cust_dim
2 modify default attributes
3 for partition PAY_CUST_DIM_2
4 tablespace test;
Table altered.
ops$tkyte@ORA10GR2> alter table pay_cust_dim
2 modify default attributes
3 for partition PAY_CUST_DIM_3
4 tablespace test;
Table altered.
ops$tkyte@ORA10GR2> exec sys.dbms_tts.transport_set_check('TEST', true, true );
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR2> SELECT * FROM sys.TRANSPORT_SET_VIOLATIONS;
no rows selected
Tom , you are best ... :)
A reader, July 01, 2006 - 10:10 am UTC
Matthew, July 04, 2006 - 10:49 am UTC
Checking on partition tablespace
Ayo, July 24, 2006 - 1:18 pm UTC
Whenever i read the questions and answers from the Ask Tom site, i always leave with more knowledge on the topics that led me there in the first place. Please keep this up as there are many more people learning more by reading the answers to some of the issues raised by other collegues.
Thank you so very much.
Ayo
Partitions
Ayo, July 24, 2006 - 1:24 pm UTC
Can you please explain the circustance under which a composite partition will be advisable, i.e. having a hash partition defined under a range partition.
Thanks.
Ayo
July 24, 2006 - 2:56 pm UTC
you choose to apply partitioning for one of three main reasons:
performance (mostly in warehousing)
ease of admin (maintain smaller bits)
higher availability
so, you use subpartitions of a partition when you want to achieve one of the three for the partition.
that is, "for the same reasons you use single level partitioning of a segment".
The rules don't change, you do it to a partition for the same exact reasons you do it to a segment in the first place.
Code to do this with a script
TylerP, November 09, 2006 - 12:49 pm UTC
I had to move my subpartitions separately, here is a script I wrote to automate-you will need a tablespace called TS_HOLD
create or replace procedure BUILDUP_TABLES(partname in varchar2,tsname in varchar2) is
spname varchar2(100);
tname varchar2(100);
cmd varchar2(1000);
CURSOR sp_cursor
is
SELECT a.subpartition_name,a.table_name
from user_tab_subpartitions a
where a.partition_name = partname;
CURSOR ind_cursor
is
SELECT b.subpartition_name,b.index_name
from user_ind_subpartitions b
where b.partition_name = partname;
begin
open sp_cursor;
loop
fetch sp_cursor into spname,tname;
EXIT WHEN sp_cursor%NOTFOUND;
cmd := 'alter table ' || tname ||' move subpartition ' ||spname||' tablespace TS_HOLD' ;
execute immediate cmd;
end loop;
close sp_cursor;
open sp_cursor;
loop
fetch sp_cursor into spname,tname;
EXIT WHEN sp_cursor%NOTFOUND;
cmd := 'alter table ' || tname ||' move subpartition ' ||spname||' tablespace ' ||tsname;
execute immediate cmd;
end loop;
close sp_cursor;
open ind_cursor;
loop
fetch sp_cursor into spname,tname;
EXIT WHEN ind_cursor%NOTFOUND;
cmd := 'alter index ' || tname ||' rebuild subpartition' ||spname;
execute immediate cmd;
end loop;
close sp_cursor;
Change to script
TylerP, November 09, 2006 - 12:53 pm UTC
Actually, last part needs to be:
close sp_cursor;
open ind_cursor;
loop
fetch ind_cursor into spname,tname;
EXIT WHEN ind_cursor%NOTFOUND;
cmd := 'alter index ' || tname ||' rebuild subpartition' ||spname;
execute immediate cmd;
end loop;
close sp_cursor;
The point of this script is to move a partition out and back to cleanup space. Could just run first part to leave it there.
A reader, February 15, 2007 - 11:23 am UTC
Hi Tom,
Does Oracle (9.2.0.6) locks the table/partition while moving partition on new tablespace?
We have OLTP (24/7) system and DMLS are happening every 10Secs and we need to move the partitions of around 40G of size to their new own tablespaces without outage. I am sure it will take long time. What will happen to the DMLS?
Many Thanks
Girish
February 16, 2007 - 12:35 pm UTC
when you move a segment
a) you can read it - query it
b) you cannot modify it
read about dbms_redefinition
an online redefine tool, it might be more applicable.
Inserts DML - What will happen
Hitesh Bajaj, February 20, 2007 - 2:00 am UTC
Hi Tom,
We can query the partition which is in a moveable state and can't modify the contents of that partition.
But I am presuming that inserts should not fail on that partition or any other partition for that table and should n't get blocked by the DDL statement.
Is my assumption correct? Just Curious?
Many thanks
February 20, 2007 - 9:39 am UTC
why would you presume that? the segment being moved cannot be modified (unless it is an online move of an IOT - index organized table)
Query Response slow
Vikas, March 12, 2007 - 11:06 am UTC
Hi Tom,
We moved one table of 30 partitions to another tablespace having size 105G. But after the move we are facing performance dip impacts surrounding that table whenever it is joined to.
I even analyzed the table but nothing on that fron has helped to get the query response time any faster. We have identified the problem to be a FULL table scan rather that scanning only a few partitions of the table.
The query is listed for your reference:
Before : The query used to give response in 5 mins. and p_get_date is being passed to this query from a PL/SQL procedure, The plan using dbms_xplan.display_cursor shows Pstart and Pstop Columns as Key Key but the Impression table is fully scanned.
Select /*+ PARALLEL(y 16) */ * from Invalid_imp_suspect_data y
Where
y.INSERT_DATE >= p_get_date AND y.INSERT_DATE < p_get_date + 1
AND IMPRESSION_TAG is not null
AND
IMPRESSION_TAG NOT IN
(Select /*+ HASH_AJ PARALLEL(z 16) */ IMPRESSION_TAG from IMPRESSION_DATA z
Where
z.INSERT_DATE >= p_get_date -3 AND
z.Insert_date < p_get_date + 1 AND IMPRESSION_TAG is not null);
After the move :
The query is taking nearly 40 mins, I checked the V$sort_usage and it shows that nearly 102G of space was written by parallel slaves to do a hash join operation. This identifies that the table has not getting the last 3 partitions scanned but the whole table is scanned to get the unwanted rows.
Here are the plan statements
dwuser@ADMW>delete from plan_table;
0 rows deleted.
dwuser@ADMW>explain plan for Select /*+ PARALLEL(y 16) */ * from INVALID_IMP_SUSPECT_DATA y
2 Where
3 y.INSERT_DATE >= to_date('11/03/2007 08:59:59','dd/mm/yyyy hh24:mi:ss') AND y.INSERT_DATE < to_date('11/03/2007 08:59:59','dd/mm/yyyy hh24:mi:ss') + 1 AND IMPRESSION_TAG is not null
4 AND
5 IMPRESSION_TAG NOT IN
6 (Select /*+ HASH_AJ no_index(z IDX_IMPRESSION_DATA_INSERTDT) PARALLEL(z 16) */ IMPRESSION_TAG from IMPRESSION_DATA z
7 Where
8 z.INSERT_DATE >= to_date('11/03/2007 08:59:59','dd/mm/yyyy hh24:mi:ss') -3 AND z.Insert_date < to_date('11/03/2007 08:59:59','dd/mm/yyyy hh24:mi:ss') + 1 AND IMPRESSION_TAG is not null);
Explained.
dwuser@ADMW>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3540720515
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9875 | 887K| 38463 (4)| 00:07:42 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 9875 | 887K| 38463 (4)| 00:07:42 | | | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN RIGHT ANTI | | 9875 | 887K| 38463 (4)| 00:07:42 | | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 1 | 25 | 34914 (4)| 00:06:59 | | | Q1,01 | PCWP | |
| 5 | PX SEND BROADCAST | :TQ10000 | 1 | 25 | 34914 (4)| 00:06:59 | | | Q1,00 | P->P | BROADCAST |
| 6 | PX BLOCK ITERATOR | | 1 | 25 | 34914 (4)| 00:06:59 | 23 | 27 | Q1,00 | PCWC | |
|* 7 | TABLE ACCESS FULL| IMPRESSION_DATA | 1 | 25 | 34914 (4)| 00:06:59 | 23 | 27 | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 9876 | 646K| 3549 (3)| 00:00:43 | 1 | 33 | Q1,01 | PCWC | |
|* 9 | TABLE ACCESS FULL | INVALID_IMP_SUSPECT_DATA | 9876 | 646K| 3549 (3)| 00:00:43 | 1 | 33 | Q1,01 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("IMPRESSION_TAG"="IMPRESSION_TAG")
7 - filter("Z"."INSERT_DATE">=TO_DATE('2007-03-08 08:59:59', 'yyyy-mm-dd hh24:mi:ss') AND "Z"."INSERT_DATE"<TO_DATE('2007-03-12
08:59:59', 'yyyy-mm-dd hh24:mi:ss'))
9 - filter("Y"."INSERT_DATE">=TO_DATE('2007-03-11 08:59:59', 'yyyy-mm-dd hh24:mi:ss') AND "Y"."INSERT_DATE"<TO_DATE('2007-03-12
08:59:59', 'yyyy-mm-dd hh24:mi:ss'))
25 rows selected.
dwuser@ADMW>delete from plan_table;
10 rows deleted.
dwuser@ADMW>explain plan for Select /*+ PARALLEL(y 16) */ * from INVALID_IMP_SUSPECT_DATA y
2 Where
3 y.INSERT_DATE >= to_date('11/03/2007 08:59:59','dd/mm/yyyy hh24:mi:ss') AND y.INSERT_DATE < to_date('11/03/2007 08:59:59','dd/mm/yyyy hh24:mi:ss') + 1 AND IMPRESSION_TAG is not null
4 AND
5 IMPRESSION_TAG NOT IN
6 (Select /*+ HASH_AJ no_index(z IDX_IMPRESSION_DATA_INSERTDT) PARALLEL(z 16) */ IMPRESSION_TAG from IMPRESSION_DATA z
7 Where
8 z.INSERT_DATE >= to_date('11/03/2007 08:59:59','dd/mm/yyyy hh24:mi:ss') -30 AND z.Insert_date < to_date('11/03/2007 08:59:59','dd/mm/yyyy hh24:mi:ss') + 1 AND IMPRESSION_TAG is not null);
Explained.
dwuser@ADMW>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1748923661
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 92 | 167K (4)| 00:33:25 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 92 | 167K (4)| 00:33:25 | | | Q1,02 | P->S | QC (RAND) |
|* 3 | HASH JOIN ANTI BUFFERED| | 1 | 92 | 167K (4)| 00:33:25 | | | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 9876 | 646K| 3549 (3)| 00:00:43 | | | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 9876 | 646K| 3549 (3)| 00:00:43 | | | Q1,00 | P->P | HASH |
| 6 | PX BLOCK ITERATOR | | 9876 | 646K| 3549 (3)| 00:00:43 | 1 | 33 | Q1,00 | PCWC | |
|* 7 | TABLE ACCESS FULL | INVALID_IMP_SUSPECT_DATA | 9876 | 646K| 3549 (3)| 00:00:43 | 1 | 33 | Q1,00 | PCWP | |
| 8 | PX RECEIVE | | 49399 | 1206K| 163K (4)| 00:32:43 | | | Q1,02 | PCWP | |
| 9 | PX SEND HASH | :TQ10001 | 49399 | 1206K| 163K (4)| 00:32:43 | | | Q1,01 | P->P | HASH |
| 10 | PX BLOCK ITERATOR | | 49399 | 1206K| 163K (4)| 00:32:43 | 1 | 27 | Q1,01 | PCWC | |
|* 11 | TABLE ACCESS FULL | IMPRESSION_DATA | 49399 | 1206K| 163K (4)| 00:32:43 | 1 | 27 | Q1,01 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("IMPRESSION_TAG"="IMPRESSION_TAG")
7 - filter("Y"."INSERT_DATE">=TO_DATE('2007-03-11 08:59:59', 'yyyy-mm-dd hh24:mi:ss') AND "Y"."INSERT_DATE"<TO_DATE('2007-03-12
08:59:59', 'yyyy-mm-dd hh24:mi:ss'))
11 - filter("Z"."INSERT_DATE">=TO_DATE('2007-02-09 08:59:59', 'yyyy-mm-dd hh24:mi:ss') AND "Z"."INSERT_DATE"<TO_DATE('2007-03-12
08:59:59', 'yyyy-mm-dd hh24:mi:ss'))
27 rows selected.
What should be done in order to avoid the full scan & rather scan only the 5 partitions.
Please help!
Excellent info on Partitioning
Mike, June 05, 2007 - 11:58 am UTC
For a composite partition (range-hash) oracle allows only moving sub partition since that is the segment.
alter table table_name move subpartition tablespace_name ;
However, after the move is done user_tab_subpartitions shows correct info for the tablespace whereas the user_tab_partitions shows the old tablespace name.
Isn't this wrong? How to fix ?
Thx
Mike
June 06, 2007 - 1:12 pm UTC
user_tab_subpartitions is talking about a real physical "THING"
user_tab_partitions in this case is describing a meta-data object - there are no partitions, just subpartitions for real. Anything at the partition level shows "the default"
you did not modify the default for the partition, you moved a physical segment.
Move an IOT table
Tita, November 11, 2008 - 12:14 am UTC
Hi Tom,
And what about moving an IOT table which is composed of 1000 partitions... Can I alter move each partition online in order to allow DMLs to occur on the table while doing so?.. Can I also do this in nologging mode?
November 11, 2008 - 4:23 pm UTC
You can 'shrink' them online (like a coalesce) if that is the goal:
ops$tkyte%ORA10GR2> CREATE TABLE t
2 (
3 x int primary key,
4 dt date,
5 y varchar2(30)
6 )
7 organization index
8 PARTITION BY RANGE (x)
9 (
10 PARTITION part1 VALUES LESS THAN (10000),
11 PARTITION part2 VALUES LESS THAN (20000),
12 PARTITION part3 VALUES LESS THAN (30000),
13 PARTITION part4 VALUES LESS THAN (40000)
14 )
15 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t
2 select rownum, created, object_name from all_objects where rownum < 40000;
39999 rows created.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> delete from t where mod(x,2) = 0;
19999 rows deleted.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> select blevel, leaf_blocks from user_ind_partitions where partition_name = 'PART1';
BLEVEL LEAF_BLOCKS
---------- -----------
1 48
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values (0,null,null);
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
2 pragma autonomous_transaction;
3 begin
4 execute immediate 'alter table t modify partition part1 shrink space compact';
5 end;
6 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> select blevel, leaf_blocks from user_ind_partitions where partition_name = 'PART1';
BLEVEL LEAF_BLOCKS
---------- -----------
1 27
Tablespace of partition
A reader, December 02, 2008 - 5:39 pm UTC
Tom,
This problem is similar to the one posted by Ajeet above regarding move of sub-partition. I have a table t1 with partitions pt1, pt2. Each of these partitions have two sub-partitions, spt11, spt12 and spt21, spt22. I moved the sub-partitions into tablespace ts_t1.
Now when I check the dba_tab_partitions view, I see two partitions for t1. The subpartition_count=2 for each of these partitions. I also see that under tablespace_name, the tablespace shown is users_data.
My questions are:
a) When I have already moved sub-partitions to ts_t1, why the partitions still show the tablespace as users_data?
b) Is there a way to change this tablespace from users_data to ts_t1?
c) If I do not change the tablespaces to ts_t1 and drop the tablespace users_data, what will happen to table t1? Oracle 10.2.0.3.
Thanks for your patience in explaining the deep inner workings of Oracle.
December 09, 2008 - 10:04 am UTC
I need an example
ops$tkyte%ORA10GR2> CREATE TABLE t
2 (
3 dt date,
4 x int,
5 y varchar2(30)
6 )
7 PARTITION BY RANGE (dt)
8 subpartition by hash(x)
9 subpartition template
10 ( subpartition sp1 tablespace users,
11 subpartition sp2 tablespace users
12 )
13 (
14 PARTITION part1 VALUES LESS THAN (to_date('01-jan-2008','dd-mon-yyyy')),
15 PARTITION part2 VALUES LESS THAN (to_date('01-jan-2009','dd-mon-yyyy'))
16 )
17 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select table_name, partition_name, subpartition_name, tablespace_name
2 from user_tab_subpartitions
3 where table_name = 'T'
4 order by partition_name, subpartition_name
5 /
TABLE PARTI SUBPARTITI TABLESPACE_NAME
----- ----- ---------- ------------------------------
T PART1 PART1_SP1 USERS
T PART1 PART1_SP2 USERS
T PART2 PART2_SP1 USERS
T PART2 PART2_SP2 USERS
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t move subpartition part1_sp1 tablespace test
2 /
Table altered.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select table_name, partition_name, subpartition_name, tablespace_name
2 from user_tab_subpartitions
3 where table_name = 'T'
4 order by partition_name, subpartition_name
5 /
TABLE PARTI SUBPARTITI TABLESPACE_NAME
----- ----- ---------- ------------------------------
T PART1 PART1_SP1 TEST
T PART1 PART1_SP2 USERS
T PART2 PART2_SP1 USERS
T PART2 PART2_SP2 USERS
Tablespace of partition
A reader, December 09, 2008 - 11:40 am UTC
Here is an example which I have created using your example but added the tablespace clause to the table. I am assuming that initially this is how the table was created.
The part which I cannot understand is in bold. As you can see even after moving the subpartitions to users_data, the partition still shows users_idx.
Thanks...
SQL> drop table t purge;
Table dropped.
SQL> CREATE TABLE t
2 (
3 dt date,
4 x int,
5 y varchar2(30)
6 )
7 PARTITION BY RANGE (dt)
8 subpartition by hash(x)
9 subpartition template
10 ( subpartition sp1 tablespace users_idx,
11 subpartition sp2 tablespace users_idx
12 )
13 (
14 PARTITION part1 VALUES LESS THAN (to_date('01-jan-2008','dd-mon-yyyy')),
15 PARTITION part2 VALUES LESS THAN (to_date('01-jan-2009','dd-mon-yyyy'))
16 ) tablespace users_idx;
Table created.
SQL> select table_name, partition_name,tablespace_name from user_tab_partitions where table_name='T';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
---------- -------------- ----------------
T PART1 USERS_IDX
T PART2 USERS_IDX
SQL> select table_name, partition_name, subpartition_name, tablespace_name
2 from user_tab_subpartitions
3 where table_name = 'T'
4 order by partition_name, subpartition_name;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME
---------- -------------- ----------------- ----------------
T PART1 PART1_SP1 USERS_IDX
T PART1 PART1_SP2 USERS_IDX
T PART2 PART2_SP1 USERS_IDX
T PART2 PART2_SP2 USERS_IDX
SQL> alter table t move subpartition part1_sp1 tablespace users_data;
Table altered.
SQL> alter table t move subpartition part1_sp2 tablespace users_data;
Table altered.
SQL> alter table t move subpartition part2_sp1 tablespace users_data;
Table altered.
SQL> alter table t move subpartition part2_sp2 tablespace users_data;
Table altered.
SQL> select table_name, partition_name,tablespace_name from user_tab_partitions where table_name='T';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
---------- -------------- ---------------
T PART1 USERS_IDX
T PART2 USERS_IDX
SQL> select table_name, partition_name, subpartition_name, tablespace_name
2 from user_tab_subpartitions
3 where table_name = 'T'
4 order by partition_name, subpartition_name;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME
---------- -------------- ----------------- ---------------
T PART1 PART1_SP1 USERS_DATA
T PART1 PART1_SP2 USERS_DATA
T PART2 PART2_SP1 USERS_DATA
T PART2 PART2_SP2 USERS_DATA
SQL>
December 09, 2008 - 2:26 pm UTC
because you used a template. You'd have to modify the template.
The partition is a logical construct here. The subpartitions are "real". The partitions have no tablespace really - it has no meaning really. It picked it up from the template.
Tablespace of partition
A reader, December 09, 2008 - 3:58 pm UTC
Here is another example without templates. It also keeps showing the partition in the same tablespace even after the sub-partitions have been moved. I checked and there are no segments allocated to the partition. The segments are allocated to the sub-partitions only.
Even after dropping the tablespace, the partition still keeps showing it. The reason I am following it up is that I want to be on technically sound grounds before dropping the tablespace which shows against the partition (test_tbs1 in this test case). Testing indicates that there is no effect on table data and select/insert/update/delete operations against table T. I still want to confirm if I am on right track.
Thanks again for your time and help on this question.
SQL> CREATE TABLE t
2 (
3 dt date,
4 x varchar2(1),
5 y varchar2(30)
6 )
7 PARTITION BY RANGE (dt)
8 subpartition by list(x)
9 (
10 PARTITION part1 VALUES LESS THAN (to_date('01-jan-2008','dd-mon-yyyy')) tablespace test_tbs1 (subpartition sp11 values ('A'
) tablespace test_tbs1,subpartition sp12 values ('B') tablespace test_tbs1),
11 PARTITION part2 VALUES LESS THAN (to_date('01-jan-2009','dd-mon-yyyy')) tablespace test_tbs1 (subpartition sp21 values ('C'
) tablespace test_tbs1,subpartition sp22 values ('D') tablespace test_tbs1)
12 )
13 /
Table created.
SQL> select table_name, partition_name,tablespace_name from user_tab_partitions where table_name='T';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
---------- -------------- ---------------
T PART1 TEST_TBS1
T PART2 TEST_TBS1
SQL> select table_name, partition_name, subpartition_name, tablespace_name
2 from user_tab_subpartitions
3 where table_name = 'T'
4 order by partition_name, subpartition_name;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME
---------- -------------- ----------------- ---------------
T PART1 SP11 TEST_TBS1
T PART1 SP12 TEST_TBS1
T PART2 SP21 TEST_TBS1
T PART2 SP22 TEST_TBS1
SQL> alter table t move subpartition sp11 tablespace test_tbs2;
Table altered.
SQL> alter table t move subpartition sp12 tablespace test_tbs2;
Table altered.
SQL> alter table t move subpartition sp21 tablespace test_tbs2;
Table altered.
SQL> alter table t move subpartition sp22 tablespace test_tbs2;
Table altered.
SQL> select table_name, partition_name,tablespace_name from user_tab_partitions where table_name='T';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
---------- -------------- ---------------
T PART1 TEST_TBS1
T PART2 TEST_TBS1
SQL> select table_name, partition_name, subpartition_name, tablespace_name
2 from user_tab_subpartitions
3 where table_name = 'T'
4 order by partition_name, subpartition_name;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME
---------- -------------- ----------------- ---------------
T PART1 SP11 TEST_TBS2
T PART1 SP12 TEST_TBS2
T PART2 SP21 TEST_TBS2
T PART2 SP22 TEST_TBS2
SQL> drop tablespace test_tbs1 including contents and datafiles;
Tablespace dropped.
SQL> select table_name, partition_name,tablespace_name from user_tab_partitions where table_name='T';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
---------- -------------- ---------------
T PART1 TEST_TBS1
T PART2 TEST_TBS1
SQL> select table_name, partition_name, subpartition_name, tablespace_name
2 from user_tab_subpartitions
3 where table_name = 'T'
4 order by partition_name, subpartition_name;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME
---------- -------------- ----------------- ---------------
T PART1 SP11 TEST_TBS2
T PART1 SP12 TEST_TBS2
T PART2 SP21 TEST_TBS2
T PART2 SP22 TEST_TBS2
SQL>
December 09, 2008 - 4:36 pm UTC
it is part of the 'template', the default attributes.
You do realize the partition 'has no tablespace' really, the partitions 'do not physically exist' and things that do not physically exist, do not consume storage, do not have a tablespace. That is the default tablespace for that partition - if you were to do something to the underlying structure - anything that needed to inherit an attribute from the partition would get it from there. It is a 'default'.
Just like moving a table for a user from their default tablespace to a non-default tablespace would NOT alter the users default tablespace... moving some subpartitions doesn't change the default you specified (or implied) at create time.
You have to alter them.
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14231/partiti.htm#sthref2703 specifically
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14231/partiti.htm#i1007780 ops$tkyte%ORA11GR1> CREATE TABLE t
2 (
3 dt date,
4 x varchar2(1),
5 y varchar2(30)
6 )
7 PARTITION BY RANGE (dt)
8 subpartition by list(x)
9 (
10 PARTITION part1 VALUES LESS THAN
11 (to_date('01-jan-2008','dd-mon-yyyy')) tablespace users
12 (subpartition sp11 values ('A') tablespace users,
13 subpartition sp12 values ('B') tablespace users),
14 PARTITION part2 VALUES LESS THAN
15 (to_date('01-jan-2009','dd-mon-yyyy')) tablespace users
16 (subpartition sp21 values ('C') tablespace users,
17 subpartition sp22 values ('D') tablespace users)
18 )
19 /
Table created.
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select partition_name, tablespace_name from user_tab_partitions where table_name = 'T';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
PART1 USERS
PART2 USERS
ops$tkyte%ORA11GR1> alter table t modify default attributes for partition part1 tablespace tools;
Table altered.
ops$tkyte%ORA11GR1> select partition_name, tablespace_name from user_tab_partitions where table_name = 'T';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
PART1 TOOLS
PART2 USERS
note: no data was moved, this is just changing the *default* that would be used whenever it would be used if you don't specify something else at that time.
Tablespace of partition
A reader, December 09, 2008 - 4:06 pm UTC
Thanks so much Tom!! This perfectly clears the doubt. Sometimes reading through the documentation, I can't see the trees from the forest (or vice-versa whatever is correct)...
Tariq From Bahrain
Tariq, February 14, 2009 - 3:55 pm UTC
Hi Tom,
I Create Partitions every month in one of my production database table 9.2.0.7
4 partition for 1 month in a table.
Every 8th, 16th, 24th and 1 of new month (weekly basis)
Current month is Feb,09 and by mistake I create the parition 8th March, 16th marach, 24th and 1 April
As per my plan my table should be on 2nd partition but still on first.
Now records are growing day by day.
NOw i want to modify the parition with the Feb Month.
I need the command with exmple for modify partition.
Current Partitions as follow:
ALTER TABLE PPCALLS_HIST ADD PARTITION feb2008_01 VALUES LESS THAN (TO_DATE ('2009-Mar-08','YYYY-MON-DD')) TABLESPACE HISTDATA_OCT;
ALTER TABLE PPCALLS_HIST ADD PARTITION feb2008_02 VALUES LESS THAN (TO_DATE ('2009-Mar-16','YYYY-MON-DD')) TABLESPACE HISTDATA_OCT;
ALTER TABLE PPCALLS_HIST ADD PARTITION feb2008_03 VALUES LESS THAN (TO_DATE ('2009-Mar-24','YYYY-MON-DD')) TABLESPACE HISTDATA_OCT;
ALTER TABLE PPCALLS_HIST ADD PARTITION feb2008_04 VALUES LESS THAN (TO_DATE ('2009-Apr-01','YYYY-MON-DD')) TABLESPACE HISTDATA_OCT;
Please do the needful ASAP.
Tariq Bahrain
Tariq Bahrain, February 16, 2009 - 3:08 am UTC
Dear Tom,
I need your assistance for the above mentioned questions regarding Partition.
Please do the needful
Regards,
February 16, 2009 - 12:28 pm UTC
let's see....
You ask a question on a saturday..... And you expect me to respond to you before 3am on MONDAY (my time, times posted on the site are in my timezone)....
How about you give me your phone number and I'll call you with the answer in the middle of your night....
geez.
Tariq - Bahrain
Tariq Bahrain, February 17, 2009 - 3:09 am UTC
Thank you very much for the assistance.
I am sorry for that.
Can you please tell me!
+can we do this online when users are performing DML Operations or we should take downtime for this activity.
Regards,
February 17, 2009 - 8:39 am UTC
you will not be doing DML on the partitions you are splitting - they will not be available - but everything else can be
Tariq Bahrain
Tariq, February 17, 2009 - 3:01 pm UTC
Dear Toam,
Thank you very much for everything.
We started this activity and take 4 hrs from Management to perform this activity but we didn't completed this activty during the time period.
its first partition took more thn 3hrs and 30 minutes to split.
Total size of the partition was 44.5GB
We will plan it to continue this activity later.
During this activity lots of Arch files generates, temp increased on max stage 32G both files, Tablespace grow twice.
Please suggest more to continue this activity.
Regards,
February 17, 2009 - 4:21 pm UTC
you need to read about
a) nonlogged operations, you can avoid the redo if you want (but you NEED to understand what that means, basically: backing up of the affected datafiles is paramount before you let users modify the data again, else their changes could be lost)
b) parallel
and you might want to split them into another tablespace - empty out the one you have and then remove it.
Tariq Bahrainn
Tariq, February 19, 2009 - 1:55 am UTC
Hi Tom,
Yes I understand! I had tried this thing on test-database because you know its too critical.
Thank you very much for the assistance.
Is it possible to exchange this partition to other new_table partition and later on we can move it into the old_table with our requirement?
what will be the effect of space?
what will be the effect of temp?
what will be the effect of actual tablespace where (old and new table exist)
Current Sitition of the table is follow:
-----------------------------------------
SEGMENT_NAME PARTITION_NAME GB
--------------- ------------------------------ ----------
PPCALLS_HIST NOV2008_01 17.1679688
PPCALLS_HIST NOV2008_02 20.625
PPCALLS_HIST NOV2008_03 19.6875
PPCALLS_HIST NOV2008_04 17.3242188
PPCALLS_HIST DEC2008_01 17.4609375
PPCALLS_HIST DEC2008_02 21.3085938
PPCALLS_HIST DEC2008_03 19.5703125
PPCALLS_HIST DEC2008_04 22.34375
PPCALLS_HIST JAN2009_01 18.6328125
PPCALLS_HIST JAN2009_02 20.6835938
PPCALLS_HIST JAN2009_03 20.390625
PPCALLS_HIST JAN2009_04 20.859375
PPCALLS_HIST FEB2009_01 48.5546875
Regards,
Tariq Bahrain
Tariq, February 20, 2009 - 8:32 am UTC
Dear Tom,
Please advice: (In my case regardin Exchange Partition)
I have to perform the following steps
In my case:
step-1: PPCALLS:FEB2009_01 (PARTITION TABLE)-->convert NON-PARTITION TABLE
step-2: we must remove PPCALLS:FEB2009_01 partition then
create new four partition inside (with feb 8, 16, 24 & 1 of next month)
step-3: Then Execute Insert statment from Non-Partition table to actual table
Regards,
February 21, 2009 - 9:03 pm UTC
you could
a) just leave it be and don't worry about it - that is an option. do you *really* need to repartition this data? do you? really - really and truly. Or would it be ok to leave this one and just make sure the next partitions are done correctly.
b) create a new table with N partitions. insert /*+ APPEND */ into this new partition the data from the big partition (spreading it out into N partitions as you like). index it (the new table) drop the big partition from the existing table. alter the existing table to add the N empty partitions. create an empty table that looks like these partitions and index it accordingly. take the new table and exchange the first partition with empty table, take now NOT empty table and exchange with first empty partition in old table. Keep doing that.
If you like - that will result in the least movement of data.
I would opt for a) you could probably live with this one oversized partition, it is not outrageously huge.
changing initial extent clause of sub-partition
pratik, September 15, 2009 - 6:23 pm UTC
Hi Tom,
Can we change the initial extent clause of sub-partitioned table? we have one table with 11 partitions(LIST partition) and 16 sub-partitions(HASH) in each partition(Total 176 sub-partition). The INITIAL extent is 128 MB so even if table is empty, it takes 22GB. The tablespace initial extent is 4M.
Thanks,
Pratik
September 16, 2009 - 9:34 am UTC
you should have used system allocated exents - setting initial, next, pctincrease or using a uniform extent size is so 20th century....
If you used system allocated extents - you would have 64kb initial extents...
So, it sounds like you are using a locally managed tablespace with 4m uniform extents (guessing, you don't tell us). Assuming that ASSM (automatic segment space management) is being used - then:
ops$tkyte%ORA10GR2> CREATE TABLE t
2 (
3 dt date,
4 x int,
5 y varchar2(30)
6 )
7 PARTITION BY RANGE (dt)
8 subpartition by hash(x) subpartitions 4
9 (
10 PARTITION part1 VALUES LESS THAN (to_date('01-jan-2008','dd-mon-yyyy')) ,
11 PARTITION part2 VALUES LESS THAN (to_date('01-jan-2009','dd-mon-yyyy'))
12 )
13 storage ( initial 128k )
14 /
Table created.
ops$tkyte%ORA10GR2> column partition_name new_value x
ops$tkyte%ORA10GR2> alter table t enable row movement;
Table altered.
ops$tkyte%ORA10GR2> select partition_name, extent_id, bytes
2 from user_extents
3 where segment_name = 'T'
4 order by partition_name, extent_id;
PARTITION_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
SYS_SUBP2143 0 65536
SYS_SUBP2143 1 65536
SYS_SUBP2144 0 65536
SYS_SUBP2144 1 65536
SYS_SUBP2145 0 65536
SYS_SUBP2145 1 65536
SYS_SUBP2146 0 65536
SYS_SUBP2146 1 65536
SYS_SUBP2147 0 65536
SYS_SUBP2147 1 65536
SYS_SUBP2148 0 65536
SYS_SUBP2148 1 65536
SYS_SUBP2149 0 65536
SYS_SUBP2149 1 65536
SYS_SUBP2150 0 65536
SYS_SUBP2150 1 65536
16 rows selected.
ops$tkyte%ORA10GR2> alter table t modify subpartition &x shrink space;
old 1: alter table t modify subpartition &x shrink space
new 1: alter table t modify subpartition SYS_SUBP2150 shrink space
Table altered.
ops$tkyte%ORA10GR2> select partition_name, extent_id, bytes
2 from user_extents
3 where segment_name = 'T'
4 and partition_name = '&X'
5 order by partition_name, extent_id;
old 4: and partition_name = '&X'
new 4: and partition_name = 'SYS_SUBP2150'
PARTITION_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
SYS_SUBP2150 0 65536
shrink space will work, else - if not ASSM or not locally managed, then:
ops$tkyte%ORA10GR2> select *
2 from (
3 select partition_name, extent_id, bytes
4 from user_extents
5 where segment_name = 'T'
6 and partition_name <> '&X'
7 order by partition_name, extent_id
8 )
9 where rownum <= 2;
old 6: and partition_name <> '&X'
new 6: and partition_name <> 'SYS_SUBP2326'
PARTITION_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
SYS_SUBP2319 0 65536
SYS_SUBP2319 1 65536
ops$tkyte%ORA10GR2> CREATE TABLE temp
2 (
3 dt date,
4 x int,
5 y varchar2(30)
6 )
7 storage ( initial 1k );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t
2 exchange subpartition &x
3 with table temp;
old 2: exchange subpartition &x
new 2: exchange subpartition SYS_SUBP2319
Table altered.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select *
2 from (
3 select partition_name, extent_id, bytes
4 from user_extents
5 where segment_name = 'T'
6 and partition_name = '&X'
7 order by partition_name, extent_id
8 )
9 where rownum <= 2;
old 6: and partition_name = '&X'
new 6: and partition_name = 'SYS_SUBP2319'
PARTITION_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
SYS_SUBP2319 0 65536
but I'd suggest considering a rebuild of the table with proper storage parameters (eg: none of them) in a locally managed tablespace with system allocated extents.
A reader, September 16, 2009 - 10:37 am UTC
Excellent Reply :)
changing initial extent clause of sub-partition
pratik, September 16, 2009 - 6:02 pm UTC
Hi Tom,
Actually there are some standard set at enterprise level in my company. We have tablespaces with 4M and 128M extent size only. Initially the table was in 128M extent tablespace with initial and next extent = 128M. Now we need to move it to 4M extent tablespace with initial and next allocated extent to be 4M only.
September 16, 2009 - 7:53 pm UTC
... Actually there are some standard set at enterprise level in my company. ...
they need to RETHINK (or, just for the first time - THINK) about that "standard"
that is beyond "not smart". I cannot image an intelligent discussion being had that would make this standard a 'good idea'
what a waste of time, space, resources.
move table into a new tablespace with different block size
Mato Lovrak, January 13, 2010 - 1:27 am UTC
Hi Tom,
What is the best way to move range partition table from one tablespace with 8K block size, to another with 16K block size? It’s about Data Warehouse fact table.
I know two solutions:
-to create identical partition empty table in a new tablespace, and to insert all records from the original table into a new one, drop original table, rename new table and recreate all constraints and local indexes
-to export original table with expdp utility, drop original table and finally to import table with remap option into new tablespace with 16K block size. In that case, since the statistics will also be imported (what is wrong because the statistics won’t be the same in the 16K tablespace), I need to collect the statistics at the end,
Regards,
Mato
January 18, 2010 - 4:00 pm UTC
neither of those options should be used.
alter table t move tablespace new_tablespace_with16k_blocks;
or, for a partitioned table, move each partition.
you'll still have some of the statistics issues after a reorg, no matter what approach you use.
data transform and partition movement
A reader, March 05, 2011 - 11:38 am UTC
Assume you want to move huge amount of data from schema A to new schema B in the same DB where schema B is refactored from schema A. Obviously you would need some form of data transformation before populating schema B. Assume also that schema B is being populated from other sources as well in addition to data movement from schema A.
Can you still use partition exchange approach here given the need for data manipulation in the transit? How would you handle primary key regeneration and such integrity issues?
March 07, 2011 - 9:51 am UTC
Your transformation would likely be best dealt with via CREATE TABLE AS SELECT to query the data from A into B and then index it and exchange it in.
A reader, March 08, 2011 - 2:30 pm UTC
alter table move partition tablespace name
moves the partition to another tablespace
March 08, 2011 - 3:21 pm UTC
yeah, that's what we said many years ago?
tkyte@TKYTE816> alter table partitioned move partition part_3 tablespace users;
Table altered.
data transform and partition movement
A reader, March 10, 2011 - 12:43 am UTC
follow-up on your CTAS based response above:
you mean use CTAS to create the temporary destination table structure with data and then perform the partition exchange?
How would you handle the primary key regeneration part. This destination table now has two sessions feeding the data- partition exchange and the other regular source.
March 10, 2011 - 10:29 am UTC
this is a rather large page.
"follow-up on your CTAS based response above" is rather vague.
I'll assume this is in reference to a March 5th 2011 comment?
If the primary key is part of the partition key - then the primary key index would be a local index and it is all done for you - you would already have it indexed.
If the primary key is NOT part of the partition key - then the primary key would have to be globally indexed. You could EITHER
a) exchange in, invalidate the index, rebuild it entirely
b) exchange in while maintaining the global indexes - no invalidation, takes longer but data is always available (so the only one that knows it takes longer is you - the end users continue life as normal)
data movement
A reader, March 12, 2011 - 7:46 pm UTC
How about using dbms_redefinition instead of partition xchange to move the huge amount of data between schemas in the same DB? Because the structure of the source and target tables are different and there is also some form of data transformation.
March 14, 2011 - 7:45 am UTC
if your structures are different - we wouldn't even be considering a partition exchange.
I wouldn't bother with dbms_redefinition for this, that is for an ONLINE reorganization of an existing table - resulting in a "new existing table".
You would just use create table as select for this operation - if the data needs to change schema's and be transformed, just create table as select.
bulk data loading
A reader, March 21, 2011 - 11:46 pm UTC
Just to clarify:
Can you not perform a partition exchange when table structure is different? Meaning the target table is just a refactored one.
Or did you mean, table structure can be different but partition scheme must remain same to perform partition exchange? I am trying to see the criteria here.
Coming to dbms_redefinition, why isn't it a viable option to move data across? Say, you refactored T1 into T2 in a new release. Why can't I redefine T1 (in schema A) into T2 (in schema B)? Isn't it just renaming the table without moving the data at all? Only question is if dbms_redefinition supports table redefinition between schemas.
I guess if you already have an empty target schema created, CTAS may not accomplish the goal as it tries create the same table again.
March 22, 2011 - 7:25 am UTC
the structures must be identical - think about this please. Can you imagine a table where just a bit piece of the table has a varchar2(20) column whereas the rest has a number? Or a column that exists in all of the table EXCEPT for a small slice? It would not even begin to make sense.
In order to do a partition exchange - there is just ONE partitioning scheme by definition. You are exchanging an existing partition with an existing non-partitioned table.
You can use dbms_redefinition to reorganize and transform data, yes, but that is NOT AT ALL what you asked before - not even close - or at least is was not very clear at all. You were talking about using dbms_redefinition instead of partition exchange.
I don't know what part of this very very large page you are referring to or what problem you are trying to solve.
But in general dbms_redefinition cannot be used in place of exchange partition. dbms redefinition redefines an entire table if you are using transformations.
A reader, March 22, 2011 - 3:24 pm UTC
My questions are in reference to huge data migration in less amount of time (may be as part of upgrade e.g.).
i.e. Data move from SchemaA.TA to SchemaB.TB where TB is an empty table that is re-factored from TA. Both schemas exist in the same DB.
I was thinking about just redefining TA to match the definition of TB with some data transform and mapping. But looks like dbms_redefinition doesn't seem to support redefinition across schemas by looking at those APIs.
Was also exploring partition exchange and CTAS options as well.
March 23, 2011 - 8:13 am UTC
to do this online you can:
a) put a materialized view log on TA
b) create the table TB in B
c) create a materialized view TB "on prebuilt table" as select ..... from A.TA
d) keep them in sync with dbms_mview.refresh
e) when ready to cut over, you can stop people from accessing A, do a final refresh, drop the materialized view TB - which will LEAVE the table since it was a prebuilt table - and you are done.
A reader, March 24, 2011 - 11:28 pm UTC
The stuff you mentioned is not using online redef. Does dbms_redefinition supports schema1.tbl1 to schema2.tbl2 type redefs and schema1.tblx to {schema2.tbly and schema2.tblz) type redef?
March 25, 2011 - 10:57 am UTC
dbms redefinition is designed to do an online re-organization of a table in a single schema.
And dbms_redefinition has as a goal to have but one table left when you are done. I didn't read anywhere that the original table was to disappear.
A reader, March 31, 2011 - 12:03 am UTC
Can you map table tA to tB and tC using dbms redefinition? Looks like not.
What is the quickest way to move a table with billion rows from schema1 to schema2 in the same database?
April 12, 2011 - 10:01 am UTC
if you mean 'split a table into two tables' - no, not using dbms_redefinition, but using materialized views on prebuilt tables - sure.
I already told you how to do that above, that is the same method dbms_refenition uses basically - materialized views under the covers.
The quickest way to do something is to not do it. So, the quickest way is a view or a synonym in schema schema 2 that points to the data which remains in schema 1.
If you must move it, then your choices are basically:
a) create table as select - offline, no updates while this happens.
b) materialized views on prebuilt tables, as outlined above.
A reader, April 24, 2011 - 3:15 pm UTC
In a data migration scenario between 2 schemas in the same DB using Insert Append, is there a way to pre-determine the table space requirements to fail-fast rather than failing after 24 hours due to space issues?
April 25, 2011 - 11:47 am UTC
you could use resumable operations which allows you to have the insert stop and wait for more space:
http://docs.oracle.com/docs/cd/E11882_01/server.112/e17120/schema002.htm#ADMIN11582 other than that, you would basically look at the size of the segment you are copying and ensure you have that much space free, while the new table might be larger - it is a rare case that that happens.
The goal: COMPRESS partitions online on tables with global indexes
Adrian Angelov, June 29, 2012 - 7:17 am UTC
Hi Tom,
thanks for all the valuable content in this thread.
I have a question about compression of partitions without downtime on tables with global indexes. We use 11.2.0.2.3 on Linux x86_64.
We have a range partitioned table with partitions for the past 10 years(1 partition per year: P2002 .... P2012).
The application uses them in such a way that there are :
- many DMLs that use the last partition P2012
- almost no DMLs on partitions P2002-P2011
- many SELECTs on all partitions P2002-P2011,P2012
Up to now, I've tested :
- ALTER TABLE ... MOVE PARTITION .... COMPRESS UPDATE INDEXES; but this is not 100% percent ONLINE since it makes local indexes unusable for 'short' periods of time. It is documented on Metalink : Local Index UNUSABLE for Short Period in Alter Table Move Partition [ID 1374199.1]
There is another problem with this approach - we hit bug 13827402 one day after the compression took place and this led to 8 minutes of downtime of our application.
- DBMS_REDEFINITION using interim table that was created with COMPRESS option. In this case, when global indexes are present, during the execution of DBMS_REDEFINITION.FINISH_REDEF_TABLE exclusive lock is held on the whole table and due to the global indexes, the execution of it is slow compared to when there are no global indexes. In our case, the execution for one partition takes ~2-3 minutes, which means that all DMLs and SELECTs are blocked during this period.
I've tried to document the above on OTN forums too:
https://forums.oracle.com/forums/thread.jspa?messageID=10388458� I can provide test cases too.
My question is : how to compress partitions online for range-partitioned table with global indexes.
Thank You
June 29, 2012 - 11:52 am UTC
what copy
create a compressed table as select from that one partition
index it to get the local indexes
alter exchange it in update indexes
drop the old uncompressed data
The goal: COMPRESS partitions online on tables with global indexes
Adrian Angelov, July 02, 2012 - 3:41 am UTC
Thanks for the provided steps, Tom.
But this is not online( by online, I mean that there is no major slowdown or disruption of all application sessions in the database during the maintenance).
During the execution of: ALTER TABLE ... EXCHANGE PARTITION ... with TABLE ... UPDATE INDEXES;
all sessions that try to execute DMLs on the currently exchanged partition will be blocked and wait for 'enq: TM - contention'.
Thank You
July 02, 2012 - 8:14 am UTC
Only in the partition you are compressing - if you want to do a reorganization - you need a direct path operation (serial) OR you need the advanced compression option in 11gr1 and above.
Asad, October 30, 2012 - 5:37 am UTC
Very helpful!!
When the data of the partition is stored in the segments of the sub-partitions then we should not specify tablespace for the subpartitions, as subpartition would get the tablespace of the owing partition.
October 30, 2012 - 7:02 pm UTC
depends on what you want to have happen!
Taiwo Adedeji, February 20, 2013 - 8:38 am UTC
The "alter table xyz partition p_xyz tablespace xyz" command was very useful to me in helping me solve space reclamation challenge i had. Many thanks Tom.
RobertoMG, August 02, 2013 - 4:05 pm UTC
Hi Tom,
I want to understand perfectly how MOVE PARTITION works:
If I execute MOVE PARTITION in a partition for move it to another tablespace, how the operation work:
Option 1) It's copied the data to the other tablespace and after that, it's deleted from origin?
Option 2) It's really moved the data?
Resume: For move a partition between tablespaces, should I have the double of space of that partition?
August 02, 2013 - 7:41 pm UTC
it'll copy, drop the old segment, and point to the new segment.
you need double the space, just like a mv command would (across file systems anyway...)
Updating tablespace_name at table level
Andrea, January 29, 2014 - 3:15 pm UTC
This thread was very useful, I successfully moved partitions and sub_partitions to new tablespaces, however I can't find out how to show the correct tablespace_name at the table level:
alter index PK_T_ABTR_LEBEN modify default attributes tablespace TBS_TA_DL_AR_CDB_PV -- Statement executed
select index_name, tablespace_name from dba_indexes where index_name = 'PK_T_ABTR_LEBEN' -- tablespace_name is still Null
The same thing happens for indexes: everything looks fine at partition and sub_partition levels, but not at index level (on dba_indexes).
online move partition in 12c (12.1.0.2)
Rajeshwaran, Jeyabal, November 25, 2016 - 1:55 pm UTC
Team,
Preparing for a local user group on online operation in 12c.
Tried to move a partition online from one tablespace to another - but ended up with errors and data loss - please suggest (I am on 12c 12.1.0.2 on linux)
http://docs.oracle.com/database/121/SQLRF/statements_3001.htm#CJACIBIA
demo@ORA12C> drop table t purge;
Table dropped.
demo@ORA12C> column segment_name format a10
demo@ORA12C> column partition_name format a10
demo@ORA12C> column segment_type format a20
demo@ORA12C> column tablespace_name format a20
demo@ORA12C> create table t (x int, y int)
2 partition by range(x)
3 ( partition p1 values less than(6),
4 partition p2 values less than(11) ) ;
Table created.
demo@ORA12C>
demo@ORA12C> insert into t(x,y)
2 select rownum, rownum
3 from all_users
4 where rownum <=10;
10 rows created.
demo@ORA12C>
demo@ORA12C> select * from t partition(p1);
X Y
---------- ----------
1 1
2 2
3 3
4 4
5 5
5 rows selected.
demo@ORA12C>
demo@ORA12C> select segment_name,partition_name,
2 segment_type,tablespace_name
3 from user_segments
4 where segment_name ='T';
SEGMENT_NA PARTITION_ SEGMENT_TYPE TABLESPACE_NAME
---------- ---------- -------------------- --------------------
T P1 TABLE PARTITION DATA_12C_NONCDB
T P2 TABLE PARTITION DATA_12C_NONCDB
2 rows selected.
demo@ORA12C>
demo@ORA12C> declare
2 pragma autonomous_transaction;
3 begin
4 execute immediate ' alter table t move partition p1 tablespace users online ';
5 rollback;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-06512: at line 4
demo@ORA12C> select segment_name,partition_name,
2 segment_type,tablespace_name
3 from user_segments
4 where segment_name ='T';
SEGMENT_NA PARTITION_ SEGMENT_TYPE TABLESPACE_NAME
---------- ---------- -------------------- --------------------
T SYS_P21209 TABLE PARTITION DATA_12C_NONCDB
T P2 TABLE PARTITION DATA_12C_NONCDB
T P1 TABLE PARTITION USERS
3 rows selected.
demo@ORA12C> select * from t partition(p1);
no rows selected
demo@ORA12C> select * from t partition(SYS_P21209);
select * from t partition(SYS_P21209)
*
ERROR at line 1:
ORA-02149: Specified partition does not exist
demo@ORA12C> select * from t ;
X Y
---------- ----------
6 6
7 7
8 8
9 9
10 10
5 rows selected.
demo@ORA12C>
November 26, 2016 - 3:15 am UTC
An autonomous transaction is *not* the same as doing it in a separate session, eg
-- session 1
SQL> column segment_name format a10
SQL> column partition_name format a10
SQL> column segment_type format a20
SQL> column tablespace_name format a20
SQL> create table t (x int, y int)
2 partition by range(x)
3 ( partition p1 values less than(6),
4 partition p2 values less than(11)
5 ) tablespace LARGE_TS;
Table created.
SQL>
SQL> insert into t(x,y)
2 select rownum, rownum
3 from all_users
4 where rownum <=10;
10 rows created.
SQL>
SQL> select * from t partition(p1);
X Y
---------- ----------
1 1
2 2
3 3
4 4
5 5
SQL>
SQL> select segment_name,partition_name,
2 segment_type,tablespace_name
3 from user_segments
4 where segment_name ='T';
SEGMENT_NA PARTITION_ SEGMENT_TYPE TABLESPACE_NAME
---------- ---------- -------------------- --------------------
T P1 TABLE PARTITION LARGE_TS
T P2 TABLE PARTITION LARGE_TS
SQL>
-- session 2
SQL> alter table t move partition p1 tablespace users online;
[it waited until I did commit in session 1, and then]
Table altered.
SQL> column segment_name format a10
SQL> column partition_name format a10
SQL> column segment_type format a20
SQL> column tablespace_name format a20
SQL> select segment_name,partition_name,
2 segment_type,tablespace_name
3 from user_segments
4 where segment_name ='T';
SEGMENT_NA PARTITION_ SEGMENT_TYPE TABLESPACE_NAME
---------- ---------- -------------------- --------------------
T P1 TABLE PARTITION USERS
T P2 TABLE PARTITION LARGE_TS
SQL>
but I agree - we should not leave the table in a corrupted state.
ORA-02149: Specified partition does not exist during GATHER TABLE STATS
A reader, November 03, 2021 - 5:54 am UTC
Greetings!
Database version - 12.1.0.2
SQL> SQL> SQL> SQL> SQL> SQL> set echo on timing on
SQL> exec dbms_stats.gather_table_stats(ownname => 'EPRICE', tabname => 'FIXED_INC_PRC', degree => 16);
BEGIN dbms_stats.gather_table_stats(ownname => 'EPRICE', tabname => 'FIXED_INC_PRC', degree => 16); END;
*
ERROR at line 1:
ORA-02149: Specified partition does not exist
ORA-06512: at "SYS.DBMS_STATS", line 35235
ORA-06512: at line 1
Elapsed: 00:00:50.61
SQL>
I keep getting this error while gathering stats on this partition table since last few days. This is composite partition table. One change on my side was , I was trying to move subpartition from one tablespace to another tablespace ONLINE and it was waiting/hanging for minutes and then decided to kill the move subpartition session. Not sure if that is causing the issue.
Note that, the data in the subpartition are not corrupted and i am able to query the data.
Thanks for your time.
November 09, 2021 - 4:07 am UTC
If that is repeatable in serial, then I would do the following:
exec dbms_monitor.session_trace_enable(binds=>true,waits=>true)
exec dbms_stats.gather_table_stats(ownname => 'EPRICE', tabname => 'FIXED_INC_PRC');
and then log an SR with that trace file, because it sounds like a corruption of some sort