Follow below steps from moving lob segment from one tablespace to another. SQL> ;   1*  select table_name,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME from dba_lobs where OWNER=’DBACLASS’ SQL> / TABLE_NAME     COLUMN_NAME  SEGMENT_NAME                       TABLESPACE_NAME ————– ———— ———————————- —————————— FD_BREL_AUDIT PAYLOAD      SYS_LOB0000100201C00011$$          WEBMDATA   SQL> alter table DBACLASS.FD_BREL_AUDIT move lob (PAYLOAD) store as SYS_LOB0000100201C00011$$ ( tablespace USERS); Table altered.   SQL>  select […]

 While dropping a user in oracle database , you may face below error. ORA-01940 Problem: SQL> drop user SCOTT cascade 2 / drop user SCOTT cascade * ERROR at line 1: ORA-01940: cannot drop a user that is currently connected Solution: 1. Find the sessions running from this userid: SQL> SELECT SID,SERIAL#,STATUS from v$session where […]

  Follow below steps for changing the oracle database name using nid utility. We will change the database name from P2PRMD2 to P1D2ST. 1. Mount the database SQL> STARTUP MOUNT ORACLE instance started. Total System Global Area 8754618368 bytes Fixed Size 4646288 bytes Variable Size 3556776560 bytes Database Buffers 5033164800 bytes Redo Buffers 160030720 bytes […]

    If you want to find, when a table was last modified like insert,update ,delete, then use the dictionary table dba_tab_modifications. SCENARIO: 1. Insert into test data: SQL[SCOTT@TDB01]SQL>>]insert into TEST values (10); 1 row created. SQL[SCOTT@TDB01]SQL>>]commit; Commit complete. 2. Check dba_tab_modification: SQL[SYS@TCRMDB01]SQL>>]select INSERTS,UPDATES,DELETES,TRUNCATED,TIMESTAMP from dba_tab_modifications where TABLE_NAME=’TEST’ and TABLE_OWNER=’SCOTT’; no rows selected   As […]

            Follow below steps for enabling archive log mode in oracle RAC. 1. stop the database service. srvctl stop database -d DBACLASS 2. start the database in mount state. srvctl start database -d DBACLASS -o mount 3. enable archive log mode. SQL> alter database archivelog; Database altered. 4. Restart the […]

                  Below are the steps for applying JVM patch in oracle 12c database.   Steps 1 : Download the respective patch from oracle support  and unzip it in the server. unzip 21555660_hxuz.zip cd 21555660 Steps 2 : check the conflict against ORACLE_HOME   cd 21555660 $ORACLE_HOME/OPatch/opatch prereq […]

     ADRCI is an command line oracle utility for managing diagnostic data. We can create incident packages and provide it to oracle support team for analysis. Follow below steps to create incident package. View the incidents: $ adrci ADRCI: Release 12.1.0.2.0 – Production on Tue Dec 15 18:52:47 2015 Copyright (c) 1982, 2014, Oracle […]

If you want to enable trace for a listener, Then follow below steps. Set current listener:   LSNRCTL> set cur LISTENER_TEST Current Listener is LISTENER_TEST Check status LSNRCTL> status Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_TEST))) STATUS of the LISTENER ———————— Alias LISTENER_TEST Version TNSLSNR for HPUX: Version 12.1.0.2.0 – Production Start Date 14-DEC-2015 16:28:54 Uptime 0 days 0 […]

              We can deinstall oracle client by runnning the deinstall tool provided by oracle client software. [oracle@bt-Prov-devst1 client_1]$ cd deinstall/ [oracle@bt-Prov-devst1 deinstall]$ ls -ltr total 92 -rwxr-xr-x. 1 oracle oinstall 32343 Dec 16 2009 sshUserSetup.sh -rw-r–r–. 1 oracle oinstall 409 Aug 18 2010 readme.txt -rw-r–r–. 1 oracle oinstall 3466 […]

Use below script to find the long running queries in your database. select sid,inst_id,opname,totalwork,sofar,start_time,time_remaining from gv$session_longops where totalwork<>sofar / SEE – COMPLETE COLLECTION OF DATABASE SCRIPTS