How to reduce table size using delete or truncate statement in 10G

[ananthaisin]

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.....

[OraMaster]

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

[amitpatel66]

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

[ananthaisin]

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.

[amitpatel66]

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.

[ananthaisin]

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....

[amitpatel66]

That can be the reason. Just try setting RECYCLEBIN to OFF either at system or session level and run your procedure and check.

[ananthaisin]

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. :)

[amitpatel66]

You are Welcome :)
Do post back in case of any further issues

Moderator