The user HR receives the following error while inserting data into the TTK table:
ERROR at line 1:
ORA-01653: unable to extend table HR.TTK by 128 in tablespace SMD Upon
investigation, you find that SMD is a small file tablespace.Which three action would allow
the user to insert data? (Choose three.)
A.
Add a data file to the SMD tablespace.
B.
Add a data file to the temporary tablespace associated with the user HR.
C.
Resize the data file associated with the SMD tablespace to make it larger.
D.
Alter the data file associated with the SMD tablespace to grow automatically.
E.
Change the segment space management for the SMD tablespace to automatic.
Increasing the Size of a Tablespace
You can increase the size of a tablespace by either increasing the size of a data file in the tablespace or adding one. See “Creating Data Files and Adding Data Files to a Tablespace” for more information.
Additionally, you can enable automatic file extension (AUTOEXTEND) to data files and bigfile tablespaces. See “Enabling and Disabling Automatic Extension for a Data File”.
Manually Resizing a Data File
You can manually increase or decrease the size of a data file using the ALTER DATABASE statement. Therefore, you can add more space to your database without adding more data files. This is beneficial if you are concerned about reaching the maximum number of data files allowed in your database.
For a bigfile tablespace you can use the ALTER TABLESPACE statement to resize a data file. You are not allowed to add a data file to a bigfile tablespace.
Manually reducing the sizes of data files enables you to reclaim unused space in the database. This is useful for correcting errors in estimates of space requirements.
In the next example, assume that the data file /u02/oracle/rbdb1/stuff01.dbf has extended up to 250M. However, because its tablespace now stores smaller objects, the data file can be reduced in size.
The following statement decreases the size of data file /u02/oracle/rbdb1/stuff01.dbf:
ALTER DATABASE DATAFILE ‘/u02/oracle/rbdb1/stuff01.dbf’
RESIZE 100M;
Note:
It is not always possible to decrease the size of a file to a specific value. It could be that the file contains data beyond the specified decreased size, in which case the database will return an error.
Enabling and Disabling Automatic Extension for a Data File
You can create data files or alter existing data files so that they automatically increase in size when more space is needed in the database. The file size increases in specified increments up to a specified maximum.
Setting your data files to extend automatically provides these advantages:
Reduces the need for immediate intervention when a tablespace runs out of space
Ensures applications will not halt or be suspended because of failures to allocate extents
To determine whether a data file is auto-extensible, query the DBA_DATA_FILES view and examine the AUTOEXTENSIBLE column.
You can specify automatic file extension by specifying an AUTOEXTEND ON clause when you create data files using the following SQL statements:
CREATE DATABASE
ALTER DATABASE
CREATE TABLESPACE
ALTER TABLESPACE
You can enable or disable automatic file extension for existing data files, or manually resize a data file, using the ALTER DATABASE statement. For a bigfile tablespace, you are able to perform these operations using the ALTER TABLESPACE statement.
Cause: Failed to allocate an extent of the required number of blocks for a table segment in the tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.
Oracle offer great resources on resolving ORA-01653, namely the nature of the problem as it relates to space availability:
Only with SMD