This discussion is archived
3 Replies Latest reply: May 21, 2013 11:15 AM by 864103 RSS

drop temp TBS

864103 Newbie
Currently Being Moderated
HI ,

oracle 11g ASM RAC under OEL 5.6 ,
i have temporary tablespace with size more than 100 GB so i create new one and make the new one as a default
and then i drop the first one but it take more time and i change the default for temporary since 2 days ago
so there is no data in old temp but when i execute the drop command it take more time ?
SQL> select temporary_tablespace from dba_users;

TEMPORARY_TABLESPACE
------------------------------
TMP
TMP
TMP
TMP
TMP
TMP
TMP
TMP
TMP
TMP
TMP

TEMPORARY_TABLESPACE
------------------------------
TMP
TMP
TMP
TMP
TMP
TMP
TMP
TMP
TMP
TMP

21 rows selected.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TMP
UNDOTBS2
USERS
ABS_MID
ABS_MID_IND
TEMP



9 rows selected.

SQL> set time on
16:44:18 SQL> drop tablespace TEMP including contents and datafiles;
^Cdrop tablespace TEMP including contents and datafiles
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation



16:53:00 SQL>
16:53:01 SQL>
please advise why it take more time ?
  • 1. Re: drop temp TBS
    sb92075 Guru
    Currently Being Moderated
    861100 wrote:
    HI ,

    oracle 11g ASM RAC under OEL 5.6 ,
    i have temporary tablespace with size more than 100 GB so i create new one and make the new one as a default
    and then i drop the first one but it take more time and i change the default for temporary since 2 days ago
    so there is no data in old temp but when i execute the drop command it take more time ?
    SQL> select temporary_tablespace from dba_users;
    
    TEMPORARY_TABLESPACE
    ------------------------------
    TMP
    TMP
    TMP
    TMP
    TMP
    TMP
    TMP
    TMP
    TMP
    TMP
    TMP
    
    TEMPORARY_TABLESPACE
    ------------------------------
    TMP
    TMP
    TMP
    TMP
    TMP
    TMP
    TMP
    TMP
    TMP
    TMP
    
    21 rows selected.
    
    SQL> select name from v$tablespace;
    
    NAME
    ------------------------------
    SYSTEM
    SYSAUX
    UNDOTBS1
    TMP
    UNDOTBS2
    USERS
    ABS_MID
    ABS_MID_IND
    TEMP
    
    
    
    9 rows selected.
    
    SQL> set time on
    16:44:18 SQL> drop tablespace TEMP including contents and datafiles;
    ^Cdrop tablespace TEMP including contents and datafiles
    *
    ERROR at line 1:
    ORA-01013: user requested cancel of current operation
    
    
    
    16:53:00 SQL>
    16:53:01 SQL>
    please advise why it take more time ?
    you could do as below prior to doing DROP TABLESPACE

    ALTER SESSION SET SQL_TRACE=TRUE;

    content of trace file will show what occurs during the DROP command
  • 2. Re: drop temp TBS
    1009889 Newbie
    Currently Being Moderated
    HI,

    I think you should wait for the Tablespace to be released and let the statement running.

    You may check from time to time the Alert.log and also check for the v$session_wait view:

    http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_3023.htm

    Regards.
  • 3. Re: drop temp TBS
    864103 Newbie
    Currently Being Moderated
    thanks guys i solve it .

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points