albert's blog: SchemaSpy

Showing posts with label SchemaSpy. Show all posts

2011/01/07

Utilize JDeveloper and SchemaSpy to maintain database schema & document

Scenario
1. I need to change database schema frequently, and add comments for each column (it does not have any comments originally).

2. I have to deliver the up-to-date schema document to customer.

Solution

1. Use JDeveloper to maintain database schema and add comments for each column.

2. Use SchemaSpy to generate the up-to-date database schema [Concerning SchemaSpy, please refer to http://albert-myptc.blogspot.com/2010/12/schemaspy-quick-start.html]

Prerequisite

1. Download JDeveloper from http://www.oracle.com/technetwork/developer-tools/jdev/downloads/soft11-098086.html

2. Launch JDeveloper after you installed it

3. JDeveloper workspace

4. Setup database connection

5. Check the connection in Database Navigator

5.Create a new Project

6. Choose "Generic Project", and click OK

7. Click Finish

8. Project had been created

9. Create Database Diagram from project

Before change

I would like to add three columns to NIGT036, and add comments for each column.

Use JDeveloper to change database schema

1. Select specific table --> Column --> Add

2. Assign "Column Name", "Data Type", "Precision", and then click Apply

3. Check the result

4. Add comments for each column

5. Assign Comment and then click Apply

Generate the latest schema document via SchemaSpy

1. execute the command:

java -jar "C:\schemaSpy_5.0.0.jar" -dp "C:\ojdbc6.jar" -t orathin -db dev02 -s ap_tax -host 192.168.30.102 -port 1521 -u ap_tax -p taxtest -schemas AP_TAX -connprops "C:\schemaSpyGUI\properties\orathin.properties" -i "NIG.*" -charset UTF-8 -o "C:\schemaSpyGUI\output" -norows -hq

You can get more information about this command from http://schemaspy.sourceforge.net/

2. check the result

2010/12/24

java.lang.UnsatisfiedLinkError: no ocijdbc11 in java.library.path

Scenario

As I run SchemaSpy and use this command line:

java -jar "C:\schemaSpy_5.0.0.jar" -dp "C:\ojdbc6.jar" -t ora -db dev02 -s ap_tax -host 192.168.30.102 -port 1521 -u ap_tax -p taxtest -schemas AP_TAX -connprops "C:\schemaSpyGUI\properties\ora.properties" -charset UTF-8 -o "C:\schemaSpyGUI\output" -norows -hq

I got this error message:

 Failed to load driver [oracle.jdbc.driver.OracleDriver] from classpath [file:/C:/ojdbc6.jar]  
 Make sure the reported library (.dll/.lib/.so) from the following line can be  
 found by your PATH (or LIB*PATH) environment variable  
 java.lang.UnsatisfiedLinkError: no ocijdbc11 in java.library.path  
 at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1734)  
 at java.lang.Runtime.loadLibrary0(Runtime.java:823)  
 at java.lang.System.loadLibrary(System.java:1028)  
 at oracle.jdbc.driver.T2CConnection$1.run(T2CConnection.java:3535)  
 at java.security.AccessController.doPrivileged(Native Method)  
 at oracle.jdbc.driver.T2CConnection.loadNativeLibrary(T2CConnection.java:3531)  
 at oracle.jdbc.driver.T2CConnection.logon(T2CConnection.java:266)  
 at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:536)  
 at oracle.jdbc.driver.T2CConnection.(T2CConnection.java:162)  
 at oracle.jdbc.driver.T2CDriverExtension.getConnection(T2CDriverExtension.java:53)  
 at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)  
 at net.sourceforge.schemaspy.SchemaAnalyzer.getConnection(SchemaAnalyzer.java:582)  
 at net.sourceforge.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:157)  
 at net.sourceforge.schemaspy.Main.main(Main.java:42)

Root Cause

I assign wrong database type and connection property in command line.

Solution

Change database type from ora to orathin:

java -jar "C:\schemaSpy_5.0.0.jar" -dp "C:\ojdbc6.jar" -t orathin -db dev02 -s ap_tax -host 192.168.30.102 -port 1521 -u ap_tax -p taxtest -schemas AP_TAX -connprops "C:\schemaSpyGUI\properties\orathin.properties" -charset UTF-8 -o "C:\schemaSpyGUI\output" -norows -hq