Bigfile tablespace in oracle DB
Bigfile tablespace in oracle DB
--get list of tablespaces
select tablespace_name,bigfile from dba_tablespaces;
--get tablespace name and datafile location
set pages 999 long 999 lines 999
col TABLESPACE_NAME for a15
col file_name for a40
select tablespace_name,file_name,file_id,bytes/1024/1024 from dba_data_files order by 1;
--create bigfile tablespace
create bigfile tablespace dbtechbg datafile '/u02/oradata/dbtech/dbtechbg.dbf' size 10m;
--resize bigfile tablespace
alter database datafile '/u02/oradata/dbtech/dbtechbg.dbf' resize 50m;
--we can not add datafile to bigfile tablespace.
--if your db block size is 8K , we can resize bigfile to 32TB.