How To create New Oracle Tablespace
SQL>create tablespace ts_stage datafile '+NSP' size 100m;
Adding a new datafile to Oracle Tablespace
SQL> alter tablespace ts_stage add datafile '+NSP' size 20m;
Creating Tablespace with Auto Extend On #
create tablespace ts_vmstat datafile ‘+NSP’ size 20m auto extend on;
Size of table space is automatically extended as the datafile is filled
We cannot auto extend on for tablespace. It can occupy the size of the file system. To avoid this scenario we specify max size option while we create tablespace on database as follows
SQL> create tablespace ts_vmdata datafile ‘+NSP’ size 100m AUTOEXTEND ON NEXT 20m MAXSIZE 200m;
When we create Tablespace, To Check Tablespace is Autoextensible ?
column tablespace_name format A20
column file_name format A20
column bytes format A10
column autoextensible format A15
column increment_by format A15
select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible from dba_data_files order by file_id;
Turnning off Auto Extended for datafile #
SQL> alter database datafile ‘PATH’ autoextend off;
To check datafile Offline and Online Status
column file_name format A50
column online_status format A18
select file_name,file_id,online_status from dba_data_files;
TO check datafile size
set linesize 120
col file_name formar a80
select file_name, sum(bytes)/1024/1024 df_size from dba_data_files group by file_name;
How to Resize datafile
SQL> alter database datafile '+NSP/oval009/datafile/ts_stage.281.912735591' resize 40m;
Query To check Free and Used Space
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;
If you are creating files using OMF
By default the files are autoextend on
set value for “db_create_file_dest” Parameter #
Related Topics
Procedure to flush SQL_ID from Shared Pool : Oracle 12c
Plan Hash Values for a Given SQLID Over Given Period & Historical Plans from the AWR
INDEX ORGANIZED TABLES: IOT
Create IPS Package: ADRCI
DBMS_METADATA.GET_DDL To extract Index DDL
--Nikhil Tatineni--
--Oracle Database--
