Oracle tablespace space usage stats.

In Oracle you can look tablespace space usage stats from couple of places.


You can use dba_data_files and dba_segments to check free space, used space, total space, plus percents of free space. Here is example:
SQL> select dfiles.tablespace_name "Tablespace", totalusedspace "Used MB",
 (dfiles.totalspace - tusage.totalusedspace) "Free MB",
 dfiles.totalspace "Total MB",
 round(100 * ( (dfiles.totalspace - tusage.totalusedspace)/ dfiles.totalspace)) "Free %"
 from
 (select tablespace_name,
 round(sum(bytes) / 1048576) TotalSpace
 from dba_data_files
 group by tablespace_name) dfiles,
 (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
 from dba_segments
 group by tablespace_name) tusage
 where dfiles.tablespace_name = tusage.tablespace_name ;

Tablespace                        Used MB    Free MB   Total MB     Free %
------------------------------ ---------- ---------- ---------- ----------
TEST2_DATA                               3        253         256           99
SYSAUX                                 1094        56          1150           5
UNDOTBS1                              145       2070       2215          93
TEST_DATA                            1369       443        1812           24


Or you can use dba_free_space to check free (usable) space in tablespace. Here is example:
SQL> select tablespace_name , sum(bytes)/1024/1024 "free space Mb" from dba_free_space group by tablespace_name;

TABLESPACE_NAME         free space Mb
------------------------------ -------------
TEST2_DATA                        251.625
SYSAUX                               55.25
UNDOTBS1                          1928.0625
TEST_DATA                          573.25



NOTE!! (in Oracle 10/11) If you see differences between these two selects  (free space might be different) that is because dba_free_space does not show data that is in recyclebin but dba_segment shows that data. If you delete data from table or whole table/index that data goes into recycle bin (it is still in the same tablespace) but space used by recyclebin can be used for new data if it is needed. And this is why dba_free_space does not show it as used space. You can clean recyclebin with purge.

For certain user recyclebin:
SQL> purge recyclebin;

All recyclebins (with user that have privileges):
SQL> purge dba_recyclebin;

Oracle create temporary tablespace group

Since Oracle 10 you could have made temporary tablespace group. In this group you can add multiple temporary tablespaces which contains multiple tempfiles. You can also set temporary tablespace group to your default temp tablespace.

When you use temporary tablespace group Oracle will distribute temp operations in all tablespaces in group. If you use single temp tablespace Oracle won't distribute operations on several tempfiles even tablespace would have more tempfiles in it. So with temporary tablespace group you can spread I/O from big sort operations on several tempfiles (and different disks if you have the tempfiles in different disks).


# Create 2 new temp tablespaces into temp tablespace group temp_group (this will also create the temporary tablespace group if it does not exists yet):

SQL> create temporary tablespace temp_g01_tbs
tempfile '/u01/app/oradata/orcl/TEMPg01.DBF' size 2G
extent management local uniform size 1M
tablespace group temp_group;


SQL> create temporary tablespace temp_g02_tbs
tempfile '/u01/app/oradata/orcl/TEMPg02.DBF' size 2G
extent management local uniform size 1M
tablespace group temp_group;


From following views you can see tempfiles and temporary tablespace groups:
SQL> select * from dba_temp_files;
SQL> select * from dba_tablespace_groups;


Here you can see users default tablespaces:
SQL> select username, default_tablespace, temporary_tablespace from dba_users order by username;

And here you can change database default temporary tablespace to temporary tablespace group you just created:
SQL> alter database default temporary tablespace temp_group;

Oracle which tables given table is using as foreign key tables.

With the following SQL-clause you can check which tables the table that you put into SQL-clause is using as foreign key tables:

SQL> select dbac.table_name,
       dbac.constraint_name "Primary Constraint Name",
       r.constraint_name "Referenced Constraint Name"
from dba_constraints dbac,
     (select cons.constraint_name,
             cons.r_constraint_name,
             cons.table_name
      from dba_constraints cons
      where table_name='<TABLE_NAME>'
      and constraint_type='R') r
where dbac.constraint_name=r.r_constraint_name;

Example:

SQL> select dbac.table_name,
       dbac.constraint_name "Primary Constraint Name",
       r.constraint_name "Referenced Constraint Name"
from dba_constraints dbac,
     (select cons.constraint_name,
             cons.r_constraint_name,
             cons.table_name
      from dba_constraints cons
      where table_name='TEST_TABLE2'
      and constraint_type='R') r
where dbac.constraint_name=r.r_constraint_name;

TABLE_NAME                     Primary Constraint Name
------------------------------ ------------------------------
Referenced Constraint Name
------------------------------
TEST_TABLE1                   PKEY_TEST_TABLE1_TEST_TYPE
FKEY_TEST_TABLE2_TEST_TYPE

Oracle which tables are using given table as foreign key table.

With the following SQL-clause you can check which tables are using table that you put into SQL-clause as foreign key table:

SQL> select table_name, constraint_name, status, owner
from all_constraints
where r_owner = '<SCHEMA_NAME>'
and constraint_type = 'R'
and r_constraint_name in
 (
   select constraint_name from all_constraints
   where constraint_type in ('P', 'U')
   and table_name = '<TABLE_NAME>'
   and owner = '<SCHEMA_NAME>'
 )
order by table_name, constraint_name;

Example:

SQL> select table_name, constraint_name, status, owner
from all_constraints
where r_owner = 'TEST_USER'
and constraint_type = 'R'
and r_constraint_name in
 (
   select constraint_name from all_constraints
   where constraint_type in ('P', 'U')
   and table_name = 'TEST_TABLE1'
   and owner = 'TEST_USER'
 )
order by table_name, constraint_name;





TABLE_NAME                     CONSTRAINT_NAME                STATUS
------------------------------ ------------------------------ --------
OWNER
------------------------------
TEST_TABLE2                        FKEY_TEST_TABLE2_TEST_TYPE      ENABLED
TEST_USER

Oracle kill all sessions from one schema.

Sometimes you need to kill all sessions from one schema. You can do that with following sql's.

1.) Kill all session from one schema from single instance:


SQL> set head off
SQL> spool kill_sessions.tmp
SQL> select 'alter system kill session '''||sid||','||serial#|| ''' immediate ;' from v$session where username='TEST_USER' ;
SQL> spool off
SQL> @kill_sessions.tmp


When you run kill_sessions.tmp script it will give you two "SP2-0734: unknown command beginning ..."
errors because there are orginal select clause and spool off  in file. But you can ignore those. Or you can also remove those two lines from tmp file before running it.


2.) Kill all sessions from one schema from whole RAC cluster (all instances):

SQL> set head off
SQL> spool kill_sessions_rac.tmp
SQL> select 'alter system kill session '''||sid||','||serial#|| ',@' || inst_id || ''' immediate;' from gv$session where username='TEST_USER' ;
SQL> spool off
SQL> @kill_sessions_rac.tmp

When you run kill_sessions.tmp script it will give same errors as single instance version.

And of course you can also run only select clause and run manually kill clauses it prints.