Advanced functions of SQL Developer

SQL Developer is a graphical version of SQL*Plus and it is used by database developers mainly for writing complex sql statements. The article gives a survey of the functionality of SQL Developer and deals with advanced functions by showing how SQL Develper 1.5 migrates a MySQL® database to an Oracle® Database and analyses it with text and graphical reports.

SQL Developer gives the facility to connect to databases and develop SQL or PL/SQL code, execute and debug it. You can create execution plans and view, create, modify or delete database objects. It is possible to connect via JDBC to databases of other manufacturer. With the connection you can read Metadata or execute SQL statements. Furthermore there are among other things the following features:

• Support of some SQL*Plus commands like desc[ribe] or '@' for script execution.
• Comfort functions in the SQL Worksheet like explain plan, auto trace, SQL history, smart completion (showing table names, column names when writing SQL statements), popup description of objects (right mouse click: describe) and save and paste SQL snippets in categories.
• Comparing and copy of schemas.
• Advanced search of objects with wildcards. If you find for example a table or a trigger you can open it with a double click.
• Version control system integration.
• Wizzard support for exporting metadata and data in a script consisting of DDL and DML statements.
• Migration of databases, usage of standard reports and development of customized reports (covered in this article).

Simple migration scenaries in smaller companies are performed by developers themself. The quick migration wizzard can lead to a fast result. Also complex migration szenarios are supported by SQL Developer by creating scripts for the data unload und data load with SQL*Loader. How to install SQL Developer and how to configure a connection is not part of the article and can be read in the SQL Developers User's Guide. SQL Developer is a cost free product which is supported by Oracle Support if you have an Oracle Database support contract. The migration functionality of SQL Developer is a further development of the Oracle Migration Workbench.
If all supported plattforms of the Oracle Migration Workbench are supported by SQL Developer the migration workbench will be desupported - see Oracle Migration Tools Statement of Direction from Mai 2008. - Hence it is worth to get started with SQL Developer migrations. Steps to prepare a migration:

1. Define migration project requirements
2. Estimate effort
3. Estimate impacts on production systems
4. Estimate impacts on applications
5. Plan the migration process

These steps give an overview about the complexity of a migration. Who plans a concrete migration project should read the Oracle SQL Developer User's Guide because this article only describes the migration without any frame conditions. As database is a MySQL Database version 5.0.22 used and the Sakila® sample database version 0.8 as a schema. It comprises 16 tables with data, indexes and triggers. It is a rental shop for films.

Preparation

For preparation the SQL Developer must be configured to access a MySQL database using JDBC. To do that a driver has to be downloaded. In this case the MySQL Connector/J 5.1 from the mysql.com website. In the menu Tools->Preferences choose in the left menu Database-> Third Party JDBC Drivers and add the .jar file of the driver. Finally confirm with ok. Close the Preferences window with ok. Then open the database connection window by clicking in the left menu with the right mouse button on connection and choose new connection. Enter Connection name and username, password. If the driver has been installed succesfully you can see in the lower area of the window a tab with mysql label where you can supply hostname and port of the database. After a succesfull connection test the window can be closed. As a next step you need a metadata repository schema and a schema for the data import. It is necessary to create two new accounts in the Oracle Database 11g:

SQL> grant resource, connect, create view to test identified by test;

Grant succeeded.

SQL>grant resource, connect to user mig_repository identified by mig_repository;

User created.

In SQL Developer create a connection for the repository account and for the import schema (User test). In my case they are named repository and DOAG_Q32008. The repository account has to be stated at 'Migration -> Repository Management -> Create Repository'.

Migration

In SQL Developer you can call the wizzard with the menu entry 'Migration -> Quick Migrate'. In the dialog Steps you supply first the MySQL Connection and then the target schema Connection. As the last Connection the wizzard asks for is the migration repository, see image Summary Quick Migration Wizard. The next step of the wizzard is a test where a privilege warning (Create Role not present) occures. This warning can be ignored because only one schema is going to be migrated. For a migration of more than one schema you can read the missing privileges in the SQL Developer User's Guide. In the next Step the wizzard asks what to migrate. You can choose tables, tables and data or migrate all. I choose tables and data and get to the summary:

Image 1: Summary Quick Migration Wizard

If I push finish the wizzard executes the following steps:

• Online capturing of the source database - The process to read metadata from a source database is referred to as capturing. It can also be startet without wizzard. When using offline capturing a script is compiled to read data from the source database. As result an object capture file (.ocp) is generared which can be read by SQL Developer.
• Create the Converted Model - The converted model is a structure of the target database. It is created from the captured model and comprises tables, indexes, trigger and other objects. The procedures are automatically converted to PL/SQL and only when errors occur the original souce code will be kept. A manuel rework of the code is necessary.
• Generate and Build - In this Step a DDL Script is created from the converted model. It can emulate the source database. Afterwards it is executed.
• Online Data Move - Transfer of the data from the source database to the target database. SQL Developer uses the supplied Connections to read the data and transfer it without any file 'I/O to the Oracle Database. By contrast with offline data move a script for data unload and a SQL*Loader script for data load will be generated. Offline Data move is suitable for large amounts of data.

In best case the migration is now finished. But an error occured while executing the DDL script:

Image 2: Quick Migration Wizzard - Error during build

The script tried to build a domain index with more than one column which ended with an error. With clicking the yes button the wizzard finishes and opens the SQL worksheet with the DDL script. In the sheet I comment the faultily index and the already executed statements out and excuted the rest of the script. No further errors occured. The build step is finished. But the data is not yet transfered to the Oracle Database schema. Because the wizzard ended with an error this step has to be performed manuel. To do this you choose in the left part of the window the last created converted model with the right mouse button and choose move data. With a click on ok the online data movement starts.

Image 3: Moving data

The image moving data shows that more then one thread is used for data transfer. You can also see that an error occured with the table film because 0 of 1000 data rows have been transfered. The error can be analysed by investigating the migration log files. For a column of a table a number datatyp has been expected but a date datatyp has been transfered, see error dialog. Image 4: Error dialog

To fix the problem you have to find the column in the table film, change the datatyp manuel to date und then transfer only the data for the table film again. When considering the database structure it is clear that the RELEASE_YEAR datatyp of the table film should be a date. This can be changed with the edit table dialog which can be startet by clicking with the right mouse button on the table film and then choose edit:

Image 5: Edit table dialog

After changing the datatyp a new converted model is created from the captured model by choosing only the table film, see image 'Creating the Converted Model for the table film'. With a right click on the converted model you can choose move data and only the data from the table film will be transfered. Image 4: Error dialog

Analysing

After migrating the schema it can be analyzed with reporting functions of SQL Developer. There are predefined reports for database objects or states like tables without index on a foreign key. To get an overview of the reports here are some of them introduced:

• All Objects -> All Objects: This report can be launched with the entry of a bind variable und shows either all objects of a schema or all objects of the database.
• All Objects -> Invalid Objects: Shows all invalid objects like procedures which can not be compiled.
• Database Administration -> Locks by User: Gives information about locks in the database also containing user data.
• Database Administration -> Storage -> Locks-> Free Space: Report on tablespace level. Shows free/used space, among others things.
• Database Administration -> Top SQL: Here are some reports ordered by Buffer Gets, CPU Time, Disk Reads or Count of Executions.
• Tables -> Quality Assurance -> Tables with Unindexed Foreign Keys: This report shows all tables which have foreign key without an index on it.
• Migration Report -> Migration Summary: Reports the Captured und Converted Model but not about transfered data. The report must be started with the connection used for the repository.

But there is no report which shows the summary of a schema. This report can be written as an user defined report. To do this click on the left site of the application the reports tab and then at user defined reports with the right mouse button choose add report. Choose 'AnalyseDB' as report name and the following SQL statement to report the schema:

select ut.table_name, ut.num_rows, 
(select count(*) from USER_TAB_COLUMNS utc where ut.table_name = utc.table_name) colAnz,
(select count(*) from user_indexes ui where ut.table_name = ui.table_name) ind,
(select count(*) from user_triggers utr where utr.table_name = ut.table_name) trg,
(select SUM (b.bytes) / 1024 / 1024  sizeMB
FROM user_extents b WHERE ut.table_name = b.segment_name
GROUP BY ut.table_name) sizeMB
from user_tables ut order by sizeMB desc

The report can only show values where the statistics of the database are caculated. In SQL Developer click with the right mouse button on the connection DOAG_Q32008 and choose Gather Schema Statistics. In the following dialog choose estimate percent 5 and click ok. Now the report can start. Two reports should be shown at the same time. First open in the reports tab the report Data Dictionary Reports -> All Objects -> All Objects. In the bind variable dialog enter the migration schema name -> test. The Report can be fixed on the screen and kept open with the pin on the upper left window (see image). Afterwards open the self created Report under User Defined Reports -> AnalyseDB and arrange the windows side by side for a better considuration:

Image 7: Text Reports

In the image you can see in the left report all in the schema test migrated objects. The right report shows for every table the count of rows, columns, indexes and triggers and the used space. Now we have a good overview on the schema but not on the data itself. To see how much rents per month happend you can use chart analyse of the SQL Developer. Click with the right mouse button on Reports -> User Defined Reports and choose Add Report. To create a chart report the SQL statement must have the following format: SELECT -group-, -series-, -value- FROM -table(s)-. For every goup an own chart is created. In the chart the values are displayed and labeled with the name suppied at series position. Because only one year should be analysed the group field is static with a '1' and the year passed as a bind variable. In series is year, month and in value the count of rental choosen. To select the nesessary data the following SQL statment is used in the create report dialog:

select 1, to_char(rental_date, 'yyyy mm') monat,
count(*) anzahl from rental
where to_char(rental_date, 'yyyy') = :0
group by to_char(rental_date, 'yyyy mm') 
order by monat

In the upper dialog name, description and tooltip are set to 'Rental per month'. For style choose chart. In the lower dialog change to the binds tab and click the add bind button. Fill in the dialog Name=0, Prompt='Please supply report year' and default=2005. Now the appearance of the chart can be configured. Choose the details tab and choose Pie_Muli for chart and set 3D Graph and Show Legend to true. Leave the other setting on the default value. With apply the settings are saved and the report prompts for the bind variable. Choose the default and create the report:

Image 8: SQL Developer Chart Report

If you click in the chart on a part of the chart a tooltip appears with the time and value of the data record, see image SQL Developer Chart Report. A new feature of SQL Developer 1.5 is the functionality 'Add reports with gauges'. There are two different types dial (like an analog car speedmeter) and statusmeter like a bar chart. You do not have to create a report to get a graphical chart. It is also possible to use the SQL worksheet. The syntax for a Gauge report is SQLDEV:GAUGE:-min- :-max- :-low- :-high- :' || -value-column-. Where MIN and MAX define the range of values and LOW, HIGH can adjust the colored sectors of the background. In the migrated Sakila Database the following SQL shows how much films are in the categories. Execute this statement in the SQL Worksheet:

select cat.name, 'SQLDEV:GAUGE:0:80:40:60:' || count(*) anzahl
from film_category fc, category cat 
where fc.category_id = cat.category_id
group by cat.name order by count(*) desc;

Image 9: SQL Developer Gauges Report

Conclusion

SQL Developer is a compact tool that helps with lots of features in the development of SQL and PL/SQL statements. Beyond this core functionality the SQL Developer has other advanced functions like schema copy or migration and reporting of databases. Even if in the article smaller rework has to be done it is faster to use SQL Developer for a migration then do everything manuel. The reporting functionality gives good possibilities for standard or user defined text or graphical reports. All complex tasks from migration to the export are supported by wizzards which make the handling easier.


MySQL is a registered trademark of MySQL AB in the United States, the European Union and other countries.
Sakila is a registered trademark of MySQL AB in the United States, the European Union and other countries.

---

This Article applies to:	SQL Developer 1.5
Initial published:	15.08.2008
Last change:	15.08.2008