Today we will cover everything about Temporary Tablespace, Sparse files, alter session set events=’60063′ . Everyone might know this – Temporary Tablespace are used for sorting operation in Oracle database. But Have you ever noticed ? Why creating a temporary tablespace with a size of 10gb is faster then creating a data tablespace with a size of 10gb ? What are the advantages and drawbacks of this feature called sparse files create for us, How to identify we have a sparse file on the system. In another way, how to understand if events=’60063′ was set on session level before creating a tablespace. Lets get started with basic commands.
If you are interested in reading – How to Add Datafile in Tablespace in Oracle 19c
Table of Contents
How to Create Temporary Tablespace
CREATE TEMPORARY TABLESPACE temp01 TEMPFILE '/u01/oracle/oradata/temp01.dbf' SIZE 5M AUTOEXTEND ON;
How to Add Tempfiles into Temporary tablespace ?
alter tablespace temp01 add tempfile '/u01/oracle/oradata/temp02.dbf' size 10g;
How to Drop a Tempfile from a Temporary Tablespace?
When you have multiple tempfiles in different mount point. Now the request is to move them into different mount point. Please make sure you need to keep at least one file into the tablespace. If you have only one tempfile in your temporary tablespace then you have to add new tempfile first then you can remove old tempfile from old mount point.
alter tablespace temp01 drop tempfile '/u02/oracle/oradata/temp02.dbf';
Why Temporary Tablespace creation is faster then Normal Tablespace ?
Oracle uses sparse files feature ( which is a Operating System feature) which means, it doesn’t allocate the space mentioned in the command.
When you create or resize temp files, they are not always guaranteed allocation of disk space for the file size specified. On file systems such as Linux and UNIX, temp files are created as sparse files. In this case, disk blocks are allocated not at file creation or resizing, but as the blocks are accessed for the first time.
docs.oracle.com
Whereas in normal tablespace, it will guaranteed allocation of disk space mentioned while tablespace creation.
Now you might be wondering, what are Sparse files ?
What is Sparse file
Sparse file is a type of computer file that attempts to use file system space more efficiently when blocks allocated to the file are mostly empty. This is achieved by writing brief information (metadata) representing the empty blocks to disk instead of the actual “empty” space which makes up the block, using less disk space (i.e. sparse files contain blocks of zeros whose existence is recorded, but have no space allocated on disk). The full block size is written to disk as the actual size only when the block contains “real” (non-empty) data.
support.oracle.com
Sparse files are commonly used for disk images, database snapshots, log files, etc.
support.oracle.com
Advantage of Sparse files
The advantage of sparse files is that storage is only allocated when actually needed: disk space is saved, and large files can be created even if there is insufficient free space on the file system.
support.oracle.com
Disadvantage of Having Sparse files in your datafile location
Sparse file feature enabled for your tempfile has only one drawback I can think of with respective of database administration, since allocation of space is not done during the creation of addition of tempfiles in temporary tablespace, will give you wrong result with df command.
Though you have created a temporary tablespace with 500GB tempfiles in a mount point which has 600GB free. But it will still show you as say 420GB free.
Thinking it has 420GB free, we started using it for datafiles addition. This caused a problem at my client side. And database was in hung state as mount point was 100% and there was no space left to expand temporary files. Later the database crashed for us.
Current log# 10 seq# 769104 mem# 2:
/redo11/AIX/oradata/log/AIX_redo10C.log
2022-08-03T13:02:18.402344+05:30
ARC1 (PID:31850788): Archived Log entry 1121760 added for T-1.S-769103
ID 0xce81689 LAD:1
2022-08-03T13:02:42.329926+05:30
KCF: read, write or open error, block=0xafe80 online=1
file=6 '/DATA2/AIX/oradata/data/TEMP_06.dbf'
error=27063 txt: 'IBM AIX RISC System/6000 Error: 28: No space
left on device
Additional information: 4294967295
Additional information: 131072'
2022-08-03T13:02:42.335475+05:30
Errors in file
/oradumps/AIX/diag/rdbms/AIX/AIX/trace/AIX_dbw0_23527780.trc:
Automatic Tempfile offline due to write error on
file 6: /DATA2/AIX/oradata/data/TEMP_06.dbf
2022-08-03T13:02:42.517501+05:30
Errors in file
/oradumps/AIX/diag/rdbms/AIX/AIX/trace/AIX_mz00_53150070.trc:
ORA-01110: data file 6:
'/DATA/AIX/oradata/data/AIX_USERS_DATA_02.dbf'
2022-08-03T13:03:00.346610+05:30
KCF: read, write or open error, block=0xb2800 online=1
file=6 '/DATA2/AIX/oradata/data/TEMP_06.dbf'
error=27063 txt: 'IBM AIX RISC System/6000 Error: 28: No space
left on device
Additional information: 4294967295
Additional information: 131072'
2022-08-03T13:03:00.350413+05:30
Errors in file
/oradumps/AIX/diag/rdbms/AIX/AIX/trace/AIX_dbw0_23527780.trc:
Automatic Tempfile offline due to write error on
file 6: /DATA2/AIX/oradata/data/TEMP_06.dbf
2022-08-03T13:03:06.343554+05:30
KCF: read, write or open error, block=0xb7b00 online=1
file=6 '/DATA2/AIX/oradata/data/TEMP_06.dbf'
error=27063 txt: 'IBM AIX RISC System/6000 Error: 28: No space
left on device
Additional information: 4294967295
Additional information: 131072'
2022-08-03T13:03:06.347213+05:30
Errors in file
/oradumps/AIX/diag/rdbms/AIX/AIX/trace/AIX_dbw0_23527780.trc:
Automatic Tempfile offline due to write error on
file 6: /DATA2/AIX/oradata/data/TEMP_06.dbf
2022-08-03T13:03:21.371507+05:30
KCF: read, write or open error, block=0xb7b10 online=1
file=6 '/DATA2/AIX/oradata/data/TEMP_06.dbf'
error=27063 txt: 'IBM AIX RISC System/6000 Error: 28: No space
left on device
Additional information: 4294967295
Additional information: 131072'
2022-08-03T13:03:21.376458+05:30
Errors in file
/oradumps/AIX/diag/rdbms/AIX/AIX/trace/AIX_dbw0_23527780.trc:
Automatic Tempfile offline due to write error on
file 6: /DATA2/AIX/oradata/data/TEMP_06.dbf
2022-08-03T13:03:24.389497+05:30
KCF: read, write or open error, block=0xb2810 online=1
file=6 '/DATA2/AIX/oradata/data/TEMP_06.dbf'
error=27063 txt: 'IBM AIX RISC System/6000 Error: 28: No space
left on device
Sparse files may become fragmented. The file system free space reports may be misleading and copying a sparse file with a program that does not explicitly support them may copy the entire, uncompressed size of the file, including the sparse, mostly zero sections which are not on disk — losing the benefits of the sparse property in the file.
support.oracle.com
How to force Oracle not to use Sparse file Feature while creating Tempfile
While creating a temporary files, you need to set a event on “session” level
alter session set events='60063';
This will disable Sparse file feature while creating the temporary tablespace.
How to Spot Sparse files or How do I as Oracle DBA come to know if Temporary files are created with event 60063 at session level ?
There is no few which can show it at database level, but yes since it’s a OS feature, we have observe it at OS level.
For Sun Solaris Oracle has provided a python script – I have tested it on python 3.7 version on sun solaris 5.11 11.4.43.113.3 sun4v sparc machine.
#!/bin/python
import os, sys
from stat import *
if len(sys.argv) != 2:
print ('Usage: ', sys.argv[0], 'dir')
sys.exit(1)
fileList = []
sum = 0
rootdir = sys.argv[1]
if not os.path.isdir(rootdir):
print (rootdir, 'is not a directory')
sys.exit(1)
for root, subDirs, files in os.walk(rootdir):
for dir in subDirs:
if os.path.ismount(dir):
# It's a mount point, skip it
subDirs.remove(dir)
for file in files:
f = os.path.join(root, file)
if os.path.islink(f):
# It's a link, skip it
break
mode = os.stat(f).st_mode
if not S_ISREG(mode):
# It is not a regular file, skip it
break
used = os.stat(f).st_blocks*512
real = os.stat(f).st_size
if (used < real):
print ('%s : used = %ld Bytes - real = %ld Bytes \n' % (f, used, real))
sum = sum + (real - used)
print ('\nTotal difference = %ld KBytes\n' % (sum / 1024))
Please check the script in action –
oracle $ ./sparse_files /u01/oradata/NICK/ /u01/oradata/NICK/temp01.dbf : used = 4202496 Bytes - real = 20979712 Bytes /u01/oradata/NICK/oracledbworld_test2.dbf : used =1048576 Bytes - real = 104865792 Bytes SQL> alter session set events='60063'; Session altered. SQL> alter tablespace TEST7 add tempfile '/u01/oradata/NICK/oracledbworld_test122.dbf' size 60m; Tablespace altered. SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.14.0.0.0 /ora12203 $ ./sparse_files /u01/oradata/NICK/ /u01/oradata/NICK/temp01.dbf : used = 4202496 Bytes - real = 20979712 Bytes /u01/oradata/NICK/oracledbworl_test2.dbf : used =1048576 Bytes - real = 104865792 Bytes
Similarly for other OS you can always use ls -ltrk and du -sk command to understand if it’s sparse file or not.
Points to Remember
- Resizing a existing tempfile with event set at session will not disable the feature. (Based on my testing on Sun Solaris system )
- If you add a tempfile without the event in a temporary tablespace which was created with events set at session level has no impact on the new tempfile which is added. So New tempfile will get created as sparse file. So always remember to set the event 60063 at session level before adding a tempfile also.
- Don’t set event 60063 at system level there are bugs which are reported for secure files. (ORA-600)
Thank you for reading! Happy learning!
Reference –
Sparse Files Observed Disk Usage Difference With “du” And “ls” Commands (Doc ID 2305761.1)
Sparse files and how to spot them (Doc ID 1006269.1)