Data File

How Big a Data File Can Be

Posted on
DB Blocks Auto Extend in a Data File
Maximum Size Limit of a Data File According to Oracle Database Physical Database Limits, we can have approximate 222 database blocks for a data file in a smallfile tablespace (default) and 232 database blocks for a data file in a bigfile tablespace. In fact, the exact allowable database blocks in a data file should be […]
Data File

How to Move Data Files to Another Place

Posted on
Moving data files from a nearly full mount point to other mount points which could have more space to accommodate growing data files seems a common job for DBA. Here I simplify the scenario to a basic procedure that can be easy to follow. Some data files will be moved to the new location. Their […]
Data File

How to Downsize a Datafile

Posted on
A normal and reasonable scenario to resize a datafile is increasing, for example, to increase a file from 6GB to 8GB: SQL> ALTER DATABASE DATAFILE ‘/oracle/oradata/db1/datafile1.dbf’ RESIZE 8G; In a very rare cases, you might want to downsize a datafile when the space is a precious resource in your server. The syntax is the same […]
Data File

When Several Datafiles Are Missing – Restore From Local Backups

Posted on
When several datafiles are accidentally deleted, we can use RMAN to restore and recover the datafiles individually.List the backup we have now.[oracle@primary01 ~]$ rman target /…RMAN> list backup;List of Backup Sets===================…BS Key  Type LV Size       Device Type Elapsed Time Completion Time——- —- — ———- ———– ———— —————5       Full   […]
Data File

How to Resolve ORA-01653 Unable to Extend Table in Tablespace

Posted on
ORA-01653 The following two error messages are very similar. As a DBA, you’ll probably meet them someday in the future. ORA-01653 is for table, ORA-01654 is for index. Essentially, they are the same thing: Error:  ORA-01653 Text:   unable to extend table %s.%s by %s in tablespace %s ——————————————————————————- Cause:  Failed to allocate an extent […]