Solved

How to clear Temp Tablespace in oracle 9i

Posted on 2006-11-24
6
3,024 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
ID: 18009354
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
ID: 18010343
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
ID: 18010568
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
ID: 18011084
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
ID: 18011731
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
ID: 18012380
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql for Oracle views 8 49
System.InvalidCastException: Specified cast is not valid 10 68
Deny Oracle DBAs from Connecting  "/ as sysdba" 5 52
Clone Oracle 12c Database 5 43
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

867 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

20 Experts available now in Live!

Get 1:1 Help Now