Zero Table by CommandZed

Previously I posted about how to import and create users in Oracle and in those examples I used the tablespaces provided by Oracle, users and temp.  But often you will want to create your own tablespace and use that one as the default tablespace for your users.

Here is an example of how to create a tablespace with the name foo of size 1 GB. Note that it is created in /Users/oracle/oradata/orcl which is the default folder for dataspaces on Mac OS X.

create tablespace
foo
datafile '/Users/oracle/oradata/orcl/foo.dbf'
size 1g;

Here is an example of how to create the same tablespace except now you are allowing it to grow bigger in 100 MB increments up to a maximum of 2 GB.  If you don’t specify the maximum it will grow unlimited.

create tablespace
foo
datafile '/Users/oracle/oradata/orcl/foo.dbf'
size 1g
autoextend on
next 100m
maxsize 2g;

If your tablespace is not big enough you can resize it using syntax like this.

alter database
datafile '/Users/oracle/oradata/orcl/foo.dbf'
resize 10g;

To see how big your tablespaces are you can use this query.

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;

Here is an example of how to drop a tablespace including its contents and datafiles.

drop tablespace foo including contents and datafiles

For further reading please see Oracle create tablespace & alter tablespace syntax, Tablespace – Oracle FAQ and How enlarge or decrease the size of an Oracle Tablespace.