tablespace command

• create:

create tablespace mytablespace datafile '/path/to/file1.dbf' size 20m, '/path/to'file2.dbf' size 20m;

create tablespace

users

datafile

‘/ora01/oracle/oradata/booktst_users_01.dbf’ size 50m

autoextend on

next 10m

maxsize 100m;

• resize:

ALTER DATABASE DATAFILE /ora01/oracle/oradata/booktst_users_02.dbf’ resize 150M
alter tablespace data datafile '/path/to/file.dbf' resize 200M;

• alter
  

alter tabelspace data datafile '/path/to/file.dbf' autoextend on maxsize unlimited;

• move tablespace data file (2 ways: database way or tablespace way)

=====tablespace way========
note: tablespace must not be system tablespace
data file can be moved to new place during the instance is running.

1. set tablespace as offline. (alter tablespace tbsname offline;)
2. use OS commands to move file to new place(mv）
3. use alter tablespace command to rename filename in database (alter tablespace tbsname rename datafile '/path/to/file1.dbf' to '/path/to/file2.dbf';)
4. set tablespace status to online (alter tablespace tbsname online;)
   

======database way========
note: can be any data file (tablespace, online redo log file)

1. shutdown immediate
   
2. use OS commands to mv /path1/data1.dbf to /path2/data2.dbf
   
3. startup mount
   
4. alter database rename file '/path1/file1.dbf' to '/path2/file2.dbf'
5. alter database open;
   

• add:

alter tablespace name add datafile '/path/to/file.dbf' size 20m

• delete:

drop tablespace name
note: if tablespace got data inside, then using 'including contents and datafiles' keyword; this command won't delete tablespace datafile, you need to use operating system command to delete it.

• missing tablespace datafile, cannot start up oracle database:

Try the following:

shutdown abort
startup mount
alter database datafile '/directory/filename' offline drop;
alter database open;
drop tablespace ts_name;

In the above steps, you tell Oracle that the missing datafile is now missing (offline drop). You should then be able to open the database. Once open, you should be able to drop the tablespace with the missing datafile. You can then recreate the tablespace.