Resize Tablespace Hangs

Posted on 2012-09-19
Last Modified: 2012-09-29
About five hours ago i went through OEM to resize a datafile in the TEMP tablespace which had zero data.  TEMP is not the default temporary tablespace.  Looking at OEM all i see is that pink 'other'  blocked sessions 1 root blockers 1.  What needs to be done to have Oracle actually complete the resize this century?
Question by:xoxomos
    LVL 16

    Assisted Solution

    Have you checked the alert log?

    What version Oracle?
    What O/S
    Is the database actively used?
    Are you in archivelog mode? Is there room there?
    What is the size of the datafile? Is there available space on the target drive/partition?


    Author Comment

    Yes, the alert log just shows the  ALTER statement.
    At this time not highly active, but yes it's active.
    Yes it's archivelog mode.   Plenty room in archive_dest_nn.
    Datafile currently 20G.  I am trying to resize DOWN to 2G.

    Accepted Solution

    This blog looks like what may be happening

    I issued a drop tablespace temp and it hang. If you are in the same situation check the above:

    select username,session_num,session_addr from v$sort_usage
    where tablespace='[Name of the temp tablespace you want to drop]';

    if this query return rows use the session_num to find the sid from the v$session.

    select sid,serial#,status from v$session where serial#=[session_num];

    Alternatively use the session_addr.

    select sid,serial#,status from v$session where saddr='[session_addr]';

    and then kill the session at will.

    alter system kill session '[sid],[serial#]' immediate;

    Author Closing Comment

    Found 43 inactive sessions still hanging on to TEMP.  Policy dictated I could not kill them as recommended in the blog so I just waited for the application to be shut down for weekly cold backup.  As soon as application shut down, tablespace resize completed.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now