Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: another OCP question -- help me guys

Re: another OCP question -- help me guys

From: Jonathan Gennick <>
Date: Wed, 07 Jan 2004 05:09:24 -0800
Message-ID: <>

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 * 906.387.1698 *

Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit, or send email to and include the word "subscribe" in either the subject or body.

Wednesday, January 7, 2004, 6:24:24 AM, Prem Khanna J ( 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> 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> must drop constraints before issuing this command.

PKJ> this is what happens when i try on : PKJ> ERROR at line 1: ORA-02173: invalid option for DROP TABLESPACE

PKJ> but boson's  choice is A.  i wonder how ???
PKJ> -----------------------------------------------------------------
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:


Please see the official ORACLE-L FAQ:

Author: Jonathan Gennick

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message to: (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:09:24 CST

Original text of this message