Tablespaces and datafile operations

To autoextend off for a tablespace datafile. For small file tablespaces you cannot directly autoextend off the tablespace. 

You will have to get the corresponding data file for that tablespace from dba_data_files and autoextend off the datafile as below.

SELECT * FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'HR';
ALTER DATABASE DATAFILE 'C:\ORACLE\21CXE\ORADATA\XE\XEPDB1\HR.DBF' AUTOEXTEND OFF;

Create tablespace command

CREATE TABLESPACE HR_ARCHIVE 
DATAFILE 'E:\install\21cXE\oradata\XE\XEPDB1\HR_ARCHIVE\hr_archive.dbf' 
SIZE 50485760 AUTOEXTEND ON 
NEXT 50485760 MAXSIZE 309715200 
LOGGING ONLINE PERMANENT BLOCKSIZE 8192 
EXTENT MANAGEMENT LOCAL 
UNIFORM SIZE 524288 
SEGMENT SPACE MANAGEMENT AUTO;

To add a datafile to a tablespace

ALTER TABLESPACE CUST
ADD DATAFILE 'E:\install\21cXE\oradata\XE\XEPDB1\CUST\CUST_01.dat'
SIZE 10M;

To resize a datafile

ALTER DATABASE DATAFILE 'E:\install\21cXE\oradata\XE\XEPDB1\CUST\CUST_01.dat' RESIZE 5M;  

To reclaim space we can reduce the size of the tablespace. But if data is already present in the tablespace or if a large delete operation has happened and the High water mark is not been reset we will get ORA-03297: error as below.

ALTER DATABASE DATAFILE 'E:\install\21cXE\oradata\XE\XEPDB1\HR_ARCHIVE\HR_ARCHIVE.DBF' RESIZE 50M;  

ALTER DATABASE DATAFILE 'E:\install\21cXE\oradata\XE\XEPDB1\HR_ARCHIVE\HR_ARCHIVE.DBF' RESIZE 50M
Error report -
SQL Error: ORA-03297: file contains used data beyond requested RESIZE value
03297. 00000 -  "file contains used data beyond requested RESIZE value"
*Cause:    Some portion of the file in the region to be trimmed is
           currently in use by a database object
*Action:   Drop or move segments containing extents in this region prior to
           resizing the file, or choose a resize value such that only free
           space is in the trimmed.

To find the max size you can reset the datafile to without getting the above error, you can use this excellent script by Frank Pachot link for which is below.

Resize your Oracle datafiles down to the minimum without ORA-03297

You will need to run the script as SYS user

set linesize 1000 pagesize 0 feedback off trimspool on
with
 hwm as (
  -- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
  select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
  from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
 ),
 hwmts as (
  -- join ts# with tablespace_name
  select name tablespace_name,relative_fno,hwm_blocks
  from hwm join v$tablespace using(ts#)
 ),
 hwmdf as (
  -- join with datafiles, put 5M minimum for datafiles with no extents
  select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes
  from hwmts right join dba_data_files using(tablespace_name,relative_fno)
 )
select
 case when autoextensible='YES' and maxbytes>=bytes
 then -- we generate resize statements only if autoextensible can grow back to current size
  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
   ||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '
   ||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'
 else -- generate only a comment when autoextensible is off
  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
   ||'M from '||to_char(ceil(bytes/1024/1024),999999)
   ||'M after setting autoextensible maxsize higher than current size for file '
   || file_name||' */'
 end SQL
from hwmdf
where
 bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed
order by bytes-hwm_bytes desc
/

  
/* reclaim     376M from     405M */ alter database datafile 'E:\INSTALL\21CXE\ORADATA\XE\XEPDB1\UNDOTBS01.DBF' resize 30M;
/* reclaim      60M from      97M after setting autoextensible maxsize higher than current size for file C:\ORACLE\21CXE\ORADATA\XE\XEPDB1\HR.DBF */
/* reclaim      54M from     146M */ alter database datafile 'E:\INSTALL\21CXE\ORADATA\XE\XEPDB1\HR_ARCHIVE\HR_ARCHIVE.DBF' resize 92M;
/* reclaim      43M from      49M */ alter database datafile 'E:\INSTALL\21CXE\ORADATA\XE\XEPDB1\HR_ARCHIVE\INDX.DBF' resize 6M;
/* reclaim      34M from     530M */ alter database datafile 'E:\INSTALL\21CXE\ORADATA\XE\XEPDB1\SYSAUX01.DBF' resize 497M;
/* reclaim       4M from     290M */ alter database datafile 'E:\INSTALL\21CXE\ORADATA\XE\XEPDB1\SYSTEM01.DBF' resize 287M;  

The output above shows the minimum size I can reduce HR_ARCHIVE to is 92M with getting ORA-03297. Now if I resize the datafile, the size will reduce without any error.

ALTER DATABASE DATAFILE 'E:\INSTALL\21CXE\ORADATA\XE\XEPDB1\HR_ARCHIVE\HR_ARCHIVE.DBF' resize 92M;  

database datafile 'E:\INSTALL\21CXE\ORADATA\XE\XEPDB1\HR_ARCHIVE\HR_ARCHIVE.DBF' altered. 

To further reduce or resize the tablespace you can run the shrink space cascade command

ALTER TABLE {table_name} SHRINK SPACE CASCADE;

To create temporary tablespace

CREATE TEMPORARY TABLESPACE TEMP01
   TEMPFILE 'temp01.dbf' SIZE 5M AUTOEXTEND ON;

To find out the location in which this file is created you can query DBA_TEMP_FILES data dictionary table.

SELECT * FROM DBA_TEMP_FILES;

FILE_NAME	FILE_ID	TABLESPACE_NAME	BYTES	BLOCKS	STATUS	RELATIVE_FNO	AUTOEXTENSIBLE	MAXBYTES	MAXBLOCKS	INCREMENT_BY	USER_BYTES	USER_BLOCKS	SHARED	INST_ID
E:\INSTALL\21CXE\DBHOMEXE\DATABASE\TEMP01.DBF	4	TEMP01	5242880	640	ONLINE	1	YES	34359721984	4194302	1	4194304	512	SHARED

You can create the temporary tablespace in a path as per the datafile structure for the database.

CREATE TEMPORARY TABLESPACE TEMP_TBSP_01
   TEMPFILE 'E:\install\21cXE\oradata\XE\XEPDB1\temp_tbsp_01.dbf' SIZE 5M AUTOEXTEND ON;

The following example sets the default location for datafile creation and then creates a tablespace with an Oracle-managed tempfile in the default location. The tempfile is 100 M and is autoextensible with unlimited maximum size (the default values for Oracle-managed files): 

ALTER SYSTEM SET DB_CREATE_FILE_DEST = 'E:\install\21cXE\oradata\XE\XEPDB1\temp';

SHOW PARAMETER DB_CREATE_FILE_DEST
NAME                                               TYPE        VALUE                                                                                                
-------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------- 
db_create_file_dest                                string      E:\install\21cXE\oradata\XE\XEPDB1\temp

CREATE TEMPORARY TABLESPACE TEMP_TBSP_01; 

The concept of temporary tablespace was introduced in Oracle 9i. Prior to this users were assigned SYSTEM as the default temporary tablespace if the DBA was not careful in assigning the correct temporary tablespace to the user when creating the account. From Oracle 9i onwards, with the introduction of the default temporary tablespace, Oracle assigns that tablespace to each new user account as it is created. If you define a temporary tablespace for a user account, that overrides the default setting.

You define or change a default temporary tablespace using the alter database default temporary tablespace command as below

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_TBSP_01; 

Or it can also be defined at the time of creating the database. All users assigned to the old default temporary tablespace will now be assigned to the newly defined default temporary tablespace.  Users assigned to other temporary tablespaces will remain unchanged. 

Oracle Managed Files

This feature allows the oracle database to manage just about all facets of Oracle database file administration. With OMF configured properly you dont need to define the names and locations of Oracle files or the size of the file. You dont need to worry about removing the file from the OS after deleting the datafile or dropping the tablespace holding that datafile. OMF manages REDO and control file creation.  

Following parameters need to be set to configure OMF

db_create_file_dest    Defines the location that OMF uses to create all database files.
db_create_online_log_dest_n    Allows you to define upto 5 different locations for multiplexed online REDO logs and control file.

Below is a sample dataset output from DBA_DATA_FILES and DBA_TABLESPACES. 

From dba_data_files you can get information like the datafile location on the OS, file size, datafile is online, offline etc. 

From dba_tablespaces you can get the characteristics of the tablespace like type of tablespace (permanent, temporary, UNDO), extent management, logging, segment space managment (manual, auto).

SELECT * FROM DBA_DATA_FILES;

SELECT * FROM DBA_TABLESPACES;

To be continued ...

References  

Oracle / PLSQL: Set Default Tablespaces (both Permanent and Temp)

CREATE TEMPORARY TABLESPACE

To create a foreign key reference on table belonging to another schema 

To create a foreign key reference on table belonging to another schema, you will need to give the references privelage on that table to the referencing schema.

Example

Customer table in CUST schema has a column REGION_ID.

The REGION_ID column references table REGIONS IN HR schema.

If I try to create the foreing key directly, I will get error

SQL Error: ORA-00942: table or view does not exist.

Even if SELECT privelages are there on that table, you will get below error

SQL Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

To resolve this error in the HR schema give the following grant

GRANT REFERENCES ON REGIONS TO CUST;

Now you can create the foreign key constraint as below.

ALTER TABLE CUSTOMER ADD CONSTRAINT FK_CUST_REGN FOREIGN KEY (REGION_ID) REFERENCES HR.REGIONS;
table CUSTOMER altered.

Automatic Segment Space Management

In versions prior to Oracle 9i, Oracle tracked block availability with freelists that tracked all blocks that were available to write to. This method could cause contention and performance issues. Automatic Segment Space Management takes care of these problems by using a series of bitmap blocks (BMBs) which are stored in the segment being created. The BMBs are kept current by Oracle as data in the segment is being modified, and the BMBs are used to track data block space allocation.

ASSM can be enabled only with locally managed tablespaces, and all segments within a tablespace designated to use ASSM must use ASSM. To create a tablespace that uses ASSM, use the SEGMENT SPACE MANAGEMENT AUTO parameter of the CREATE TABLESPACE command.

ASSM eliminates the need for freelists and freelist groups commonly associated with RAC environments. ASSM leads to better performance in many cases, particularly if your segments contain rows that vary  in size. In an RAC environment, segments build using ASSM can perform much better than those using freelist space management.

An example of creating a tablespace with ASSM is below.

CREATE TABLESPACE USER_DATA
DATAFILE 'E:\install\21cXE\oradata\XE\USER_DATA.DBF'
SIZE 2048M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

To be continued ....

Thought for the day

A wise king scatters the wicked like wheat, then runs his threshing wheel over them. 

The Lord’s light penetrates the human spirit, exposing every hidden motive.

 Proverbs 20:26-27