3 Replies Latest reply: May 21, 2013 1:15 PM by 864103 RSS

    drop temp TBS

    864103
      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
          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
            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
              thanks guys i solve it .