Query temp segments blank
It appears one database I can not query the temp tablespace. It's local, unform extents, but nothing shows up in dba_segments or any where I have found thus far. Has this progressed to a trust me type of situation? Even TOAD gives me nothing but blanks on objects in TEMP yet on other databases will show me a temp segment. Database is: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production Platform is HP: HP-UX xx016xxx B.11.11 U 9000/800 86714596 unlimited-user license -- 13308 Thornridge Ct Midlothian, VA 23112 804-744-1545 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Query temp segments blank
Well, you could try... select view_name from all_views where view_name like '%TEMP%'; ..and see what you get. I have lots of views that give me information about the TEMP tablespace. YMMV, but it's a place to start. Cheers, Mike -Original Message- Sent: Wednesday, November 26, 2003 6:00 AM To: Multiple recipients of list ORACLE-L It appears one database I can not query the temp tablespace. It's local, unform extents, but nothing shows up in dba_segments or any where I have found thus far. Has this progressed to a trust me type of situation? Even TOAD gives me nothing but blanks on objects in TEMP yet on other databases will show me a temp segment. Database is: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production Platform is HP: HP-UX xx016xxx B.11.11 U 9000/800 86714596 unlimited-user license -- 13308 Thornridge Ct Midlothian, VA 23112 804-744-1545 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vergara, Michael (TEM) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Query temp segments blank
Below was the closest I got, but it still pretty much shows everything empty sort of. Guess it's okay, I'll monitor. This is a warehouse so it's hard to say. So close to the holidays, it's possible they aren't using a lot of temp. set linesize 162 set pagesize 30 column tablespace_name format a15 heading Tablespace|Name column file_id format 9 heading File|Id column bytes_used format 999,999,999,999,999 heading Bytes Used column blocks_used format 999,999,999,999,999 heading Bytes Used column bytes_free format 999,999,999,999,999 heading Bytes Free column blocks_free format 999,999,999,999,999 heading Blocks Free column relative_fno format 9,999 heading R File|No select * from sys.v_$temp_space_header order by tablespace_name, file_id; column extent_size format 999,999,999,999,999 heading Extent Size column total_extents format 999,999,999,999,999 heading Tot Extents column used_extents format 999,999,999,999,999 heading Used Extents column free_extents format 999,999,999,999,999 heading Free Extents select tablespace_name, extent_size, total_extents, used_extents, free_extents from v$sort_segment; -- 13308 Thornridge Ct Midlothian, VA 23112 804-744-1545 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL and TEMP segments
select distinct b.sid, b.serial#, substr(b.username, 1, 8) USERNAME, b.process, substr(c.segment_name, 1, 8) SEGMENT_NAME, bytes BYTES, substr(b.osuser, 1, 7) OS_USER, substr(b.machine, 1, 6) M_NAME, substr(b.program, 1, 12) PROGRAM from v$access a, v$session b, dba_segments c where c.owner = a.owner and a.sid = b.sid and b.status = 'ACTIVE' and c.segment_type = 'TEMPORARY'; HTH Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -Original Message- Sent: 14 February 2003 11:49 To: Multiple recipients of list ORACLE-L Quick question, I want to find out what sql is currently using my temporary segments. Is their a simple solution. Am getting the ORA-1652 for my locally managed temporary tablespace and I would like to find out what sql is causing this. My temp tablespace is currently 1.2GB. Oracle 8.1.7.3 on HPUX 11. TIA With Yahoo! Mail you can get a bigger mailbox -- choose a size that fits your needs -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
SQL and TEMP segments
Quick question, I want to find out what sql is currently using my temporary segments. Is their a simple solution. Am getting the ORA-1652 for my locally managed temporary tablespace and I would like to find out what sql is causing this. My temp tablespace is currently 1.2GB. Oracle 8.1.7.3 on HPUX 11. TIAWith Yahoo! Mail you can get a bigger mailbox -- choose a size that fits your needs
RE: Create Mat View uses DEFAULT TABLESPACE for temp segments?
Title: RE: Create Mat View uses DEFAULT TABLESPACE for temp segments?
The
only people who don't occasionally look a bit dumb are those who don't do
anything... Check my CLOB/RAW question... can I help paddle the bit
dumb boat :)
Regards,
Bill Burke "The
Kinder and Gentler DBA" IOUG University
Master Class Faculty 2001-2002 "iDBA
Management, High Performance Infrastructure and HA" IOUG Board of Directors 2000-2002 ODTUG Board of Directors 1996-2000 www.OracleGuru.com www.KBMotorsports.biz
-Original Message-From: Thomas Jeff
[mailto:[EMAIL PROTECTED]]Sent: Thursday, January 09, 2003 7:44
AMTo: Multiple recipients of list ORACLE-LSubject: RE:
Create Mat View uses DEFAULT TABLESPACE for temp
segments?
sigh It's always fun to come out looking a bit
dumb in front of such a prestigious group.
Thanks Jared.
My problem turned out to be the placement of the BUILD clause.
Yes, BUILD IMMEDIATE is the default, but my personal
preference is to display all default parameters within
reason within all DDL.
-Original Message- From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 08, 2003 5:44 PM To: Multiple recipients of list ORACLE-L Subject: RE: Create Mat View uses DEFAULT TABLESPACE for temp
segments?
create materialized view MY_MV pctfree
10 pctused 80 tablespace
medium_table_tbs using index pctfree 5
tablespace medium_index_tbs refresh
fast start with sysdate --
every 30 minutes next sysdate + 30 / 1440
as select *
All is explained in the fine manual.
Jared
Thomas Jeff [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/08/2003
02:14 PM Please respond to ORACLE-L
To:
Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject: RE: Create Mat View uses
DEFAULT TABLESPACE for temp segments?
You were right. Tracing the operation
did show at the end that there's a CREATE UNIQUE INDEX
statement with no tablespace specified.
Checking the manuals a bit more carefully shows that
Oracle will create at least one internal table and at
least one index for the mat view. It also states that
the matview must have sufficient quota in the the
target tablespace to store the master table and index. The obvious question then is: how I do specify a target
tablespace for this index? I've tried
various parameters but can't seem to get it to work.
-Original Message- Sent:
Wednesday, January 08, 2003 2:21 PM To: Multiple
recipients of list ORACLE-L
It's not blatantly obvious, but if you've created
the whole thing in the standard way Oracle will
be creating an index on the table that holds
the materialized view data - and that index will
go into your default tablespace.
Regards Jonathan Lewis
http://www.jlcomp.demon.co.uk Coming
soon a new one-day tutorial: Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )
Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html )
England__January 21/23 USA_(CA, TX)_August
The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-Original Message- To:
Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: 08 January 2003 16:06
This is 8.1.7.4 on AIX 4.3.3. When
I try to create a materialized view in the specified tablespace, I hit the
following error. Is this a behavior
of the create
materialized view statement, to create temp segments in the user's default tablespace, and not use the target tablespace?
I've double-checked all ts
quotas and appropriate privs. Changing the user's default
tablespace to the target
tablespace works of course. Or am I missing something blatantly obvious here?
SQL CREATE MATERIALIZED VIEW tcs_ord_hist_mv 2 TABLESPACE TCS_MD_DT01 3 BUILD IMMEDIATE
4 REFRESH ON DEMAND 5 ENABLE
QUERY REWRITE 6 AS 7 SELECT customer_number,
8
product_number,
9
SUM(order_quantity) history_ordered_qty
10 FROM tcs.tcs_order_history
11 GROUP BY
customer_number, product_number 12 /
FROM
tcs.tcs_order_history
* ERROR at line 10: ORA-01630: max # extents (505) reached in temp segment in
tablespace USERS
Jeffery D Thomas DBA Thomson Information Services Thomson,
Inc. Email:
[EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba Select 'Indy DBA' then 'DBA Web Pages'
-- Please see the official
ORACLE-L FAQ: http://www.orafaq.net --
Author: Jonathan Lewis
INET: [EMAIL PROTECTED] Fat City Network
Services -- 858-538-5051 http://www.fatcity.com San Diego,
California -- Mailing list and web
hosting services -
To REMOVE yourself from this mailin
RE: Create Mat View uses DEFAULT TABLESPACE for temp segments?
Title: RE: Create Mat View uses DEFAULT TABLESPACE for temp segments? sigh It's always fun to come out looking a bit dumb in front of such a prestigious group. Thanks Jared. My problem turned out to be the placement of the BUILD clause. Yes, BUILD IMMEDIATE is the default, but my personal preference is to display all default parameters within reason within all DDL. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 08, 2003 5:44 PM To: Multiple recipients of list ORACLE-L Subject: RE: Create Mat View uses DEFAULT TABLESPACE for temp segments? create materialized view MY_MV pctfree 10 pctused 80 tablespace medium_table_tbs using index pctfree 5 tablespace medium_index_tbs refresh fast start with sysdate -- every 30 minutes next sysdate + 30 / 1440 as select * All is explained in the fine manual. Jared Thomas Jeff [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/08/2003 02:14 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject: RE: Create Mat View uses DEFAULT TABLESPACE for temp segments? You were right. Tracing the operation did show at the end that there's a CREATE UNIQUE INDEX statement with no tablespace specified. Checking the manuals a bit more carefully shows that Oracle will create at least one internal table and at least one index for the mat view. It also states that the matview must have sufficient quota in the the target tablespace to store the master table and index. The obvious question then is: how I do specify a target tablespace for this index? I've tried various parameters but can't seem to get it to work. -Original Message- Sent: Wednesday, January 08, 2003 2:21 PM To: Multiple recipients of list ORACLE-L It's not blatantly obvious, but if you've created the whole thing in the standard way Oracle will be creating an index on the table that holds the materialized view data - and that index will go into your default tablespace. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 08 January 2003 16:06 This is 8.1.7.4 on AIX 4.3.3. When I try to create a materialized view in the specified tablespace, I hit the following error. Is this a behavior of the create materialized view statement, to create temp segments in the user's default tablespace, and not use the target tablespace? I've double-checked all ts quotas and appropriate privs. Changing the user's default tablespace to the target tablespace works of course. Or am I missing something blatantly obvious here? SQL CREATE MATERIALIZED VIEW tcs_ord_hist_mv 2 TABLESPACE TCS_MD_DT01 3 BUILD IMMEDIATE 4 REFRESH ON DEMAND 5 ENABLE QUERY REWRITE 6 AS 7 SELECT customer_number, 8 product_number, 9 SUM(order_quantity) history_ordered_qty 10 FROM tcs.tcs_order_history 11 GROUP BY customer_number, product_number 12 / FROM tcs.tcs_order_history * ERROR at line 10: ORA-01630: max # extents (505) reached in temp segment in tablespace USERS Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba Select 'Indy DBA' then 'DBA Web Pages' -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing
Create Mat View uses DEFAULT TABLESPACE for temp segments?
Title: Create Mat View uses DEFAULT TABLESPACE for temp segments? This is 8.1.7.4 on AIX 4.3.3. When I try to create a materialized view in the specified tablespace, I hit the following error. Is this a behavior of the create materialized view statement, to create temp segments in the user's default tablespace, and not use the target tablespace? I've double-checked all ts quotas and appropriate privs. Changing the user's default tablespace to the target tablespace works of course. Or am I missing something blatantly obvious here? SQL CREATE MATERIALIZED VIEW tcs_ord_hist_mv 2 TABLESPACE TCS_MD_DT01 3 BUILD IMMEDIATE 4 REFRESH ON DEMAND 5 ENABLE QUERY REWRITE 6 AS 7 SELECT customer_number, 8 product_number, 9 SUM(order_quantity) history_ordered_qty 10 FROM tcs.tcs_order_history 11 GROUP BY customer_number, product_number 12 / FROM tcs.tcs_order_history * ERROR at line 10: ORA-01630: max # extents (505) reached in temp segment in tablespace USERS Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba Select 'Indy DBA' then 'DBA Web Pages'
RE: Create Mat View uses DEFAULT TABLESPACE for temp segments?
Title: Create Mat View uses DEFAULT TABLESPACE for temp segments? Perfectly normal. Temporary objects are used for more than just sorting. They are used when creating indexes, tables, mviews and certain other objects and operations (such as some parallel operations). The temp segment in this case will become the permanent segment once the MView is created. Robert Robert G. Freeman Technical Management ConsultantTUSC - The Oracle Experts www.tusc.com -Original Message-From: Thomas Jeff [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 08, 2003 8:04 AMTo: Multiple recipients of list ORACLE-LSubject: Create Mat View uses DEFAULT TABLESPACE for temp segments? This is 8.1.7.4 on AIX 4.3.3. When I try to create a materialized view in the specified tablespace, I hit the following error. Is this a behavior of the create materialized view statement, to create temp segments in the user's default tablespace, and not use the target tablespace? I've double-checked all ts quotas and appropriate privs. Changing the user's default tablespace to the target tablespace works of course. Or am I missing something blatantly obvious here? SQL CREATE MATERIALIZED VIEW tcs_ord_hist_mv 2 TABLESPACE TCS_MD_DT01 3 BUILD IMMEDIATE 4 REFRESH ON DEMAND 5 ENABLE QUERY REWRITE 6 AS 7 SELECT customer_number, 8 product_number, 9 SUM(order_quantity) history_ordered_qty 10 FROM tcs.tcs_order_history 11 GROUP BY customer_number, product_number 12 / FROM tcs.tcs_order_history * ERROR at line 10: ORA-01630: max # extents (505) reached in temp segment in tablespace USERS Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba Select 'Indy DBA' then 'DBA Web Pages'
RE: Create Mat View uses DEFAULT TABLESPACE for temp segments?
Title: Create Mat View uses DEFAULT TABLESPACE for temp segments? Yes, but these operations should be using the TARGET tablespace, and not the DEFAULT tablespace to create the TEMP segments, correct? I know when I rebuild indexes at least, that's the case. -Original Message-From: Freeman Robert - IL [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 08, 2003 10:15 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Create Mat View uses DEFAULT TABLESPACE for temp segments? Perfectly normal. Temporary objects are used for more than just sorting. They are used when creating indexes, tables, mviews and certain other objects and operations (such as some parallel operations). The temp segment in this case will become the permanent segment once the MView is created. Robert Robert G. Freeman Technical Management ConsultantTUSC - The Oracle Experts www.tusc.com -Original Message-From: Thomas Jeff [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 08, 2003 8:04 AMTo: Multiple recipients of list ORACLE-LSubject: Create Mat View uses DEFAULT TABLESPACE for temp segments? This is 8.1.7.4 on AIX 4.3.3. When I try to create a materialized view in the specified tablespace, I hit the following error. Is this a behavior of the create materialized view statement, to create temp segments in the user's default tablespace, and not use the target tablespace? I've double-checked all ts quotas and appropriate privs. Changing the user's default tablespace to the target tablespace works of course. Or am I missing something blatantly obvious here? SQL CREATE MATERIALIZED VIEW tcs_ord_hist_mv 2 TABLESPACE TCS_MD_DT01 3 BUILD IMMEDIATE 4 REFRESH ON DEMAND 5 ENABLE QUERY REWRITE 6 AS 7 SELECT customer_number, 8 product_number, 9 SUM(order_quantity) history_ordered_qty 10 FROM tcs.tcs_order_history 11 GROUP BY customer_number, product_number 12 / FROM tcs.tcs_order_history * ERROR at line 10: ORA-01630: max # extents (505) reached in temp segment in tablespace USERS Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba Select 'Indy DBA' then 'DBA Web Pages'
Re: Create Mat View uses DEFAULT TABLESPACE for temp segments?
It's not blatantly obvious, but if you've created the whole thing in the standard way Oracle will be creating an index on the table that holds the materialized view data - and that index will go into your default tablespace. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 08 January 2003 16:06 This is 8.1.7.4 on AIX 4.3.3. When I try to create a materialized view in the specified tablespace, I hit the following error.Is this a behavior of the create materialized view statement, to create temp segments in the user's default tablespace, and not use the target tablespace? I've double-checked all ts quotas and appropriate privs. Changing the user's default tablespace to the target tablespace works of course.Or am I missing something blatantly obvious here? SQL CREATE MATERIALIZED VIEW tcs_ord_hist_mv 2 TABLESPACE TCS_MD_DT01 3 BUILD IMMEDIATE 4 REFRESH ON DEMAND 5 ENABLE QUERY REWRITE 6 AS 7 SELECT customer_number, 8product_number, 9SUM(order_quantity) history_ordered_qty 10 FROM tcs.tcs_order_history 11 GROUP BY customer_number, product_number 12 / FROM tcs.tcs_order_history * ERROR at line 10: ORA-01630: max # extents (505) reached in temp segment in tablespace USERS Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba Select 'Indy DBA' then 'DBA Web Pages' -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Create Mat View uses DEFAULT TABLESPACE for temp segments?
Title: RE: Create Mat View uses DEFAULT TABLESPACE for temp segments? You were right. Tracing the operation did show at the end that there's a CREATE UNIQUE INDEX statement with no tablespace specified. Checking the manuals a bit more carefully shows that Oracle will create at least one internal table and at least one index for the mat view. It also states that the matview must have sufficient quota in the the target tablespace to store the master table and index. The obvious question then is: how I do specify a target tablespace for this index? I've tried various parameters but can't seem to get it to work. -Original Message- From: Jonathan Lewis [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 08, 2003 2:21 PM To: Multiple recipients of list ORACLE-L Subject: Re: Create Mat View uses DEFAULT TABLESPACE for temp segments? It's not blatantly obvious, but if you've created the whole thing in the standard way Oracle will be creating an index on the table that holds the materialized view data - and that index will go into your default tablespace. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 08 January 2003 16:06 This is 8.1.7.4 on AIX 4.3.3. When I try to create a materialized view in the specified tablespace, I hit the following error. Is this a behavior of the create materialized view statement, to create temp segments in the user's default tablespace, and not use the target tablespace? I've double-checked all ts quotas and appropriate privs. Changing the user's default tablespace to the target tablespace works of course. Or am I missing something blatantly obvious here? SQL CREATE MATERIALIZED VIEW tcs_ord_hist_mv 2 TABLESPACE TCS_MD_DT01 3 BUILD IMMEDIATE 4 REFRESH ON DEMAND 5 ENABLE QUERY REWRITE 6 AS 7 SELECT customer_number, 8 product_number, 9 SUM(order_quantity) history_ordered_qty 10 FROM tcs.tcs_order_history 11 GROUP BY customer_number, product_number 12 / FROM tcs.tcs_order_history * ERROR at line 10: ORA-01630: max # extents (505) reached in temp segment in tablespace USERS Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba Select 'Indy DBA' then 'DBA Web Pages' -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Create Mat View uses DEFAULT TABLESPACE for temp segments?
create materialized view MY_MV pctfree 10 pctused 80 tablespace medium_table_tbs using index pctfree 5 tablespace medium_index_tbs refresh fast start with sysdate -- every 30 minutes next sysdate + 30 / 1440 as select * All is explained in the fine manual. Jared Thomas Jeff [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/08/2003 02:14 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Create Mat View uses DEFAULT TABLESPACE for temp segments? You were right. Tracing the operation did show at the end that there's a CREATE UNIQUE INDEX statement with no tablespace specified. Checking the manuals a bit more carefully shows that Oracle will create at least one internal table and at least one index for the mat view. It also states that the matview must have sufficient quota in the the target tablespace to store the master table and index. The obvious question then is: how I do specify a target tablespace for this index? I've tried various parameters but can't seem to get it to work. -Original Message- Sent: Wednesday, January 08, 2003 2:21 PM To: Multiple recipients of list ORACLE-L It's not blatantly obvious, but if you've created the whole thing in the standard way Oracle will be creating an index on the table that holds the materialized view data - and that index will go into your default tablespace. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 08 January 2003 16:06 This is 8.1.7.4 on AIX 4.3.3. When I try to create a materialized view in the specified tablespace, I hit the following error.Is this a behavior of the create materialized view statement, to create temp segments in the user's default tablespace, and not use the target tablespace? I've double-checked all ts quotas and appropriate privs. Changing the user's default tablespace to the target tablespace works of course.Or am I missing something blatantly obvious here? SQL CREATE MATERIALIZED VIEW tcs_ord_hist_mv 2 TABLESPACE TCS_MD_DT01 3 BUILD IMMEDIATE 4 REFRESH ON DEMAND 5 ENABLE QUERY REWRITE 6 AS 7 SELECT customer_number, 8product_number, 9SUM(order_quantity) history_ordered_qty 10 FROM tcs.tcs_order_history 11 GROUP BY customer_number, product_number 12 / FROM tcs.tcs_order_history * ERROR at line 10: ORA-01630: max # extents (505) reached in temp segment in tablespace USERS Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba Select 'Indy DBA' then 'DBA Web Pages' -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: TEMP segments
Sorry it took so long to reply... The first direct-path mechanism in Oracle (i.e. Oracle v6.0 FASTLOAD on MVS platforms, followed by Oracle7 v7.0 SQL*Loader DIRECT=TRUE) did not create a separate segment. It merely made use of space in the existing table segment above the HWM and performed loads there. If the operation succeeded, then the HWM was simply moved to include the populated blocks. If the load failed, then the HWM would stay where it was -- no need for a rollback! The blocks would presumably just get eaten over time as the HWM advanced naturally... Starting in v7.1 and continuing today, all new direct-path (now referred to as APPEND) features create one or more separate TEMPORARY segment(s). When the load completes successfully, the TEMPORARY segment is merged into the table or index. If the operation fails, the TEMPORARY segment is left in place to be eventually cleaned up by SMON... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 21, 2002 4:13 AM Okay, it's early here, I'm fuzzy. I think I meant the same thing (I'd had one of those user calls in the middle of the night) what I think I meant to say was that any operation that would end up adding blocks above the HWM if it COMPLETED would do it by creating TEMP segments that are converted to data blocks on commit of the operation. basically, temp segments are created for any operation that does not touch existing blocks or am I still fuzzy? --- Tim Gorman [EMAIL PROTECTED] wrote: No, only the original SQL*Loader DIRECT=TRUE does that (adding blocks above HWM), which was introduced with v7.0.x... Since then, all direct-path (a.k.a. append) operations (including SQL*Loader DIRECT=TRUE PARALLEL=TRUE, parallel CREATE INDEX, parallel CREATE TABLE AS SELECT, and INSERT /*+ APPEND PARALLEL */, etc) have created temporary segments for each parallel execution slave process which will be merged into eventual segment. As Anjo commented, it makes for really fast and easy rollback; just let SMON clean up the temporary segments... ...don't know if it's ever really been documented; I did include this description in our books on data warehousing in 1997 and 1999... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 20, 2002 4:08 AM Tim -- that adds new blocks above the HWM? I wonder if any command that adds new blocks (vs inserting rows into existing ones) automatically creates the new blocks as TEMP segments. Logically it makes sense but I wonder if it's documented anywhere. --- Tim Gorman [EMAIL PROTECTED] wrote: ...as does INSERT /*+ APPEND PARALLEL */... - Original Message - From: Fink, Dan To: Multiple recipients of list ORACLE-L Sent: Tuesday, November 19, 2002 1:15 PM Subject: TEMP segments I just found a new command that creates TEMP segments. It is well known that index creations first create the index segments as TEMP segments then 'convert' them to index segments upon completion. What I just found out (thanks to a failed operation) is that 'CREATE TABLE AS SELECT' (ctas) also creates the segments as TEMP first. Dan Fink __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Mail Plus
Re: TEMP segments
thanks! there was no rush on the reply, I know some people on this list work on occasion (of course I'm not one of them yeah right but...) :) I think I've got it now. And I think I had it straight in my head but got it confused when it came out my fingertips. --- Tim Gorman [EMAIL PROTECTED] wrote: Sorry it took so long to reply... The first direct-path mechanism in Oracle (i.e. Oracle v6.0 FASTLOAD on MVS platforms, followed by Oracle7 v7.0 SQL*Loader DIRECT=TRUE) did not create a separate segment. It merely made use of space in the existing table segment above the HWM and performed loads there. If the operation succeeded, then the HWM was simply moved to include the populated blocks. If the load failed, then the HWM would stay where it was -- no need for a rollback! The blocks would presumably just get eaten over time as the HWM advanced naturally... Starting in v7.1 and continuing today, all new direct-path (now referred to as APPEND) features create one or more separate TEMPORARY segment(s). When the load completes successfully, the TEMPORARY segment is merged into the table or index. If the operation fails, the TEMPORARY segment is left in place to be eventually cleaned up by SMON... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 21, 2002 4:13 AM Okay, it's early here, I'm fuzzy. I think I meant the same thing (I'd had one of those user calls in the middle of the night) what I think I meant to say was that any operation that would end up adding blocks above the HWM if it COMPLETED would do it by creating TEMP segments that are converted to data blocks on commit of the operation. basically, temp segments are created for any operation that does not touch existing blocks or am I still fuzzy? --- Tim Gorman [EMAIL PROTECTED] wrote: No, only the original SQL*Loader DIRECT=TRUE does that (adding blocks above HWM), which was introduced with v7.0.x... Since then, all direct-path (a.k.a. append) operations (including SQL*Loader DIRECT=TRUE PARALLEL=TRUE, parallel CREATE INDEX, parallel CREATE TABLE AS SELECT, and INSERT /*+ APPEND PARALLEL */, etc) have created temporary segments for each parallel execution slave process which will be merged into eventual segment. As Anjo commented, it makes for really fast and easy rollback; just let SMON clean up the temporary segments... ...don't know if it's ever really been documented; I did include this description in our books on data warehousing in 1997 and 1999... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 20, 2002 4:08 AM Tim -- that adds new blocks above the HWM? I wonder if any command that adds new blocks (vs inserting rows into existing ones) automatically creates the new blocks as TEMP segments. Logically it makes sense but I wonder if it's documented anywhere. --- Tim Gorman [EMAIL PROTECTED] wrote: ...as does INSERT /*+ APPEND PARALLEL */... - Original Message - From: Fink, Dan To: Multiple recipients of list ORACLE-L Sent: Tuesday, November 19, 2002 1:15 PM Subject: TEMP segments I just found a new command that creates TEMP segments. It is well known that index creations first create the index segments as TEMP segments then 'convert' them to index segments upon completion. What I just found out (thanks to a failed operation) is that 'CREATE TABLE AS SELECT' (ctas) also creates the segments as TEMP first. Dan Fink __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: TEMP segments
Okay, it's early here, I'm fuzzy. I think I meant the same thing (I'd had one of those user calls in the middle of the night) what I think I meant to say was that any operation that would end up adding blocks above the HWM if it COMPLETED would do it by creating TEMP segments that are converted to data blocks on commit of the operation. basically, temp segments are created for any operation that does not touch existing blocks or am I still fuzzy? --- Tim Gorman [EMAIL PROTECTED] wrote: No, only the original SQL*Loader DIRECT=TRUE does that (adding blocks above HWM), which was introduced with v7.0.x... Since then, all direct-path (a.k.a. append) operations (including SQL*Loader DIRECT=TRUE PARALLEL=TRUE, parallel CREATE INDEX, parallel CREATE TABLE AS SELECT, and INSERT /*+ APPEND PARALLEL */, etc) have created temporary segments for each parallel execution slave process which will be merged into eventual segment. As Anjo commented, it makes for really fast and easy rollback; just let SMON clean up the temporary segments... ...don't know if it's ever really been documented; I did include this description in our books on data warehousing in 1997 and 1999... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 20, 2002 4:08 AM Tim -- that adds new blocks above the HWM? I wonder if any command that adds new blocks (vs inserting rows into existing ones) automatically creates the new blocks as TEMP segments. Logically it makes sense but I wonder if it's documented anywhere. --- Tim Gorman [EMAIL PROTECTED] wrote: ...as does INSERT /*+ APPEND PARALLEL */... - Original Message - From: Fink, Dan To: Multiple recipients of list ORACLE-L Sent: Tuesday, November 19, 2002 1:15 PM Subject: TEMP segments I just found a new command that creates TEMP segments. It is well known that index creations first create the index segments as TEMP segments then 'convert' them to index segments upon completion. What I just found out (thanks to a failed operation) is that 'CREATE TABLE AS SELECT' (ctas) also creates the segments as TEMP first. Dan Fink __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Mail Plus Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: TEMP segments
It did that for the longest time. It creates a temp segment so that if the create table table fails near the end, there doesnt have to be a complete rollback. SMON can come along and cleanup the segment. If the create table as has completed, the temp segment will be converted to a ordinary segment in the data dictionary. Anjo. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Fink, Dan Sent: Tuesday, November 19, 2002 9:16 PM To: Multiple recipients of list ORACLE-L Subject: TEMP segments I just found a new command that creates TEMP segments. It is well known that index creations first create the index segments as TEMP segments then 'convert' them to index segments upon completion. What I just found out (thanks to a failed operation) is that 'CREATE TABLE AS SELECT' (ctas) also creates the segments as TEMP first. Dan Fink
Re: TEMP segments
Tim -- that adds new blocks above the HWM? I wonder if any command that adds new blocks (vs inserting rows into existing ones) automatically creates the new blocks as TEMP segments. Logically it makes sense but I wonder if it's documented anywhere. --- Tim Gorman [EMAIL PROTECTED] wrote: ...as does INSERT /*+ APPEND PARALLEL */... - Original Message - From: Fink, Dan To: Multiple recipients of list ORACLE-L Sent: Tuesday, November 19, 2002 1:15 PM Subject: TEMP segments I just found a new command that creates TEMP segments. It is well known that index creations first create the index segments as TEMP segments then 'convert' them to index segments upon completion. What I just found out (thanks to a failed operation) is that 'CREATE TABLE AS SELECT' (ctas) also creates the segments as TEMP first. Dan Fink __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: TEMP segments
No, only the original SQL*Loader DIRECT=TRUE does that (adding blocks above HWM), which was introduced with v7.0.x... Since then, all direct-path (a.k.a. append) operations (including SQL*Loader DIRECT=TRUE PARALLEL=TRUE, parallel CREATE INDEX, parallel CREATE TABLE AS SELECT, and INSERT /*+ APPEND PARALLEL */, etc) have created temporary segments for each parallel execution slave process which will be merged into eventual segment. As Anjo commented, it makes for really fast and easy rollback; just let SMON clean up the temporary segments... ...don't know if it's ever really been documented; I did include this description in our books on data warehousing in 1997 and 1999... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 20, 2002 4:08 AM Tim -- that adds new blocks above the HWM? I wonder if any command that adds new blocks (vs inserting rows into existing ones) automatically creates the new blocks as TEMP segments. Logically it makes sense but I wonder if it's documented anywhere. --- Tim Gorman [EMAIL PROTECTED] wrote: ...as does INSERT /*+ APPEND PARALLEL */... - Original Message - From: Fink, Dan To: Multiple recipients of list ORACLE-L Sent: Tuesday, November 19, 2002 1:15 PM Subject: TEMP segments I just found a new command that creates TEMP segments. It is well known that index creations first create the index segments as TEMP segments then 'convert' them to index segments upon completion. What I just found out (thanks to a failed operation) is that 'CREATE TABLE AS SELECT' (ctas) also creates the segments as TEMP first. Dan Fink __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
TEMP segments
I just found a new command that creates TEMP segments. It is well known that index creations first create the index segments as TEMP segments then 'convert' them to index segments upon completion. What I just found out (thanks to a failed operation) is that 'CREATE TABLE AS SELECT' (ctas) also creates the segments as TEMP first. Dan Fink
Re: TEMP segments
...as does INSERT /*+ APPEND PARALLEL */... - Original Message - From: Fink, Dan To: Multiple recipients of list ORACLE-L Sent: Tuesday, November 19, 2002 1:15 PM Subject: TEMP segments I just found a new command that creates TEMP segments. It is well known that index creations first create the index segments as TEMP segments then 'convert' them to index segments upon completion. What I just found out (thanks to a failed operation) is that 'CREATE TABLE AS SELECT' (ctas) also creates the segments as TEMP first. Dan Fink
Where are temp segments created?
Hi DBAs, I have a user Test who has a default tablespace trf_data and temporary tablespace temporary. When trying to create a table I got unable to extend temp segment in tablespace trf_data. My question is if I have temporary tablespace set up as temporary why is Oracle creating temp segments in trf_data? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Where are temp segments created?
Heh, this is a common problem. The error message is misleading. It really means it can't build a temp segment used to create the new object of the right size. Remember when creating the initial extent there has to be one of the same size or bigger. It means you don't have enough space or too fragmented. It is refering to the temp segment needed to create the table, not a true' temp segment that you are thinking of. When you create a table, it creates a temporary segment the destination tablespace before it actually marks it for the table segment. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Thursday, September 06, 2001 9:46 AM To: Multiple recipients of list ORACLE-L Hi DBAs, I have a user Test who has a default tablespace trf_data and temporary tablespace temporary. When trying to create a table I got unable to extend temp segment in tablespace trf_data. My question is if I have temporary tablespace set up as temporary why is Oracle creating temp segments in trf_data? Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
