ORA-01652: unable to extend temp segment by 128 in tablespace DATA
I was trying to create a table in my schema with the syntax,
sql>create table test_tab as select * from sys.dba_objects;
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace DATA
My tempfile is located at /oracle/product/dbn/temp01.dbf and its size was 30MB
Troubleshooting steps followed:
1.I have resized my tempfile to 1g and checked, but the error persists.
SQL> alter database tempfile '/oracle/product/dbn/temp01.dbf' resize 1g;
2.Then i created a temporary tablespace with 2G size and assigned it to my schema.
SQL> create temporary tablespace temper tempfile '/oracle/temper.dbf' size 2G;
Tablespace created.
SQL> alter user datas temporary tablespace temper;
User altered.
3. Then I logged into my schema and tried creating the table. but still the error persists.
SQL> create table test_tab as select * from dba_objects;
create table newww as select * from dba_objects
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace DATA
4. Then i add a datafile with size 500M to my tablespace and tried creating the table. This time the error disappeared and i was able to create the table.
SQL> alter tablespace data add datafile '/oracle/datass.dbf' size 500M;
Tablespace altered.
SQL> conn datas
Enter password:
Connected.
SQL> create table newer as select * from sys.dba_objects;
Table created.
-----------------------------------------
My question here is, Is this error (ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace DATA) relates with the size of temporary tablespace or normal tablespace??????
and second, why is it accessing temporary tablespace while creating a table, temporary tablespace is accessed only when we do sort operations, right?
3. I have dropped the temporary tablespace I have created, but the space in the disk has not been released.
sql> drop tablespace temper including contents and datafiles;
Table dropped.
the datafile has been deleted from the disk, but when I query
$df -h
the released space is not showing up...
Please help.