Four ways to increase table space size
MEATHOD1: Adding data files to table spaces
ALTER tablespace App_Data ADD datafile
' D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03. DBF ' SIZE 50M;
MEATHOD2: Add data file and allow data file to grow automatically
ALTER tablespace App_Data ADD datafile
' D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04. DBF ' SIZE 50M
Autoextend on NEXT 5M MAXSIZE 100M;
MEATHOD3: Allow data files that already exist to grow automatically
ALTER DATABASE datafile ' D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03. DBF '
Autoextend on NEXT 5M MAXSIZE 100M;
MEATHOD4: Manually changing the size of existing data files
ALTER DATABASE datafile ' D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP02. DBF '
RESIZE 100M;
--Querying table space usageSELECT Upper(f.tablespace_name) "tablespace name", D.TOT_GROOTTE_MB "table space size (M)", D.TOT_GROOTTE_MB-f.total_bytes "used Space (M)", To_char (Round((D.TOT_GROOTTE_MB-F.total_bytes)/D.tot_grootte_mb* -,2),'990.99') || '%'"Use ratio", f.total_bytes "free Space (M)", f.max_bytes "Max Block (M)" from(SELECTTablespace_name,Round(Sum(BYTES)/(1024x768 * 1024x768),2) Total_bytes,Round(Max(BYTES)/(1024x768 * 1024x768),2) Max_bytes fromSYS. Dba_free_spaceGROUP bytablespace_name) F, (SELECTDD. Tablespace_name,Round(Sum(DD. BYTES)/(1024x768 * 1024x768),2) TOT_GROOTTE_MB fromSYS. Dba_data_files DDGROUP byDD. Tablespace_name) DWHERED.tablespace_name=F.tablespace_nameORDER by 1--Free space for query table spaceSelectTablespace_name,Count(*) asExtendsround(sum(bytes)/ 1024x768 / 1024x768,2) asMB,sum(blocks) asBlocks fromDba_free_spaceGroup byTablespace_name;--Total capacity of query table spaceSelectTablespace_name,sum(bytes)/ 1024x768 / 1024x768 asMB fromDba_data_filesGroup byTablespace_name;--Querying table space usageSELECTTotal.tablespace_name,Round(Total. MB,2) asTOTAL_MB,Round(Total. MB-Free. MB,2) asUSED_MB,Round((1 -Free. MB/Total. MB)* -,2) || '%' asused_pct from(SELECTTablespace_name,Sum(bytes)/ 1024x768 / 1024x768 asMB fromDba_free_spaceGROUP bytablespace_name) free, (SELECTTablespace_name,Sum(bytes)/ 1024x768 / 1024x768 asMB fromDba_data_filesGROUP bytablespace_name) TotalWHEREFree.tablespace_name=Total.tablespace_name;
-- identify the occupied process Select from --Erase process alterkill session'156,108' ; -- Delete User Drop User Cascade;
Oracle adds four ways to table space, querying table space usage