Oracle datafile info

Datafile info --


define tablespace_name='TBS_ANUJ'

set pages 500 lines 400 term off
col fn new_value fname
select 'a'||(max(length(FILE_NAME))+1) fn from DBA_DATA_FILES;
set pages 700 lines 500
col TABLESPACE_NAME for a15
col FILE_NAME format a95
col MAXSIZE_MB for 99999
col COMPRESS_FOR for a14
col fn new_value fname
select 'a'||(max(length(FILE_NAME))+1) fn from DBA_DATA_FILES;
col file_name 	        for &fname   heading "File Name"
select d.FILE_ID,d.TABLESPACE_NAME
, d.FILE_NAME
, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, 
d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
,CREATION_TIME
,t.BIGFILE,ENCRYPTED,t.COMPRESS_FOR,t.CONTENTS,d.STATUS
from dba_data_files d, v$datafile v
 ,DBA_TABLESPACES t 
where 1=1
and d.FILE_ID = v.FILE#
--and d.TABLESPACE_NAME='ANUJ' ---<<<
and d.tablespace_name = upper( decode('&&tablespace_name',null,d.tablespace_name,'&&tablespace_name'))
and d.TABLESPACE_NAME=t.TABLESPACE_NAME
order by d.TABLESPACE_NAME, d.FILE_NAME
;


CREATE TABLESPACE tbs_anuj DATAFILE '+DATA' SIZE 1M;

Tablespace created.



output --
  FILE_ID TABLESPACE_NAME File Name                                             SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB CREATION_ BIG ENC COMPRESS_FOR   CONTENTS           STATUS
---------- --------------- -------------------------------------------------- ---------- --- ---------- --------------- --------- --- --- -------------- --------------------- ---------
        18 TBS_ANUJ        +DATA/IBRAC/DATAFILE/tbs_anuj.3620.1210509953               1 NO           0               0 30-AUG-25 NO  NO                 PERMANENT          AVAILABLE


alter database datafile 18 resize  1g;


output --
   FILE_ID TABLESPACE_NAME File Name                                             SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB CREATION_ BIG ENC COMPRESS_FOR   CONTENTS           STATUS
---------- --------------- -------------------------------------------------- ---------- --- ---------- --------------- --------- --- --- -------------- --------------------- ---------
        18 TBS_ANUJ        +DATA/IBRAC/DATAFILE/tbs_anuj.3620.1210509953            1024 NO           0               0 30-AUG-25 NO  NO                 PERMANENT          AVAILABLE


alter database datafile 18 size 1G autoextend on next 10M;


col datafile for a100
select 'alter database datafile '''||file_name||''' AUTOEXTEND On;'  datafile from   dba_data_files 
where  1=1
--autoextensible='YES'
and tablespace_name = upper( decode('&&tablespace_name',null,tablespace_name,'&&tablespace_name'))
;



output --

DATAFILE
----------------------------------------------------------------------------------------------------
alter database datafile '+DATA/IBRAC/DATAFILE/tbs_anuj.3620.1210509953' AUTOEXTEND On;

1 row selected.




SQL> alter database datafile '+DATA/IBRAC/DATAFILE/tbs_anuj.3620.1210509953' AUTOEXTEND On;

Database altered.


output --
   FILE_ID TABLESPACE_NAME File Name                                             SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB CREATION_ BIG ENC COMPRESS_FOR   CONTENTS           STATUS
---------- --------------- -------------------------------------------------- ---------- --- ---------- --------------- --------- --- --- -------------- --------------------- ---------
        18 TBS_ANUJ        +DATA/IBRAC/DATAFILE/tbs_anuj.3620.1210509953            1024 YES      32768        .0078125 30-AUG-25 NO  NO                 PERMANENT          AVAILABLE




ALTER DATABASE DATAFILE 18 AUTOEXTEND ON MAXSIZE UNLIMITED;

Database altered.


output --

   FILE_ID TABLESPACE_NAME File Name                                             SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB CREATION_ BIG ENC COMPRESS_FOR   CONTENTS           STATUS
---------- --------------- -------------------------------------------------- ---------- --- ---------- --------------- --------- --- --- -------------- --------------------- ---------
        18 TBS_ANUJ        +DATA/IBRAC/DATAFILE/tbs_anuj.3620.1210509953            1024 YES      32768        .0078125 30-AUG-25 NO  NO                 PERMANENT          AVAILABLE

SQL>




set line 999 pages 999
col FILE_NAME format a50
col tablespace_name format a15
col "MAXSIZE GB"  for 99999.99
Select tablespace_name, file_name, autoextensible, bytes/1024/1024/1024 "USEDSPACE GB", maxbytes/1024/1024/1024 "MAXSIZE GB" 
from dba_data_files 
where 1=1
and tablespace_name = upper( decode('&&tablespace_name',null,tablespace_name,'&&tablespace_name'))
order by tablespace_name;



alter database datafile 18 autoextend off;  ---> MAXSIZE_MB=0


output --
  FILE_ID TABLESPACE_NAME File Name                                             SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB CREATION_ BIG ENC COMPRESS_FOR   CONTENTS           STATUS
---------- --------------- -------------------------------------------------- ---------- --- ---------- --------------- --------- --- --- -------------- --------------------- ---------
        18 TBS_ANUJ        +DATA/IBRAC/DATAFILE/tbs_anuj.3620.1210509953            1024 NO           0               0 30-AUG-25 NO  NO                 PERMANENT          AVAILABLE




define tablespace_name='TBS_ANUJ'

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

select 
tablespace_name,
dbms_metadata.get_ddl(object_type => 'TABLESPACE', name=> tablespace_name) metadata from dba_tablespaces
where 1=1
and tablespace_name = upper( decode('&&tablespace_name',null,tablespace_name,'&&tablespace_name'))
;

SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON


TBS_ANUJ

  CREATE TABLESPACE "TBS_ANUJ" DATAFILE
  SIZE 1073741824
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;
 
 
 




with con_id


define tablespace_name='INDX'

set pages 500 lines 400 term off
col fn new_value fname
select 'a'||(max(length(FILE_NAME))+1) fn from CDB_DATA_FILES;
set pages 700 lines 500
col CON_ID for 9999
col TABLESPACE_NAME for a15
col FILE_NAME format a95
col MAXSIZE_MB for 99999
col COMPRESS_FOR for a14
col fn new_value fname
select 'a'||(max(length(FILE_NAME))+1) fn from CDB_DATA_FILES;
col file_name           for &fname    heading "File Name"
select 
  d.CON_ID,
  d.FILE_ID,
  d.TABLESPACE_NAME,
  d.FILE_NAME,
  d.BYTES/1024/1024 SIZE_MB, 
  d.AUTOEXTENSIBLE, 
  d.MAXBYTES/1024/1024 MAXSIZE_MB,
  d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB,
  v.CREATION_TIME,
  t.BIGFILE,
  t.ENCRYPTED,
  t.COMPRESS_FOR,
  t.CONTENTS,
  d.STATUS
from cdb_data_files d
join v$datafile v 
  on d.file_id = v.file# 
 and d.con_id = v.con_id
join cdb_tablespaces t 
  on d.tablespace_name = t.tablespace_name 
 and d.con_id = t.con_id
where d.tablespace_name = upper(decode('&&tablespace_name', null, d.tablespace_name, '&&tablespace_name'))
order by d.CON_ID, d.TABLESPACE_NAME, d.FILE_NAME;



set line 999 pages 999
col FILE_NAME format a100
col tablespace_name format a15
col "MAXSIZE GB"  for 99999.99
Select con_id,tablespace_name, file_name, autoextensible, bytes/1024/1024/1024 "USEDSPACE GB", maxbytes/1024/1024/1024 "MAXSIZE GB" 
from cdb_data_files 
where 1=1
and tablespace_name = upper( decode('&&tablespace_name',null,tablespace_name,'&&tablespace_name'))
order by tablespace_name;