- Login database
oracle@suse1:/u01/app/oracle/product/12.1.0/dbhome_1/dbs> sqlplus sys/xxxx123@plm as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun May 24 11:47:04 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
PLM READ WRITE PRIMARY
- Check Tempfile location
SQL> SELECT NAME FROM V$TEMPFILE;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/plm/temp01.dbf
SQL>
- Remove tempfile at OS level
host rm -f /u01/app/oracle/oradata/plm/temp01.dbf
- Create global temporary table it will through error
SQL> CREATE GLOBAL TEMPORARY TABLE test ON COMMIT PRESERVE ROWS AS SELECT * FROM DBA_TABLES ;
CREATE GLOBAL TEMPORARY TABLE test ON COMMIT PRESERVE ROWS AS SELECT * FROM DBA_TABLES
*
ERROR at line 1:
ORA-01116: error in opening database file 201
ORA-01110: data file 201: '/u01/app/oracle/oradata/plm/temp01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> select * from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- -------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
/u01/app/oracle/oradata/plm/temp01.dbf
1 TEMP 206569472 25216 ONLINE
1 YES 3.4360E+10 4194302 80 205520896 25088
- Add new datafile on TEMP tablespace
SQL> ALTER TABLESPACE temp ADD TEMPFILE '/u01/app/oracle/oradata/plm/temp02.dbf' size 1g;
Tablespace altered.
SQL>
- Still it will through same error
SQL> CREATE GLOBAL TEMPORARY TABLE test ON COMMIT PRESERVE ROWS AS SELECT * FROM DBA_TABLES;
CREATE GLOBAL TEMPORARY TABLE test ON COMMIT PRESERVE ROWS AS SELECT * FROM DBA_TABLES
*
ERROR at line 1:
ORA-01116: error in opening database file 201
ORA-01110: data file 201: '/u01/app/oracle/oradata/plm/temp01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
- OFFLINE tempfile datafile which thow an error.
SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/plm/temp01.dbf' OFFLINE;
Database altered.
- Check the datafile on temp tablespace.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/plm/temp01.dbf
/u01/app/oracle/oradata/plm/temp02.dbf
- Create Global temporary table again this time it will create successfully
SQL> CREATE GLOBAL TEMPORARY TABLE test ON COMMIT PRESERVE ROWS AS SELECT * FROM DBA_TABLES;
Table created.
SQL>
No comments:
Post a Comment