Monitoring tablespace usage

 Here is the script to monitor space used by tablespaces of a database. This is especially useful when tablespaces are autoextend off. Note that we are doing left outer join with dba_free_space, this is useful when the tablespace is completely exhausted and does not have any extents showing in dba_free_space (I do not find any query on Internet that does left outer join to dba_free_space, so wrote this one).

select *

  from (select ts.TABLESPACE_NAME                                       Tablespace_Name,

               round( ( total_mb                   ) )                  Total_MB,

               round( ( total_mb - nvl(free_mb, 0) ) )                  Used_MB,

               round( ( nvl(free_mb, 0)            ) )                  Free_MB,

               round( ( total_mb - nvl(free_mb, 0) ) * 100 / total_mb ) Pecentage_Used

          from (select TABLESPACE_NAME, sum(BYTES) / (1024*1024) total_mb

                  from dba_data_files

                 group by TABLESPACE_NAME) ts,

               (select TABLESPACE_NAME, sum(BYTES) / (1024*1024) free_mb

                  from dba_free_space

                 group by TABLESPACE_NAME) fs

         where ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+))

 where TABLESPACE_NAME like '%' -- Filter as needed

 order by Pecentage_Used desc;  

How to find to which database I'm connected?

This is a simple question for a DBA. Use this query for it.

select name from v$database;

But a normal user can use this only if there is select_catalog_role or select privilege on v$database views. What if a user without select privilege on v$database wants to see the database name? Here are the queries that can be used

select dbms_standard.database_name() database from dual;

select sys_context('userenv', 'db_name') from dual;

Range partitioned table with LOB example

create table t1

(

  a  number not null,

  b  date   not null,

  c  clob

) tablespace mytbs

  lob(c) store as lob_c (tablespace mytbs)

  partition by range (b)

  (

   partition b1 values less than (to_date('2010-01-01', 'YYYY-MM-DD')) tablespace mytbs

     lob(c) store as  lob_c1 (tablespace mytbs),

   partition b2 values less than (to_date('2011-01-01', 'YYYY-MM-DD')) tablespace mytbs

     lob(c) store as  lob_c2 (tablespace mytbs),

   partition b3 values less than (to_date('2012-01-01', 'YYYY-MM-DD')) tablespace mytbs

     lob(c) store as  lob_c3 (tablespace mytbs),

   partition b4 values less than (to_date('2013-01-01', 'YYYY-MM-DD')) tablespace mytbs

     lob(c) store as  lob_c4 (tablespace mytbs)

  )

;

create index t1_i1 on t1(a) local

(

  partition i1_1 tablespace mytbs,

  partition i1_2 tablespace mytbs,

  partition i1_3 tablespace mytbs,

  partition i1_4 tablespace mytbs

);

Dumping Oracle data block

Getting Absolute File number and Block number of a rowid

select rowid,

       dbms_rowid.rowid_to_absolute_fno(rowid, 'MURTY', 'T1') "File no" ,

       dbms_rowid.rowid_block_number(rowid) "Block no"

from t1;

ROWID              File no      Block no

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

AAADaGAAEAAAACEAAA          4        132

Getting header block of a segment

select header_file, header_block

from   dba_segments

where  owner='MURTY'

and    segment_name = 'T1';

HEADER_FILE HEADER_BLOCK

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

          4          130

Dumping a single block

alter system dump datafile 4 block 132;

Finding an extent start and end

select file_id, block_id, blocks

from   dba_extents

where  owner = 'MURTY'

and    segment_name = 'T1';

   FILE_ID   BLOCK_ID     BLOCKS

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

         4        128          8

Dumping more than one block

alter system dump datafile 4 block min 128 block max 135;

PL/SQL program to get metadata by transforming

set serveroutput on

set linesize 32767

declare

  handle        number;

  ddl_handle    number;

  modify_handle number;

  fetched_ddl   clob;

  owner         varchar2(100);

  table_name    varchar2(100);

  ts_arg        varchar2(10);

begin

  owner := 'MURTY';

  table_name := 'TAB1';

  -- Do we need tablespace names in table and index DDLs?

  ts_arg := upper('YES');

 

  handle := dbms_metadata.open('TABLE');

  dbms_metadata.set_filter(handle, 'NAME', table_name);

  dbms_metadata.set_filter(handle, 'SCHEMA', owner);

 

  modify_handle := dbms_metadata.add_transform(handle, 'MODIFY');

  -- Instead of original schema, we can remap to other schema, 

  -- blank '' to remove schema name

  dbms_metadata.set_remap_param(modify_handle, 'REMAP_SCHEMA', owner, '');

 

  ddl_handle := dbms_metadata.add_transform(handle, 'DDL');

  -- PK and FKs will be generated as ALTER, not in table definitions

  dbms_metadata.set_transform_param(ddl_handle, 'CONSTRAINTS_AS_ALTER', true);

  dbms_metadata.set_transform_param(ddl_handle, 'SQLTERMINATOR', true);

 

  if(ts_arg = 'YES')

  then

   dbms_metadata.set_transform_param(ddl_handle, 'SEGMENT_ATTRIBUTES', true);

  else

   dbms_metadata.set_transform_param(ddl_handle, 'SEGMENT_ATTRIBUTES', false);

  end if;

 

  -- We do not need INITIAL, NEXT extent in DDLs

  dbms_metadata.set_transform_param (ddl_handle, 'STORAGE', false);

 

  fetched_ddl := dbms_metadata.fetch_clob(handle);

  dbms_output.put_line(replace(

                               replace(fetched_ddl, '"', ''), -- Remove quotes in DDLs

                               ';',

  -- Extra blank line between table and constraint definitions

                               ';' || chr(10)  

                              )

                      );

  -- Now dependent INDEX objects

  handle := dbms_metadata.open('INDEX');

  dbms_metadata.set_filter(handle, 'BASE_OBJECT_NAME', table_name);

  dbms_metadata.set_filter(handle, 'BASE_OBJECT_SCHEMA', owner);

 

  modify_handle := dbms_metadata.add_transform(handle, 'MODIFY');

  dbms_metadata.set_remap_param(modify_handle, 'REMAP_SCHEMA', owner, '');

 

  ddl_handle := dbms_metadata.add_transform(handle, 'DDL');

  dbms_metadata.set_transform_param(ddl_handle, 'SQLTERMINATOR', true);

 

  if(ts_arg = 'YES')

  then

   dbms_metadata.set_transform_param(ddl_handle, 'SEGMENT_ATTRIBUTES', true);

  else

   dbms_metadata.set_transform_param(ddl_handle, 'SEGMENT_ATTRIBUTES', false);

  end if;

 

  dbms_metadata.set_transform_param (ddl_handle, 'STORAGE', false);

  -- Get one index at a time.

  fetched_ddl := replace(dbms_metadata.fetch_clob(handle), '"', '');

  while (fetched_ddl is not null) loop

    dbms_output.put_line(fetched_ddl);

    fetched_ddl := replace(dbms_metadata.fetch_clob(handle), '"', '');

  end loop;

end;

/