DBMS_SPACE.CREATE_TABLE_COST

[Roshan]

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

[ErmanArslansOracleBlog]

Hi Roshan,

I didn't understand what exactly you are asking.  But I will still try to answer..

If you are asking about the length of date column -> Oracle Database uses its own internal format to store dates. Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second.

So, there is no length that you can supply for date, it is already fixed.

So you can use the DATE as follows;

sys.create_table_cost_colinfo('DATE',NULL)

As for the number data type -> column_name NUMBER (precision, scale)
precision = """total""" number of digits
scale = number of digits to the right of the decimal point

So you can use the precision for the colinfo, like the following;

sys.create_table_cost_colinfo('NUMBER',9),

[Roshan]

Hi,

thanks. In fact this was what I wanted to know. The DATE data which is stored in fixed length format.

As for the pct increase in the function below

DBMS_SPACE.CREATE_TABLE_COST('BIREPORT',cl,10,10,ub,ab);

could you please advise how do I calculate the pct increase for the table?

Suppose the data increase between this month and last month is 100GB, would the pct increase be 100GB/last month *100?
 

[ErmanArslansOracleBlog]

You want the math, huh? :)

FACT:

In 12C

The storage_clause is interpreted differently for locally managed tablespaces. For locally managed tablespaces, Oracle Database uses INITIAL, NEXT, PCTINCREASE, and MINEXTENTS to compute how many extents are allocated when the object is first created. After object creation, these parameters are ignored.

The recommendation is to set it 0, bytheway.

So whay don'tu you set it to 0 and make a test?