Monday, 12 September 2011

SQL Developer Data Pump Support - Part 2

Welcome to part 2 of this feature on our introduction of data pump functionality into SQL Developer.  Previously, we walked through exporting from the database.  This post will go through the importing the data export to a new schema in our database.
In the DBA navigator, go to the data pump node, and choose 'Data Pump Import Wizard'.

When the wizard appears, choose the type of import you want.   In our previous episode, we exported the 'BARRY' Schema.  We'll now choose to do a schema import.

For the input directories, choose a directory that exists and you have access to.  The dump files from the export session need to reside on this directory and conform to the filename as specified.
Hitting next, will parse the files and step 2 shows us the available schema to choose from.  Since we only export 'BARRY', we only have one to choose from in this case.
Step 3 involves remapping.  In this case, on my database, the Barry schema exists.  I need to create another schema to create these objects in.  I've done this already before we started the wizard.
Once the schema has been created and we're back in the wizard, we can set the destination as 'BARRY2'



Step 4 has two parts, one is for logging and we choose an appropriate database directory for that which exists and we have access to.  The second is for the actions on tables if they exist.  In my case, I want to replace them.  For this example, we know its a fresh schema with nothing in it, but if it did, we'd be replacing the tables.



Lastly, we can schedule the import, and like last time, I want to do this immediately.



The summary shows us what will be done on our behalf and once we hit the finish button, an import job will be created and kicked off immediately.



When the job starts, there will be an import job shown in the dba navigator.  its corresponding editor will show the job executing.



Finally, we can create a connection for the BARRY2 schema and look at the data.





 So, that's it in a nutshell.  Over two parts, we've shown you how to export any part of a database to file using the data pump utilities which have been integrated into Oracle SQL Developer.  In this part, we took those files from the previous post and imported them into Oracle.







SQL Developer Data Pump Support - Part 1

In 3.1 we're adding support for Data Pump, which has replaced exp and imp.  In SQL Developer, we've added this in the DBA navigator which has support for several new things this release.  In this post, I'm only going through Data Pump export and in part 2 of this post, we'll visit the import.  We'll  revisit several of other new DBA features in the very near future.  For export, the demonstration will show that this is pretty easy to use, however, get a cup of coffee, cos we've a few snapshots to go through! 

Ok, so first off, we need to fire up the DBA Navigator which can be reached from the view menu.  Select DBA and you get the navigator shown on the right. This image shows the sys user which has been selected as the current connection.  We're going to use the barry connection for using data pump today.





From the data pump node, select the menu item for the Data Pump Export Wizard.  


On the first page of this wizard, we are choosing schema today.  You also have the option of exporting the entire database, some tablespaces or a block of tables.  When you're ready press next





As we said earlier, we are only choosing schema today and in the image below, we are only selecting one schema for export. The 'BARRY' schema.

Step 3 allows you to filter what is exported by including or excluding various things, you can choose none of these things or, a  selection of each with an appropriate filter string in the value field.
We won't add any today and let it take everything out of the schema.


In Step 4, the data tab, we can add data filters to all the tables, or individual tables as required.  Again for this demonstration, I'm not going to choose any of that and let the data pump export everything.



Step 5 allows us to specify the options for the export.  Our primary interest is in the directory for the  logging.

Step 6 focuses on our Output directory.  Specify the one you want to use here.  Remember, as in Step 5, this directory must exist, and you must have the privileges to write to it from your user.  Check on the main connections navigator for directories.  By default there is a DATA_PUMP_DIR setup, but make sure that the directory exists.  We've created a directory called 'BARRYS_DPUMP_DIR' for this example.




Step 7 specifies the schedule which will dump the data for you.  We're choosing immediately as our option. You can specify whenever you like for this job to run, repeatedly if required.


Lastly, we have the summary screen, which is split in two parts. The main summary screen shows what actions you are taking and how they will be carried out.  The second panel shows the actual PL/SQL which will be run on your behalf to create the data pump job, and dump the data.
When you click finish, and job submission is successful, you'll get  a new job listed in the DBA navigator under Data Pump export Jobs.






If it doesn't appear, then it's highly likely you'll get this error, primarily due to the directory that you are writing the files or the logs to.  This is easily fixed by going back and making sure the directories you have chosen, exist and that you have access to read from and write to them.


 Lastly, you can go to your database directory and see your exported file, together with the log file.


 Your log file will contain something like this if you're successful. (I've cut a lot out of it as it is long)

Starting "BARRY"."EXPORT_JOB_SQLDEV_327":  
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
.  estimated "BARRY"."MD_ADDITIONAL_PROPERTIES"              3 MB
.  estimated "BARRY"."STAGE_TERADATA_TABLETEXT"          2.062 MB
.  estimated "BARRY"."MD_DERIVATIVES"                        2 MB
.  estimated "BARRY"."MD_FILE_ARTIFACTS"                     2 MB
...
.  estimated "BARRY"."文化大革命"                                 0 KB
Total estimation using BLOCKS method: 17.75 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
.....
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "BARRY"."MD_ADDITIONAL_PROPERTIES"          13.54 KB      59 rows
. . exported "BARRY"."STAGE_TERADATA_TABLETEXT"          1.197 MB      25 rows
. . exported "BARRY"."MD_DERIVATIVES"                    49.60 KB     386 rows
...
. . exported "BARRY"."文化大革命"                                 0 KB       0 rows
Master table "BARRY"."EXPORT_JOB_SQLDEV_327" successfully loaded/unloaded
******************************************************************************
Dump file set for BARRY.EXPORT_JOB_SQLDEV_327 is:
  D:\DEMO\DPUMP\EXPDAT01.DMP
Job "BARRY"."EXPORT_JOB_SQLDEV_327" successfully completed at 15:42:52

So, for today, thats exporting from the Oracle database using the Datapump built into Oracle SQL Developer 3.1 which will be available soon!  We'll have part 2 on importing this dump file next.

Wednesday, 7 September 2011

SQL*Plus Formatting Commands

Since the last post on SQL*Plus commands was completed, we have also added more functionality to the SQL Developer's SQL*Plus support.  
  Specifically, we've added some formatting reports to help the folks who have tons of plus reports running for various things. These features will be a part of the next major release of SQL Developer. Lets have a look and see whats supported.

It might be easier to tell you whats not supported now. Its 'break' and 'sum'. Most other SQLPlus formatting is now supported. So, lets take a look and see how it looks.


These features will be available in the next release of SQL Developer.

In SQL Developer, as you add SQL*plus command, they stay around for the lifetime of your worksheet, just like SQL*Plus unless you get rid of them. To Clear all your column formatting just use

clear columns

Now we've a clean setup and ready to start.  As usual, if you want to see what settings are in force, you can issue the command:
show all
which will give you the usual list of suspects as you have seen before here.  With formatting now, there are a few more.  We'll see those shortly when we do a little bit of formatting.  Using the standard HR schema in XE, a simple query on the employees table
select * from employees where rownum < 5;
gives us this
EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE                JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ------------------------ ---------- ---------- -------------- ---------- -------------
        100 Steven               King                      SKING                     515.123.4567         17-JUN-03                AD_PRES         24000                                      90 
        101 Neena                Kochhar                   NKOCHHAR                  515.123.4568         21-SEP-05                AD_VP           17000                       100            90 
        102 Lex                  De Haan                   LDEHAAN                   515.123.4569         13-JAN-01                AD_VP           17000                       100            90 
        103 Alexander            Hunold                    AHUNOLD                   590.423.4567         03-JAN-06                IT_PROG          9000                       102            60 

 4 rows selected 


Lovely, right?  Of course, but we could do better.  So, lets remove the columns we're not interested in using the column noprint option.


column employee_id noprint
column job_id noprint
column commission_pct noprint
column manager_id noprint
column department_id noprint
select * from employees where rownum < 5;


which now gives us this.


FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE                    SALARY 
-------------------- ------------------------- ------------------------- -------------------- ------------------------ ---------- 
Steven               King                      SKING                     515.123.4567         17-JUN-03                     24000 
Neena                Kochhar                   NKOCHHAR                  515.123.4568         21-SEP-05                     17000 
Lex                  De Haan                   LDEHAAN                   515.123.4569         13-JAN-01                     17000 
Alexander            Hunold                    AHUNOLD                   590.423.4567         03-JAN-06                      9000 

 4 rows selected 


Ok, its looking better, but theres a lot of white space in there.  Lets take that out.


column employee_id noprint
column job_id noprint
column commission_pct noprint
column manager_id noprint
column department_id noprint
column first_name format a10 
column last_name format a7 
column email format a8 
column hire_date format a10 
select * from employees where rownum < 5;


which now looks a lot tidier.


FIRST_NAME LAST_NA EMAIL    PHONE_NUMBER         HIRE_DATE      SALARY 
---------- ------- -------- -------------------- ---------- ---------- 
Steven     King    SKING    515.123.4567         17-JUN-03       24000 
Neena      Kochhar NKOCHHAR 515.123.4568         21-SEP-05       17000 
Lex        De Haan LDEHAAN  515.123.4569         13-JAN-01       17000 
Alexander  Hunold  AHUNOLD  590.423.4567         03-JAN-06        9000 

 4 rows selected 


Now, this fits nicely in to the page and we can also make the separators and titles a little fancier



clear columns
set colsep "|"
column employee_id noprint
column job_id noprint
column commission_pct noprint
column manager_id noprint
column department_id noprint
column first_name format a10 heading "First|Name"
column last_name format a7 heading "Last|Name"
column email format a8 heading "Email"
column hire_date format a10 heading "Hired"
column phone_number format a12 heading "Phone"
column salary format $999,999 heading "Salary"
select * from employees where rownum < 5;



resulting in this



First     |Last   |        |            |          |             
Name      |Name   |Email   |Phone       |Hired     |    Salary 
---------- ------- -------- ------------ ---------- ---------- 
Steven    |King   |SKING   |515.123.4567|17-JUN-03 |   $24,000
Neena     |Kochhar|NKOCHHAR|515.123.4568|21-SEP-05 |   $17,000
Lex       |De Haan|LDEHAAN |515.123.4569|13-JAN-01 |   $17,000
Alexander |Hunold |AHUNOLD |590.423.4567|03-JAN-06 |    $9,000

 4 rows selected 


which spruces it up a bit.  Now, we can add titles top and bottom using standard SQL*Plus syntax too



clear columns
CLEAR screen

set colsep "|"
column employee_id noprint
column job_id noprint
column commission_pct noprint
column manager_id noprint
column department_id noprint
column first_name format a10 heading "First|Name"
column last_name format a7 heading "Last|Name"
column email format a8 heading "Email"
column hire_date format a10 heading "Hired"
column phone_number format a12 heading "Phone"
column salary format $999,999 heading "Salary"
SET linesize 80
SET pagesize 15

ttitle LEFT '09/07/2011' RIGHT 'Page:' SQL.pno SKIP 2 CENTER 'HR REPORT' SKIP
BTITLE COL 35 'CONFIDENTIAL' ON

select * from employees where rownum < 7;



and our report looks like this now.



09/07/2011                                                             Page:   1
                                                                                
                                   HR REPORT                                    
First     | Last  |        |            |          |            
Name      | Name  | Email  | Phone      |  Hired   |   Salary 
---------- ------- -------- ------------ ---------- ----------
Steven    |King   |SKING   |515.123.4567|17-JUN-03 |   $24,000
Neena     |Kochhar|NKOCHHAR|515.123.4568|21-SEP-05 |   $17,000
Lex       |De Haan|LDEHAAN |515.123.4569|13-JAN-01 |   $17,000
Alexander |Hunold |AHUNOLD |590.423.4567|03-JAN-06 |    $9,000
Bruce     |Ernst  |BERNST  |590.423.4568|21-MAY-07 |    $6,000
David     |Austin |DAUSTIN |590.423.4569|25-JUN-05 |    $4,800



                                                                                
                                   CONFIDENTIAL                                 

 6 rows selected 


Pretty nifty, if you've got several SQL*Plus reports around that you need to run on SQL Developer.  Remember, like SQL*Plus, you can check the formatting set at any time by typing 


column
giving
COLUMN  'first_name' ON
FORMAT  a10
HEADING  'First|Name' headsep '|'
JUSTIFY right

COLUMN  'hire_date' ON
FORMAT  a10
HEADING  'Hired' headsep '|'


COLUMN  'phone_number' ON
FORMAT  a12
HEADING  'Phone' headsep '|'


COLUMN  'commission_pct' ON
noprint

COLUMN  'email' ON
FORMAT  a8
HEADING  'Email' headsep '|'

COLUMN  'manager_id' ON
noprint

COLUMN  'department_id' ON
noprint

COLUMN  'last_name' ON
FORMAT  a7
HEADING  'Last|Name' headsep '|'

COLUMN  'salary' ON
FORMAT  $99,999
HEADING  'Salary' headsep '|'


COLUMN  'employee_id' ON
noprint

COLUMN  'job_id' ON
noprint

or
column employee_id
giving

COLUMN  'employee_id' ON
noprint


You can find out more about SQLDeveloper in the usual place, and if there are features you'd like to see in the tool, submit a Feature Request.  If you need more details on SQL*Plus, the latest documentation is here.