Oracle Database Error Solutions

ORA-01940: cannot drop a user that is currently connected

SQL>drop user MY_USER cascade;
drop user username cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
SQL>select s.sid, s.serial#, s.status, p.spid from v$session s, v$process p where s.username = ‘MY_USER’ and p.addr (+) = s.paddr;
SID           SERIAL#       STATUS           SPID
———   ———-    ——–       ————
579               27        INACTIVE        17849
279                9        INACTIVE        89543
SQL> alter system kill session ’579,29′;
System alterred.
SQL> alter system kill session ’279,9′;
System alterred.
Now try to drop USER:
SQL>drop user MY_USER cascade;
User dropped.

Delete expired archivelogs using RMAN

Below commands will helpful for deleting expired archivelog files through RMAN :
RMAN>list expired archivelog all;
RMAN>crosscheck archivelog all;
RMAN>delete noprompt expired archivelog all;
Now check the output with below command it should not return any list
RMAN> list expired archivelog all;
using target database control file instead of recovery catalog
specification does not match any archived log in the repository
RMAN>

ORA-12547 TNS: Lost Contact

Getting the following error when trying to connect using Sql*Plus
ERROR:
$sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 – Production on Wed Mar 30 11:59:06 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
ERROR:
ORA-12547: TNS:lost contact
CAUSE:
1) to kernel parameters settings
2) Incorrect permissions on the ORACLE executable
3) Insufficient ulimit setting for stack
4) $ORACLE_HOME/rdbms/lib/config.o is 0 bytes
5) Oracle binaries have not been linked correctly
SOLUTION:
1) Please check the notes below that provide the required settings for kernel parameters
Note 169706.1 Oracle Database on AIX,HP-UX,Linux,MacOSX,Solaris,Tru64
2) Please check permissions by running:
$ cd $ORACLE_HOME/bin
$ ls -l oracle
The output should show the correct permission which is:
-rwsr-s–x 1 oracle dba
If not, then please execute the following to correct the permissions:
$ cd $ORACLE_HOME/bin
$ chmod 6751 oracle
$ ls -l oracle
3) Check the current ulimit setting for stack:
ulimit -a
Check the install guide for your specific platform and version of Oracle and set the stack appropriately.
4) Check to ensure the following two files are not 0 bytes:
$ORACLE_HOME/bin/oracle
$ORACLE_HOME/rdbms/lib/config.o
If yes, rename the following file:
$ cd $ORACLE_HOME/rdbms/lib
$ mv config.o config.o.bad
Then, relink the oracle binary:
$ relink oracle

How to relink oracle binaries?

Sometimes We need to require to relink oracle binaries because of some number of our cases and also We need to do it because of solution given by oracle support.
The following method will relink the oracle executables.
For Unix system
set $ORACLE_HOME,
set $ORACLE_SID
If you are using E-Business Suite login your system as oramgr user than source env file
Go to $ORACLE_HOME/bin  path than run below commands for can relink binaries
$ORACLE_HOME/bin/relink all;
or you can use
$ORACLE_HOME/bin/relink oracle;
Referance:
How to Relink Oracle Database Software on UNIX [ID 131321.1]
If you are on 11gR2 level than I suggest also see below note:
Oracle 11gR2 Relink New Feature [ID 883299.1]
If you are using RAC than I suggest also see below note:
How to Check Whether Oracle Binary/Instance is RAC Enabled and Relink Oracle Binary in RAC [ID 284785.1]


5) If the above does not resolve I suggest that you shutdown the database and listener and then “relink all”
Please reffer the ORACLE SUPPORT DOCUMENT ID 422173.1

Disable Case Sensitive in Oracle Database 11g

Prior to 11g Passwords in Oracle are not case sensitive. Now from 11g Oracle passwords are Case sensitive. Parameter sec_case_sensitive_logon has been included in Oracle 11g.
sql> create user test identified by test;
sql> Grant create session to test;
SQL> show parameter logon
NAME                                       TYPE                              VALUE
—————————-     ———————-   ——————————
sec_case_sensitive_logon     boolean                           TRUE
SQL> conn test/TEST
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn test/test
Connected.
Disable Password case sensitive:
SQL> alter system set sec_case_sensitive_logon=false;
System altered.
SQL> conn test/TEST
Connected.

Find size of Table,Index and USER/SCHEMA in Oracle

How to find size of Table or Index or User/Schema in Oracle Database.
SIZE OF TABLE:
Select segment_name,sum(bytes)/1024/1024/1024 as “SIZE in GB” from user_segments where segment_name=’TABLE_NAME’ group by segment_name;
OR
select owner,segment_name,sum(bytes)/1024/1024/1024 as “SIZE in GB” from dba_segments where owner=’SCHEMA_NAME’ and segment_name=’TABLE_NAME’ group by owner,segment_name;
SIZE OF INDEX:
select segment_name,sum(bytes)/1024/1024/1024 as “SIZE in GB” from user_segments where segment_name=’INDEX_NAME’ group by segment_name;
OR
select owner,segment_name,sum(bytes)/1024/1024/1024 as “SIZE in GB” from dba_segments where owner=’SCHEMA_NAME’ and segment_name=’INDEX_NAME’ group by owner,segment_name
SIZE OF A USER/SCHEMA:
select owner,sum(bytes)/1024/1024/1024 as “SIZE in GB” from dba_segments where owner=’SCHEMA_NAME’ group by owner;

Oracle 11gR2 installation on windows machine

Oracle Database Patch Set 11.2.0.2 (Patch#10098816) for Win32 and Win64 is available in Oracle support for downloading.

After downloading patchset, Unzip the patch which will creates folder “database”
Run the setup executable with “Run as administrator”

Unchecked the box for not to receive any security updates from Oracle Support


Click on Skip software updates radio button

Click on instal database software only, As we are installing only software.

Drop temp tablespace taking long time

Created new temporary tablespace:
CREATE TEMPORARY TABLESPACE TEMP01 TEMPFILE ‘/u01/oracle/oradata/temp02.dbf’ SIZE 2g;
Make new temporary tablespace as default:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP01;
Dropping old temporary tablespace, Which is taking long time to complete:
DROP TABLESPACE TEMP INCLDING CONTENTS AND DATAFILES;
The issue because of temp is being used by oracle sessions
SOLUTION 1:
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP
SQL>SELECT NAME,OPEN_MODE,LOG_MODE FROM V$DATABASE;
SQL>DROP TABLEPSACE TEMP INCLUDIING CONTENTS AND DATAFILES;
SOLUTION 2:
Run below query and see any sessions using temp, Find out the all the sessions that are not active and kill those sessions.
select  tu.username,s.sid,s.serial# from v$tempseg_usage tu, v$session s
where tu.session_addr=s.saddr;
alter system kill session ‘sid,serial’;

DROP DATABASE THROUGH RMAN

In Oracle Database 10gR1 Oracle introduced the RMAN command DROP DATABSE. This one simple statement has the ability to completely remove a database including all RMAN backups with the optional INCLUDING BACKUPS clause.
sqlplus “/ as sysdba”
SQL> shutdown immediate;
SQL> startup mount exclusive
SQL> alter system enable restricted session
SQL> exit
rman target /
RMAN> drop database including backups;

CHECK TABLESPACE SIZE, FREE AND USED SPACE

Below query is used for finding total tablespace size and free, used space.
select tablespace_name, ROUND(bytes_free,2) free,ROUND(bytes_total-bytes_free,2) used, ROUND(bytes_total,2) total,perused
from (select a.tablespace_name, a.bytes_free,b.bytes_total,
ROUND((100-(100*a.bytes_free/b.bytes_total)),2) perused from
(Select tablespace_name,sum(bytes)/1024/1024 bytes_free From dba_free_space Group by tablespace_name) a,
(Select tablespace_name,sum(bytes)/1024/1024 bytes_total From dba_data_files Group by tablespace_name) b
where a.tablespace_name=b.tablespace_name) where perused >= 0 ORDER BY tablespace_name;

ORA-12537: TNS:connection closed

Giving below error when trying to connect oracle user
ERROR:
Sqlplus scott/tiger@PROD
SQL*Plus: Release 10.2.0.1.0 – Production on Thu Oct 16 15:06:46 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
ERROR:
ORA-12537: TNS:connection closed
SOLUTION:
Add the relevant LD_LIBRARY_PATH into the SID_DESC section in listener.ora file
(SID_DESC =
(ORACLE_HOME = /home/oracle/product/1020)
(ENVS=”LD_LIBRARY_PATH=/home/oracle/product/1020/lib:/usr/dt/lib:/usr/openwin/lib”)
(SID_NAME = PROD)
)