What makes a great DBA?

  What makes a great DBA?

Knowing how to approach and solve performance problems is one of the vital skills.

• if you want to be a great DBA, you must learn how to solve performance problems 
• if you want to know how to solve performance problems, you need a step by step process for it
• if you want to understand the Oracle memory components, you need to study the Oracle memory
• if you want to understand the workings of the Oracle cursor, you need to study the 7 stages of the Oracle cursors
• if you want to understand Oracle statistics, you need to study all the Oracle statistics types and how to use them.
• if you want to feel confident using the AWR, ADDM and ASH, you need to study these topics in detail.
• if you want to be able to solve any performance problems, you need to know how to approach the performance problems.

 

How To Enable/Disable Archive Log Mode In Oracle Database

There are 2 types of logging mode in oracle database.

1. Archivelog mode :

In this mode, after the online redo logs are filled , it will move to archive location.

2. Noarchivelog mode :

In this mode, filled online redo logs wont be archives, instead they will be overwritten.

How to Enable archive log mode :

 

SQL > select name,log_mode from v$database;
 
NAME      LOG_MODE
--------- -----------
PROD      NOARCHIVELOG
 

SQL > archive log list
Database log mode              No Archive Mode
Automatic archival             Disbled
Archive destination            /u01/archive/PROD
Oldest online log sequence     506
Next log sequence to archive   513
Current log sequence           513
 

---- make sure db is running in spfile

SQL > alter system set log_archive_dest_1='LOCATION=/u01/archive/PROD' scope=spfile;
database altered.
 

SQL >shutdown  immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
 

SQL > startup mount
ORACLE instance started.
Total System Global Area 6415597568 bytes
Fixed Size                  2170304 bytes
Variable Size             905970240 bytes
Database Buffers         5502926848 bytes
Redo Buffers                4530176 bytes
Database mounted.
 

SQL >alter database archivelog;
 
database altered.
 

SQL >alter database open;
 
database altered.
 

SQL >select name,log_mode from v$database;
 
NAME      LOG_MODE
--------- -----------
PROD      ARCHIVELOG
 

SQL >archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/archive/PROD
Oldest online log sequence     506
Next log sequence to archive   513
Current log sequence           513
 

How to Disable archivelog mode :

SQL >select name,log_mode from v$database;

 

NAME      LOG_MODE

--------- -----------

PROD      ARCHIVELOG

 

SQL > archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/archive/PROD

Oldest online log sequence     506

Next log sequence to archive   513

Current log sequence           513

 

 

SQL > shutdown  immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL > startup mount

ORACLE instance started.

Total System Global Area 6415597568 bytes

Fixed Size                  2170304 bytes

Variable Size             905970240 bytes

Database Buffers         5502926848 bytes

Redo Buffers                4530176 bytes

Database mounted.

 

SQL >alter database noarchivelog;

database altered.

SQL >alter database open;

 

database altered.

SQL > select name,log_mode from v$database;

 

NAME      LOG_MODE

--------- -----------

PROD      NOARCHIVELOG

 

SQL > archive log list

Database log mode              No Archive Mode

Automatic archival             Disbled

Archive destination            /u01/archive/PROD

Oldest online log sequence     506

Next log sequence to archive   513

Current log sequence           513

 

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning… Have a great day!!!

 

How to Rename or Move Redo log files in Oracle

It is possible to move/rename the online redo logs should the need arise. This document will detail the steps required to move/rename the online redo logs in an 12cR2 environment on Linux. These steps also apply to a 10g, and 11gR2 environment.

First we will verify the current location of the online redo log files :

SQL>

SQL>
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/redo03.log
/u01/app/oracle/oradata/ORCL/redo02.log
/u01/app/oracle/oradata/ORCL/redo01.log

We are going to move the online redo logs from /u01/app/oracle/oradata/ORCL/ to /u01/app/oracle/oradata/ORCL/redo/. The redo logs cannot be moved/renamed while the database is online. The database must be in a mount state to move/rename the online redo logs.

First we will shutdown the database and move the online redo logs to their new location :

SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@orakldba ~]$
[oracle@orakldba ~]$ mv /u01/app/oracle/oradata/ORCL/redo03.log /u01/app/oracle/oradata/ORCL/redo/redo03.log

[oracle@orakldba ~]$ mv /u01/app/oracle/oradata/ORCL/redo02.log /u01/app/oracle/oradata/ORCL/redo/redo02.log

[oracle@orakldba ~]$ mv /u01/app/oracle/oradata/ORCL/redo01.log /u01/app/oracle/oradata/ORCL/redo/redo01.log

[oracle@orakldba ~]$

Next we bring up the database into mount mode and issue ALTER DATABASE RENAME FILE statements to update the data dictionary and control files. The last thing we do is open the database.

SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area  536870912 bytes
Fixed Size                  8622776 bytes
Variable Size             448793928 bytes
Database Buffers           75497472 bytes
Redo Buffers                3956736 bytes
Database mounted.

SQL>
SQL>
SQL> alter database rename file '/u01/app/oracle/oradata/ORCL/redo03.log' to '/u01/app/oracle/oradata/ORCL/redo/redo03.log';

Database altered.

SQL>
SQL> alter database rename file '/u01/app/oracle/oradata/ORCL/redo02.log' to '/u01/app/oracle/oradata/ORCL/redo/redo02.log';

Database altered.

SQL>
SQL> alter database rename file '/u01/app/oracle/oradata/ORCL/redo01.log' to '/u01/app/oracle/oradata/ORCL/redo/redo01.log';

Database altered.

SQL>
SQL> alter database open;

Database altered.

We can see that the changes were made in the data dictionary by issuing the following query again.

SQL>
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/redo/redo03.log
/u01/app/oracle/oradata/ORCL/redo/redo02.log
/u01/app/oracle/oradata/ORCL/redo/redo01.log

SQL>

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning… Have a great day!!!

 

How To Rename Or Move A Datafile In Oracle

 

In Oracle 12c :

 

If you are in 12c version ,then renaming a datafile can be done online, without making the datafile offline.

 

SQL>
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='SHASHI';
FILE_NAME                                                              TABLESPACE_NAME                ONLINE_
---------------------------------------------------------------------- ------------------------------ -------
/u01/app/oracle/oradata/ORCL/shashi.dbf                                SHASHI                         ONLINE

 

SQL>
SQL>
SQL> alter database move datafile '/u01/app/oracle/oradata/ORCL/shashi.dbf' to '/home/oracle/shashi01.dbf';
Database altered.

 

SQL>
SQL>
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='SHASHI';
FILE_NAME                                                              TABLESPACE_NAME                ONLINE_
---------------------------------------------------------------------- ------------------------------ -------
/home/oracle/shashi01.dbf                                              SHASHI                         ONLINE

 

 

 

In 11g and previous versions :

 

If you are in 11g or previous version, you need to follow below steps to move or rename a datafile without shutting down the database.

 

•        First make the datafile offline.
•        move the datafile as os level.
•        Rename the datafile at database level.
•        recover the particular datafile.
•        make the datafile online.


SQL>
SQL> set lines 200 pages 100
SQL> col FILE_NAME for a70
SQL>
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='SHASHI';
FILE_NAME                                                              TABLESPACE_NAME                ONLINE_
---------------------------------------------------------------------- ------------------------------ -------
/u01/app/oracle/oradata/ORCL/shashi.dbf                                SHASHI                         ONLINE

 

SQL>
SQL>
SQL> alter database datafile '/u01/app/oracle/oradata/ORCL/shashi.dbf' offline;
Database altered.

 

SQL>
SQL>
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='SHASHI';
FILE_NAME                                                              TABLESPACE_NAME                ONLINE_
---------------------------------------------------------------------- ------------------------------ -------
/u01/app/oracle/oradata/ORCL/shashi.dbf                                SHASHI                         RECOVER

 

SQL>
SQL>
SQL> !mv /u01/app/oracle/oradata/ORCL/shashi.dbf /home/oracle/shashi01.dbf

 

SQL>
SQL>
SQL> alter database rename file '/u01/app/oracle/oradata/ORCL/shashi.dbf' to '/home/oracle/shashi01.dbf';
Database altered.

 

SQL>
SQL>
SQL> alter database datafile '/home/oracle/shashi01.dbf' online;
alter database datafile '/home/oracle/shashi01.dbf' online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/home/oracle/shashi01.dbf'

 

SQL>
SQL>
SQL> recover datafile 6;
Media recovery complete.

 

SQL>
SQL>
SQL>
SQL> alter database datafile '/home/oracle/shashi01.dbf' online;
Database altered.

 

SQL>
SQL>
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='SHASHI';
FILE_NAME                                                              TABLESPACE_NAME                ONLINE_
---------------------------------------------------------------------- ------------------------------ -------
/home/oracle/shashi01.dbf                                              SHASHI                         ONLINE

 

 

 

 

I hope this article helped you. Your suggestions/feedback are most welcome.
Keep learning… Have a great day!!!

 

How to find out tablespaces with free space < 15%

How to find out tablespaces with free space < 15%

set pagesize 300
set linesize 100
column tablespace_name format a15 heading 'Tablespace'
column sumb format 999,999,999
column extents format 9999
column bytes format 999,999,999,999
column largest format 999,999,999,999
column Tot_Size format 999,999 Heading 'Total Size(Mb)'
column Tot_Free format 999,999,999 heading 'Total Free(Kb)'
column Pct_Free format 999.99 heading '% Free'
column Max_Free format 999,999,999 heading 'Max Free(Kb)'
column Min_Add format 999,999,999 heading 'Min space add (MB)'
ttitle center 'Tablespaces With Less Than 15% Free Space' skip 2
set echo off
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
   sum(a.sumb/1024) Tot_Free,
  sum(a.sumb)*100/sum(a.tots) Pct_Free,
   ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
   from
 (
  select tablespace_name,0 tots,sum(bytes) sumb
  from dba_free_space a
  group by tablespace_name
  union
 select tablespace_name,sum(bytes) tots,0 from
  dba_data_files
group by tablespace_name) a
 group by a.tablespace_name
 having sum(a.sumb)*100/sum(a.tots) < 15
 order by pct_free;

OUTPUT:--

                              Tablespaces With Less Than 15% Free Space

Tablespace      Total Size(Mb) Total Free(Kb)  % Free Min space add (MB)
--------------- -------------- -------------- ------- ------------------
SYSAUX                     570         34,880    5.98                 61
SYSTEM                     870        122,048   13.70                 14

All about SGA

All about SGA

SYS @ catdb > select value/1024/1024 mb from v$parameter where upper(name) = 'SGA_MAX_SIZE';

        MB
----------
       800

SYS @ catdb > select pool, round(sum(BYTES)/1024/1024) MB from V$SGASTAT group by pool;

POOL                 MB
------------ ----------
                    292
java pool             4
shared pool         208
large pool           12

SYS @ catdb > select name , value/1024/1024 MB
  from v$parameter
  where upper(name) in (
  'DB_CACHE_SIZE','DB_RECYCLE_CACHE_SIZE','DB_KEEP_CACHE_SIZE', '
  DB_2k_CACHE_SIZE', 'DB_8k_CACHE_SIZE', 'DB_16k_CACHE_SIZE');  2    3    4    5

NAME                                                                                     MB
-------------------------------------------------------------------------------- ----------
db_cache_size                                                                             0
db_keep_cache_size                                                                        0
db_recycle_cache_size                                                                     0

SYS @ catdb > SELECT COMPONENT , CURRENT_SIZE/1024/1024 MB
FROM V$SGA_DYNAMIC_COMPONENTS
WHERE CURRENT_SIZE <>0;  2    3

COMPONENT                                                                MB
---------------------------------------------------------------- ----------
shared pool                                                             208
large pool                                                               12
java pool                                                                 4
DEFAULT buffer cache                                                    284

SYS @ catdb > SELECT COMPONENT, ROUND(CURRENT_SIZE/1024/1024) CURRENT_SIZE ,
ROUND(MIN_SIZE/1024/1024) MIN, ROUND(MAX_SIZE/1024/1024) MAX
FROM V$MEMORY_DYNAMIC_COMPONENTS;  2    3

COMPONENT                                                        CURRENT_SIZE        MIN        MAX
---------------------------------------------------------------- ------------ ---------- ----------
shared pool                                                               208        132        208
large pool                                                                 12         12         72
java pool                                                                   4          4          4
streams pool                                                                0          0          0
SGA Target                                                                520        520        520
DEFAULT buffer cache                                                      284        284        356
KEEP buffer cache                                                           0          0          0
RECYCLE buffer cache                                                        0          0          0
DEFAULT 2K buffer cache                                                     0          0          0
DEFAULT 4K buffer cache                                                     0          0          0
DEFAULT 8K buffer cache                                                     0          0          0
DEFAULT 16K buffer cache                                                    0          0          0
DEFAULT 32K buffer cache                                                    0          0          0
Shared IO Pool                                                              0          0          0
PGA Target                                                                280        280        280
ASM Buffer Cache                                                            0          0          0

16 rows selected.

SYS @ catdb > select START_TIME, END_TIME, STATUS, COMPONENT, OPER_TYPE, OPER_MODE,
PARAMETER, INITIAL_SIZE/1024/1024 INITIAL_SIZE_MB,
TARGET_SIZE/1024/1024 TARGET_SIZE_MB, FINAL_SIZE/1024/1024 FINAL_SIZE_MB
from V$MEMORY_RESIZE_OPS
order by START_TIME, END_TIME ;  2    3    4    5

START_TIM END_TIME  STATUS    COMPONENT                                                        OPER_TYPE     OPER_MODE PARAMETER                    INITIAL_SIZE_MB TARGET_SIZE_MB FINAL_SIZE_MB
--------- --------- --------- ---------------------------------------------------------------- ------------- --------- -------------------------------------------------------------------------------- --------------- -------------- -------------
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT 8K buffer cache                                          STATIC                  db_8k_cache_size                   0       0             0
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT 4K buffer cache                                          STATIC                  db_4k_cache_size                   0       0             0
24-JUN-20 24-JUN-20 COMPLETE  RECYCLE buffer cache                                             STATIC                  db_recycle_cache_size              0       0             0
24-JUN-20 24-JUN-20 COMPLETE  KEEP buffer cache                                                STATIC                  db_keep_cache_size                 0       0             0
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT 2K buffer cache                                          STATIC                  db_2k_cache_size                   0       0             0
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT buffer cache                                             STATIC                  db_cache_size                      0     300           300
24-JUN-20 24-JUN-20 COMPLETE  ASM Buffer Cache                                                 STATIC                  db_cache_size                      0       0             0
24-JUN-20 24-JUN-20 COMPLETE  PGA Target                                                       STATIC                  pga_aggregate_target               0     280           280
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT 16K buffer cache                                         STATIC                  db_16k_cache_size                  0       0             0
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT 32K buffer cache                                         STATIC                  db_32k_cache_size                  0       0             0
24-JUN-20 24-JUN-20 COMPLETE  SGA Target                                                       STATIC                  sga_target                         0     520           520
24-JUN-20 24-JUN-20 COMPLETE  streams pool                                                     STATIC                  streams_pool_size                  0       0             0
24-JUN-20 24-JUN-20 COMPLETE  java pool                                                        STATIC                  java_pool_size                     0       4             4
24-JUN-20 24-JUN-20 COMPLETE  large pool                                                       STATIC                  large_pool_size                    0      72            72
24-JUN-20 24-JUN-20 COMPLETE  shared pool                                                      STATIC                  shared_pool_size                   0     132           132
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT buffer cache                                             INITIALIZING            db_cache_size                    300     300           300
24-JUN-20 24-JUN-20 COMPLETE  shared pool                                                      GROW          IMMEDIATE shared_pool_size                 132     136           136
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        IMMEDIATE db_cache_size                    300     296           296
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT buffer cache                                             GROW          DEFERRED  db_cache_size                    296     356           356
24-JUN-20 24-JUN-20 COMPLETE  large pool                                                       SHRINK        DEFERRED  large_pool_size                   72      12            12
24-JUN-20 24-JUN-20 COMPLETE  shared pool                                                      GROW          DEFERRED  shared_pool_size                 136     140           140
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        DEFERRED  db_cache_size                    356     352           352
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        DEFERRED  db_cache_size                    352     348           348
24-JUN-20 24-JUN-20 COMPLETE  shared pool                                                      GROW          DEFERRED  shared_pool_size                 140     144           144
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        DEFERRED  db_cache_size                    348     344           344
24-JUN-20 24-JUN-20 COMPLETE  shared pool                                                      GROW          DEFERRED  shared_pool_size                 144     148           148
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        DEFERRED  db_cache_size                    344     340           340
24-JUN-20 24-JUN-20 COMPLETE  shared pool                                                      GROW          DEFERRED  shared_pool_size                 148     152           152
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        DEFERRED  db_cache_size                    340     336           336
24-JUN-20 24-JUN-20 COMPLETE  shared pool                                                      GROW          DEFERRED  shared_pool_size                 152     156           156
24-JUN-20 24-JUN-20 COMPLETE  shared pool                                                      GROW          DEFERRED  shared_pool_size                 156     160           160
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        DEFERRED  db_cache_size                    336     332           332
26-JUN-20 26-JUN-20 COMPLETE  shared pool                                                      GROW          DEFERRED  shared_pool_size                 160     168           168
26-JUN-20 26-JUN-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        DEFERRED  db_cache_size                    332     324           324
02-JUL-20 02-JUL-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        DEFERRED  db_cache_size                    324     316           316
02-JUL-20 02-JUL-20 COMPLETE  shared pool                                                      GROW          DEFERRED  shared_pool_size                 168     176           176
02-JUL-20 02-JUL-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        DEFERRED  db_cache_size                    316     308           308
02-JUL-20 02-JUL-20 COMPLETE  shared pool                                                      GROW          DEFERRED  shared_pool_size                 176     184           184
02-JUL-20 02-JUL-20 COMPLETE  shared pool                                                      GROW          DEFERRED  shared_pool_size                 184     192           192
02-JUL-20 02-JUL-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        DEFERRED  db_cache_size                    308     300           300
02-JUL-20 02-JUL-20 COMPLETE  shared pool                                                      GROW          DEFERRED  shared_pool_size                 192     200           200
02-JUL-20 02-JUL-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        DEFERRED  db_cache_size                    300     292           292
03-JUL-20 03-JUL-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        DEFERRED  db_cache_size                    292     284           284
03-JUL-20 03-JUL-20 COMPLETE  shared pool                                                      GROW          DEFERRED  shared_pool_size                 200     208           208

44 rows selected.

How To Open The Standby Database When The Primary Is Lost in oracle11g ?

How To Open The Standby Database When The Primary Is Lost in oracle11g ?
July 02, 2020


Scenario:- So this scenario shows how to open your standby database in read/write mode when you dont have any access(Lost) on primary database.
Here i'm trying to make a test case to demonstrate the following scenario.Currenly both primary and standby database are in sync.

Oracle version :-  11.2.0.1.0
Primary Database : prim
Standby Database : stand

At primary database:-
SYS @ catdb >select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             26
  
  
At standby database:-
SYS @ catdb >select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             26

So at this situation we totally power off the primary database server to test the scenario.

At primary:-
SYS @ catdb >shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS @ catdb >exit

[root@server1 ~]# poweroff

Now at standby database:-
Open the database in mount stage.
SYS @ catdb >shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL:> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             310380536 bytes
Database Buffers           96468992 bytes
Redo Buffers                4308992 bytes
Database mounted.

SYS @ catdb >SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
MOUNTED              MAXIMUM PERFORMANCE  PHYSICAL STANDBY


Finish the Recovery process in standby database:-
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

Once done, now activate the standby database:-
SYS @ catdb >ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

Database altered.

Check the status:-
SYS @ catdb >SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
MOUNTED              MAXIMUM PERFORMANCE  PRIMARY


Now open the database in read/write mode.
SYS @ catdb >ALTER DATABASE OPEN;

Database altered.

SYS @ catdb >select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SYS @ catdb >SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
READ WRITE           MAXIMUM PERFORMANCE  PRIMARY


So, finally the previous standby database is now a new primary database with read/write mode in open stage.

Script to delete multiple tables using a single query

Script to delete multiple tables using a single query
July 03, 2020

SYS @ catdb > create table xyz (id number);

Table created.

SYS @ catdb > create table t1 as select * from xyz;

Table created.


SYS @ catdb > SELECT * FROM TAB where TNAME IN ('T1','XYZ');

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T1                             TABLE
XYZ                            TABLE


SYS @ catdb >
SYS @ catdb > BEGIN

 FOR i IN (SELECT table_name FROM user_tables WHERE table_name IN ('T1','XYZ'))

 LOOP

      EXECUTE IMMEDIATE 'DROP TABLE ' || i.table_name;

  END LOOP;

END;
/  

PL/SQL procedure successfully completed.

SYS @ catdb > SELECT * FROM TAB where TNAME IN ('T1','XYZ');

no rows selected