How to create tablespace and resize datafile in oracle database

Опубликовано: 08 Октябрь 2024
на канале: DBTechNinja

189

How to create tablespace and resize datafile in oracle database

select name from v$tablespace;

--get tablespace name and datafile location with size
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 tablespace
create tablespace dbtechts datafile '/u02/oradata/dbtech/dbtechts.dbf' size 50m;

--add datafile to tablespace
alter tablespace dbtechts add datafile '/u02/oradata/dbtech/dbtechts1.dbf' size 10m;

--resize datafile
alter database datafile '/u02/oradata/dbtech/dbtechts1.dbf' resize 100m;

--offline tablespace
alter tablespace dbtechts offline;

--status of tablespace online or offline
select tablespace_name,status from dba_tablespaces;

--online tablespace
alter tablespace dbtechts online;

--drop tablespace
drop tablespace dbtechts including contents and datafiles;