Cambio tablespace TEMP per ridurre la dimensione

-- situazione attuale:

select tablespace_name,file_name,bytes,autoextensible from dba_temp_files

TABLESPACE_NAME FILE_NAME BYTES AUT

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

TEMP /u01/app/oracle/oradata/PICPC19/PRO19/temp01.dbf 34358689792 YES

TEMP /u01/app/oracle/oradata/PICPC19/PRO19/temp02.dbf 34358689792 YES

--controllo se la stanno usando:

SQL>

SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,

a.username,a.osuser, a.status

FROM v$session a,v$sort_usage b

WHERE a.saddr = b.session_addr;

no rows selected

-- ne creo un'altra

SQL> CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE '/u01/app/oracle/oradata/PICPC19/PRO19/temp1_01.dbf' SIZE 2G autoextend on;

-- la rendo di default

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;

Database altered.

--droppo la vecchia

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

--controllo tempfiles cancellato

SQL> !ls -latr /u01/app/oracle/oradata/PICPC19/PRO19/temp01.dbf

ls: cannot access /u01/app/oracle/oradata/PICPC19/PRO19/temp01.dbf: No such file or directory

--ricreo la vecchia

SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/oradata/PICPC19/PRO19/temp01.dbf' SIZE 2000M autoextend on;

Tablespace created.

--la rendo di default

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

Database altered.

--droppo la TEMP1

SQL> DROP TABLESPACE temp1 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

--controllo finale

SQL> select tablespace_name,file_name,bytes,autoextensible

from dba_temp_files ;

TABLESPACE_NAME FILE_NAME BYTES AUT

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

TEMP /u01/app/oracle/oradata/PICPC19/PRO19/temp01.dbf 2097152000 YES