Home » SQL & PL/SQL » SQL & PL/SQL » Archiving (Oracle 9i)
Archiving [message #567406] Mon, 01 October 2012 02:32 Go to next message
redfire082
Messages: 2
Registered: October 2012
Junior Member
Hi all,

I'm wondering if anyone can assist with a task I need to complete ASAP. We have an Oracle 9i database which is running out of diskspace (multiple mount points exist and the mount point where the datafiles are kept has about 20GB free).

A couple of years ago the previous DBA created a 200GB partition to archive data from the largest table we have based on a year (2009 is the last archive).

What I understand is:

1) I create a new tablespace with datafiles sized to the same as the previous archive tablespace - This is guess work as I can't tell how much space will be required.
2) I run the following statement to create the table and populate it:
create table schema_name.largetable2010 like (select count(*) from schema_name.largetable where CREATED_DTM >= '01-JAN-2010' and CREATED_DTM <= '31-DEC-2010');
3) Once the table is created we do something like "select count(*) from archivedtable where ..." and same for the "live" table to ensure the same amount of records exist.
4) Once confirmed we can delete the records from the "live" table then re-index/gather fresh stats.

Firstly, can anyone comment on the above procedure and suggest ways of making this better/offer advice?
Secondly, once the data is archived and deleted I understand that the "live" table will still have "white-space" in the datafiles and therefor will not free space on the mount point ... can anyone offer advice on how we go about clearing this space up?

I appreciate all feedback for this and understand that it's a pretty simple task to carry out for a DBA - I'm just against time here with pressure's of sensitive data we can't afford to loose.

Thanks in advance,

Carl
Re: Archiving [message #567408 is a reply to message #567406] Mon, 01 October 2012 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 58625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
2) '01-JAN-2010' is a string NOT a date (I told you it last week), so '01-FEB-2012' is lower that '01-JAN-2012' and you will not get it, but '01-NOV-1999' is between the strings you gave so you will take it.
ALWAYS use TO_DATE with a format.

3) It is useless just a waste of time and resources. If Oracle returns with no error then the count is correct.

"Secondly": use ALTER TABLE SHRINK to compact the table or, if you want to compact the file, export/drop/shrink the file/import.

Regards
Michel
Re: Archiving [message #567413 is a reply to message #567408] Mon, 01 October 2012 02:52 Go to previous messageGo to next message
redfire082
Messages: 2
Registered: October 2012
Junior Member
Thanks for the quick reply Michel.

I apologies if you gave me information regarding the date format previously, I created an account on orafaq last Thursday and since then the site was down and my account didn't get created (I re-created it this morning) ... I assumed the post was also deleted like my account.

Anyway, back on topic Smile I seem to remember the previous DBA did the export/drop/shrink approach and I will search around to find some type of walkthrough to get me through it - appreciate the confirmation.

Just a quick refresh though Michel, the date format I used which was a string ... In the table I can see :
CREATED_DTM NOT NULL DATE
and if I run a select * on the table for the top 10 records, the Created_DTM column has values like "15-MAY-09", etc.

when I run the below:
where EVENT_DTM>= '01-JAN-2009' and EVENT_DTM <= '31-DEC-2009';

I get something like 22 million rows back ... I appreciate the data could be wrong if the where clause is incorrect - can you suggest a more efficient statement to grab all records from 2009 using that column?

Really appreciate your advice Michel and apologies again if you've already offered support of this.

Kind Regards,

Carl
Re: Archiving [message #567418 is a reply to message #567413] Mon, 01 October 2012 03:14 Go to previous message
Michel Cadot
Messages: 58625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the Created_DTM column has values like "15-MAY-09",


No, the column has a value in an internal format (which does not matter), you display it, using an implicit conversion and default date format like this.

Quote:
when I run the below:
where EVENT_DTM>= '01-JAN-2009' and EVENT_DTM <= '31-DEC-2009';

I get something like 22 million rows back


Which may be an incorrect number given the remarks I made, use TO_DATE.

Quote:
I appreciate the data could be wrong if the where clause is incorrect


Correct, the where clause is already wrong.

Quote:
can you suggest a more efficient statement to grab all records from 2009 using that column?


Anyway you have to scan the table, so no more efficient statement.
You can use PARALLEL hint to get them in parallel if your hardware and concurrent workload allow it.

Regards
Michel
Previous Topic: SQL Statement
Next Topic: SQL Query, Substring
Goto Forum:
  


Current Time: Wed Jul 30 18:29:23 CDT 2014

Total time taken to generate the page: 0.09886 seconds