By gayan 
It has been a while since Oracle introduced Bigfile tablespaces. Bigfile tablespaces carry attributes such as,
- Maintaining a single datafile/tempfile per tablespace, making it easier for administration.
- Physical size supported up to 128TB (based on the block size)
- With a single file (vs multiples of files) it helps to improve overall checkpoint time as the number of datafile headers to be updated is reduced.
- Ability to perform RMAN parallel operations on Bigfile datafiles using SECTION SIZE
It should be noted that the expected Bigfile tablespace are to be created as locally managed with Automatic Segment Space Management (ASSM) , except for system, undo and temporary tablespaces created with Manual Segment Space Management.
Default tablesaces presented in the root of CDB, will be created with Bigfile.
And will looks the same for PDBs
Also, the default tablespace is going to be Bigfile
Resize or shrink space?
As the tablespaces grow, there’s a likelihood of having fragmentation within them due to data deletions, table/schema drops, etc. Although there could be unused space, the database is unable to reuse / release them appropriately. Due to the nature of heap-organization, tablespaces may have challenges in releasing blocks underneath the last used block, hence fragmented. “Last used block” is the maximum to which the data file can be resized. Therefore, a table resize may not be very appropriate to release unused space within a fragmented tablespace.
On the other hand, Shrink space will first defragment the tablespace by removing unused blocks below the last used block. Shrink operation provides better space savings over resize, though it is considered slightly slow operation.
Shrink Bigfile tablespace 23ai
With Bigfiles growing bigger, DBA’s found it challenging to resize and reclaim the unused space from fragmented Bigfiles. This could be due to large DDLs such as drop tables, etc.
The USERS2 Bigfile tablespace we added earlier is currently occupying 73 GB of the total database capacity and the free space is less.
Lets first try to Resize
As You could see, we are unable to reduce the file less than 1GB
Lets explore on the Bigfile tablespace shrink now.
Lets first identify the potential returns from the bigfile tablespace shrink.
set serverout on
SQL> EXEC DBMS_SPACE.SHRINK_TABLESPACE(TS_NAME=>’USERS2′,SHRINK_MODE=>DBMS_SPACE.TS_SHRINK_MODE_ANALYZE)
Let’s provide a target value (in bytes) : 1GB
set serverout on
SQL> EXEC DBMS_SPACE.SHRINK_TABLESPACE(TS_NAME=>’USERS2′,TARGET_SIZE=> 1073741824)
As we see above, the file has now got reduced to 1GB.
Finally, let’s see what best the shrink tablespace do for us
set serverout on
SQL> EXEC DBMS_SPACE.SHRINK_TABLESPACE(TS_NAME=>’USERS2′);
When Resize wasn’t able to reduce the file size to 1GB, shrink tablespace has gained an impressive reduction on the Bigfile tablespace.
