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

SQL Error: ORA-01940: cannot drop a user that is currently connected
01940. 00000 -  "cannot drop a user that is currently connected"
*Cause:    Attempt was made to drop a user that is currently logged in.
*Action:   Make sure user is logged off, then repeat command.

Issue: When I am trying to drop a user from Oracle database, I am getting the above error though my application is in stopped stated which uses this schema. 
 Basically I am not a DBA to find out SID and SERIAL number and issue command to kill the session.

Solution: Use "Oracle SQL Developer" if you already having it. It is very simple and quick process. I will demonstrate how to achieve this. Just follow below steps.

 We can kill user session with SQL Developer option.

Step 1: Login to SQL Developer with system user account or any other user who are having DBA privileges and try dropping a user.

 

Step 2: Go to Tools -> Monitor Sessions.

Step 3: Select your database connection from the drop down list.

Step 4:It displays all user session which are active.

 Step 5: Select the user session you want to kill and click "Apply" button to kill the session.

 

 Step 6: You will get the below confirmation message.

 

 This demonstration is intended only for testing or demo environments who are learning the technology and not for advanced users and DBA.

Display connection passwords in SQL Developer

Issue:- How to get / display stored passwords of the connections in SQL Developer.

Solution:-  Sometimes you might want to know saved password in SQL Developer connection. Saved password will be displayed like below. But this password can't be directly decrypted.

 Here is the detail information about how to decrypt these passwords without much effort.

 Follow these guidelines to complete this simple tutorial.

Step 1: Download SQL Developer extension from here.

Download SQL Developer extension

Step 2: Unzip and copy the .jar file from it and paste it in 

"\sqldeveloper-.2.20.09.87\sqldeveloper\extensions" path.

Step 3: Close the SQL Developer and reopen it. From the "File" menu you will see new "Show me Database password" option.

Step 4: Click the above option to see all saved passwords as below.

CHEERS :)

Migrating/Transferring SQL Developer connections from one system to another system

Issue: Migrating SQL Developer connections from one system to another system

Solution: 
SQL Developer providing a cool option to export and import connections. 

Try this option for sure. Every one should know this tip.

Step 1: Open your SQL Developer. This will already contains configured database connections as below.

Step 2: Now right click on "Connections", select "Export Connections" as shown below.

Step 3: Provide a desired path to export the connections. Basically connections exported as XML file. Here you have an option to export 

a) all the connections (First Image) or b) Only selected connections (Second Image)

Step 4: Now transfer this XML file to your friend's computer. Use "Import connections" option in SQL Developer as follows.

Step 6: Select previously transferred XML to import the connections. This will lists all the connections available in XML file.

Step 7: Finally click "Ok" button to complete importing connections.

CHEERS :)

SQL Developer to connect to MySQL Database

Tip: Use SQL Developer to connect to MySQL database

Most of the people use Oracle SQL Developer to connect to Oracle database only.

We can use SQL Developer to connect to MySQL and SQL Server databases also.

To connect to different types of databases, we have to use appropriate drivers in SQL Developer.

Here I am showing configuring SQL Developer to connect to MySQL Database

Step 1: Download the driver file from the below URL

http://cdn.mysql.com/Downloads/Connector-J/mysql-connector-java-5.0.8.zip

Step 2: Extract .zip file and get "mysql-connector-java-5.0.8-bin.jar" file.

Step 3: Now open SQL Developer navigate to the below path to add the above jar file.

Tools -> Preferences -> Database(expand it) -> Third Party JDBC Drivers

Step 4: Click "Add entry" button and browse previously extracted .jar file.

Step 5: We are almost done. Now try to create new connection in SQL Developer.

Step 6: Now you will see new tab for MySQL Database in connection creation window.

Similarly we can find other jdbc drivers to connect to other database types. 

CHEERS :)

Creating folders in Oracle SQL Developer

Issue: 

People who works on Oracle development, they create many connections for DEV, TEST, PROD instances using several user ID's. But sometimes, maintaining these many connections little annoying.

Solution: SQL Developer is having one decent feature to maintain many connections. i.e FOLDER.

Here is the way to create folders and arranging the multiple connections.

Step 1: Create folder in the SQL Devloper.

a) Right click on any connection which are created already, then "Add Folder" and select "New Folder".

 b) Give some meaningful name to the folder. I am giving the folder name as DEV to put all Dev related connections.

 c) Once after creating the folder, drag and drop all your Dev related connection to the folder. 

d) Repeat the same steps for other instances.

e) Nice feature right...!!!! Observe the above image, how all connections are arranged to different folders.

CHEERS :)

MSVCR71.dll is missing from your computer

1) Many people faced the below error while starting SQL Developer for the first time.

Error:
--------

The program can't start because MSVCR71.dll is missing from your computer. Try reinstalling the program to fix this problem.

Solution:

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

1) Copy msvcr71.dll file from E:\sqldeveloper-3.2.20.09.87\jdk\jre\bin, and paste it in the main directory.

 i.e E:\sqldeveloper-3.2.20.09.87

2) Now try starting SQLDeveloper.exe from the main directory.

3) You don't see the missing .dll file error again.

CHEERS