Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3100
  • Last Modified:

How to clear Temp Tablespace in oracle 9i

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
c_law
Asked:
c_law
  • 3
  • 2
1 Solution
 
mohammadzahidCommented:
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
 
ravindran_eeeCommented:
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
 
mohammadzahidCommented:
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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
rbrookerCommented:
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
 
ravindran_eeeCommented:
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
 
mohammadzahidCommented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now