How to reduce the table size for any table while using truncate or delete statements. In oracle 8i it was truncating the storage space but in 10g it is not ....
I have given truncate statement in a procedure to drop the storage of a table used... when its record count exceeds 500. -
BEGIN
-
-
i:= 0;
-
-
FOR CC IN NLD LOOP
-
-
DELETE FROM CHN_BED5A_T4_FX ;
-
-
COMMIT;
-
-
DELETE FROM CHN_BED5B_T4_FX ;
-
-
COMMIT;
-
-
-
if i=500
-
then
-
EXECUTE IMMEDIATE('TRUNCATE table CHN_BED5A_T4_FX');
-
EXECUTE IMMEDIATE('TRUNCATE table CHN_BED5B_T4_FX');
-
i:= 0;
-
end if;
-
-
commit;
-
This is the part where i truncate..
Can anyone help me with truncating the storage space as well since i am not able to run my reports with this increasing table size.....
8 7659
Hi
Please try to put correct code enclosed with code tags when you ask for help.
Also do you want to reduce the size of datafile by releasing unused extents or you want to keep not more than 500 records in your tables? Please be specific.
Best Regds,
Bhushan
When ever you perform a TRUNCATE, the water mark is reset and the space occupied by the data in the table is released.
In case of delete, the data is deleted and moved to rollback segments, but the storage space is not released. The Water mark remains at same level.
For more detail, Check here
Hi ,
I am sorry for not pasting the complete code... here is the complete one... -
SET SERVEROUTPUT ON;
-
-
DECLARE
-
-
i number;
-
-
CURSOR NLD IS
-
SELECT A.ROWID AS TMP_ROWID,A.* FROM CHN_BILLREFDUMP3_T1_FX A WHERE A.STAT=0 AND A.STRM=1;
-
-
-
BEGIN
-
-
i:= 0;
-
-
FOR CC IN NLD LOOP
-
-
DELETE FROM CHN_BED3A_T4_FX ;
-
-
COMMIT;
-
-
DELETE FROM CHN_BED3B_T4_FX ;
-
-
COMMIT;
-
-
-
if i=500
-
then
-
EXECUTE IMMEDIATE('TRUNCATE table CHN_BED3A_T4_FX');
-
EXECUTE IMMEDIATE('TRUNCATE table CHN_BED3B_T4_FX');
-
i:= 0;
-
end if;
-
-
commit;
-
-
-
-
INSERT /*+ append */ INTO CHN_BED3A_T4_FX
-
SELECT /*+ ordered*/ 0,
-
A.JURISDICTION,A.RATE_DT,A.PRIMARY_UNITS,A.SECOND_UNITS,A.THIRD_UNITS,A.RATED_UNITS,
-
A.AMOUNT,B.MSG_ID,B.MSG_ID2,B.MSG_ID_SERV,B.SPLIT_ROW_NUM,B.ACCOUNT_NO,B.SUBSCR_NO,
-
B.SUBSCR_NO_RESETS,B.BILL_REF_NO AS INDEX_BILL_REF,B.BILL_REF_RESETS,B.BILLED_AMOUNT,
-
B.DISCOUNT,B.TRANS_DT,B.TYPE_ID_USG
-
FROM chn_cdr_data_CU3 A,chn_cdr_billed_CU3 B
-
WHERE a.CDR_DATA_PARTITION_KEY=b.CDR_DATA_PARTITION_KEY and
-
A.MSG_ID=B.MSG_ID AND A.MSG_ID2=B.MSG_ID2 AND A.MSG_ID_SERV=B.MSG_ID_SERV AND B.BILL_REF_NO=CC.INDEX_BILL_REF AND B.BILLED_AMOUNT>0 AND A.SPLIT_ROW_NUM=B.SPLIT_ROW_NUM;
-
-
COMMIT;
-
-
-
INSERT /*+ append */ INTO CHN_BED3B_T4_FX
-
SELECT /*+ ordered*/ MSG_ID,MSG_ID2,MSG_ID_SERV,BILL_REF_NO,COUNT(*) FROM chn_cdr_billed_CU3 WHERE BILLED_AMOUNT>0 AND
-
BILL_REF_NO=CC.INDEX_BILL_REF GROUP BY MSG_ID,MSG_ID2,MSG_ID_SERV,BILL_REF_NO;
-
-
COMMIT;
-
-
--DUMPING ALL THE CALLS WITH CALL SPLIT =1 EVEN IT IS AIRTIME OR PSTN
-
-
UPDATE CHN_BED3A_T4_FX A SET A.CNT=(SELECT NCNT FROM CHN_BED3B_T4_FX B WHERE B.INDEX_BILL_REF=A.INDEX_BILL_REF AND NCNT=1
-
AND A.MSG_ID=B.MSG_ID AND A.MSG_ID2=B.MSG_ID2 AND B.MSG_ID_SERV=A.MSG_ID_SERV) ;
-
-
COMMIT;
-
-
UPDATE CHN_BED3A_T4_FX A SET A.CNT=(SELECT NCNT FROM CHN_BED3B_T4_FX B WHERE B.INDEX_BILL_REF=A.INDEX_BILL_REF AND NCNT<>1
-
AND A.MSG_ID=B.MSG_ID AND A.MSG_ID2=B.MSG_ID2 AND B.MSG_ID_SERV=A.MSG_ID_SERV) WHERE
-
( CNT=0 OR CNT IS NULL) AND A.TYPE_ID_USG IN ( SELECT SUBTYPE_CODE FROM mis.CHTYPEMAST2 WHERE UPPER(ITEM_DESCRIPTION) LIKE '%AIR%'
-
OR UPPER(ITEM_DESCRIPTION) LIKE '%HIERARCHY%');
-
-
COMMIT;
-
-
DELETE FROM CHN_BED3A_T4_FX WHERE CNT IS NULL;
-
-
INSERT /*+ append */ INTO CHN_BILLBKUP3BDUMP_T3_FX
-
SELECT /*+ ordered*/ SUBSCR_NO,SUBSCR_NO_RESETS,TYPE_ID_USG,INDEX_BILL_REF,JURISDICTION,SUM(PRIMARY_UNITS) AS ACTDURN,
-
SUM(RATED_UNITS) AS CHARGED_UNITS,COUNT(*) AS CALLCOUNT ,SUM(SECOND_UNITS) AS ROAMDURN
-
FROM CHN_BED3A_T4_FX
-
GROUP BY SUBSCR_NO,SUBSCR_NO_RESETS,TYPE_ID_USG,INDEX_BILL_REF,JURISDICTION;
-
-
COMMIT;
-
-
INSERT /*+ append */ INTO CHN_BILLBKUP3_T2_FX (INDEX_BILL_REF,SUBSCR_NO,SUBSCR_NO_RESETS, SUBTYPE_CODE, RATE_TYPE, REVENUE_TYPE,
-
ITEM_CODE, ITEM_DESCRIPTION,AMOUNT,TAX,DISCOUNT, NET_AMT_IN_RUPEES)
-
SELECT /*+ ordered*/
-
BID.BILL_REF_NO,BID.SUBSCR_NO,SUBSCR_NO_RESETS,BID.SUBTYPE_CODE,BID.RATE_TYPE,
-
DECODE(BID.TYPE_CODE,2,'RC',3,'NRC',1,'PAY',7,'USG',4,'ADJ',6,'UC',TYPE_CODE,'OTH') AS REVENUE_TYPE,
-
BID.SUBTYPE_CODE AS ITEM_CODE,
-
DES.DESCRIPTION_TEXT AS ITEM_DESCRIPTION,
-
SUM(BID.AMOUNT) /100 AS AMOUNT ,
-
SUM(BID.TAX ) /100 AS TAX ,
-
SUM(BID.DISCOUNT) /100 AS DISCOUNT ,
-
SUM(BID.AMOUNT+BID.TAX+BID.DISCOUNT) /100 AS NET_AMT_IN_RUPEES
-
FROM chn_bill_invoice_detail_CU3 BID, chn_descriptions_CU3 DES
-
WHERE BID.BILL_REF_NO = CC.INDEX_BILL_REF
-
AND BID.subtype_code <>'-1050'
-
AND BID.DESCRIPTION_CODE = DES.DESCRIPTION_CODE(+)
-
AND BID.TYPE_CODE IN (2,3,4,7)
-
GROUP BY BID.BILL_REF_NO,BID.SUBSCR_NO,SUBSCR_NO_RESETS,DECODE(BID.TYPE_CODE,2,'RC',3,'NRC',1,'PAY',7,'USG',4,'ADJ',6,'UC',TYPE_CODE,'OTH'),
-
BID.SUBTYPE_CODE,DES.DESCRIPTION_TEXT,BID.SUBTYPE_CODE,BID.RATE_TYPE;
-
-
COMMIT;
-
-
UPDATE CHN_BILLREFDUMP3_T1_FX SET STAT= 1 WHERE ROWID = CC.TMP_ROWID;
-
-
COMMIT;
-
-
i:=i+1;
-
-
END LOOP;
-
-
DBMS_OUTPUT.PUT_LINE ('Done');
-
-
END;
-
/
-
-
-
-
I truncate since the space given for user id is very limited and i have to run this query is 15 servers using DBlinks.Hence truncating the tablespace is the only way for me.
It was truncating my table size previously but now suddenly i have this problem.
You might be facing the problem due to delete operation performed nearly 500 times. Make sure you re build your indexes so that the orphan leaf are removed from the Indexes for atleast each 100 delete operations.
Hmm.. thanks for the reply....
Actually the script is really fast and without giving Truncate command there is no other option to reduce the table size to default while creating it...
This was working until 2 days before.I heard there is an option as "Recycle bin ON" which stops the table size from getting reduced....
That can be the reason. Just try setting RECYCLEBIN to OFF either at system or session level and run your procedure and check.
Yeah got it guys....... The trick is to create the table with initial storage space specified.
Ex .. Create table tmp storage(initial 50M)
so when truncating oracle returns the table space to be 50M .This applies in 10G.
Thanks for the support. :)
You are Welcome :)
Do post back in case of any further issues
Moderator
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Gordon |
last post by:
I have 2 tables t and t1. In this case, t1 is a copy of t. I want to
delete rows from t1 based on criteria on the t table and a relationship
between t ad t1 (in this case the id column). In the...
|
by: martin |
last post by:
Hi,
We have a heavily used production server and a table which logs every
hit on a web site. This table has grown large over time and we want to
clear it down as efficiently as possible. We would...
|
by: Fred |
last post by:
Hi, my database size has grown out of control and I need help with the
following issues. (I am very new to databases)
I am storing financial tick data in one of the tables and after two months...
|
by: LineVoltageHalogen |
last post by:
Greeting All, I have a stored proc that dynamically truncates all the
tables in my databases. I use a cursor and some dynamic sql for this:
......
create cursor
Loop through sysobjects and...
|
by: Hervé Piedvache |
last post by:
Bug or problem with PostgreSQL ?
version
---------------------------------------------------------------
PostgreSQL 7.4.3 on i686-pc-linux-gnu, compiled by GCC 2.95.4
select * from my_table;...
|
by: pankaj_wolfhunter |
last post by:
Greetings,
I have an application that need to get all the user
defined child tables first before their parents.
I wrote a query, given in this newsgroup only, as below
SELECT o.name
FROM...
|
by: polocar |
last post by:
Hi,
I'm writing a program in Visual C# 2005 Professional Edition.
This program connects to a SQL Server 2005 database called
"Generations" (in which there is only one table, called...
|
by: Sala |
last post by:
Hi
I want to truncate all data in database ... pls help me how i ll
truncate?
|
by: Lennart |
last post by:
Any thoughts on the following scenario anyone?
During a performance test I discovered that the application asked one
specific query extremely often. It turned out that this particular
query...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |