Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted May 6, 2002

Listen Software's How To: Tablespaces

By David Nishimoto

Adding a datafile

Indented lines in the following example are intended to be continuations of the lines above them. The lines are wrapped here for page formatting purposes. -Ed.

alter tablespace 
   user_data
add datafile 
   'e:\{sid}\data\usr2.dbf' 
   size 30M;

Autoextend

alter tablespace user_data autoextend off;

Next and Maxsize are set to zero.

alter tablespace user_data
add datafile 'e:\{sid}\data\usr2.dbf';

alter tablespace user_data autoextend on max_size=40M;

Sets the maximum disk space allowed for automatic extension of the datafile.

Taking the Tablespace Online and Offline

Online

alter tablespace user_data online;

Offline

alter tablespace user_data offline;

Hot Backup of a Tablespace

alter tablespace user_data begin backup;

alter tablespace user_data end backup;

This option does not prevent user from performing transactions.

Tablespace Coalese

alter tablespace user_data coalesce;

Coalesing gathers free segments. SMON usual cleans up free space.

Calculate Tablespace Size for Multiple Datafiles

Tablespace Byte Size
1 tablespace - multiple datafiles

Contribution by Stephen Mitchell

SELECT dfs.tablespace_name tablespace_name,
ddf.total_size total_size,
ddf.total_size - dfs.total_free total_used,
dfs.total_free total_free,
(ddf.total_size - dfs.total_free) / ddf.total_size * 100 cap,
dfs.total_chunks total_chunks,
dfs.largest_chunk largest_chunk
FROM (SELECT a.tablespace_name,
      SUM(a.bytes) / 1024 / 1024 total_free,
      COUNT(a.bytes) total_chunks,
      MAX(a.bytes) / 1024 / 1024 largest_chunk
      FROM dba_free_space a
      GROUP BY a.tablespace_name) dfs,
     (SELECT b.tablespace_name,
      SUM(b.bytes) / 1024 / 1024 total_size
      FROM dba_data_files b
      GROUP BY b.tablespace_name) ddf
WHERE dfs.tablespace_name = ddf.tablespace_name
ORDER BY dfs.tablespace_name


Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM