Oracle: Create a temporary TEMP tablespace

CREATE TEMPORARY TABLESPACE TEMP_NEW TEMPFILE '/usr/opt/app/oracle/admin/gentest3/temp01/temp_01.dbf' SIZE 10m; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW; Retrieve ‘SID_NUMBER’ & ‘SERIAL#NUMBER’ of existing live session’s who are using old temporary tablespace ( i.e. TEMP ) and kill them. set linesize 200 col STATUS format a10 col username format a15 col osuser format a10 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; Provide above inputs to following query, and kill session’s. alter system kill session 'SID_NUMBER, SERIAL#NUMBER'; For example: alter system kill session '59,57391'; DROP TABLESPACE TEMP including contents and datafiles; CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/usr/opt/app/oracle/admin/gentest3/temp01/temp01.dbf' SIZE 10m autoextend on next 10m maxsize 32024M; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP; DROP TABLESPACE TEMP_NEW including contents and datafiles;

Comments

Post a Comment

Popular posts from this blog

Oracle: To clean up WRI$_ADV_OBJECTS

 set linesize 200; select segment_name, segment_type, bytes from dba_segments where tablespace_name='SYSAUX' order by bytes asc; COL OCCUPANT_NAME FORMAT A30 SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES FROM V$SYSAUX_OCCUPANTS ORDER BY SPACE_USAGE_KBYTES DESC; COL SEGMENT_NAME FORMAT A30 COL OWNER FORMAT A10 COL TABLESPACE_NAME FORMAT A10 COL SEGMENT_TYPE FORMAT A15 SELECT * FROM (SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,BYTES/1024/1024 "SIZE(MB)",SEGMENT_TYPE FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='SYSAUX' ORDER BY BYTES DESC) WHERE ROWNUM<=10; 1.  automatic clean-up default 31 days           SELECT dbms_stats.get_stats_history_availability from Dual; 2. modify retention policy        exec  dbms_stats.alter_stats_history_retention (14); 3. a manual cleaning  exec dbms_stats.purge_stats (sysdate-14);   ALTER TABLE WRI$_ADV_OBJECTS MOVE; ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD; ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 R...
Read more