DBMS_SPACE.CREATE_TABLE_COST

Posted by Roshan on Nov 01, 2018; 9:08am
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/DBMS-SPACE-CREATE-TABLE-COST-tp6907.html

Hi Erman,

I have a few questions on the DBMS_SPACE.CREATE_TABLE_COST procedure.

Support I have a table with structure below

DROP TABLE BIREPORT.CBS_SUM_DEB_REP CASCADE CONSTRAINTS;

CREATE TABLE BIREPORT.CBS_SUM_DEB_REP
(
CUSTOMER_CAT VARCHAR2(50 BYTE),
OPEN_BAL NUMBER(12,4),
CURR_INV_AMT NUMBER(12,4),
DEBIT_NOTE_AMT NUMBER(12,4),
SURCHARGE_AMT NUMBER(12,4),
ADJ_INCR_AMT NUMBER(12,4),
SUB_TOTAL NUMBER(12,4),
RECEIPTS_AMT NUMBER(12,4),
CREDIT_NOTE_AMT NUMBER(12,4),
BULK_WAIV_AMT NUMBER(12,4),
BAD_DETS_AMT NUMBER(12,4),
ADJ_DESC_AMT NUMBER(12,4),
OFFSET_CRED_AMT NUMBER(12,4),
SUB_TOTAL1 NUMBER(12,4),
CLOSE_BALANCE NUMBER(12,4),
CUST_SUB_CAT VARCHAR2(50 BYTE),
TRANS_DATE DATE
)
TABLESPACE DATA4
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 160K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
MONITORING;

Can you help me with the procudre since number has precision and DATE field does not has size(value : 03/11/2017 13:41:26)

What will be the procedure
set serveroutput on

DECLARE
ub NUMBER;
ab NUMBER;
cl sys.create_table_cost_columns;

BEGIN
cl := sys.create_table_cost_columns(

sys.create_table_cost_colinfo('DATE',6) ,
sys.create_table_cost_colinfo('VARCHAR2',50),
sys.create_table_cost_colinfo('NUMBER(12,4),
sys.create_table_cost_colinfo('NUMBER(12,4),
sys.create_table_cost_colinfo('NUMBER(12,4),
sys.create_table_cost_colinfo('NUMBER(12,4),
sys.create_table_cost_colinfo('NUMBER(12,4),
sys.create_table_cost_colinfo('NUMBER(12,4),
sys.create_table_cost_colinfo('NUMBER(12,4),
sys.create_table_cost_colinfo('NUMBER(12,4),
sys.create_table_cost_colinfo('NUMBER(12,4),
sys.create_table_cost_colinfo('NUMBER(12,4),
sys.create_table_cost_colinfo('NUMBER(12,4),
sys.create_table_cost_colinfo('NUMBER(12,4),
sys.create_table_cost_colinfo('NUMBER(12,4),
sys.create_table_cost_colinfo('NUMBER(12,4),
sys.create_table_cost_colinfo('VARCHAR2',50),
sys.create_table_cost_colinfo('DATE
)
)
DBMS_SPACE.CREATE_TABLE_COST('BIREPORT',cl,10,10,ub,ab);
DBMS_OUTPUT.PUT_LINE('Used s = ' || TO_CHAR(ub));
DBMS_OUTPUT.PUT_LINE('Allocated s = ' || TO_CHAR(ab));
END;
/

Thanks,

Roshan