Solved

How to clear Temp Tablespace in oracle 9i

Posted on 2006-11-24
6
3,023 Views
Last Modified: 2008-01-09
Hi there,

I am writing a very large materialised view to be stored in a custom tablespace.
The temp tablespace is on a RAM drive, and is fixed at 1.5Gb. This cannot be extended any more (on the RAM drive).

My problem is that creating my view in the custom tablespace fllls up the temp tablespace, and causes errors when it cannot be extended.

My questions are as follows:

1) If I extend my temp tablespace via a hard drive datafile, is there any way to specify that I wish for my RAM drive to be used primarily?

2) Even when my mat view is created successfully in my custom tablespace, the temp tablespace is not cleared after. Is there any way to clear this without having to shutdown and restart?

3) Is there anything fundamentally worng with having a temp tablespace of only 1.5Gb in the first place?

Thank you in advance for your efforts.

Regards
Chris
0
Comment
Question by:c_law
  • 3
  • 2
6 Comments
 
LVL 11

Expert Comment

by:mohammadzahid
Comment Utility
You can create a new temporary tablespace and make that a default temp tablespace for the user creating a materialized view.

For example:

If user1 is the owner of the materialized view.

sql> Create tablespace temp_user1
       CREATE TEMPORARY TABLESPACE temp02
       TEMPFILE 'C:\ORACLE\ORADATA\HCMC\ts02.tmp' SIZE xxxxxxM;  (say 2GB)

sql> alter user user1 temporary tablespace temp_user1;

sql> Create view.

Creating process will now use new temp tablespace (user1_temp) for sorting.

Here is the url that can help you in setting up correct sort_area_size

http://www.ixora.com.au/q+a/0011/08114657.htm

Please examine the view sql statements and make sure it is optimized correctly. 1.5GB space for sorting sounds little too much for 1 view.

Regards.







0
 
LVL 10

Accepted Solution

by:
ravindran_eee earned 125 total points
Comment Utility
1) If I extend my temp tablespace via a hard drive datafile, is there any way to specify that I wish for my RAM drive to be used primarily?
Oracle does that automatically. It will go for the second data file only when it needs. So what u need to do is to add another datafile to the temp tablespace. But I dont know how this will help you. Once u create a datafile, the space is going to be used. Its not like normal files. That is size increases as data increases.

2) Even when my mat view is created successfully in my custom tablespace, the temp tablespace is not cleared after. Is there any way to clear this without having to shutdown and restart?
Temp tablespaces cannot be cleared manually. Oracle gets back the space when u perform another operation. In that case it clears up the tablespace and uses it again. So for us it might appear to be full always. Only restarting the DB will clear it completely.

3) Is there anything fundamentally worng with having a temp tablespace of only 1.5Gb in the first place?
As suggested already please optimize the query used for creating the materialized view
0
 
LVL 11

Expert Comment

by:mohammadzahid
Comment Utility
You can create a temp tablespace manually using my script and use that manually created tablespace for a user as default for sorting purpose.

Here is a script:

sql> create temporary tablespace temp3
tempfile 'D:\ORACLE\ORADATA\ORACLE9I\TEMP3.dbf' size 50m;

sql> Alter user user1 temporary tablespace temp;

sql> create table t1 as select * from all_objects;

sql> SELECT DISTINCT OWNER, OBJECT_NAME, SUBOBJECT_NAME, object_id, data_object_id, object_Type
FROM T2;

sql> SELECT * FROM V$sort_segment WHERE tablespace_name = 'TEMP2'

You will notice that there are some extents allocated on TEMP2 temporary tablespace. You can run this query to find out exact space allocated

SELECT (total_blocks * 8192)/1024/1024 FROM V$sort_segment WHERE tablespace_name = 'TEMP2'
8192 is a block size of your database.

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 18

Expert Comment

by:rbrooker
Comment Utility
Hi,

all i have done to clear temp space is to write a script that takes it off line and then puts it back online again.  of course, this cannot be run during the day, nor on a 24x7 database, but i have the luxury of not working on databases of either type....

:)
0
 
LVL 10

Expert Comment

by:ravindran_eee
Comment Utility
Just to tweek rbrooker's suggestion a bit..

Create another temporary tablespace.. Probably of smaller size.. Then for all users assign this as the temp tablespace.. SQL scripts could be generated dynamically by querying the DBA views for this purpose..
Then either take offline and bring back online the older temp tablespace.. Also it could be dropped and re-created.. Then make this the temp tablespace for the users.. In this way the user is not going to see anything happening..
0
 
LVL 11

Expert Comment

by:mohammadzahid
Comment Utility
ravindran suggestion is good but for a database with fewer transactions with minimum sorting. If you create a temporary tablespace with small size and you have high transactions in database require sorting then you might run into problem(s).

What is the current allocation on your TEMP tablespace? And size?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

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…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

762 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

7 Experts available now in Live!

Get 1:1 Help Now