how to drop a tablespace

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS AND DATAFILES;

If you don’t specify “AND DATAFILES”, Oracle will not drop the physical datafile after the DROP TABLESPACE command, but you can always delete the files from operating system shell (If the data files are locked, restart the server).
But always perform a backup of the database, as the “DROP TABLESPACE” command is irreversible. It’s also a good practice to check and identify how many datafiles a tablespace actually has before performing the drop action in order to avoid mistake.

SQL> select name from v$database;
NAME
---------
PNDW
SQL>
SQL>
SQL> select STATUS from v$datafile where NAME like '%xxtne%';
STATUS
-------
OFFLINE
OFFLINE
OFFLINE
OFFLINE
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces where tablespace_name in ('XXTNED','XXTNEX');
TABLESPACE_NAME                STATUS
------------------------------ ---------
XXTNED                         OFFLINE
XXTNEX                         OFFLINE

SQL> select file_name from dba_data_files where tablespace_name in ('XXTNED','XXTNEX');
FILE_NAME
--------------------------------------------------------------------------------
/aa_gen_dvd/oradata02/pndw/xxtned01.dbf
/aa_gen_dvd/oradata02/pndw/xxtnex01.dbf
/aa_gen_dvd/oradata02/pndw/xxtned02.dbf
/aa_gen_dvd/oradata02/pndw/xxtnex02.dbf
pndw$bdf /aa_gen_dvd/oradata02/
Filesystem          kbytes    used   avail %used Mounted on
/dev/vgaa_gen_dvd/lvol4
                   131072000 123009269 7581899   94% /aa_gen_dvd/oradata02

SQL> select name from v$database;
NAME
---------
PNDW
SQL> DROP TABLESPACE XXTNED INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL>
SQL>
SQL> DROP TABLESPACE XXTNEX INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL>
SQL> select file_name from dba_data_files where tablespace_name in ('XXTNED','XXTNEX');
no rows selected
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces where tablespace_name in ('XXTNED','XXTNEX');
no rows selected
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
pndw$bdf /aa_gen_dvd/oradata02/
Filesystem          kbytes    used   avail %used Mounted on
/dev/vgaa_gen_dvd/lvol4
                   131072000 102529205 26781960   79% /aa_gen_dvd/oradata02

find the details of concurrent requests from request id

set linesize 900
column RESPONSIBILITY_NAME format a30
column USER_CONCURRENT_PROGRAM_NAME format a30
column MACHINE  format a10
column REQUESTOR format a15

select cmp.USER_CONCURRENT_PROGRAM_NAME ,cm.request_id,
(select user_name from apps.fnd_user where USER_ID =cm.requested_by) Requestor, PARENT_REQUEST_ID,t.USED_UREC,t.USED_UBLK,cm.phase_code,
cm.status_code,s.sid,s.serial#,s.saddr,s.username,s.status, s.OSUSER,s.PROCESS,
s.MODULE,s.ACTION,fr.RESPONSIBILITY_NAME
from v$transaction t,v$session s,apps.FND_CONCURRENT_REQUESTS cm,
apps.FND_RESPONSIBILITY_VL fr,
apps.FND_CONCURRENT_PROGRAMS_VL cmp
where t.addr(+)=s.taddr and s.process(+)=cm.os_process_id
AND fr.RESPONSIBILITY_ID= cm.RESPONSIBILITY_ID
AND cmp.CONCURRENT_PROGRAM_ID=cm.CONCURRENT_PROGRAM_ID
and cm.request_id=&reqid
/

R12

In this post I am going to cover R12 architecture. Discernible change from its predecessors is INST_TOP.

R12 file system has come up with new model - Code, Data, Configurations are segregated nicely to have easy maintenance, to avert NFS mount issues on shared appltier configuration systems. Autoconfig will not write anything in appltop, commontop area in R12. All instance specific configurations, log files are written in INST_TOP area. Instance Home provides the ability to share Applications and technology stack code among multiple instances. I have highlighted the INST_TOP in the box. In the forthcoming posts I will clearly explain the in depth of INST_TOP(As a DBA you will spend much time in INST_TOP area when you comes to application tier node management ( e.g checking logs, starting/stopping services, ssl certificates management, CONTEXT_FILE modification etc.)

Techstack Components:

DB_TIER:
10.2.0.2 RDBMS ORACLE_HOME

APPL-TIER

10.1.2 C ORACLE_HOME / FORMS ORACLE_HOME (8.0.6 ORACLE HOME equivalence)
10.1.3 Java ORACLE_HOME/OC4J ORACLE_HOME (iAS ORACLE_HOME equivalence)

Concurrent Manager

set verify off
set lines 256
set trims ON
set pages 60
col concurrent_queue_id format 99999 heading "QUEUE Id"
col concurrent_queue_name format a20 trunc heading "QUEUE Code"
col user_concurrent_queue_name format a30 trunc heading "Concurrent Queue Name"
col max_processes format 999 heading "Max"
col running_processes format 999 heading "Act"
col running format 999 heading "Run"
col target_node format a15 heading "Node"
col status format a12 trunc heading "Status"
col run format 9999 heading 'Run'
col pend format 9999 heading 'Pending'
col cmgr_program FOR a65;
SELECT 'Instance : '
    ||NAME instance_name
FROM   v$database;

Prompt ===========================
Prompt concurrent manager status
Prompt ===========================
SELECT q.concurrent_queue_id,
      q.concurrent_queue_name,
      q.user_concurrent_queue_name,
      q.target_node,
      q.max_processes,
      q.running_processes,
      running.run                               running,
      pending.pend,
      Decode(q.control_code, 'D', 'Deactivating',
                             'E', 'Deactivated',
                             'N', 'Node unavai',
                             'A', 'Activating',
                             'X', 'Terminated',
                             'T', 'Terminating',
                             'V', 'Verifying',
                             'O', 'Suspending',
                             'P', 'Suspended',
                             'Q', 'Resuming',
                             'R', 'Restarting') status
FROM   (SELECT concurrent_queue_name,
              COUNT(phase_code) run
       FROM   fnd_concurrent_worker_requests
       WHERE  phase_code = 'R'
              AND hold_flag != 'Y'
              AND requested_start_date <= SYSDATE
       GROUP  BY concurrent_queue_name) running,
      (SELECT concurrent_queue_name,
              COUNT(phase_code) pend
       FROM   fnd_concurrent_worker_requests
       WHERE  phase_code = 'P'
              AND hold_flag != 'Y'
              AND requested_start_date <= SYSDATE
       GROUP  BY concurrent_queue_name) pending,
      apps.fnd_concurrent_queues_vl q
WHERE  q.concurrent_queue_name = running.concurrent_queue_name(+)
      AND q.concurrent_queue_name = pending.concurrent_queue_name(+)
      AND q.enabled_flag = 'Y'
ORDER BY Decode(q.application_id, 0, Decode(q.concurrent_queue_id, 1, 1,4, 2)),
         Sign(q.max_processes) DESC,
         q.concurrent_queue_name,
         q.application_id;