Re: View questions...

From: Andy Finkenstadt <andy_at_homebase.vistachrome.com>
Date: Fri, 6 Nov 1992 17:32:51 GMT
Message-ID: <1992Nov6.173251.22102_at_homebase.vistachrome.com>


Views do not occupy database blocks and therefore do not occupy extents or segments. The resulting R-table (SELECTed rows and internal queries) can occupy TEMPorary space in the database.

Temporary tablespace is allocated on a USER level, not a VIEW level. You can change a user's temporary tablespace as thus:

   SQL> ALTER USER andy

	 2> TEMPORARY TABLESPACE temp
     3> DEFAULT TABLESPACE largeone
     4> /

   User altered.    

   SQL> I do not recall if the user has to initiate a new connection for the new settings to take place. I am inclined to GUESS yes. So, just have them log out and back in.

If you know that you will be executing a LONG running query that requires a good deal of ROLLBACK space in addition to TEMP space (I don't remember the conditions under which temp doesn't use up rollback entries), you can have a LARGE rollback segment in a ROLL tablespace (hint hint) and then use this in version of Oracle beyond 6.0.33 (pretty sure that's 33)

   SQL> commit;  /* end transaction which may have been in process */
   SQL> SET TRANSACTION USE ROLLBACK SEGMENT LARGE_RBS;
   SQL> select * from large_view ;
   SQL> commit;  /* release rollback */

Hope that helps.

-Andy

un_at_gdstech.GRUMMAN.COM (Un Fu) writes:
>How can I get the block usage of a view I created? Looked
>into "user_extents" and it only displays block usages of tables.
>
>Also how do I direct the creation of a view or several views to use
>a temporary, TEMP_TS, tablespace. I see one can specify which
>tablespace to create a table at, but not on the 'CREATE VIEW' command.
>
>I have been asking lots of question lately and this newsgroup has been
>tremendously responsive. THANK YOU ALL, FOLKS!
 

-- 
Andrew Finkenstadt, Vista-Chrome, Inc., Homes & Land Publishing Corporation
GEnie Unix RoundTable Manager, andy_at_vistachrome.com, andy_at_genie.geis.com.
  Send mail to ora-request_at_vistachrome.com to join Unix, CASE, and 
  Desktop Oracle RDBMS Database discussions.
Received on Fri Nov 06 1992 - 18:32:51 CET

Original text of this message