• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1258
  • Last Modified:

Global temporary table has a temp lob segment in the system tablespace

Hi experts.

I gave a user enough privilege to create the repository of Ibm Cognos Content.  I notice that there is a global Temporary table with its Lob in the system tablespace.
I wonder if its ok, to move that Lob to the index tablespace.
lobs-temp-in-System-tablespace.bmp
0
LindaC
Asked:
LindaC
  • 7
  • 6
1 Solution
 
slightwv (䄆 Netminder) Commented:
I don't see a problem changing the tablespace of a lob segment but with anything like this, it is always best to test it.

There is the SQL syntax here:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:378418239571

A better questrion to ask is if the user's default tablespace is SYSTEM.
0
 
LindaCAuthor Commented:
The crn11 user default tablespace is crn11_data. but it also has unlimited quota on the crn11_idx tablespace.

I don't have an idea how it is on the system tablespace..
When I see via Toad, choosing the alter table and clicking on the lob columns it shows that it is located on the temp tablespace.
Blob-on-temp.bmp
0
 
slightwv (䄆 Netminder) Commented:
There may be something different about lob segments on a Global Temporary table.

TEMP actually makes since since the data is never permanent.  Why it shows SYSTEM might be a 'bug' in the GUI.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LindaCAuthor Commented:
Thank you.  So indeed it seems like a bug.
So I will let the lob on the temp?
0
 
slightwv (䄆 Netminder) Commented:
>>So I will let the lob on the temp?

You might not be able to change it.  In my original post the 'Global Temporary' didn't fully sink in.
0
 
LindaCAuthor Commented:
Then I wll not try to move it.
0
 
LindaCAuthor Commented:
feedback?
0
 
slightwv (䄆 Netminder) Commented:
What are you looking for as feedback?

You last post seemed final to me:  Then I wll not try to move it.
0
 
LindaCAuthor Commented:
I was looking to "I agree Linda, good luck" or something similar.

Good grief!!!
What an actitude!!!!
0
 
LindaCAuthor Commented:
Thanks.
0
 
slightwv (䄆 Netminder) Commented:
>>What an actitude!!!!

I apologize if you took it that way.  It was not intended.
0
 
slightwv (䄆 Netminder) Commented:
This sparked my interest and I had a little time this evening.

Below is a test case that shows the possibilities that I could come up with.

All tested using sqlplus.

-- creates lob segment in USERS tablespace
drop table tab1 purge;
create table tab1( col1 char(1), col2 clob)
lob (col2) store as myclob (TABLESPACE USERS)
/

select tablespace_name from user_lobs where segment_name='MYCLOB';

-- creates lob segment in SYSTEM tablespace
drop table tab1 purge;
create table tab1( col1 char(1), col2 clob)
lob (col2) store as myclob
/

select tablespace_name from user_lobs where segment_name='MYCLOB';

-- using global temp, not allowed to specify tablespace.  It generates an error.
-- tablespace is TEMP
drop table tab1 purge;
create global temporary table tab1( col1 char(1), col2 clob)
lob (col2) store as myclob
/

select tablespace_name from user_lobs where segment_name='MYCLOB';

Open in new window

0
 
LindaCAuthor Commented:
Thank you!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now