Adding a Datafile/Tempfile to Temp Tablespace

Connect with oracle database and check tempfile location & information :

[oracle@localhost ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 5 14:58:33 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> set pages 999
set lines 400
col FILE_NAME format a50
col TABLESPACE_NAME format a10
select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
from dba_temp_files d,
 v$tempfile v
where d.FILE_ID = v.FILE#
order by d.TABLESPACE_NAME, d.FILE_NAME;SQL> SQL> SQL> SQL>   2    3    4    5

TABLESPACE FILE_NAME                                             SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB
---------- -------------------------------------------------- ---------- --- ---------- ---------------
TEMP       /u01/app/oracle/oradata/risdev/temp01.dbf                  32 YES 32767.9844            .625

SQL>

Add tempfile to temp tablepsace : 

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/risdev/temp02.dbf' SIZE 5G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;

Tablespace altered.

SQL>



Verify added tempfile : 



SQL> set pages 999
set lines 400
col FILE_NAME format a50
col TABLESPACE_NAME format a10
select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
from dba_temp_files d,
 v$tempfile v
where d.FILE_ID = v.FILE#
order by d.TABLESPACE_NAME, d.FILE_NAME;SQL> SQL> SQL> SQL>   2    3    4    5

TABLESPACE FILE_NAME                                             SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB
---------- -------------------------------------------------- ---------- --- ---------- ---------------
TEMP       /u01/app/oracle/oradata/risdev/temp01.dbf                  32 YES 32767.9844            .625
TEMP       /u01/app/oracle/oradata/risdev/temp02.dbf                5120 YES      32767            1024

SQL>

* Make sure enough space available at mount point .

Query:

set pages 999
set lines 400
col FILE_NAME format a50
col TABLESPACE_NAME format a10
select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
from dba_temp_files d,
 v$tempfile v
where d.FILE_ID = v.FILE#

order by d.TABLESPACE_NAME, d.FILE_NAME;

Find Out Virtual Memory PAGESIZE on Linux

[root@localhost ~]# getconf PAGESIZE
4096
[root@localhost ~]# getconf PAGE_SIZE
4096
[root@localhost ~]#

[root@localhost ~]# getconf -a | grep -i pagesize
PAGESIZE                           4096
[root@localhost ~]#

Flush Bad SQL Plan from Shared Pool

SQL>
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='c99yw1xkb4f1u';

ADDRESS          HASH_VALUE
---------------- ----------
0000000064E29800 1689401402

SQL> exec DBMS_SHARED_POOL.PURGE ('0000000064E29800,1689401402','C');

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='c99yw1xkb4f1u';

no rows selected

SQL>

PLS-00201: identifier 'DBMS_SHARED_POOL.PURGE' must be declared

To declare the DBMS_SHARED_POOL.PURGE you have to run dbmspool.sql under ORACLE_HOME/rdbms/admin/  directory

SQL> exec DBMS_SHARED_POOL.PURGE ('0000000064E29800,1689401402','C');
BEGIN DBMS_SHARED_POOL.PURGE ('0000000064E29800,1689401402','C'); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.PURGE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

[oracle@localhost admin]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 30 16:55:58 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL>

SQL> @?/rdbms/admin/dbmspool.sql

Session altered.

Package created.

Grant succeeded.

Session altered.

SQL>

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='c99yw1xkb4f1u';

ADDRESS          HASH_VALUE

---------------- ----------

0000000064E29800 1689401402

SQL> exec DBMS_SHARED_POOL.PURGE ('0000000064E29800,1689401402','C');

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='c99yw1xkb4f1u';

no rows selected

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL>