Jonathan, I successfully use "drop tablespace including contents and datafiles" all the time. And a quick check of the docs shows that "cascade constraints" is valid but, as Richard said, belongs at the end of the statement.
As for the second question. DBA_SEGMENTS gives you the total allocated blocks in a single select, without aggregation. To use DBA_EXTENTS you'd have to use sum(blocks) in the select. From prior experience with OCP exams, they tend to go for the simplest way to get an answer. So DBA_SEGMENTS is the correct answer in their view, although you *can* get the same information from DBA_EXTENTS. --- Jonathan Gennick <[EMAIL PROTECTED]> wrote: > Hello Prem, > > The syntax the question gives for DROP TABLESPACE doesn't > look right to me. Without looking at the current docs<grin>, > I believe INCLUDING CONTENTS is a valid option, and that the > others are invalid. Thus, answer B, the statement will fail, > looks correct. Whoever wrote that test question does not > appear to have tested their test. > > As for question 2, until you asked, I too would have looked > in DBA_EXTENTS. But as I look at one table in my database, I > see that DBA_SEGMENTS.BLOCKS reports the same value as > DBA_EXTENTS.BLOCKS. It may be that DBA_SEGMENTS reports on > SUM(BLOCKS) for all extents in each segment. Interesting. > I'll have to read the docs on this, to find out for certain > what's going on. > > One thing to be aware of with respect to question 2 below is > that in a partitioned table, each partition is a segment. > Thus, whether you go to DBA_EXTENTS or DBA_SEGMENTS, you do > need to be sure to consider all partitions of the table in > question. > > Best regards, > > Jonathan Gennick --- Brighten the corner where you are > http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] > > Join the Oracle-article list and receive one > article on Oracle technologies per month by > email. To join, visit > http://four.pairlist.net/mailman/listinfo/oracle-article, > or send email to [EMAIL PROTECTED] and > include the word "subscribe" in either the subject or body. > > > Wednesday, January 7, 2004, 6:24:24 AM, Prem Khanna J > ([EMAIL PROTECTED]) wrote: > PKJ> Hi list , sorry to pester you with questions regarding > PKJ> boson OCP questions . i have scheduled for #1Z0-031 exam > PKJ> and so desperately need help from this list . > PKJ> please bear with me for while . look at the 2 questions below . > > PKJ> > ----------------------------------------------------------------- > PKJ> QUESTION #1 > PKJ> what happens when you issue the command below . > PKJ> drop tablespace testtbs including contents cascade constraints > and > PKJ> datafiles; > > PKJ> A.the tablespace will be dropped , constraints will be droppped > and the > PKJ> datafiles will be taken out of the o/s. > > PKJ> B. statement will fail > > PKJ> C.you must drop constraints before issuing this command. > > PKJ> this is what happens when i try on 9.2.0.4 : > PKJ> ERROR at line 1: ORA-02173: invalid option for DROP TABLESPACE > > PKJ> but boson's choice is A. i wonder how ??? > PKJ> > ----------------------------------------------------------------- > PKJ> QUESTION #2 > PKJ> you need to determine how much space has been allocated for a > table. > PKJ> which view would give you this information ? > > PKJ> A. dba_extents > PKJ> B. dba_ts_quotas > PKJ> C. dba_segments > > PKJ> my choice is C . > PKJ> but boson's choice is A . it says other views cannot give > PKJ> the required details . > > PKJ> a metalink doc says that dba_segments.blocks gives the > PKJ> total number of blocks allocated to the table. > > PKJ> what will be the choice that you would go for ? > PKJ> > ----------------------------------------------------------------- > > PKJ> so now i have the question whether boson is reliable ? > PKJ> how many in this list have used it ? > PKJ> or am i missing something : (( > > PKJ> Regards, > PKJ> Prem. > > PKJ> -- > PKJ> Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jonathan Gennick > 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! Hotjobs: Enter the "Signing Bonus" Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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).
