- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Delete millions of records in a table - easier met...
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-24-2008 08:00 AM
тАО07-24-2008 08:00 AM
Delete millions of records in a table - easier methode needed
iam in process of deleting the records before jan 1 2007 in sys.aud$ table.The auditing is enabled on 02-dec-2000 07:15:11 .i need delete the records from 02-dec-2000 to 31-12-2006.Till date record count is more than 100 million records.
Can you tell the approach which take less time to delete these records and also less genration of archives.
Database version : orracle 8.1.7.2.0
Thanks,
Nirmal.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-24-2008 08:58 AM
тАО07-24-2008 08:58 AM
Re: Delete millions of records in a table - easier methode needed
Database Groups > Technical-functional > db2-l > Message
About This Group | Invite peers to join this group
<< Prev thread < Prev msg Next msg > Next thread >>
Need to delete 15 million records from production table
Reply from Bill Brooks on 4/9/2008 3:34:00 PM
Do this:
# Get the number of bytes needed to delete from your table:
db2 "select decimal(CARD)/decimal(NPAGES) * PAGESIZE * 1000000
bytestodelete from syscat.tables t, syscat.tablespaces t2 where
t.tabname='YOURTABLENAMEGOESHERE' and
t.tabschema='YOURTABLESCHEMAGOESHERE' and t.TBSPACEID=t2.TBSPACEID"
# Now get the number of bytes you have in your log space here
db2 "get db cfg for
db2 "get db cfg for
db2 "get db cfg for
Now do this operation from the numbers you obtained from above
LOGPRIMARY + LOGSECOND * LOGFILSIZ
If bytestodelete is > LOGPRIMARY + LOGSECOND * LOGFILSIZ you'll need a
cursor of some sort. If bytestodelete is < LOGPRIMARY + LOGSECOND *
LOGFILSIZ then you'll probably be ok. Make sure you have some overhead
for other transactions.
You should also think about a reorg chk after you run your delete. I'm
sure it'll tell you it needs a reorg after 1,000,000 rows deleted. If
you don't have time for a reorg at the minimum do a runstats.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-24-2008 09:02 AM
тАО07-24-2008 09:02 AM
Re: Delete millions of records in a table - easier methode needed
-- In this example we are deleting 1,000 rows at a time.
Set @num = (Select Count(*) from transactions
where datediff(hh,dt_tm_created, getdate()) > 72 and OrderId =0)
While @num <> 0
Begin
begin transaction
set rowcount 1000
Delete from transactions
where datediff(hh,dt_tm_created, getdate()) > 72 and OrderId =0
set @num = @@rowcount
commit transaction
end
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-24-2008 09:37 AM
тАО07-24-2008 09:37 AM
Re: Delete millions of records in a table - easier methode needed
Use "CREATE TABLE
On success rename aud$ to old, and new-aud to aud$.
Truncate or delete old when ready.
See Oracle doc, or google for "CREATE TABLE AS SELECT" for details and examples.
btw... Maybe you want date based partitioning (a year at a time) for the new table?
hth,
Hein.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-25-2008 03:22 AM
тАО07-25-2008 03:22 AM
Re: Delete millions of records in a table - easier methode needed
Agree with Hein van den Heuvel , the best way is to create tmp table using CREATE TABLE tmp as select .. where ├в ┬ж (make sure that your tablespace has enough free space to create tmp teble)
rename aud$ to xxx
rename tmp to aud$
And you also need to create index on new aud$
This method normally do not generate lots of redo
If you have access to Metalink have a look at Note:1019377.6
Regards
Vadim
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-25-2008 09:44 AM
тАО07-25-2008 09:44 AM
Re: Delete millions of records in a table - easier methode needed
As mentioned by Hein and Vadim, unless you make sense out of the data in the sys,aud$ table, you may simply truncate the table.
As a precautionary measure, you can backup the data to a temporary table using:
create table tmp_sys_aud as select * from sys.aud$;
good luck!
kind regads
yogeeraj