Combine Mutiple Statements into one

[tekpr00]

Hello I was wondering if anyone could help combine the following multiple statements into one without the need for the first 2 semi-colons ";"

drop table t1 [COLOR=red];[/color] -- no need for this colon

create table t1 as
select
   o.owner          owner,
   o.object_name    object_name,
   o.subobject_name subobject_name,
   o.object_type    object_type,
   count(distinct file# || block#)         num_blocks
from
   dba_objects  o,
   v$bh         bh
where
   o.data_object_id  = bh.objd
and
   o.owner not in ('SYS','SYSTEM')
and
   bh.status != 'free'
group by
   o.owner,
   o.object_name,
   o.subobject_name,
   o.object_type
order by
   count(distinct file# || block#) desc [COLOR=red];[/color] -- no need for this colon also


column c0 heading "Owner"                                    format a12
column c1 heading "Object|Name"                              format a30
column c2 heading "Object|Type"                              format a8
column c3 heading "Number of|Blocks in|Buffer|Cache"         format 99,999,999
column c4 heading "Percentage|of object|blocks in|Buffer"    format 999
column c5 heading "Buffer|Pool"                              format a7
column c6 heading "Block|Size"                               format 99,999

select
   t1.owner                                          c0,
   object_name                                       c1,
   case when object_type = 'TABLE PARTITION' then 'TAB PART'
        when object_type = 'INDEX PARTITION' then 'IDX PART'
        else object_type end c2,
   sum(num_blocks)                                     c3,
   (sum(num_blocks)/greatest(sum(blocks), .001))*100 c4,
   buffer_pool                                       c5,
   sum(bytes)/sum(blocks)                            c6
from
   t1,
   dba_segments s
where
   s.segment_name = t1.object_name
and
   s.owner = t1.owner
and
   s.segment_type = t1.object_type
and
   nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')
group by
   t1.owner,
   object_name,
   object_type,
   buffer_pool
having
   sum(num_blocks) > 10
order by
   sum(num_blocks) desc ;

 

[tekpr00]

colon=semi-colon

 

[carp]

Short answer is "no".
These are three different commands (2 DDL and one query). They need to be separated by semicolons.

 

[SantaMufasa]

Tekpr00,

Please tell us more about your need to remove the semi-colons...As currently written, you need the semi-colons because they mark the end of SQL statements. Is there some sort of overriding reason you need/want to get rid of them?

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[taupirho]

If the statments are in a sql script which you are just SQLPLUS'ing as it were you can separate them out using the slash character

drop table t1
/
create table t1 as select some_column from some_table
/
column some_column format a20
select some_column
from t1
/
exit


Does that help in any way ?


In order to understand recursion, you must first understand recursion.

 

[Dagon]

You can use the "create schema" command to create multiple options in one session, but I don't think you can also include a drop table. I'm not entirely sure why it would be such a big deal, though.