Re: View questions...
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