Estimate Index size before creating

      Sometimes it is better to estimate the index before creating so to not see any surprise errors. From the below Oracle note got the exact code to get estimated size of index.

How to Estimate the Size of Tables and Indexes Before Being Created and Populated in the Database? (Doc ID 1585326.1)

I have queried this in 11204 database, please do comment if you test in other versions.

SQL> set serveroutput on 
SQL> declare
  2  l_used_bytes number;
  3  l_alloc_bytes number;
  4  begin
  5  dbms_space.create_index_cost (
  6  ddl => 'create index orp.ACT_IDX on orp.act (LOAN) global partition by hash (LOAN) partitions 32',
  7  used_bytes => l_used_bytes,
  8  alloc_bytes => l_alloc_bytes
  9  );
 10  dbms_output.put_line ('Used Bytes          = '||l_used_bytes);
 11  dbms_output.put_line ('Allocated Bytes     = '||l_alloc_bytes);
 12  end;
 13  /
Used Bytes              = 3464634958
Allocated Bytes         = 42949672960

PL/SQL procedure successfully completed.

 The used_bytes : how much space is for the index data

 The alloc_bytes : how much space is allocated within the tablespace for the index segment. (ACTUAL SIZE)

Another Way

There is another way using explain plan, please don't confuse with the numbers both are different indexes just trimmed names for easy

 SQL> explain plan for 

   2  create index orp.ACT_IDX on orp.act (PRO_TIME);

Explained.

01:42:48 SQL> select * from table (dbms_xplan.display());

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------

Plan hash value: 3821618068

-------------------------------------------------------------------------------------------------------

| Id  | Operation              | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------------

|   0 | CREATE INDEX STATEMENT |                              | 46441 |  1224K|   113   (1)| 00:00:02 |

|   1 |  INDEX BUILD NON UNIQUE| ACT_IDX                      |       |       |            |          |

|   2 |   SORT CREATE INDEX    |                              | 46441 |  1224K|            |          |

|   3 |    INDEX FAST FULL SCAN| ACT_IDX                      | 46441 |  1224K|    71   (2)| 00:00:01 |

-------------------------------------------------------------------------------------------------------

Note

-----

   - estimated index size: 2097K bytes

14 rows selected.

enq: HW - contention and Secure file LOB

In one of our 11204 database, we encountered the deadly ‘Enq: HW contention’ on critical business day. The majority of the DB activity is insert and the table has CLOBs. The CLOBs were traditional BASIC LOB, With the below note able to identify the LOB segment on which the contention is.

Analyzing 'enq: HW - contention' Wait Event (Doc ID 740075.1)

Manual extent allocation or event 44951 didn’t help much, so finally decided to migrate the CLOBs to SECURE FILES. Due to the segment size is in 7+ Terabyte considering the time /storage we decided to create new table and let the logs write there. This may not be feasible to many of your environment. This particular table is monthly RANGE partitioned to truncate the older partition every 2 months. Now it is created with hash sub partition to distribute the inserts and reduce concurrency.

I could not locate appropriate DDL for the RANGE-HASH composite partition, so the below test case developed. Currently the new table is with secure file LOBs and hash sub partitioned. The performance is great with the same amount of load.

Note from 12c the default LOB is Secure file LOB.

TEST CASE

CREATE TABLE hash_test

  ( prod_id       NUMBER(6)

  , cust_id       NUMBER

  , time_id       DATE

    )

PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) SUBPARTITION BY HASH (cust_id)

  SUBPARTITIONS 4 STORE IN (USERS)

(PARTITION "P0"  VALUES LESS THAN (TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));

set long 99999 lines 200 pages 9999

col DDL for a200

select dbms_metadata.get_ddl('TABLE','HASH_TEST','SYS') DDL from dual;

insert into hash_test values(10023,101,sysdate);

 insert into hash_test values(10024,104,sysdate);

 insert into hash_test values(10025,107,sysdate);

 insert into hash_test values(10026,110,sysdate);

 insert into hash_test values(10027,113,sysdate);

 insert into hash_test values(10028,116,sysdate);

 insert into hash_test values(10029,119,sysdate+30);

 insert into hash_test values(10030,122,sysdate+30);

 insert into hash_test values(10031,125,sysdate+30);

 insert into hash_test values(10032,128,sysdate+30);

 insert into hash_test values(10033,131,sysdate+30);

 insert into hash_test values(10034,134,sysdate+30);

 insert into hash_test values(10035,137,sysdate+30);

 insert into hash_test values(10036,140,sysdate+60);

 insert into hash_test values(10037,143,sysdate+60);

 insert into hash_test values(10038,146,sysdate+60);

 insert into hash_test values(10039,149,sysdate+60);

 insert into hash_test values(10040,152,sysdate+60);

 insert into hash_test values(10041,155,sysdate+60);

 insert into hash_test values(10042,158,sysdate+60);

 insert into hash_test values(10043,161,sysdate+60);

 SQL> select count(*) from hash_test subpartition        (SYS_SUBP19335);

  COUNT(*)

----------

         1

SQL> select count(*) from hash_test subpartition        (SYS_SUBP19336);

  COUNT(*)

----------

         1

SQL> select count(*) from hash_test subpartition        (SYS_SUBP19337);

  COUNT(*)

----------

         5

SQL> select count(*) from hash_test subpartition        (SYS_SUBP19338);

  COUNT(*)

----------

         0

SQL> select count(*) from hash_test partition (SYS_P19339);

  COUNT(*)

----------

         7

exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SYS' ,tabname => 'HASH_TEST',cascade => true, estimate_percent => dbms_stats.auto_sample_size,method_opt=>'for all indexed columns size AUTO', granularity => 'ALL', degree => 8);

                       

alter table hash_test truncate partition SYS_P19339;

CREATE INDEX prod_id_ix ON hash_test(prod_id) LOCAL;

Subpartition    Partition           Tablespace     Last Analyzed High Value      High Value Length      Num Rows   B-Level            Distinct Keys

                        Name  Name

SYS_SUBP19326      P0                                USERS           09-Feb-20 10:57:26 AM                      0          0            0          0

SYS_SUBP19327      P0                                USERS           09-Feb-20 10:57:26 AM                      0          0            0          0

SYS_SUBP19328      P0                                USERS           09-Feb-20 10:57:26 AM                      0          0            0          0

SYS_SUBP19329      P0                                USERS           09-Feb-20 10:57:26 AM                      0          0            0          0

SYS_SUBP19330      SYS_P19390  SYSTEM         09-Feb-20 10:57:26 AM                      0          0          0            0

SYS_SUBP19331      SYS_P19390  SYSTEM         09-Feb-20 10:57:26 AM                      0          0          0            0

SYS_SUBP19332      SYS_P19390  SYSTEM         09-Feb-20 10:57:26 AM                      0          0          0            0

SYS_SUBP19333      SYS_P19390  SYSTEM         09-Feb-20 10:57:26 AM                      0          0          0            0

SYS_SUBP19335      SYS_P19391  SYSTEM         09-Feb-20 10:57:26 AM                      0          0          0            0

SYS_SUBP19336      SYS_P19391  SYSTEM         09-Feb-20 10:57:26 AM                      0          0          0            0

SYS_SUBP19337      SYS_P19391  SYSTEM         09-Feb-20 10:57:26 AM                      0          0          0            0

SYS_SUBP19338      SYS_P19391  SYSTEM         09-Feb-20 10:57:26 AM                      0          0          0            0

SYS_SUBP19340      SYS_P19392  SYSTEM         09-Feb-20 10:57:26 AM                      0          0          0            0

SYS_SUBP19341      SYS_P19392  SYSTEM         09-Feb-20 10:57:26 AM                      0          0          0            0

SYS_SUBP19342      SYS_P19392  SYSTEM         09-Feb-20 10:57:26 AM                      0          0          0            0

SYS_SUBP19343      SYS_P19392  SYSTEM         09-Feb-20 10:57:26 AM                      0          0          0            0

 DATA Generator.

DECLARE

  t_seed NUMBER(20);

BEGIN

  -- Initialize Random Number Generator

  SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'MMDDHH24MISS'))

    INTO t_seed

  FROM Dual;

  DBMS_RANDOM.INITIALIZE(t_seed);

  -- Generate Random Data

  FOR t_counter IN 1..1000 LOOP

    INSERT INTO aolmob.HASH_TEST (

      PROD_ID, CUST_ID, TIME_ID

    ) VALUES (

     DBMS_RANDOM.VALUE(0.00, 999999.00), DBMS_RANDOM.VALUE(0.00, 2147483647.00), TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2415020, 2488068)), 'J')

    );

  END LOOP;

  DBMS_RANDOM.TERMINATE;

END;

/

Please leave your valuable comments to improve the blog and let me know if any specific topic to be explained.

Proud to be with my virtual Guru

With Mr. RAC, Syed jaffer Hussain

New table name in DDL using DBMS_METADATA

If you want to get a DDL with the desired name for a table this is function will be used.

Step 1:

Replace YOUR SCHEMA name in line # 13 in below block i.e., from 'A' to 'SCHEMA name'

  1  create or replace function remap_name(orig_name varchar2, new_name varchar2 )
  2  return clob is
  3    -- Define local variables.
  4    h   number; --handle returned by OPEN
  5    th  number; -- handle returned by ADD_TRANSFORM
  6    doc clob;
  7  begin
  8    -- Specify the object type.
  9    h := dbms_metadata.open('TABLE');
 10    -- Use filters to specify the particular object desired.
 11    dbms_metadata.set_filter(h
 12                            ,'SCHEMA'
 13                            ,'A');
 14    dbms_metadata.set_filter(h
 15                            ,'NAME'
 16                            ,orig_name);
 17    -- Request that the schema name be modified.
 18    th := dbms_metadata.add_transform(h
 19                                     ,'MODIFY');
 20    dbms_metadata.set_remap_param(th
 21                                 ,'REMAP_NAME'
 22                                 ,orig_name
 23                                 ,new_name);
 24    -- Request that the metadata be transformed into creation DDL.
 25    th := dbms_metadata.add_transform(h
 26                                     ,'DDL');
 27    -- Specify that segment attributes are not to be returned.
 28    dbms_metadata.set_transform_param(th
 29                                     ,'SEGMENT_ATTRIBUTES'
 30                                     ,false);
 31    dbms_metadata.set_transform_param(th,'SQLTERMINATOR',TRUE);
 32    -- Fetch the object.
 33    doc := dbms_metadata.fetch_clob(h);
 34    -- Release resources.
 35    dbms_metadata.close(h);
 36    return doc;
 37  end remap_name;
 38  /

PLEASE REMOVE LINE NUMBERS BEFORE EXECUTION

Step 2. Call the remape_name and spool it

set echo off head off feed off

spool renameDDL.sql

SELECT 'SELECT remap_name('''||  TABLE_NAME || ''','''||  TABLE_NAME || '_new'') FROM DUAL;' FROM DBA_TABLES where owner='A';

spool off

Step 3. Review the above script and execute accordingly

Example:

sys@TESTDB> SELECT remap_name('TEST_OBJ','TEST_OBJ_new') FROM DUAL;

REMAP_NAME('TEST_OBJ','TEST_OBJ_NEW')

-------------------------------------------------

  CREATE TABLE "A"."TEST_OBJ_new"

   (    "OWNER" VARCHAR2(30),

        "OBJECT_NAME" VARCHAR2(128),

Note: 

REMAP_NAME is not available in 10g , available from 11g

NO REMAP_NAME ins 10g

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm#BGBBIEGA

REMAP_NAME is available in SET_REMAP_PARAM 11g only

http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_metada.htm#BGBBIEGA 

You will get the below error in 10g but function will be created 

ORA-31600: invalid input value REMAP_NAME for parameter NAME in function SET_REMAP_PARAM

Logon banner in SQLPLUS

To display a customized banner while login to sqlplus

sys@TESTDB> show user
USER is "SYS"

sys@TESTDB> create or replace procedure custom_logon_message as
  2  begin
  3  dbms_output.put_line ( ' ');
  4  dbms_output.put_line ( '-------------------------' );
  5  dbms_output.put_line ( ' ');
  6  dbms_output.put_line ( 'This is restricted system' );
  7  dbms_output.put_line ( ' ');
  8  dbms_output.put_line ( '-------------------------' );
  9  dbms_output.put_line ( ' ');
 10  end;
 11  /

Call the "custom_logon_message" from glogin.sql

ORACLE_HOME\sqlplus\admin\glogin.sql
set feed off
exec custom_logon_message;

local \ OS authentication

C:\Users\mmeerha>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 7 10:52:26 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

-------------------------
This is restricted system
-------------------------
sys@TESTDB>

Remote connection

C:\Users\mmeerha>sqlplus a/a@testdb

SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 7 10:50:38 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

-------------------------
This is restricted system
-------------------------

NOTE: though granting exec on privilege to public/ individual users failed with

sys@TESTDB> conn a/a
Connected.
BEGIN custom_logon_message; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'CUSTOM_LOGON_MESSAGE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

So created the procedure in that user it worked.