Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

December 20, 2016

How to Export or Import oracle database

Oracle database is providing some command line tools to import or export any schema , data of a schema or the total oracle database from one to another. These tools come along with the oracle database installation by default.

Exporting:
There are two command tools to export database exp and expdp.

exp command:
Open the console and give the exp command on the command prompt. It will prompt for information like username, password, export filename and path etc as below.

Example of using oracle database command "exp"

And also it will show few options like below. You can export entire database or only users/schemas or only the tables. You can also mention, whether you want to export permission and data inside the tables also.


 After providing all these information, it will start exporting the database. After finishing it you can observe a statement like "Export terminated successfully"


Instead of following the above step by step procedure, we can give all this in a single command which will do the same. Below is the example of the command.

Ex:  Exp userid=mytestings/welcome1@xe file=d:\mydump.dmp



expdp command:
Another tool to export database is expdp command. Which works like exp command but it is more powerful. Below is the example to use this command.

expdp mytestings/welcome1 dumpfile=mydump_1.dmp logfile=mydump.log


Importing:
There are two command tools to import database from the .dmp files are imp and impdp.
These two works like the above export commands.

Below are the syntax and example to use imp command.
Syntax:
imp userid/password@Connect_identifier fromuser=user_name_you_have_data_unloaded_from touser=new_user_name file=Path_to_*.dmp file

Ex: imp testuser/welcome1@xe fromuser=mytestings touser=testuser file=D:\Data\MYDUMP.dmp

The result would look like as below.


Below is the example to use impdp command.

impdp mytestings/welcome1@orcl TABLE_EXISTS_ACTION=REPLACE dumpfile=MYDUMP.dmp

Instead of passing options to the command line, we can create a .par file and give all these properties in that file and pass that file to the command as below. This .par file option is available for both impdp and expdp commands but not imp and exp commands.

impdp sccinternal/welcome1@orcl parfile=impdata.par

Sample .par file look like as below.

schemas=mytestings
logfile=importlog.log
dumpfile=MYDUMP.dmp
TABLE_EXISTS_ACTION=REPLACE




November 18, 2015

java.sql.SQLException: stale connection

If you are getting the exception "java.sql.SQLException: stale connection" then you should check whether the Connection object is closed anywhere in the app and you are trying to access the same object. In this case, the object is closed and no longer available, so you can not do any execution on that object. 

Solution is to create a fresh connection object. 

If the error received in MAF apps, then remove the close statements as you don't need to close the connection in the mobile.

May 14, 2014

Using excel file as DB and read data from Java

We can use an excel file as a data base. Using java we can create connection to excel file and execute sql query as we do with database. Use the below code to use excel file as a DB and read data from it using java.

String stExcelFile=”c:\\temp\\test.xls”;
String stSheetName=”Sheet1”;
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection conExcel =
            DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" +
                                        stExcelFile +
                                        ";DriverID=22;READONLY=false");
        Statement stmtExcel = conExcel.createStatement();
        ResultSet rsExcel =stmtExcel.executeQuery("Select * from [" + stSheetName + "$]");
while (rsExcel.next()) {
System.out.println(“id: ”+rsExcel.getString(“id”)));
System.out.println(“name: ”+rsExcel.getString(“name”)));
}
rsExcel.close();
stmtExcel.close();
conExcel.close();

June 7, 2013

Create new user in Database


If I want to create a new login or user in database, use the below statements.

create user adfuser identified by adfuser;

grant create session, resource, dba to adfuser; 

February 6, 2013

Data Source connection to AppModule


By default AppModule is configured to database through the connection created in JDeveloper. But if we want to change the connection type to “JDBC DataSource” from “JDBC URL”, it expects a Datasource name as an input. But we need to pass the JNDI name to this field instead of data source name.

Inserting Date and Time in database

I want to insert date and time into a field of a Oracle table whose data type is "Date".

To do so, I have used the below command.


to_date('2011/12/12 11:15:00', 'yyyy/mm/dd hh24:mi:ss')

Example:

Table definition: 
create table ATG(local_id varchar2(50) primary key,Last_Sale Date, Last_Sale_Value varchar2(50),Total_12_month_Value  varchar2(50));

In the above table, I want to insert data and time to "Last_Sale field".

Insert data:

insert into ATG values ('9986',to_date('2011/12/12 11:15:00', 'yyyy/mm/dd hh24:mi:ss'),'£319.99','£1,234,96');