Solved

How to clear Temp Tablespace in oracle 9i

Posted on 2006-11-24
6
3,051 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: 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!

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

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…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

729 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