Below is the list of commands you can use to increase / decrease / Resize your tablespace in Oracle Database. I run this commands in Oracle 11g database however these should also run fine in other versions of Oracle as well.
Adding more tablespace
Alter tablespace add datafile 'datafile_location_where_file_will_be_added_without_file_name' size 1G;
Checking size of datafiles
Select name, bytes from v$datafile;
Decreasing Tablespace
Say your datafile is of size of 10 gb and you want to decrease it by 6 GB you can use below command.
Alter database datafile 'complete_path_to_datafile_goes_here' resize 4G;
Above command may not work it space is already utilized and data is alredy there in the file. Oracle may throw appropriate error message like below.
ORA-03297: file contains used data beyond requested RESIZE value
Adding more tablespace
Alter tablespace
Checking size of datafiles
Select name, bytes from v$datafile;
Decreasing Tablespace
Say your datafile is of size of 10 gb and you want to decrease it by 6 GB you can use below command.
Alter database datafile 'complete_path_to_datafile_goes_here' resize 4G;
Above command may not work it space is already utilized and data is alredy there in the file. Oracle may throw appropriate error message like below.
ORA-03297: file contains used data beyond requested RESIZE value
No comments:
Post a Comment