select any table privilege

•If you have O7_DICTIONARY_ACCESSIBILITY=TRUE then SELECT ANY TABLE privilege provides access to all SYS and non-SYS objects.

•If you have O7_DICTIONARY_ACCESSIBILITY=FALSE then SELECT ANY TABLE privilege provides access only to non-SYS objects.

•If only SELECT_CATALOG_ROLE is enabled then it provides access to all SYS views only.

•If only SELECT ANY DICTIONARY privilege is enabled then it provides access to SYS schema objects only.

•If both SELECT ANY TABLE and SELECT any DICTIONARY privilege is enabled then it allow access to all SYS and non-SYS objects.

•SELECT ANY DICTIONARY privilege and SELECT_CATALOG_ROLE has no affect over O7_DICTIONARY_ACCESSIBILITY settings.

How to select special character(like _ ) tables in Oracle

Example SQL query :
select table_name from dba_tables where owner='ownername' AND table_name like '%\_%' escape '\'

One more example
where ksppinm like '%\_io\_%' ESCAPE '\';

Oracle Escape Characters
Oracle allows the assignment of special escape characters to tell Oracle that the character is interpreted literally.  Certain characters such as the underscore “_” are not interpreted literally because they have special meaning within Oracle.
So we have to use escape command for selecting the special character tables like _

example for escape command
Example
select table_name from dba_tables where owner='ownername' AND table_name like '%\_%'escape '\'
One more example
where ksppinm like '%\_io\_%' ESCAPE '\';

apply cpu patch

Apply CPU Patch on linux server

cpu patch location on the Server
/opt/oracle/July2010/9655017

 1) take the below commands outputs.

select name from v$database;
select * from registry$history;
select * from v$version;
select * from dba_registry_history;
select count(1) from dba_objects where status like 'I%';
SELECT OBJECT_NAME,OBJECT_TYPE,owner FROM DBA_OBJECTS WHERE STATUS= 'INVALID';
cd $ORACLE_HOME/OPatch
opatch version
opatch lsinventory

2) Shut down the database & stop the listener.

3) check the iventory path and make sure the inventory path should be point correct inventory.

4)use below commands and take the home and inventory backups.
cd /oracle10g/PRDRCD1/product/10.2
tar cvf - .|gzip -c > /oradb/PRDRCD1/oradata1/back_home/home_prdrcd1_`hostname`_`date +%Y%m%d`.tar.gz
cd /oracle10g/PRDRCD1/product/10.2/inventory
tar cvf - .|gzip -c > /oradb/PRDRCD1/oradata1/back_home/oraInvent_prdrcd1_`hostname`_`date +%Y%m%d`.tar.gz
cd /oracle10g/oraInventory
tar cvf - .|gzip -c > /oradb/PRDRCD1/oradata1/back_home/oracle10g_oraInventory_`hostname`_`date +%Y%m%d`.tar.gz

5)take the OPatch backup.
cd $ORACLCE_HOME
cp OPatch OPatch_bak

6) unzip the p6880880_102000_SOLARIS64.zip under ORACLE_HOME

7) go to CPU patch directory and apply the CPU patch

cd /opt/oracle/July2010/9655017
export PATH=$PATH:/usr/ccs/bin
export PATH=$ORACLE_HOME/OPatch:$PATH:.
opatch version
opatch napply -skip_subset -skip_duplicate

8) Run catbunle.sql
cd $ORACLE_HOME/rdbms/admin
sqlplus /'as sysdba'
startup
@catbundle.sql cpu apply

9) run the utlrp.sql

10) excute the below statement

SELECT * FROM registry$history where ID = '6452863';

if that statement returns no rows please execute below commands
cd $ORACLE_HOME/cpu/view_recompile
sqlplus /'as sysdba'
@recompile_precheck_jan2008cpu.sql
sql>shut immediate
startup upgrade
@view_recompile_jan2008cpu.sql
shut immediate
startup
@utlrp.sql

11) perform 1st step for taking patch information.

RAC Architecture

RAC Architecture

Oracle Real Application clusters allows multiple instances to access a single database, the instances will be running on multiple nodes. In an standard Oracle configuration a database can only be mounted by one instance but in a RAC environment many instances can access a single database.

Oracle's RAC is heavy dependent on a efficient, high reliable high speed private network called the interconnect, make sure when designing a RAC system that you get the best that you can afford.

The table below describes the difference of a standard oracle database (single instance) an a RAC environment

Component	Single Instance Environment	RAC Environment
SGA	Instance has its own SGA	Each instance has its own SGA
Background processes	Instance has its own set of background processes	Each instance has its own set of background processes
Datafiles	Accessed by only one instance	Shared by all instances (shared storage)
Control Files	Accessed by only one instance	Shared by all instances (shared storage)
Online Redo Logfile	Dedicated for write/read to only one instance	Only one instance can write but other instances can read during recovery and archiving. If an instance is shutdown, log switches by other instances can force the idle instance redo logs to be archived
Archived Redo Logfile	Dedicated to the instance	Private to the instance but other instances will need access to all required archive logs during media recovery
Flash Recovery Log	Accessed by only one instance	Shared by all instances (shared storage)
Alert Log and Trace Files	Dedicated to the instance	Private to each instance, other instances never read or write to those files.
ORACLE_HOME	Multiple instances on the same server accessing different databases ca use the same executable files	Same as single instance plus can be placed on shared file system allowing a common ORACLE_HOME for all instances in a RAC environment.

RAC Components

The major components of a Oracle RAC system are

• Shared disk system
• Oracle Clusterware
• Cluster Interconnects
• Oracle Kernel Components

The below diagram describes the basic architecture of the Oracle RAC environment

Here are a list of processes running on a freshly installed RAC

Disk architecture

With today's SAN and NAS disk storage systems, sharing storage is fairly easy and is required for a RAC environment, you can use the below storage setups

• SAN (Storage Area Networks) - generally using fibre to connect to the SAN
• NAS ( Network Attached Storage) - generally using a network to connect to the NAS using either NFS, ISCSI
• JBOD - direct attached storage, the old traditional way and still used by many companies as a cheap option

All of the above solutions can offer multi-pathing to reduce SPOFs within the RAC environment, there is no reason not to configure multi-pathing as the cost is cheap when adding additional paths to the disk because most of the expense is paid when out when configuring the first path, so an additional controller card and network/fibre cables is all that is need.

The last thing to think about is how to setup the underlining disk structure this is known as a raid level, there are about 12 different raid levels that I know off, here are the most common ones

raid 0 (Striping)	A number of disks are concatenated together to give the appearance of one very large disk. Advantages Improved performance Can Create very large Volumes Disadvantages Not highly available (if one disk fails, the volume fails)
raid 1 (Mirroring)	A single disk is mirrored by another disk, if one disk fails the system is unaffected as it can use its mirror. Advantages Improved performance Highly Available (if one disk fails the mirror takes over) Disadvantages Expensive (requires double the number of disks)
raid 5	Raid stands for Redundant Array of Inexpensive Disks, the disks are striped with parity across 3 or more disks, the parity is used in the event that one of the disks fails, the data on the failed disk is reconstructed by using the parity bit. Advantages Improved performance (read only) Not expensive Disadvantages Slow write operations (caused by having to create the parity bit)

There are many other raid levels that can be used with a particular hardware environment for example EMC storage uses the RAID-S, HP storage uses Auto RAID, so check with the manufacture for the best solution that will provide you with the best performance and resilience.

Once you have you storage attached to the servers, you have three choices on how to setup the disks

• Raw Volumes - normally used for performance benefits, however they are hard to manage and backup
• Cluster FileSystem - used to hold all the Oracle datafiles can be used by windows and linux, its not used widely
• Automatic Storage Management (ASM) - Oracle choice of storage management, its a portable, dedicated and optimized cluster filesystem

I will only be discussing ASM, which i have already have a topic on called Automatic Storage Management.

Oracle Clusterware

Oracle Clusterware software is designed to run Oracle in a cluster mode, it can support you to 64 nodes, it can even be used with a vendor cluster like Sun Cluster.

The Clusterware software allows nodes to communicate with each other and forms the cluster that makes the nodes work as a single logical server. The software is run by the Cluster Ready Services (CRS) using the Oracle Cluster Registry (OCR) that records and maintains the cluster and node membership information and the voting disk which acts as a tiebreaker during communication failures. Consistent heartbeat information travels across the interconnect to the voting disk when the cluster is running.

The CRS has four components

• OPROCd - Process Monitor Daemon
• CRSd - CRS daemon, the failure of this daemon results in a node being reboot to avoid data corruption
• OCSSd - Oracle Cluster Synchronization Service Daemon (updates the registry)
• EVMd - Event Volume Manager Daemon

The OPROCd daemon provides the I/O fencing for the Oracle cluster, it uses the hangcheck timer or watchdog timer for the cluster integrity. It is locked into memory and runs as a realtime processes, failure of this daemon results in the node being rebooted. Fencing is used to protect the data, if a node were to have problems fencing presumes the worst and protects the data thus restarts the node in question, its better to be save than sorry.

The CRSd process manages resources such as starting and stopping the services and failover of the application resources, it also spawns separate processes to manage application resources. CRS manages the OCR and stores the current know state of the cluster, it requires a public, private and VIP interface in order to run. OCSSd provides synchronization services among nodes, it provides access to the node membership and enables basic cluster services, including cluster group services and locking, failure of this daemon causes the node to be rebooted to avoid split-brain situations.

The below functions are covered by the OCSSd

• CSS provides basic Group Services Support, it is a distributed group membership system that allows applications to coordinate activities to archive a common result.
• Group services use vendor clusterware group services when it is available.
• Lock services provide the basic cluster-wide serialization locking functions, it uses the First In, First Out (FIFO) mechanism to manage locking
• Node services uses OCR to store data and updates the information during reconfiguration, it also manages the OCR data which is static otherwise.

The last component is the Event Management Logger, which runs the EVMd process. The daemon spawns a processes called evmlogger and generates the events when things happen. The evmlogger spawns new children processes on demand and scans the callout directory to invoke callouts. Death of the EVMd daemon will not halt the instance and will be restarted.

Quick recap

CRS Process	Functionality	Failure of the Process	Run AS
OPROCd - Process Monitor	provides basic cluster integrity services	Node Restart	root
EVMd - Event Management	spawns a child process event logger and generates callouts	Daemon automatically restarted, no node restart	oracle
OCSSd - Cluster Synchronization Services	basic node membership, group services, basic locking	Node Restart	oracle
CRSd - Cluster Ready Services	resource monitoring, failover and node recovery	Daemon restarted automatically, no node restart	root

The cluster-ready services (CRS) is a new component in 10g RAC, its is installed in a separate home directory called ORACLE_CRS_HOME. It is a mandatory component but can be used with a third party cluster (Veritas, Sun Cluster), by default it manages the node membership functionality along with managing regular RAC-related resources and services

RAC uses a membership scheme, thus any node wanting to join the cluster as to become a member. RAC can evict any member that it seems as a problem, its primary concern is protecting the data. You can add and remove nodes from the cluster and the membership increases or decrease, when network problems occur membership becomes the deciding factor on which part stays as the cluster and what nodes get evicted, the use of a voting disk is used which I will talk about later.

The resource management framework manage the resources to the cluster (disks, volumes), thus you can have only have one resource management framework per resource. Multiple frameworks are not supported as it can lead to undesirable affects.

The Oracle Cluster Ready Services (CRS) uses the registry to keep the cluster configuration, it should reside on a shared storage and accessible to all nodes within the cluster. This shared storage is known as the Oracle Cluster Registry (OCR) and its a major part of the cluster, it is automatically backed up (every 4 hours) the daemons plus you can manually back it up. The OCSSd uses the OCR extensively and writes the changes to the registry

The OCR keeps details of all resources and services, it stores name and value pairs of information such as resources that are used to manage the resource equivalents by the CRS stack. Resources with the CRS stack are components that are managed by CRS and have the information on the good/bad state and the callout scripts. The OCR is also used to supply bootstrap information ports, nodes, etc, it is a binary file.

The OCR is loaded as cache on each node, each node will update the cache then only one node is allowed to write the cache to the OCR file, the node is called the master. The Enterprise manager also uses the OCR cache, it should be at least 100MB in size. The CRS daemon will update the OCR about status of the nodes in the cluster during reconfigurations and failures.

The voting disk (or quorum disk) is shared by all nodes within the cluster, information about the cluster is constantly being written to the disk, this is know as the heartbeat. If for any reason a node cannot access the voting disk it is immediately evicted from the cluster, this protects the cluster from split-brains (the Instance Membership Recovery algorithm IMR is used to detect and resolve split-brains) as the voting disk decides what part is the really cluster. The voting disk manages the cluster membership and arbitrates the cluster ownership during communication failures between nodes. Voting is often confused with quorum the are similar but distinct, below details what each means

Voting	A vote is usually a formal expression of opinion or will in response to a proposed decision
Quorum	is defined as the number, usually a majority of members of a body, that, when assembled is legally competent to transact business

The only vote that counts is the quorum member vote, the quorum member vote defines the cluster. If a node or group of nodes cannot archive a quorum, they should not start any services because they risk conflicting with an established quorum.

The voting disk has to reside on shared storage, it is a a small file (20MB) that can be accessed by all nodes in the cluster. In Oracle 10g R1 you can have only one voting disk, but in R2 you can have upto 32 voting disks allowing you to eliminate any SPOF's.

The original Virtual IP in Oracle was Transparent Application Failover (TAF), this had limitations, this has now been replaced with cluster VIPs. The cluster VIPs will failover to working nodes if a node should fail, these public IPs are configured in DNS so that users can access them. The cluster VIPs are different from the cluster interconnect IP address and are only used to access the database.

The cluster interconnect is used to synchronize the resources of the RAC cluster, and also used to transfer some data from one instance to another. This interconnect should be private, highly available and fast with low latency, ideally they should be on a minimum private 1GB network. What ever hardware you are using the NIC should use multi-pathing (Linux - bonding, Solaris - IPMP). You can use crossover cables in a QA/DEV environment but it is not supported in a production environment, also crossover cables limit you to a two node cluster.

Oracle Kernel Components

The kernel components relate to the background processes, buffer cache and shared pool and managing the resources without conflicts and corruptions requires special handling.

In RAC as more than one instance is accessing the resource, the instances require better coordination at the resource management level. Each node will have its own set of buffers but will be able to request and receive data blocks currently held in another instance's cache. The management of data sharing and exchange is done by the Global Cache Services (GCS).

All the resources in the cluster group form a central repository called the Global Resource Directory (GRD), which is distributed. Each instance masters some set of resources and together all instances form the GRD. The resources are equally distributed among the nodes based on their weight. The GRD is managed by two services called Global Caches Services (GCS) and Global Enqueue Services (GES), together they form and manage the GRD. When a node leaves the cluster, the GRD portion of that instance needs to be redistributed to the surviving nodes, a similar action is performed when a new node joins.

RAC Background Processes

Each node has its own background processes and memory structures, there are additional processes than the norm to manage the shared resources, theses additional processes maintain cache coherency across the nodes.

Cache coherency is the technique of keeping multiple copies of a buffer consistent between different Oracle instances on different nodes. Global cache management ensures that access to a master copy of a data block in one buffer cache is coordinated with the copy of the block in another buffer cache.

The sequence of a operation would go as below

1. When instance A needs a block of data to modify, it reads the bock from disk, before reading it must inform the GCS (DLM). GCS keeps track of the lock status of the data block by keeping an exclusive lock on it on behalf of instance A
2. Now instance B wants to modify that same data block, it to must inform GCS, GCS will then request instance A to release the lock, thus GCS ensures that instance B gets the latest version of the data block (including instance A modifications) and then exclusively locks it on instance B behalf.
3. At any one point in time, only one instance has the current copy of the block, thus keeping the integrity of the block.

GCS maintains data coherency and coordination by keeping track of all lock status of each block that can be read/written to by any nodes in the RAC. GCS is an in memory database that contains information about current locks on blocks and instances waiting to acquire locks. This is known as Parallel Cache Management (PCM). The Global Resource Manager (GRM) helps to coordinate and communicate the lock requests from Oracle processes between instances in the RAC. Each instance has a buffer cache in its SGA, to ensure that each RAC instance obtains the block that it needs to satisfy a query or transaction. RAC uses two processes the GCS and GES which maintain records of lock status of each data file and each cached block using a GRD.

So what is a resource, it is an identifiable entity, it basically has a name or a reference, it can be a area in memory, a disk file or an abstract entity. A resource can be owned or locked in various states (exclusive or shared). Any shared resource is lockable and if it is not shared no access conflict will occur.

A global resource is a resource that is visible to all the nodes within the cluster. Data buffer cache blocks are the most obvious and most heavily global resource, transaction enqueue's and database data structures are other examples. GCS handle data buffer cache blocks and GES handle all the non-data block resources.

All caches in the SGA are either global or local, dictionary and buffer caches are global, large and java pool buffer caches are local. Cache fusion is used to read the data buffer cache from another instance instead of getting the block from disk, thus cache fusion moves current copies of data blocks between instances (hence why you need a fast private network), GCS manages the block transfers between the instances.

Finally we get to the processes

Oracle RAC Daemons and Processes
LMSn	Lock Manager Server process - GCS	this is the cache fusion part and the most active process, it handles the consistent copies of blocks that are transferred between instances. It receives requests from LMD to perform lock requests. I rolls back any uncommitted transactions. There can be up to ten LMS processes running and can be started dynamically if demand requires it. they manage lock manager service requests for GCS resources and send them to a service queue to be handled by the LMSn process. It also handles global deadlock detection and monitors for lock conversion timeouts. as a performance gain you can increase this process priority to make sure CPU starvation does not occur you can see the statistics of this daemon by looking at the view X$KJMSDP
LMON	Lock Monitor Process - GES	this process manages the GES, it maintains consistency of GCS memory structure in case of process death. It is also responsible for cluster reconfiguration and locks reconfiguration (node joining or leaving), it checks for instance deaths and listens for local messaging. A detailed log file is created that tracks any reconfigurations that have happened.
LMD	Lock Manager Daemon - GES	this manages the enqueue manager service requests for the GCS. It also handles deadlock detention and remote resource requests from other instances. you can see the statistics of this daemon by looking at the view X$KJMDDP
LCK0	Lock Process - GES	manages instance resource requests and cross-instance call operations for shared resources. It builds a list of invalid lock elements and validates lock elements during recovery.
DIAG	Diagnostic Daemon	This is a lightweight process, it uses the DIAG framework to monitor the health of the cluster. It captures information for later diagnosis in the event of failures. It will perform any necessary recovery if an operational hang is detected.

---

RAC Interview qutions

What are Oracle Clusterware processes for 10g on Unix and Linux

Cluster Synchronization Services (ocssd) — Manages cluster node membership and runs as the oracle user; failure of this process results in cluster restart.

Cluster Ready Services (crsd) — The crs process manages cluster resources (which could be a database, an instance, a service, a Listener, a virtual IP (VIP) address, an application process, and so on) based on the resource's configuration information that is stored in the OCR. This includes start, stop, monitor and failover operations. This process runs as the root user

Event manager daemon (evmd) —A background process that publishes events that crs creates.

Process Monitor Daemon (OPROCD) —This process monitor the cluster and provide I/O fencing. OPROCD performs its check, stops running, and if the wake up is beyond the expected time, then OPROCD resets the processor and reboots the node. An OPROCD failure results in Oracle Clusterware restarting the node. OPROCD uses the hangcheck timer on Linux platforms.

RACG (racgmain, racgimon) —Extends clusterware to support Oracle-specific requirements and complex resources. Runs server callout scripts when FAN events occur.

What are Oracle database background processes specific to RAC

•LMS—Global Cache Service Process

•LMD—Global Enqueue Service Daemon

•LMON—Global Enqueue Service Monitor

•LCK0—Instance Enqueue Process

To ensure that each Oracle RAC database instance obtains the block that it needs to satisfy a query or transaction, Oracle RAC instances use two processes, the Global Cache Service (GCS) and the Global Enqueue Service (GES). The GCS and GES maintain records of the statuses of each data file and each cached block using a Global Resource Directory (GRD). The GRD contents are distributed across all of the active instances.

What are Oracle Clusterware Components

Voting Disk — Oracle RAC uses the voting disk to manage cluster membership by way of a health check and arbitrates cluster ownership among the instances in case of network failures. The voting disk must reside on shared disk.

Oracle Cluster Registry (OCR) — Maintains cluster configuration information as well as configuration information about any cluster database within the cluster. The OCR must reside on shared disk that is accessible by all of the nodes in your cluster

How do you troubleshoot node reboot

Please check metalink ...

Note 265769.1 Troubleshooting CRS Reboots
Note.559365.1 Using Diagwait as a diagnostic to get more information for diagnosing Oracle Clusterware Node evictions.

How do you backup the OCR

There is an automatic backup mechanism for OCR. The default location is : $ORA_CRS_HOME\cdata\"clustername"\

To display backups :
#ocrconfig -showbackup
To restore a backup :
#ocrconfig -restore

With Oracle RAC 10g Release 2 or later, you can also use the export command:
#ocrconfig -export -s online, and use -import option to restore the contents back.
With Oracle RAC 11g Release 1, you can do a manaual backup of the OCR with the command:
# ocrconfig -manualbackup

How do you backup voting disk

#dd if=voting_disk_name of=backup_file_name

How do I identify the voting disk location

#crsctl query css votedisk

How do I identify the OCR file location

check /var/opt/oracle/ocr.loc or /etc/ocr.loc ( depends upon platform)
or
#ocrcheck

Is ssh required for normal Oracle RAC operation ?

"ssh" are not required for normal Oracle RAC operation. However "ssh" should be enabled for Oracle RAC and patchset installation.

What is SCAN?

Single Client Access Name (SCAN) is s a new Oracle Real Application Clusters (RAC) 11g Release 2 feature that provides a single name for clients to access an Oracle Database running in a cluster. The benefit is clients using SCAN do not need to change if you add or remove nodes in the cluster.

Click here for more details from Oracle

What is the purpose of Private Interconnect ?

Clusterware uses the private interconnect for cluster synchronization (network heartbeat) and daemon communication between the the clustered nodes. This communication is based on the TCP protocol.
RAC uses the interconnect for cache fusion (UDP) and inter-process communication (TCP). Cache Fusion is the remote memory mapping of Oracle buffers, shared between the caches of participating nodes in the cluster.

Why do we have a Virtual IP (VIP) in Oracle RAC?

Without using VIPs or FAN, clients connected to a node that died will often wait for a TCP timeout period (which can be up to 10 min) before getting an error. As a result, you don't really have a good HA solution without using VIPs.
When a node fails, the VIP associated with it is automatically failed over to some other node and new node re-arps the world indicating a new MAC address for the IP. Subsequent packets sent to the VIP go to the new node, which will send error RST packets back to the clients. This results in the clients getting errors immediately.

What do you do if you see GC CR BLOCK LOST in top 5 Timed Events in AWR Report?

This is most likely due to a fault in interconnect network.
Check netstat -s
if you see "fragments dropped" or "packet reassemblies failed" , Work with your system administrator find the fault with network.

How many nodes are supported in a RAC Database?

10g Release 2, support 100 nodes in a cluster using Oracle Clusterware, and 100 instances in a RAC database.

Srvctl cannot start instance, I get the following error PRKP-1001 CRS-0215, however sqlplus can start it on both nodes? How do you identify the problem?

Set the environmental variable SRVM_TRACE to true.. And start the instance with srvctl. Now you will get detailed error stack.

what is the purpose of the ONS daemon?

The Oracle Notification Service (ONS) daemon is an daemon started by the CRS clusterware as part of the nodeapps. There is one ons daemon started per clustered node.
The Oracle Notification Service daemon receive a subset of published clusterware events via the local evmd and racgimon clusterware daemons and forward those events to application subscribers and to the local listeners.

This in order to facilitate:

a. the FAN or Fast Application Notification feature or allowing applications to respond to database state changes.
b. the 10gR2 Load Balancing Advisory, the feature that permit load balancing accross different rac nodes dependent of the load on the different nodes. The rdbms MMON is creating an advisory for distribution of work every 30seconds and forward it via racgimon and ONS to listeners and applications.

DBNEWID Utility

Prior to the introduction of the DBNEWID utility alteration of the internal DBID of an instance was impossible and alteration of the DBNAME required the creation of a new controlfile. The DBNEWID utility allows the DBID to be altered for the first time and makes changing the DBNAME simpler. Changing the DBID is necessary when you want to use an RMAN catalog to backup a cloned instance. RMAN identifies instances using the DBID, preventing the original and cloned instance being managed by the same catalog. Alteration of the BID in the cloned instance removes this restriction.
* DBID And DBNAME
* DBNAME Only
* DBID Only

DBID And DBNAME

• Backup the database.
• Mount the database after a clean shutdown:
  

  SHUTDOWN IMMEDIATE
  STARTUP MOUNT

• Invoke the DBNEWID utility (nid) specifying the new DBNAME from the command line using a user with SYSDBA privilege:
  

  nid TARGET=sys/password@TSH1 DBNAME=TSH2

  Assuming the validation is successful the utility prompts for confirmation before performing the actions. Typical output may look something like:
  

  C:\oracle\920\bin>nid TARGET=sys/password@TSH1 DBNAME=TSH2
  DBNEWID: Release 9.2.0.3.0 - Production
  Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.
  
  Connected to database TSH1 (DBID=1024166118)
  
  Control Files in database:
    C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL
    C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL
    C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL
  
  Change database ID and database name TSH1 to TSH2? (Y/[N]) => Y
  
  Proceeding with operation
  Changing database ID from 1024166118 to 1317278975
  Changing database name from TSH1 to TSH2
    Control File C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL - modified
    Control File C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL - modified
    Control File C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL - modified
    Datafile C:\ORACLE\ORADATA\TSH1\SYSTEM01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\UNDOTBS01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\CWMLITE01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\DRSYS01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\EXAMPLE01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\INDX01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\ODM01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\TOOLS01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\USERS01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\XDB01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\TEMP01.DBF - dbid changed, wrote new name
    Control File C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL - dbid changed, wrote new name
    Control File C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL - dbid changed, wrote new name
    Control File C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL - dbid changed, wrote new name
  
  Database name changed to TSH2.
  Modify parameter file and generate a new password file before restarting.
  Database ID for database TSH2 changed to 1317278975.
  All previous backups and archived redo logs for this database are unusable.
  Shut down database and open with RESETLOGS option.
  Succesfully changed database name and ID.
  DBNEWID - Completed succesfully.

• Shutdown the database:
  

  SHUTDOWN IMMEDIATE

• Modify the DB_NAME parameter in the initialization parameter file. The startup will result in an error but proceed anyway.

STARTUP MOUNT
ALTER SYSTEM SET DB_NAME=TSH2 SCOPE=SPFILE;
SHUTDOWN IMMEDIATE

• Create a new password file:
  

  orapwd file=c:\oracle\920\database\pwdTSH2.ora password=password entries=10

• Rename the SPFILE to match the new DBNAME.
• If you are using Windows you must recreate the service so the correct name and parameter file are used:
  

  oradim -delete -sid TSH1
  oradim -new -sid TSH2 -intpwd password -startmode a -pfile c:\oracle\920\database\spfileTSH2.ora

  If you are using UNIX/Linux simply reset the ORACLE_SID environment variable:
  

  ORACLE_SID=TSH2; export ORACLE_SID

• Alter the listener.ora and tnsnames.ora setting to match the new database name and restart the listener:
  

  lsnrctl reload

• Open the database with RESETLOGS:
  

  STARTUP MOUNT
  ALTER DATABASE OPEN RESETLOGS;

• Backup the database.

DBNAME Only

Repeat the process as before except use the following command to start the DBNEWID utility:

nid TARGET=sys/password@TSH2 DBNAME=TSH3 SETNAME=YES

The SETNAME parameter tells the DBNEWID utility to only alter the database name.

When opening the database the RESETLOGS option is not needed so the database can be started using the STARTUP command.

DBID Only

• Backup the database.
• Mount the database after a clean shutdown:
  

  SHUTDOWN IMMEDIATE
  STARTUP MOUNT

• Invoke the DBNEWID utility (nid) from the command line using a user with SYSDBA privilege. Do not specify a new DBNAME:
  

  nid TARGET=sys/password@TSH3

• Shutdown and open the database with RESETLOGS:
  

  SHUTDOWN IMMEDIATE
  STARTUP MOUNT
  ALTER DATABASE OPEN RESETLOGS;

• Backup the database.

Schema Rename in Oracle

I have tested by updating the schema name in user$ table. It was worked, but I didn't tried on production box so i am not sure whether below method is correct or not.

Method 1)

I want to rename schema name from SATISH to SATISH1 so used below command.

SQL> conn / as sysdba

SQL> update user$ set name='SATISH1' where name = 'SATISH';

Method 2)

I trusted always below method is safe.

1. create user new_user...
2. grant ... to new_user;
3. execute dbms_tts.transport_set_check(...);
4. lock old user;
5. exp old user
6. imp  fromuser=old_user touser=newuser
7. [drop user old_user cascade;]

resize redo log files

Now i am going to describe how i have done this. The procedure was learned from a release note of metalink .



1. First see the size of the current logs:

SQL> connect / as sysdba

SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 52428800 INACTIVE
2 52428800 CURRENT
3 52428800 INACTIVE

Logs are 50MB from above which is default in oracle 10g, let's size them to 100MB.


2. Retrieve all the log member names for the groups:

SQL> select group#, member from v$logfile;

GROUP# MEMBER
--------------- ----------------------------------------
1 /usr/oracle/dbs/log1PROD.dbf
2 /usr/oracle/dbs/log2PROD.dbf
3 /usr/oracle/dbs/log3PROD.dbf


3. Now drop the log group 1 and recreate it with increased size

SQL> alter database drop logfile group 1;

SQL> alter database add logfile group 1
'/usr/oracle/dbs/log1PROD.dbf' size 100M reuse;

4. Check the size of the current logs:

SQL> connect / as sysdba

SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 10485760 UNUSED
2 52428800 CURRENT
3 52428800 INACTIVE
5. Do the same for log group 3

SQL> alter database drop logfile group 3;

SQL> alter database add logfile group 3
'/usr/oracle/dbs/log3PROD.dbf' size 100M reuse;


SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 10485760 UNUSED
2 52428800 CURRENT
3 10485760 INACTIVE
6. Now we go for group 2 but it is now used by oracle, so first switch the log

SQL> alter system switch logfile;

SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 10485760 UNUSED
2 52428800 INACTIVE
3 10485760 CURRENT
SQL> alter database drop logfile group 2;


SQL> alter database add logfile group 2
'/usr/oracle/dbs/log2PROD.dbf' size 100M reuse;

7. Check the size of the current logs:

SQL> connect / as sysdba

SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 10485760 UNUSED
2 10485760 UNUSED
3 10485760 CURRENT

** some times you may find that a log group status is ACTIVE, in that case you should
make a database checkpoint like this

SQL> ALTER SYSTEM CHECKPOINT;

** FOR safety reason please take full backup

AWR REPORT

AWR Features

The AWR is used to collect performance statistics including:

• Wait events used to identify performance problems.
• Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
• Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
• Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
• Object usage statistics.
• Resource intensive SQL statements.

The repository is a source of information for several other Oracle 10g features including:

• Automatic Database Diagnostic Monitor
• SQL Tuning Advisor
• Undo Advisor
• Segment Advisor

Snapshots

By default snapshots of the relevant data are taken every hour and retained for 7 days. The default values for these settings can be altered using:

BEGIN
 DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
   retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.
   interval  => 30);          -- Minutes. Current value retained if NULL.
END;
/

The changes to the settings are reflected in the DBA_HIST_WR_CONTROL view. Typically the retention period should capture at least one complete workload cycle. If you system has monthly archive and loads a 1 month retention time would be more beneficial that the default 7 days. An interval of "0" switches off snapshot collection, which in turn stops much of the self-tuning functionality, hence this is not recommended. Automatic collection is only possible if the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. If the value is set to BASIC manual snapshots can be taken, but they will be missing some statistics.

Extra snapshots can be taken and existing snapshots can be removed using:

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

BEGIN
 DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
   low_snap_id  => 22,
   high_snap_id => 32);
END;
/

Snapshot information can be queried from the DBA_HIST_SNAPSHOT view.

Baselines

A baseline is a pair of snapshots that represents a specific period of usage. Once baselines are defined they can be used to compare current performance against similar periods in the past. You may wish to create baseline to represent a period of batch processing like:

BEGIN
 DBMS_WORKLOAD_REPOSITORY.create_baseline (
   start_snap_id => 210,
   end_snap_id   => 220,
   baseline_name => 'batch baseline');
END;
/

The pair of snapshots associated with a baseline are retained until the baseline is explicitly deleted:

BEGIN
 DBMS_WORKLOAD_REPOSITORY.drop_baseline (
   baseline_name => 'batch baseline',
   cascade       => FALSE); -- Deletes associated snapshots if TRUE.
END;
/

Baseline information can be queried from the DBA_HIST_BASELINE view.

Workload Repository Views

The following workload repository views are available:

• V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
• V$METRIC - Displays metric information.
• V$METRICNAME - Displays the metrics associated with each metric group.
• V$METRIC_HISTORY - Displays historical metrics.
• V$METRICGROUP - Displays all metrics groups.
• DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
• DBA_HIST_BASELINE - Displays baseline information.
• DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
• DBA_HIST_SNAPSHOT - Displays snapshot information.
• DBA_HIST_SQL_PLAN - Displays SQL execution plans.
• DBA_HIST_WR_CONTROL - Displays AWR settings.

Workload Repository Reports

Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql

The scripts prompt you to enter the report format (html or text), the start snapshot id, the end snapshot id and the report filename. The resulting report can be opend in a browser or text editor accordingly.

Smore More Example Scripts:
Display a list of snapshots
set lines 100 pages 999
select snap_id
, snap_level
, to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') begin
from dba_hist_snapshot
order by 1
/
Produce a report:
@?/rdbms/admin/awrrpt.sql
To see the snapshot interval and retention period
col snap_interval format a30
col retention format a30
select snap_interval
, retention
from dba_hist_wr_control
/
Change the snapshot interval:
Note. This example changes it to 30 minutes
exec dbms_workload_repository.modify_snapshot_settings (interval => 30)
Change the retention period
Note. This example changes it to two weeks (14 days)
exec dbms_workload_repository.modify_snapshot_settings (retention => 14*24*60)
Manually take a snapshot
exec dbms_workload_repository.create_snapshot
List all baselines
set lines 100
col baseline_name format a40
select baseline_id
, baseline_name
, start_snap_id
, end_snap_id
from dba_hist_baseline
order by 1
/
Create a baseline
exec dbms_workload_repository.create_baseline (, ,'')
Remove a baseline
exec dbms_workload_repository.drop_baseline('')
Enable/Disable automatic snapshotsNote. This job is enabled by default
exec dbms_scheduler.enable('GATHER_STATS_JOB')
and to disable...
exec dbms_scheduler.disable('GATHER_STATS_JOB')
Time model queries
System time model
set lines 100 pages 999
select stat_name
, value
from v$sys_time_model
order by value desc
/
Session time model
set lines 100 pages 999
select stat_name
, value
from v$sess_time_model
where sid = '&sid'
order by value desc
/

Table Fragmentation

How to find table fragmentation?

SQL> select count(*) from big1;

1000000 rows selected.

SQL> delete from big1 where rownum <= 300000;

300000 rows deleted.

SQL> commit;

Commit complete.

SQL> update big1 set object_id = 0 where rownum <=350000;

342226 rows updated.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');

PL/SQL procedure successfully completed.

Table size (with fragmentation)

SQL> select table_name,round((blocks*8),2)||'kb' "size"

2 from user_tables

3 where table_name = 'BIG1';

TABLE_NAME size

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

BIG1 72952kb

Actual data in table:

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"

2 from user_tables

3 where table_name = 'BIG1';

TABLE_NAME size

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

BIG1 30604.2kb

Note = 72952 - 30604 = 42348 Kb is wasted space in table

The difference between two values is 60% and Pctfree 10% (default) - so, the table has 50% extra space which is wasted because there is no data.

How to reset HWM / remove fragemenation?

For that we need to reorganize the fragmented table.

We have four options to reorganize fragmented tables:

1. alter table ... move + rebuild indexes

2. export / truncate / import

3. create table as select ( CTAS)

4. dbms_redefinition

Option: 1 "alter table ... move + rebuild indexes"

SQL> alter table BIG1 move;

Table altered.

SQL> select status,index_name from user_indexes

2 where table_name = 'BIG1';

STATUS INDEX_NAME

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

UNUSABLE BIGIDX

SQL> alter index bigidx rebuild;

Index altered.

SQL> select status,index_name from user_indexes

2 where table_name = 'BIG1';

STATUS INDEX_NAME

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

VALID BIGIDX

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');

PL/SQL procedure successfully completed.

SQL> select table_name,round((blocks*8),2)||'kb' "size"

2 from user_tables

3 where table_name = 'BIG1';

TABLE_NAME size

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

BIG1 38224kb

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"

2 from user_tables

3 where table_name = 'BIG1';

TABLE_NAME size

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

BIG1 30727.37kb

Option: 2 “Create table as select”

SQL> create table big2 as select * from big1;

Table created.

SQL> drop table big1 purge;

Table dropped.

SQL> rename big2 to big1;

Table renamed.

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');

PL/SQL procedure successfully completed.

SQL> select table_name,round((blocks*8),2)||'kb' "size"

2 from user_tables

3 where table_name = 'BIG1';

TABLE_NAME size

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

BIG1 85536kb

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"

2 from user_tables

3 where table_name = 'BIG1';

TABLE_NAME size

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

BIG1 68986.97kb

SQL> select status from user_indexes

2 where table_name = 'BIG1';

no rows selected

SQL> --Note we need to create all indexes.

Option: 3 "export / truncate / import"

SQL> select table_name, round((blocks*8),2)||'kb' "size"

2 from user_tables

3 where table_name = 'BIG1';

TABLE_NAME size

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

BIG1 85536kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"

2 from user_tables

3 where table_name = 'BIG1';

TABLE_NAME size

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

BIG1 42535.54kb

SQL> select status from user_indexes where table_name = 'BIG1';

STATUS

--------

VALID

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Pr

oduction

With the Partitioning, OLAP and Data Mining options

C:\>exp scott/tiger@Orcl file=c:\big1.dmp tables=big1

Export: Release 10.1.0.5.0 - Production on Sat Jul 28 16:30:44 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Produc

tion

With the Partitioning, OLAP and Data Mining options

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table BIG1 468904 rows exported

Export terminated successfully without warnings.

C:\>sqlplus scott/tiger@orcl

SQL*Plus: Release 10.1.0.5.0 - Production on Sat Jul 28 16:31:12 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> truncate table big1;

Table truncated.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Pr

oduction

With the Partitioning, OLAP and Data Mining options

C:\>imp scott/tiger@Orcl file=c:\big1.dmp ignore=y

Import: Release 10.1.0.5.0 - Production on Sat Jul 28 16:31:54 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Produc

tion

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.01.00 via conventional path

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

. importing SCOTT's objects into SCOTT

. . importing table "BIG1" 468904 rows imported

Import terminated successfully without warnings.

C:\>sqlplus scott/tiger@orcl

SQL*Plus: Release 10.1.0.5.0 - Production on Sat Jul 28 16:32:21 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select table_name, round((blocks*8),2)||'kb' "size"

2 from user_tables

3 where table_name = 'BIG1';

TABLE_NAME size

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

BIG1 85536kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"

2 from user_tables

3 where table_name = 'BIG1';

TABLE_NAME size

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

BIG1 42535.54kb

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||'kb' "size"

2 from user_tables

3 where table_name = 'BIG1';

TABLE_NAME size

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

BIG1 51840kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"

2 from user_tables

3 where table_name = 'BIG1';

TABLE_NAME size

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

BIG1 42542.27kb

SQL> select status from user_indexes where table_name = 'BIG1';

STATUS

--------

VALID

SQL> exec dbms_redefinition.can_redef_table('SCOTT','BIG1',-> dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

Option: 4 "dbms_redefinition"

SQL> create table TABLE1 (

2 no number,

3 name varchar2(20) default 'NONE',

4 ddate date default SYSDATE);

Table created.

SQL> alter table table1 add constraint pk_no primary key(no);

Table altered.

SQL> begin

2 for x in 1..100000 loop

3 insert into table1 ( no , name, ddate)

4 values ( x , default, default);

5 end loop;

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> create or replace trigger tri_table1

2 after insert on table1

3 begin

4 null;

5 end;

6 /

Trigger created.

SQL> select count(*) from table1;

COUNT(*)

----------

100000

SQL> delete table1 where rownum <= 50000;

50000 rows deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('SCOTT','TABLE1');

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||'kb' "size"

2 from user_tables

3 where table_name = 'TABLE1';

TABLE_NAME size

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

TABLE1 2960kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"

2 from user_tables

3 where table_name = 'TABLE1';

TABLE_NAME size

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

TABLE1 822.69kb

SQL> --Minimum Privs required "DBA" role or "SELECT" on dbms_redefinition pkg

SQL> --First check table is condidate for redefinition.

SQL>

SQL> exec sys.dbms_redefinition.can_redef_table('SCOTT',-> 'TABLE1',-> sys.dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

SQL> --After verifying that the table can be redefined online, you manually crea

te an empty interim table (in the same schema as the table to be redefined)

SQL>

SQL> create table TABLE2 as select * from table1 WHERE 1 = 2;

Table created.

SQL> exec sys.dbms_redefinition.start_redef_table ( 'SCOTT',-> 'TABLE1',-> 'TABLE2');

PL/SQL procedure successfully completed.

SQL> --This procedure keeps the interim table synchronized with the original tab

le.

SQL>

SQL> exec sys.dbms_redefinition.sync_interim_table ('SCOTT',-> 'TABLE1',-> 'TABLE2');

PL/SQL procedure successfully completed.

SQL> --Create PRIMARY KEY on interim table(TABLE2)

SQL> alter table TABLE2

2 add constraint pk_no1 primary key (no);

Table altered.

SQL> create trigger tri_table2

2 after insert on table2

3 begin

4 null;

5 end;

6 /

Trigger created.

SQL> --Disable foreign key on original table if exists before finish this proces

s.

SQL>

SQL> exec sys.dbms_redefinition.finish_redef_table ( 'SCOTT',-> 'TABLE1',-> 'TABLE2');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('SCOTT','TABLE1');

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||'kb' "size"

2 from user_tables

3 where table_name = 'TABLE1';

TABLE_NAME size

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

TABLE1 1376kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"

2 from user_tables

3 where table_name = 'TABLE1';

TABLE_NAME size

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

TABLE1 841.4kb

SQL> select status,constraint_name

2 from user_constraints

3 where table_name = 'TABLE1';

STATUS CONSTRAINT_NAME

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

ENABLED PK_NO1

SQL> select status ,trigger_name

2 from user_triggers

3 where table_name = 'TABLE1';

STATUS TRIGGER_NAME

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

ENABLED TRI_TABLE2

SQL> drop table TABLE2 PURGE;

Table dropped.