Truncate Partition

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 107
Joined: Tue, 15 Nov 2011 8:42 am

Gentlemen, I have a problem. I excote the command below to truncate the partition but it does not truncate.
Does not error, but also do not truncate. Can someone help me?

ALTER TABLE TB1 Truncate Partition PMIL;
User avatar
stcoutinho
Moderador
Moderador
Posts: 850
Joined: Wed, 11 May 2011 5:15 pm
Location: são Paulo - SP

Ola Cleberz,

The Oracle documentation http://docs.oracle.com/cd/E11882_01/ser ... m#i1008226) Ugere Some command syntaxes to truncate a partition:

a) Truncation and rebuild indicates (to which you tried):

Select all

ALTER TABLE B1 TRUNCATE PARTITION PMIL; 
ALTER INDEX <indices-globais_B1>  REBUILD;
b) Clean and then truncate:

Select all

DELETE FROM B1 PARTITION (PMIL); 
ALTER TABLE B1 TRUNCATE PARTITION PMIL;
c) truncate and update the index at the same time:

Select all

ALTER TABLE B1 TRUNCATE PARTITION PMIL UPDATE INDEXES;
You could try to test with the "B" procedure, to see if you can clean this table.

One point: Verify that this partitioned table displays sub-particles. In this case, maybe the procedure has to be another.

Hugs,

Sergio Coutinho
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 107
Joined: Tue, 15 Nov 2011 8:42 am

Dude, it's too weird. I performed the command yesterday and yesterday the data were there. Today the data was erased to that partition.

Today I truncated another partition and tb did not give a mistake, runs successfully but the data remain there. I'll have to wait for tomorrow to know if the data were deleted? This is wrong, of course!

I did the way you mentioned above and tb did not work, the data remain there, and look that I performed the command with User SYS.
User avatar
stcoutinho
Moderador
Moderador
Posts: 850
Joined: Wed, 11 May 2011 5:15 pm
Location: são Paulo - SP

Huuum,

Strange even .. Have you made these commands and select in the same SQL session?

Do you know the database version? If it is an old version, do you know if the tablespace where it is located locally or by data dictionary?

And only by disengagement, could you run "option B" by adding a Commit between Delete and Alter Table option? I know this does not explain your problem .. but I just wanted to make sure that the data will disappear.

Hugs,

Sergio Coutinho
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 107
Joined: Tue, 15 Nov 2011 8:42 am

The bank is 11g
had already placed Commit.

I do not know how to solve this.
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 107
Joined: Tue, 15 Nov 2011 8:42 am

This command does not work here.
I created another partitioned table, I loaded the table and then I executed the command below again:

Select all

ALTER TABLE TB1 TRUNCATE PARTITION PMIL;

Nothing happens, the Partition data contained there.

Is there anything that has to be enabled on the bank for this command to work?
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 107
Joined: Tue, 15 Nov 2011 8:42 am

This table has substitution, does it have something to do?
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 107
Joined: Tue, 15 Nov 2011 8:42 am

I solved the problem by placing Update Indexes at the end of the command.
I do not really know if the problem was the lack of this command, but it worked and solved my problem.

Thanks for the attention guys!
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 2 guests