Home » RDBMS Server » Server Administration » Data Purge
Data Purge [message #268115] Mon, 17 September 2007 10:24 Go to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
Hi,
I'm going to create the following new table :

CREATE TABLE ETM.EXC_LOG (
EXC_ID NUMBER(15) NOT NULL,
EXC_STATUS CHAR(1) NOT NULL,
TAX_ID VARCHAR2(44) NULL,
CR_USR_ID VARCHAR2(30) NOT NULL,
CR_DT DATE NOT NULL,
UPDATE_USR_ID VARCHAR2(30) NOT NULL,
UPDATE_DT DATE NOT NULL,
GSM_TXT CLOB NULL
);

At any point of time I wont have more than 50 Records in this table and I want to purge this table and delete records older than 30days.
Can anyone tell me what's the best practice to purge these kinda tables? Is it good to have a purge procedure to delete the records or to partition this table?

Thanks,
GK
Re: Data Purge [message #268119 is a reply to message #268115] Mon, 17 September 2007 10:28 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
How much Record expected in the future.
Use query for data deletion.
Quote:
s or to partition this table?


WHY????????????only for 50 records?
Re: Data Purge [message #268121 is a reply to message #268115] Mon, 17 September 2007 10:33 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
I will have to delete records older than 30days, so i want to make sure which is the best way to do it as i dont want to fragment the table.
Thanks,
GK
Re: Data Purge [message #268123 is a reply to message #268121] Mon, 17 September 2007 10:38 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
In both cases fragmentation would occur ,you can run SHRINK table command to remove Fragmentation if you are on 10g.
Re: Data Purge [message #268127 is a reply to message #268121] Mon, 17 September 2007 11:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Fragmentation with 50 records???
It can't be.
Depending on your data, it may be that you have only 1 block!
It is likely that you have all your data in only one extent, you can't have fragmentation.

Regards
Michel
Re: Data Purge [message #268143 is a reply to message #268115] Mon, 17 September 2007 14:37 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
Don't you thing it will fragment in a long run as it has CLOB in it?
Thanks,
GK
Re: Data Purge [message #268149 is a reply to message #268143] Mon, 17 September 2007 14:45 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put the CLOB outside the segment and more in another tablespace.

Regards
Michel
Previous Topic: ORA-01092: ORACLE instance terminated. Disconnection forced
Next Topic: DBCA does not show made template
Goto Forum:
  


Current Time: Fri Dec 02 14:35:12 CST 2016

Total time taken to generate the page: 0.26565 seconds