Oracle Tablespace Free and Used Space

The total space allocated to the tablespace can be got from dba_data_files oracle view.
The free space can be got from dba_free_space oracle view.
The difference of the two will give the used space for a tablespace.

Below query gives the output in a single select

select b.tablespace_name "Tablespace Name" ,
       kbytes_alloc/1024 Mbytes,
       (kbytes_alloc-nvl(kbytes_free,0))/1024 "Mbytes used",
       nvl(kbytes_free/1024,0) "Mbytes free",
       ((kbytes_alloc-nvl(kbytes_free,0))/
                          kbytes_alloc)*100 pct_used
from ( select sum(bytes)/1024 Kbytes_free,
        tablespace_name
       from  sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024 Kbytes_alloc,
              tablespace_name
       from sys.dba_data_files
       group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
--and a.tablespace_name = 'USERS'
order by a.tablespace_name;

Below is an alternate query for getting Used space and Total spcace for a tablespace

select sum(bytes)/1024 Kbytes_free,
        tablespace_name
       from  sys.dba_free_space
       group by tablespace_name;
select tablespace_name,
  sum(used),
  sum(total)
from
  (select
    /*+ ordered */
    d.tablespace_name,
    sum(d.bytes)/(1024*1024)-max(s.bytes) used,
    sum(d.bytes)/(1024*1024) total
  from
    (select tablespace_name,
      sum(bytes)/(1024*1024) bytes
    from
      (select
        /*+ ordered use_nl(obj tab) */
        distinct ts.name
      from sys.obj$ obj,
        sys.tab$ tab,
        sys.ts$ ts
      where obj.owner#                  = userenv('schemaid')
      and obj.obj#                      = tab.obj#
      and tab.ts#                       = ts.ts#
      and bitand(tab.property, 1)       = 0
      and bitand(tab.property, 4194400) = 0
      ) tn,
      dba_free_space sp
    where sp.tablespace_name = tn.name
    group by sp.tablespace_name
    ) s,
    dba_data_files d
  where d.tablespace_name = s.tablespace_name
  group by d.tablespace_name
  ) group by tablespace_name;

To get free and used space from Temporary Tablespace can select 

SELECT * FROM DBA_TEMP_FREE_SPACE;

To add a datafile to a tablespace

From the query to find the tablespace free and used space, we can determine when to add a new datafile to the tablespace. When the used space reaches a certain threshold, say 80% of the max tablespace size we can add a new datafile.

I want to add a new datafile of size 500M with a maxsize of 1G. Syntax for the same is below.

ALTER TABLESPACE HR ADD DATAFILE 'E:\INSTALL\21CXE\ORADATA\XE\HR_3.DBF' SIZE 500M AUTOEXTEND ON NEXT 500M MAXSIZE 1G;
tablespace HR altered.

Some useful data dictionary view queries for mviews 

To get the materialized view predicate information like refresh mode (ON COMMIT, ON DEMAND), refresh method (FAST, FORCE, COMPLETE), last refresh time, staleness, compile state select from ALL_MVIEWS

select * from all_mviews WHERE OWNER = 'HR';

TO get the last compile time(last_ddl_time), status (VALID/INVALID) etc select from all_objects

select * from all_objects where OWNER = 'HR' AND OBJECT_TYPE='MATERIALIZED VIEW';

To check the refresh statistics of the mview

select job, last_date last_refresh,next_date next_refresh, total_time,broken, failures, what
from all_jobs where what like '%dbms_refresh%';

To get details of the mview log. By default the materialized view log name will be MLOG$_<table_name>

select * from all_mview_logs;

ORA-12034: materialized view log on HR.EMPLOYEES younger than last refresh

--drop materialized view log on employees;
CREATE MATERIALIZED VIEW LOG ON EMPLOYEES;

--drop materialized view HR.MV_EMPLOYEES;
CREATE MATERIALIZED VIEW HR.MV_EMPLOYEES
  TABLESPACE HR 
  REFRESH FAST ON COMMIT
  WITH PRIMARY KEY
  AS SELECT * FROM EMPLOYEES EMPLOYEES;

COMMENT ON MATERIALIZED VIEW HR.MV_EMPLOYEES  IS 'snapshot table for snapshot HR.MV_EMPLOYEES';

--alter table employees drop column email_1; 
alter table employees add(email_1 varchar2(100));

Since a column is added/altered in underlying table, the materialized view becomes invalid. Since column in added, we drop and create the materialized view log so logs will also refect any changes in the new column.

--drop materialized view log on employees;
CREATE MATERIALIZED VIEW LOG ON EMPLOYEES;

Now if we try to update and commit the base table we get the below error

UPDATE HR.EMPLOYEES SET EMAIL = 'SKING' WHERE EMPLOYEE_ID = 100;

UPDATE "HR"."EMPLOYEES" SET EMAIL = 'SKING' WHERE ROWID = 'AAAShDAANAAAALVAAA' AND ORA_ROWSCN = '27926381' 

ORA-12034: materialized view log on HR.EMPLOYEES younger than last refresh 

To resolve this error we have to do a complete refresh of the mview as follows

SQL> execute dbms_snapshot.refresh ('HR.MV_EMPLOYEES', 'C');

Alternatively you can drop and recreate the materialized view also so that the timesptamp of the materialized view creation is later than that of the materialized view log.

Other user data dictionaly tables that you can query are

ALL_BASE_TABLE_MVIEWS

ALL_MVIEW_REFRESH_TIMES

ALL_REFRESH_CHILDREN

ALL_REGISTERED_MVIEWS

Query to get the shared pool size and available memory when using Automatic memory management

When using automatic memory management, the shared_pool_size parameter is v$parameter table will be 0. In that case you can directly get the value of the shared pool size for v$sgastat table as below.

SELECT CURRENT_SIZE "shared pool size", sga.bytes "free bytes", 
 ROUND((sga.bytes/CURRENT_SIZE)*100,2) "percentage Free"
FROM v$sgastat sga, V$MEMORY_DYNAMIC_COMPONENTS
WHERE sga.name = 'free memory'
AND sga.pool = 'shared pool'
AND COMPONENT = 'shared pool';

shared pool size	free bytes	percentage Free
956301312	176325120	18.44

v$sgastat view will show how fast the memory in the shared pool is being depleted. Free memory will go up and down throughout the day based on how the pieces are fragmented.

For some useful scripts to monitor the database health and utility scripts to get table indexes, enable/disable indexes and constraints etc you can refer to the scripts by Tim Hall in the link below

DBA monitoring and utility Scripts 

Determining the Hit Ratio for data from the Buffer Cache

To determine how often your data is being read from the memory cache, we can query the V$SYSSTAT table. It will give the hit ratio for selecting data from cache which can help determine if tuning/resizing of the buffer cache is required. This ratio should generally be greater than 95 percent. If this ratio is consistently below 95 percent you may need to increase the DB_CACHE-SIZE parameter in the initialization parameter file. Setting this size in the init.ora file will ensure that the database will not reduce this size of this cache below this set value, if you are using manual sizing or either of AMM or ASMM.             

Below is the query to find the Hit ratio from the buffer cache

SELECT 1 - ROUND((sum(decode(name,'physical reads', value, 0))/
       (sum(decode(name,'db block gets', value,0)) 
       + (sum(decode(name,'consistent gets', value, 0))))),4) "Buffer Cache Read Hit Ratio"
FROM V$SYSSTAT;

Buffer Cache Read Hit Ratio
0.991

Determining the Hit Ratio from the Library Cache

A library cache hit occurs when a database system successfully retrieves a parsed or compiled SQL statement or a PL/SQL block from the library cache, the need to reparse or compile it. For this the requested query should be present in the library cache which will lead to a faster execution of the query.

For optimal performance, the library cache hit ratio must be above 95 percent.

select sum(pins) "hits",
       sum(reloads) "misses",
       ROUND(sum(pins)/(sum(pins) + sum(reloads)),4) "Hit Ratio"
from v$LibraryCache;

hits	misses	Hit Ratio
183845	508	0.9972

References 

Oracle9i Replication Management API Reference
ORA-12034: materialized view log younger than last refresh

Books : Oracle9i Performance Tuning Tips & Techniques 

              by Richard J. Niemiec

Quote for the day
“You Are Never To Old To Set Another Goal Or To Dream A New Dream.”

- C.S. Lewis

"For I know the plans I have for you," declares the LORD, 

"plans to prosper you and not to harm you, 

  plans to give you hope and a future." 

  Jeremiah 29:11