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 <jonathan_at_gennick.com> 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:jonathan@gennick.com
>
> 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 Oracle-article-request_at_gennick.com and
> include the word "subscribe" in either the subject or body.
>
>
> Wednesday, January 7, 2004, 6:24:24 AM, Prem Khanna J
> (jprem_at_kssnet.co.jp) 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: jonathan_at_gennick.com
>
> 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: ListGuru_at_fatcity.com (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: wisernet100_at_yahoo.com
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: ListGuru_at_fatcity.com (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).
Received on Wed Jan 07 2004 - 07:29:25 CST
