Key Compressed B* Tree Indexes

Key Compressed B* Tree Indexes
1) Our block buffer cache will be able to hold more index entries than before
2) our cache-hit ratio might go up
3) our physical I/Os should go down

create table t nologging as select * from all_objects;
insert /*+ append */ into t select * from T;
commit;
insert /*+ append */ into t select * from T;
commit;

create index uncompressed_idx on t( owner,object_type,object_name,0 ) nologging;
analyze index uncompressed_idx validate structure;

scott@10G> select height, blocks,lf_rows, br_rows, del_lf_rows, name
  2  from index_stats;

    HEIGHT     BLOCKS    LF_ROWS    BR_ROWS DEL_LF_ROWS NAME
---------- ---------- ---------- ---------- ----------- -----------------------
         3       1536     205540       1474           0 UNCOMPRESSED_IDX

create index compressed_idx on t( owner,object_type,object_name,1 ) COMPRESS 3 nologging;
analyze index compressed_idx validate structure;

scott@10G> select height, blocks,lf_rows, br_rows, del_lf_rows, name
  2  from index_stats;

    HEIGHT     BLOCKS    LF_ROWS    BR_ROWS DEL_LF_ROWS NAME
---------- ---------- ---------- ---------- ----------- -----------------
         3        768     205540        718           0 COMPRESSED_IDX


********************************************************************************
SELECT /*+ index (t uncompressed_idx) */ owner,COUNT(*)
FROM T
GROUP BY owner

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.07       0.08          0       1478          0          30
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.07       0.08          0       1478          0          30

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 

Rows     Row Source Operation
-------  ---------------------------------------------------
     30  SORT GROUP BY NOSORT (cr=1478 pr=0 pw=0 time=60 us)
 205540   INDEX FULL SCAN UNCOMPRESSED_IDX (cr=1478 pr=0 pw=0 time=29 us)(object id 64687)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.08          0.08
********************************************************************************

SELECT /*+ index (t compressed_idx) */ owner,COUNT(*)
FROM T
GROUP BY owner

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.09       0.09          0        722          0          30
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.09       0.09          0        722          0          30

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 

Rows     Row Source Operation
-------  ---------------------------------------------------
     30  SORT GROUP BY NOSORT (cr=722 pr=0 pw=0 time=74 us)
 205540   INDEX FULL SCAN COMPRESSED_IDX (cr=722 pr=0 pw=0 time=35 us)(object id 64689)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  SQL*Net message from client                     3        0.00          0.00
********************************************************************************

Script to Export Pl/SQL Objects

If I have to move PL/SQL code from one schema to another, I prefer to use scripts. I have scripts to retrieve a package, procedure, or function. Another script extracts views. Yet another does triggers. Moving these types of objects is not something EXP/IMP is adept at.Ask EXP/IMP for a table and it is great. Ask EXP/IMP to give your view definition back and it is not so good.

getcode.sql

REM getcode.sql - extract any procedure, function or package
set feedback off
set heading off
set timing off
set termout off
set linesize 1000
set trimspool on
set verify off
spool &2..sql
prompt set define off
select decode( type||'-'||to_char(line,'fm99999'),
        'PACKAGE BODY-1', '/'||chr(10),
        null) ||
        decode(line,1,'create or replace ', '' ) ||
        text text
    from all_source
    where name = upper('&&2')
    and   owner = upper('&&1')
    order by type, line;
prompt /
prompt set define on
spool off
set feedback on
set heading on
set termout on
set linesize 100
set timing on



getallcode.sql

REM getallcode.sql - extract all procedure, function or package
set termout off
set heading off
set feedback off
set timing off
set linesize 32000
spool xtmpx.sql
select '@getcode.sql ' || upper('&&1') ||'  '|| name
from all_source
where type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE' )
and owner = upper('&&1')
/
spool off
spool getallcode_INSTALL.sql
select '@' || name
from all_source
where type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE' )
and owner = upper('&&1')
/
spool off
set heading on
set feedback on
set linesize 130
set termout on
set timing on
@xtmpx.sql 


getaview.sql


REM getaview.sql
set heading off
set long 99999999
set feedback off
set linesize 1000
set trimspool on
set verify off
set termout off
set timing off
set embedded on
column column_name format a1000
column text format a1000
spool &2..sql
prompt create or replace view &2 (
select decode(column_id,1,'',',') || column_name column_name
from all_tab_columns
where table_name = upper('&2')
and  owner = upper('&1')
order by column_id
/
prompt ) as
select text
from all_views
where view_name = upper('&2')
and  owner = upper('&1')
/
prompt /
spool off
set termout on
set heading on
set feedback on
set verify on
set timing on


getallview.sql


REM getallview.sql
set heading off
set feedback off
set timing off
set linesize 1000
set trimspool on
set verify off
set termout off
set embedded on
spool tmp.sql
select '@getaview.sql ' || '&1' ||'  '|| view_name
from all_views
where owner = upper('&1')
/
spool off
set termout on
set heading on
set feedback on
set verify on
set timing on
@tmp


getatrigger.sql


REM getatrigger.sql
set echo off
set verify off
set timing off
set feedback off
set termout off
set heading off
set pagesize 0
set long 99999999
spool &2..sql
select
'create or replace trigger "' ||
trigger_name || '"' || chr(10)||
decode( substr( trigger_type, 1, 1 ),
'A', 'AFTER', 'B', 'BEFORE', 'I', 'INSTEAD OF' ) ||
chr(10) ||
triggering_event || chr(10) ||
'ON "' || table_owner || '"."' ||
table_name || '"' || chr(10) ||
decode( instr( trigger_type, 'EACH ROW' ), 0, null,
'FOR EACH ROW' ) || chr(10) ,
trigger_body
from all_triggers
where trigger_name = upper('&2')
and   owner = upper('&1')
/
prompt /
spool off
set verify on
set feedback on
set termout on
set heading on
set timing on


getallsequence.sql
REM getallsequence.sql - extract all procedure, function or package
set termout off
set heading off
set feedback off
set timing off
set linesize 32000
spool d:\allsequence.sql
SELECT 'CREATE SEQUENCE '||SEQUENCE_NAME||' START WITH '||LAST_NUMBER||' INCREMENT BY '||INCREMENT_BY||' CACHE 20 NOCYCLE NOMAXVALUE ; '
FROM all_sequences
where sequence_owner = upper('&&1')
/
spool off
set heading on
set feedback on
set linesize 130
set termout on
set timing on

SQL Talking

Best Wishes from SQL

scott@10G> select decode
  2        (sign (floor (maxwidth/2) - rownum)
  3        ,1, lpad (' ', floor (maxwidth/2) - (rownum -1))
  4           ||rpad ('*', 2 * (rownum -1), ' *')
  5        ,lpad ('* * *', floor (maxwidth/2) + 3)) "Merry Christmas"
  6  from dual
  7  ,    (select 40 as maxwidth from dual)
  8  connect by level < floor (maxwidth/2) + 5
  9  /

Merry Christmas
-----------------------------------------------------------------------

                   *
                  * *
                 * * *
                * * * *
               * * * * *
              * * * * * *
             * * * * * * *
            * * * * * * * *
           * * * * * * * * *
          * * * * * * * * * *
         * * * * * * * * * * *
        * * * * * * * * * * * *
       * * * * * * * * * * * * *
      * * * * * * * * * * * * * *
     * * * * * * * * * * * * * * *
    * * * * * * * * * * * * * * * *
   * * * * * * * * * * * * * * * * *
  * * * * * * * * * * * * * * * * * *
                  * * *
                  * * *
                  * * *
                  * * *
                  * * *

24 rows selected.

Secret Sauce

A frequently misunderstood concept with NULLS and indexes is the myth that NULL values are not tracked in NORMAL indexes such as a conventional B*Tree index. Therefore, many people conclude erroneously that WHERE COLUMN IS NULL will not use, in fact cannot, use an index.

The facts are:

    * Entirely NULL keys are not entered into a NORMAL B*Tree in Oracle

    * Therefore, if you have a concatenated index on say C1 and C2, then you will likely find NULL values in it – since you could have a row Where C1 is NULL but C2 is     NOT NULL – that key value will be in the index.

scott@10G> create table T
  2  as select *
  3  from dba_objects
  4  /

Table created.

scott@10G> create index t_ind on t(object_id);

Index created.

scott@10G> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T',cascade=>TRUE);

PL/SQL procedure successfully completed.

scott@10G> set autotrace traceonly explain;
scott@10G> select * from t
  2  where object_id is null;

Execution Plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    93 |   165   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    93 |   165   (2)| 00:00:02 |
--------------------------------------------------------------------------

As long as you have some not null column in your set of indexed columns, you will find NULLs in the index.
Additionally – you always have a NOT NULL (virtual) column to use no matter what! (Now see the Effect of Secret Sauce )


scott@10G> drop index t_ind;

Index dropped.

scott@10G> create index t_ind on t(object_id,0);

Index created.

scott@10G> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T',cascade=>TRUE);

PL/SQL procedure successfully completed.

scott@10G> select * from t
  2  where object_id is null;

  Execution Plan
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    95 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    95 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IND |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------


Now we scanned the Index segment to answer our query, which is far better that scanning the entire Table segment.


The "secret sauce" here is to create what is known as a function based index
We simply added "0" to the index – we can search on OBJECT_ID as before, plus find all records where "OBJECT_ID IS NULL" without changing a thing. We can use the fact that entirely null entries are not made in B*Tree indexes as a positive thing.

Something About NULL

scott@9iR2> select * from product_component_version;

PRODUCT                        VERSION    STATUS
------------------------------ ---------- ---------------
NLSRTL                         9.2.0.8.0  Production
Oracle9i Enterprise Edition    9.2.0.8.0  Production
PL/SQL                         9.2.0.8.0  Production
TNS for 32-bit Windows:        9.2.0.8.0  Production  

I will say that I seriously wish the default state of a database column was NOT NULL – that is, instead of columns by default being NULLABLE, and therefore we would have to go out of our way to make them NULLABLE. most columns are in fact NOT NULL – and we just “forget” to state the obvious. This leads to indexes that sometimes cannot be used for a particular class of questions. For example:

scott@9iR2> create table T as
  2  select *
  3  from dba_objects
  4  where object_id is not null;

Table created.

scott@9iR2> create unique index t_ind on t(object_id);

Index created.

scott@9iR2> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T',cascade=>TRUE);

PL/SQL procedure successfully completed.

scott@9iR2> set autotrace traceonly explain;
scott@9iR2> select count(*) from T;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=46 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T' (Cost=46 Card=33712)

The Optimizer could just use these index to count these rows, and the index is small – why isn’t the database doing that?”. The database will steadfastly refuse to use the index to count the rows in this case. The reason – it is entirely possible that an index key entry in the index we created could have an entirely NULL entry – and hence not every row in the table is represented in the index!

But the reality of the situation is that OBJECT_ID is in fact “NOT NULL” and all we need to do is tell the database this fact. And when we do – good things happen
 
scott@9iR2> alter table t modify object_id not null;

Table altered.

scott@9iR2> select count(*) from T;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'T_IND' (UNIQUE) (Cost=8 Card=33712)

In fact, the optimizer makes use of constraints all of the time (the cost base optimizer mostly, the rule based optimizer is pretty much brain dead in this respect:

scott@9iR2> select /*+ rule */ count(*) from T;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T'

Transporting Tablespace.

A transportable tablespace is a mechanism for taking the formatted datafiles of one database, and attaching them to another. Instead of unloading the data from one database to a flat file, or a DMP file, and then inserting that data into the other database, transporting a tablespace lets you move the data as fast as you can copy files.

scott@10G> create tablespace TTS_TBL datafile 'C:\trash\tts_tb1.dbf' size 1m extent management local uniform size 64k;

Tablespace created.

scott@10G> create tablespace TTS_INDX datafile 'C:\trash\tts_indx.dbf' size 1m extent management local uniform size 64k;

Tablespace created.

Elapsed: 00:00:00.64
scott@10G> create user tts_test identified by tts_test default tablespace tts_tbl temporary tablespace temp;

User created.

scott@10G> grant dba to tts_test;

Grant succeeded.

scott@10G> connect tts_test/tts_test;
Connected.
tts_test@10G> create table emp tablespace tts_tbl as select * from scott.emp;

Table created.

tts_test@10G> create index emp_ix on emp(empno) tablespace tts_indx;

Index created.


tts_test@10G> SELECT object_type, object_name, tablespace_name
  2  FROM user_objects, user_segments
  3  WHERE  object_name = segment_name
  4  /

OBJECT_TYPE         OBJECT_NAME                    TABLESPACE_NAME
------------------- ------------------------------ ------------------------------
TABLE               EMP                            TTS_TBL
INDEX               EMP_IX                         TTS_INDX

Prior to attempting to export, we need to ensure we have a self‐contained set of objects for transport. We can transport a table without its indexes, but we cannot transport an index without its tables. The following shows the routine that we should use to check if a tablespace, or set of tablespaces, are self‐contained.



tts_test@10G> EXEC sys.dbms_tts.transport_set_check('TTS_TBL',TRUE);

PL/SQL procedure successfully completed.

tts_test@10G> select * from sys.transport_set_violations;

no rows selected

tts_test@10G> EXEC sys.dbms_tts.transport_set_check('TTS_INDX',TRUE);

PL/SQL procedure successfully completed.

tts_test@10G> select * from sys.transport_set_violations;

VIOLATIONS
------------------------------------------------------------------------------------------------------------
Index TTS_TEST.EMP_IX in tablespace TTS_INDX points to table TTS_TEST.EMP in tablespace TTS_TBL

tts_test@10G> EXEC sys.dbms_tts.transport_set_check('TTS_TBL,TTS_TBL',TRUE);

PL/SQL procedure successfully completed.


tts_test@10G> select * from sys.transport_set_violations;

no rows selected


This shows that we can transport TTS_TBL, because it only contains table data and is selfcontained. Any attempt to transport TTS_INDX, however, would fail since it contains indexes, but not the tables on which they are based. Lastly, both the tablespaces, TTS_TBL and TTS_INDX may be transported together, as we would be transporting both the tables and the indexes.

tts_test@10G> CONNECT scott/oracle
Connected.
scott@10G> alter tablespace tts_tbl read only;

Tablespace altered.

scott@10G> alter tablespace tts_indx read only;

Tablespace altered.

Exporting Tablespace
=====================
D:\QA>exp userid="""sys/oracle as sysdba""" transport_tablespace=Y tablespaces=(TTS_TBL,TTS_INDX) file=d:\qa\exp_tbs.dmp

Export: Release 10.2.0.3.0 - Production on Wed Feb 24 23:00:35 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TTS_TBL ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                            EMP
For tablespace TTS_INDX ...
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

Importing Tablespace
=====================
D:\QA>imp userid="""sys/itg9999@dbss8145:1521:testdb as sysdba""" file=d:\qa\exp_tbs.dmp transport_tablespace=Y "datafiles=(d:\qa\tts_TB1.DBF,d:\qa\tts_INDX.DBF)"

Import: Release 10.2.0.3.0 - Production on Wed Feb 24 23:13:00 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing TTS_TEST's objects into TTS_TEST
. . importing table                          "EMP"
. importing SYS's objects into SYS
Import terminated successfully without warnings.

D:\QA>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Feb 24 23:15:16 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

idle> connect tts_test/tts_test@dbss8145;
Connected.
tts_test@dbss8145> update emp set ename = lower(ename);
update emp set ename = lower(ename)
       *
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: 'D:\QA\TTS_TB1.DBF'


Elapsed: 00:00:00.07
tts_test@dbss8145> connect scott/oracle@dbss8145;
Connected.
scott@dbss8145> alter tablespace tts_tbl read write;

Tablespace altered.

Elapsed: 00:00:00.20
scott@dbss8145> alter tablespace tts_indx read write;

Tablespace altered.

Elapsed: 00:00:00.17
scott@dbss8145> connect tts_test/tts_test;
Connected.
tts_test@dbss8145> update emp set ename = lower(ename);

14 rows updated.

Elapsed: 00:00:00.04
tts_test@dbss8145>

Import (IMP) and Export (EXP) = COMPRESS (Y)

Import (IMP) and Export (EXP) are among the oldest surviving Oracle tools. They are command line tools used to extract tables, schemas, or entire database definitions from one Oracle instance, to be imported into another instance or schema.

COMPRESS (Y) -  This Parameter in EXP tool does not compress the contents of the exported data.It controls how the STORAGE clause for exported objects will be generated. If left as Y, the storage clause for objects will have an initial extent that is equal to the sum of its current extents. That is, EXP will generate a CREATE statement that attempts to fit the object into one single extent.

idle> connect scott/oracle
Connected.
scott@10G> drop table t purge;



Table dropped.

Elapsed: 00:00:02.51
scott@10G> create table t as select * from all_objects;

Table created.



Elapsed: 00:00:10.13
scott@10G> select sum(blocks) as blocks, sum(bytes) as bytes
  2  from user_extents
  3  where segment_name ='T';

    BLOCKS      BYTES
---------- ----------
       768    6291456

Elapsed: 00:00:00.11
scott@10G> SELECT dbms_metadata.get_ddl('TABLE','T') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------
  CREATE TABLE "SCOTT"."T"
   (    "OWNER" VARCHAR2(30) NOT NULL ENABLE,
        "OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE,
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER NOT NULL ENABLE,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE NOT NULL ENABLE,
        "LAST_DDL_TIME" DATE NOT NULL ENABLE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"



D:\>exp userid=scott/oracle file=d:\t.dmp log=d:\t_log.txt tables=T COMPRESS=Y

Export: Release 10.2.0.3.0 - Production on Tue Feb 16 23:51:52 2010

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                              T      51353 rows exported
Export terminated successfully without warnings.


D:\>imp userid=scott/tiger@10GR2 file=d:\t.dmp log=d:\imp_log.txt fromuser=scott touser=scott

Import: Release 10.2.0.3.0 - Production on Tue Feb 16 23:53:07 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table                            "T"      51353 rows imported
Import terminated successfully without warnings.




scott@10GR2> select sum(blocks) as blocks, sum(bytes) as bytes
  2  from user_extents
  3  where segment_name ='T';

    BLOCKS      BYTES
---------- ----------
       768    6291456

Elapsed: 00:00:00.79


scott@10GR2> SELECT dbms_metadata.get_ddl('TABLE','T') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."T"
   (    "OWNER" VARCHAR2(30) NOT NULL ENABLE,
        "OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE,
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER NOT NULL ENABLE,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE NOT NULL ENABLE,
        "LAST_DDL_TIME" DATE NOT NULL ENABLE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 6291456 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

Elapsed: 00:00:01.23



D:\>exp userid=scott/oracle file=d:\t.dmp log=d:\t_log.txt tables=T COMPRESS=N

Export: Release 10.2.0.3.0 - Production on Wed Feb 17 00:03:19 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                              T      51353 rows exported
Export terminated successfully without warnings.



D:\>imp userid=scott/tiger@10GR2 file=d:\t.dmp fromuser=scott touser=scott log=d:\imp_log.txt

Import: Release 10.2.0.3.0 - Production on Wed Feb 17 00:06:16 2010

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table                            "T"      51353 rows imported
Import terminated successfully without warnings.


scott@10GR2> select sum(blocks) as blocks, sum(bytes) as bytes
  2  from user_extents
  3  where segment_name ='T';

    BLOCKS      BYTES
---------- ----------
       768    6291456

Elapsed: 00:00:00.71

scott@10GR2> SELECT dbms_metadata.get_ddl('TABLE','T') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."T"
   (    "OWNER" VARCHAR2(30) NOT NULL ENABLE,
        "OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE,
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER NOT NULL ENABLE,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE NOT NULL ENABLE,
        "LAST_DDL_TIME" DATE NOT NULL ENABLE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"




ROT : It's safe to use COMPRESS = N and the use of locally managed tablespaces.

Analytics Using KEEP.

If we need to group records by certain fields and founded that some records Occupy Top most Position.
And If you need to apply Aggregates only those Top most record, this can be Easily done by using Analytics.

"Analytics are the coolest thing to happen to SQL since the keyword Select" & Comparitive Window functions are
going to be Even more Collest.

scott@10G> SELECT empno,
  2    ename,
  3    sal,
  4    deptno,
  5    TO_CHAR(hiredate,'yyyy')                                                  AS hiredate,
  6    dense_rank () over(partition BY deptno order by TO_CHAR(hiredate,'yyyy')) AS dnk
  7  FROM emp
  8  ORDER BY 4,5
  9  /

     EMPNO ENAME             SAL     DEPTNO HIRE   DNK
---------- ---------- ---------- ---------- ---- -----
      7782 CLARK            2450         10 1981     1
      7839 KING             5000         10 1981     1
      7934 MILLER           1300         10 1982     2
      7369 SMITH             800         20 1980     1
      7566 JONES            2975         20 1981     2
      7902 FORD             3000         20 1981     2
      7876 ADAMS            1100         20 1987     3
      7788 SCOTT            3000         20 1987     3
      7900 JAMES             950         30 1981     1
      7499 ALLEN            1600         30 1981     1
      7521 WARD             1250         30 1981     1
      7654 MARTIN           1250         30 1981     1
      7844 TURNER           1500         30 1981     1
      7698 BLAKE            2850         30 1981     1

14 rows selected.



Now, how to apply Aggregates only to those records with Dnk = 1 ?


scott@10G> SELECT empno,
  2    ename,
  3    sal,
  4    deptno,
  5    TO_CHAR(hiredate,'yyyy')                                                  AS hiredate,
  6    Avg(sal) keep(dense_rank first order by TO_CHAR(hiredate,'yyyy')) over(partition BY deptno) AS dnk
  7  FROM emp
  8  ORDER BY 4,5
  9  /

     EMPNO ENAME             SAL     DEPTNO HIRE         DNK
---------- ---------- ---------- ---------- ---- -----------
      7782 CLARK            2450         10 1981     3725.00
      7839 KING             5000         10 1981     3725.00
      7934 MILLER           1300         10 1982     3725.00
      7369 SMITH             800         20 1980      800.00
      7566 JONES            2975         20 1981      800.00
      7902 FORD             3000         20 1981      800.00
      7876 ADAMS            1100         20 1987      800.00
      7788 SCOTT            3000         20 1987      800.00
      7900 JAMES             950         30 1981     1566.67
      7499 ALLEN            1600         30 1981     1566.67
      7521 WARD             1250         30 1981     1566.67
      7654 MARTIN           1250         30 1981     1566.67
      7844 TURNER           1500         30 1981     1566.67
      7698 BLAKE            2850         30 1981     1566.67

14 rows selected.

Elapsed: 00:00:00.04

Segment Space Advisor in Oracle 10g

scott@9IR2> create table t as select * from all_objects;

Table created.

Elapsed: 00:00:02.63
scott@9IR2> drop table t;

Table dropped.

Elapsed: 00:00:00.12
scott@9IR2> create table t TABLESPACE DATA1 as select * from all_objects;

Table created.

Elapsed: 00:00:01.15
scott@9IR2> exec show_space(USER,'T','TABLE');
l_free_blks*******************  0
l_total_blocks****************  512
l_total_bytes*****************  4194304
l_unused_blocks***************  59
l_unused_bytes****************  483328
l_last_used_extent_file_id****  11
l_last_used_extent_block_id***  148745
l_last_used_block*************  69

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
scott@9IR2> DELETE FROM T WHERE ROWNUM <= 20000;

20000 rows deleted.

Elapsed: 00:00:00.71
scott@9IR2> COMMIT;

Commit complete.

Elapsed: 00:00:00.01
scott@9IR2> exec show_space(USER,'T','TABLE');
l_free_blks*******************  274
l_total_blocks****************  512
l_total_bytes*****************  4194304
l_unused_blocks***************  59
l_unused_bytes****************  483328
l_last_used_extent_file_id****  11
l_last_used_extent_block_id***  148745
l_last_used_block*************  69

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

Now we could see a lot of Free Blocks  under HWM. However to reset HWM we need to use ALTER TABLE MOVE command (don't forget to Rebuild Index after MOVE since Index remains in UNUSABLE state).In 10g, this task has become trivial; you can now use Inputs from Segment Space Advisor to shrink segments, tables, and indexes using SHRINK SPACE command to reclaim free blocks.
but no need to manually rebuild the Index.


scott@10G> create table T
  2  as
  3  select *
  4  from all_objects , (select level from dual connect by level <=&num);
Enter value for num: 2
old   4: from all_objects , (select level from dual connect by level <=&num)
new   4: from all_objects , (select level from dual connect by level <=2)

Table created.

Elapsed: 00:00:03.14
scott@10G> delete from t;

102706 rows deleted.

Elapsed: 00:00:03.40
scott@10G> commit;

Commit complete.

Elapsed: 00:00:00.00

scott@10G> column recommendations format a80;
scott@10G> SELECT ROUND((allocated_space/1024/1024),2) AS Allocated_mb,
  2    ROUND((used_space          /1024/1024),2) AS Used_mb,
  3    ROUND((reclaimable_space   /1024/1024),2) AS Reclaim_mb,
  4    recommendations
  5  FROM TABLE(dbms_space.asa_recommendations)
  6  WHERE segment_owner ='SCOTT'
  7  AND segment_name    ='T'
  8  AND segment_type    ='TABLE'
  9  /

ALLOCATED_MB    USED_MB RECLAIM_MB RECOMMENDATIONS
------------ ---------- ---------- --------------------------------------------------------------------------------
    53656.82   32219.14   21437.68 Enable row movement of the table SCOTT.T and perform shrink, estimated savings i
                                   s 22479040749 bytes.


Elapsed: 00:00:00.00

PGA Memory Usage In Collection's

scott@10G> SELECT  name,value
  2  FROM V$MYSTAT m,V$STATNAME s
  3  WHERE m.STATISTIC# = s.STATISTIC#
  4  AND   s.STATISTIC# IN (25,26)
  5  ;

NAME                                VALUE
------------------------------ ----------
session pga memory                 974420
session pga memory max            1957460

scott@10G> DECLARE
  2   TYPE T_Ty is TABLE OF T%ROWTYPE INDEX BY PLS_INTEGER;
  3   t1 T_TY;
  4  BEGIN
  5   SELECT *
  6   BULK COLLECT INTO t1
  7   FROM T;
  8  END;
  9  /

PL/SQL procedure successfully completed.

scott@10G> SELECT  name,value
  2  FROM V$MYSTAT m,V$STATNAME s
  3  WHERE m.STATISTIC# = s.STATISTIC#
  4  AND   s.STATISTIC# IN (25,26);

NAME                                VALUE
------------------------------ ----------
session pga memory                 974420
session pga memory max           75882068

scott@10G> DECLARE
  2   TYPE T_Ty is TABLE OF T%ROWTYPE INDEX BY PLS_INTEGER;
  3   t1 T_TY;
  4   t2 T_TY;
  5  BEGIN
  6   SELECT *
  7   BULK COLLECT INTO t1
  8   FROM T;
  9  
 10   SELECT *
 11   BULK COLLECT INTO t2
 12   FROM T; 
 13  END;
 14  /

PL/SQL procedure successfully completed.

scott@10G> SELECT  name,value
  2  FROM V$MYSTAT m,V$STATNAME s
  3  WHERE m.STATISTIC# = s.STATISTIC#
  4  AND   s.STATISTIC# IN (25,26);

NAME                                VALUE
------------------------------ ----------
session pga memory                 777812
session pga memory max          150855252

scott@10G> DECLARE
  2   TYPE T_Ty is TABLE OF T%ROWTYPE INDEX BY PLS_INTEGER;
  3   t1 T_TY;
  4   t2 T_TY;
  5  BEGIN
  6   SELECT *
  7   BULK COLLECT INTO t1
  8   FROM T;
  9  
 10   SELECT *
 11   BULK COLLECT INTO t2
 12   FROM T; 
 13  
 14   t1.DELETE;                                    <<<============= (Array Deleted)
 15   t2.DELETE;                                    <<<============= (Array Deleted.)
 16  END;
 17  /

PL/SQL procedure successfully completed.

scott@10G> SELECT  name,value
  2  FROM V$MYSTAT m,V$STATNAME s
  3  WHERE m.STATISTIC# = s.STATISTIC#
  4  AND   s.STATISTIC# IN (25,26);

NAME                                VALUE
------------------------------ ----------
session pga memory                 777812
session pga memory max          150855252            <<<=== (PGA Memory doesn't get off even Array Deleted.)

scott@10G> disconn
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
scott@10G> connect scott/oracle
Connected.
scott@10G> SELECT  name,value
  2  FROM V$MYSTAT m,V$STATNAME s
  3  WHERE m.STATISTIC# = s.STATISTIC#
  4  AND   s.STATISTIC# IN (25,26);

NAME                                VALUE
------------------------------ ----------
session pga memory                 646740
session pga memory max             777812

scott@10G> DECLARE
  2   TYPE T_Ty is TABLE OF T%ROWTYPE INDEX BY PLS_INTEGER;
  3   t1 T_TY;
  4   t2 T_TY;
  5  BEGIN
  6   SELECT *
  7   BULK COLLECT INTO t1
  8   FROM T;
  9  
 10   t1.DELETE;
 11  
 12   SELECT *
 13   BULK COLLECT INTO t2
 14   FROM T;    
 15  END;
 16  /

PL/SQL procedure successfully completed.

scott@10G> SELECT  name,value
  2  FROM V$MYSTAT m,V$STATNAME s
  3  WHERE m.STATISTIC# = s.STATISTIC#
  4  AND   s.STATISTIC# IN (25,26)
  5  ;

NAME                                VALUE
------------------------------ ----------
session pga memory                 712276
session pga memory max           75816532        <<<=========== ( Even though we used two Arrays, The second one just used the space the First one used.)

Rule of Thumb (ROT) : DELETE Collection Elements once Collection elements are Utilized (or) no more needed.

Use the Correct Datatype

rajesh@ORA10G> create table t as
  2  select to_char(to_date('01/01/2000','mm/dd/yyyy')+rownum,'yyyymmdd') as str_date,
  3        to_number(to_char(to_date('01/01/2000','mm/dd/yyyy')+rownum,'yyyymmdd')) as num_date,
  4        to_date('01/01/2000','mm/dd/yyyy')+rownum as date_date
  5  from all_objects
  6  order by dbms_random.random ;

Table created.
rajesh@ORA10G>
rajesh@ORA10G> create index t_ind_01 on t(str_date);

Index created.
rajesh@ORA10G> create index t_ind_02 on t(num_date);

Index created.
rajesh@ORA10G> create index t_ind_03 on t(date_date);

Index created.
rajesh@ORA10G> begin
  2     dbms_stats.gather_table_stats
  3     (user,'T',
  4      method_opt=>'for all indexed columns size 254');
  5  end;
  6  /

PL/SQL procedure successfully completed.
rajesh@ORA10G>

Now, let’s see what happens when we query this table using the string date column and the real date column.
Pay close attention to the Card= component of the plan:

rajesh@ORA10G> select * from t
  2  where str_date between
  3  '20121231' and '20130101';

STR_DATE   NUM_DATE DATE_DATE
-------- ---------- -----------------------
20130101   20130101 01-JAN-2013 12:00:00 AM
20121231   20121231 31-DEC-2012 12:00:00 AM

2 rows selected.
rajesh@ORA10G> select * from table( dbms_xplan.display_cursor() );

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID  5qzjrcx91mvp6, child number 1
-------------------------------------
select * from t where str_date between '20121231' and '20130101'
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    55 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |   212 |  4876 |    55   (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("STR_DATE"<='20130101' AND "STR_DATE">='20121231'))

18 rows selected.

now querying against the num_date column

rajesh@ORA10G>
rajesh@ORA10G> select * from t
  2  where num_date
  3  between 20121231 and 20130101;

STR_DATE   NUM_DATE DATE_DATE
-------- ---------- -----------------------
20130101   20130101 01-JAN-2013 12:00:00 AM
20121231   20121231 31-DEC-2012 12:00:00 AM

2 rows selected.
rajesh@ORA10G>
rajesh@ORA10G> select * from table( dbms_xplan.display_cursor() );

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID  61pahf9s175bc, child number 0
-------------------------------------
select * from t where num_date between 20121231 and 20130101
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    55 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |   212 |  4876 |    55   (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("NUM_DATE"<=20130101 AND "NUM_DATE">=20121231))

18 rows selected.

rajesh@ORA10G> select * from t
  2  where date_date
  3  between to_date('20121231','yyyymmdd')
  4  and to_date('20130101','yyyymmdd');

STR_DATE   NUM_DATE DATE_DATE
-------- ---------- -----------------------
20121231   20121231 31-DEC-2012 12:00:00 AM
20130101   20130101 01-JAN-2013 12:00:00 AM

2 rows selected.
rajesh@ORA10G>
rajesh@ORA10G> select * from table( dbms_xplan.display_cursor() );

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID  2140x47gjtgmk, child number 0
-------------------------------------
select * from t where date_date between to_date('20121231','yyyymmdd') and
to_date('20130101','yyyymmdd')
Plan hash value: 1136801923
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T        |     1 |    23 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IND_03 |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DATE_DATE">=TO_DATE('2012-12-31 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND "DATE_DATE"<=TO_DATE('2013-01-01 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss'))

So, what happened there? Well, the optimizer understands VARCHAR2 types and it understands DATE types.
The optimizer knows that between the two DATE items December 31, 2012, and January 1, 2013, there is only one day. The optimizer also thinks that between the two string items ‘20121231’ and ‘20130101’ there are a whole bunch of values.The cardinality is thrown off.


I have a very simple rule: Put dates in dates, numbers in numbers, and strings in strings.
Never use a datatype to store something other than what it was designed for, and use the most specific type possible. Furthermore, only compare dates to dates, strings to strings, and numbers to numbers. 

When dates and numbers are stored in strings, or stored using inappropriate lengths, your system suffers:
 

Space Filled in Current Data Files.

select (select decode(extent_management,'LOCAL','*',' ') ||
               decode(segment_space_management,'AUTO','a ','m ')
          from dba_tablespaces where tablespace_name = b.tablespace_name) || nvl(b.tablespace_name,
             nvl(a.tablespace_name,'UNKOWN')) name,
       kbytes_alloc kbytes,
       kbytes_alloc-nvl(kbytes_free,0) used,
       nvl(kbytes_free,0) free,
       ((kbytes_alloc-nvl(kbytes_free,0))/
                          kbytes_alloc)*100 pct_used,
       nvl(largest,0) largest,
       nvl(kbytes_max,kbytes_alloc) Max_Size,
       decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
from ( select sum(bytes)/1024 Kbytes_free,
              max(bytes)/1024 largest,
              tablespace_name
       from  sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024 Kbytes_alloc,
              sum(maxbytes)/1024 Kbytes_max,
              tablespace_name
       from sys.dba_data_files
       group by tablespace_name
       union all
      select sum(bytes)/1024 Kbytes_alloc,
              sum(maxbytes)/1024 Kbytes_max,
              tablespace_name
       from sys.dba_temp_files
       group by tablespace_name  )b
where a.tablespace_name (+) = b.tablespace_name
order by &1
/

Un-Indexed Foreign key

The number one cause of deadlocks in the Oracle database, in my experience, is
unindexed foreign keys. There are two cases where Oracle will place a full table lock on a
child table after modification of the parent table:

• If I update the parent table's primary key (a very rare occurrence if you follow the rules of relational databases that primary keys should be immutable), the child table will be locked in the absence of an index. 
• If I delete a parent table row, the entire child table will be locked (in the absence of an index) as well

drop table t1 purge;
drop table t2 purge;

create table t1(x int primary key);
create table t2(y int references t1);

Scenario - I
                         Session 1

 

                     Session 2

 

                   Session 1 (gets blocked because of Un - Indexed Foreign keys)
 

  

Scenairo - II 

                    

drop table t2 purge;
drop table t1 purge;

create table t1(x int primary key);
create table t2(y int references t1);
create index t2_ind on t2(y);

                Session 1

  

              Session 2

  

              Session 1  (No Blocking now, since Foreign keys is Indexed).

 

So, when do you not need to index a foreign key? The answer is, in general, when the following conditions are met:

• You do not delete from the parent table.
•  
• You do not update the parent table's unique/primary key value
• You do not join from the parent to the child (like DEPT to EMP)

If you satisfy all three above, feel free to skip the index - it is not needed.

So what kind of Index should be created on Foreign keys?

    Any index that contains the foreign key on the leading edge will do

-- eg:  create table emp ( empno int primary key, ...  deptno references DEPT );

•     create index on dept(deptno,empno);  -- this is OK
•     create index on dept(empno,deptno);  -- this will not prevent the fkey lock
•     create index on dept(deptno);  -- this is OK 

Show Space Procedure - To Calculate Object Size

create or replace PROCEDURE SHOW_SPACE (    p_Owner IN VARCHAR2 DEFAULT USER,
                                                  p_segment_name IN VARCHAR2,
                                                  p_segment_type IN VARCHAR2 DEFAULT 'TABLE',
                                                  p_partition_name IN VARCHAR2 DEFAULT NULL
                                              )
AUTHID CURRENT_USER
AS
  l_total_blocks NUMBER;
  l_total_bytes NUMBER;
  l_unused_blocks NUMBER;
  l_unused_bytes NUMBER;
  l_last_used_extent_file_id NUMBER;
  l_last_used_extent_block_id NUMBER;
  l_last_used_block NUMBER;
  l_unformatted_blocks NUMBER;
  l_unformatted_bytes NUMBER;
  l_fs1_blocks  NUMBER;
  l_fs1_bytes NUMBER;
  l_fs2_blocks NUMBER;
  l_fs2_bytes NUMBER;
  l_fs3_blocks NUMBER;
  l_fs3_bytes NUMBER;
  l_fs4_blocks NUMBER;
  l_fs4_bytes NUMBER;
  l_full_blocks NUMBER;
  l_full_bytes NUMBER;
  PROCEDURE p(p_Param1 IN VARCHAR2,p_Param2 IN VARCHAR2)
  AS
  BEGIN
          dbms_output.put_line ( rpad(p_param1,30,'*')||'  '|| p_param2);
  END P;
BEGIN
  dbms_space.unused_space(segment_owner=>p_Owner,
                                                  segment_name => p_segment_name,
                                                  segment_type=> p_segment_type,
                                                  total_blocks => l_total_blocks,
                                                  total_bytes => l_total_bytes,
                                                  unused_blocks => l_unused_blocks,
                                                  unused_bytes => l_unused_bytes,
                                                  last_used_extent_file_id => l_last_used_extent_file_id,
                                                  last_used_extent_block_id => l_last_used_extent_block_id,
                                                  last_used_block => l_last_used_block,
                                                  partition_name => p_partition_name
                                                  );
  dbms_space.space_usage (segment_owner=>p_Owner,
                                                  segment_name => p_segment_name,
                                                  segment_type=> p_segment_type,
                                                  unformatted_blocks => l_unformatted_blocks,
                                                  unformatted_bytes => l_unformatted_bytes,
                                                  fs1_blocks => l_fs1_blocks,
                                                  fs1_bytes => l_fs1_bytes,
                                                  fs2_blocks => l_fs2_blocks,
                                                  fs2_bytes => l_fs2_bytes,
                                                  fs3_blocks => l_fs3_blocks,
                                                  fs3_bytes => l_fs3_bytes,
                                                  fs4_blocks => l_fs4_blocks,
                                                  fs4_bytes => l_fs4_bytes,
                                                  full_blocks => l_full_blocks,
                                                  full_bytes => l_full_bytes,
                                                  partition_name => p_partition_name
                                                  );
  p('l_total_blocks',l_total_blocks);
  p('l_total_bytes',l_total_bytes);
  p('l_unused_blocks',l_unused_blocks);
  p('l_unused_bytes',l_unused_bytes);
  p('l_last_used_extent_file_id',l_last_used_extent_file_id);
  p('l_last_used_extent_block_id',l_last_used_extent_block_id);
  p('l_last_used_block',l_last_used_block);
  p('l_unformatted_blocks',l_unformatted_blocks);
  p('l_unformatted_bytes',l_unformatted_bytes);
  p('l_fs1_blocks',l_fs1_blocks);
  p('l_fs1_bytes',l_fs1_bytes);
  p('l_fs2_blocks',l_fs2_blocks);
  p('l_fs2_bytes',l_fs2_bytes);
  p('l_fs3_blocks',l_fs3_blocks);
  p('l_fs3_bytes',l_fs3_bytes);
  p('l_fs4_blocks',l_fs4_blocks);
  p('l_fs4_bytes',l_fs4_bytes);
  p('l_full_blocks',l_full_blocks);
  p('l_full_bytes',l_full_bytes);
END show_space;


Procedure created.

Total Blocks.....  Total blocks allocated to the table
Total Bytes......  Total bytes allocated to the table
Unused Blocks....  Blocks that have never contained data
Unused Bytes.....  The above in bytes
This report does show blocks above the high water mark.  Unused Blocks are exactly the
block above the high water mark. 

dbms_space.space_usage Can only be used on object in a AUTO SEGMENT SPACE Managed tablespace,
for  Manual Segment Space managment need to use dbms_space.free_blocks  instead.

For Manual Segment Space Managment (9i & Prior version)



create or replace procedure show_space
(       p_owner                         in varchar2 default USER        ,
       p_segment_name          in varchar2                                     ,
       p_segment_type          in varchar2     default 'TABLE' ,
       p_partition_name        in varchar2 default null
)
authid current_user
as
    l_free_blks number := 0;
    l_total_blocks number := 0;
    l_total_bytes  number := 0;
    l_unused_blocks number := 0;
    l_unused_bytes  number := 0;
    l_last_used_extent_file_id number := 0;
    l_last_used_extent_block_id number := 0;
    l_last_used_block number := 0;
    procedure p(p_param1 in varchar2,p_param2 in varchar2)
    as
    begin
           dbms_output.put_line ( rpad(p_param1,30,'*')||'  '|| p_param2);
    end p;
begin
    dbms_space.free_blocks  (       segment_owner=> p_owner,
                                                          segment_name => p_segment_name,
                                                          segment_type => p_segment_type,
                                                          freelist_group_id => 0,
                                                          free_blks => l_free_blks,
                                                          scan_limit => null,
                                                          partition_name => p_partition_name
                                                  );

    dbms_space.unused_space (       segment_owner => p_owner,
                                                          segment_name => p_segment_name,
                                                          segment_type => p_segment_type,
                                                          total_blocks => l_total_blocks,
                                                          total_bytes  => l_total_bytes,
                                                          unused_blocks => l_unused_blocks,
                                                          unused_bytes => l_unused_bytes,
                                                          last_used_extent_file_id => l_last_used_extent_file_id,
                                                          last_used_extent_block_id => l_last_used_extent_block_id,
                                                          last_used_block => l_last_used_block,
                                                          partition_name => p_partition_name
                                                  );
    p('l_free_blks',l_free_blks);
    p('l_total_blocks',l_total_blocks);
    p('l_total_bytes',l_total_bytes);
    p('l_unused_blocks',l_unused_blocks);
    p('l_unused_bytes',l_unused_bytes);
    p('l_last_used_extent_file_id',l_last_used_extent_file_id);
    p('l_last_used_extent_block_id',l_last_used_extent_block_id);
    p('l_last_used_block',l_last_used_block);
end show_space;

scott@9iR2> SELECT tablespace_name,segment_space_management
  2  FROM dba_tablespaces
  3  WHERE segment_space_management ='MANUAL'
  4  AND tablespace_name ='DATA1'
  5  /

TABLESPACE_NAME                SEGMEN
------------------------------ ------
DATA1                          MANUAL

Elapsed: 00:00:00.01
scott@9iR2> exec show_space(USER,'ESR_FILES','TABLE');
l_free_blks*******************  0
l_total_blocks****************  8
l_total_bytes*****************  65536
l_unused_blocks***************  7
l_unused_bytes****************  57344
l_last_used_extent_file_id****  11
l_last_used_extent_block_id***  148529
l_last_used_block*************  1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.20