Use different views for viewing information about temp files and Datafiles with V$TEMPFILE and DBA_TEMP_FILES views are analogous to the V$DATAFILE and DBA_DATA_FILES views.
Note: Remember we can extend one Datafile up it Max limit around 32GB only, It depends on a block size of oracle.
Default Block Size: 8192(Byte) Means 8Kb, please run this query to cross verify:
SQL> Select (maxblocks*8192)/1024/1024/1024 Size_GB from dba_data_files where rownum=1;
# Tablespace Creation and Resizing Datafile
SQL> col TABLESPACE_NAME format a20;
SQL> col FILE_NAME format a50;
SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='USERS';
CREATE TABLESPACE tb_name DATAFILE '/mount1/directory1/directory1/my_data_01.dbf' SIZE 20G;
ALTER TABLESPACE tb_name ADD DATAFILE '/mount1/directory1/directory1/my_data_01.dbf' SIZE 10G;
ALTER DATABASE DATAFILE '/mount1/directory1/directory1/my_data_01.dbf' AUTOEXTEND ON;
ALTER DATABASE DATAFILE '/mount1/directory1/directory1/my_data_01.dbf' RESIZE 24G;
# Temp Tablespace Creation and Managing its Datafile
By default, a single temporary tablespace named TEMP is created for each new Oracle Database installation. You can create additional temporary tablespaces with the CREATE TABLESPACE statement.
SQL> col TABLESPACE_NAME format a20;
SQL> col FILE_NAME format a50;
SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_TEMP_FILES WHERE TABLESPACE_NAME='TEMP';
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE '/mount2/directory1/directory1/lmtemp01.dbf' SIZE 15G AUTOEXTEND ON;
ALTER TABLESPACE lmtemp ADD TEMPFILE '/mount2/directory1/directory1/lmtemp02.dbf' SIZE 10G REUSE;
ALTER DATABASE TEMPFILE '/mount2/directory1/directory1/lmtemp02.dbf' RESIZE 26G;
ALTER TABLESPACE TEMP ADD TEMPFILE '/mount2/directory1/directory1/lmtemp03.dbf' SIZE 512M AUTOEXTEND ON NEXT 250M MAXSIZE UNLIMITED;
# Drop Tablespace Datafile along with data.
DROP TABLESPACE users INCLUDING CONTENTS;
DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;
ALTER TABLESPACE lmtemp DROP TEMPFILE '/mount2/directory1/directory1/lmtemp02.dbf';
ALTER TABLESPACE tb_name DROP DATAFILE '/mount1/directory1/directory1/my_data_01.dbf';
Note: Remember we can extend one Datafile up it Max limit around 32GB only, It depends on a block size of oracle.
Default Block Size: 8192(Byte) Means 8Kb, please run this query to cross verify:
SQL> Select (maxblocks*8192)/1024/1024/1024 Size_GB from dba_data_files where rownum=1;
# Tablespace Creation and Resizing Datafile
SQL> col TABLESPACE_NAME format a20;
SQL> col FILE_NAME format a50;
SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='USERS';
CREATE TABLESPACE tb_name DATAFILE '/mount1/directory1/directory1/my_data_01.dbf' SIZE 20G;
ALTER TABLESPACE tb_name ADD DATAFILE '/mount1/directory1/directory1/my_data_01.dbf' SIZE 10G;
ALTER DATABASE DATAFILE '/mount1/directory1/directory1/my_data_01.dbf' AUTOEXTEND ON;
ALTER DATABASE DATAFILE '/mount1/directory1/directory1/my_data_01.dbf' RESIZE 24G;
# Temp Tablespace Creation and Managing its Datafile
By default, a single temporary tablespace named TEMP is created for each new Oracle Database installation. You can create additional temporary tablespaces with the CREATE TABLESPACE statement.
SQL> col TABLESPACE_NAME format a20;
SQL> col FILE_NAME format a50;
SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_TEMP_FILES WHERE TABLESPACE_NAME='TEMP';
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE '/mount2/directory1/directory1/lmtemp01.dbf' SIZE 15G AUTOEXTEND ON;
ALTER TABLESPACE lmtemp ADD TEMPFILE '/mount2/directory1/directory1/lmtemp02.dbf' SIZE 10G REUSE;
ALTER DATABASE TEMPFILE '/mount2/directory1/directory1/lmtemp02.dbf' RESIZE 26G;
ALTER TABLESPACE TEMP ADD TEMPFILE '/mount2/directory1/directory1/lmtemp03.dbf' SIZE 512M AUTOEXTEND ON NEXT 250M MAXSIZE UNLIMITED;
# Drop Tablespace Datafile along with data.
DROP TABLESPACE users INCLUDING CONTENTS;
DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;
ALTER TABLESPACE lmtemp DROP TEMPFILE '/mount2/directory1/directory1/lmtemp02.dbf';
ALTER TABLESPACE tb_name DROP DATAFILE '/mount1/directory1/directory1/my_data_01.dbf';
No comments:
Post a Comment