Skip navigation

The issue

Schema A contains version v1 of the DDL of an in-house application.  The developers of that application deliver  version v2 and you are tasked to implement on schema A the DDL differences between v1 and v2.

 

What SQLDeveloper can do

 

SQLDeveloper offers a tool, called Database Diff,  to compare several schemas, or objects from several schemas.

P1.jpg

To keep it simple, let’s compare 2 schemas without taking constraints into account:

P2.jpg

Next screen, select the objects you want to be compared:

P3.jpg

In the next screen, you can filter out some objects. Here we’re going to skip it because we want to compare all the objects in both schemas.

 

Next, there’s a summary of what we’ve chosen. Always review it for mistakes!

P4.jpg

When you click on Finish, the following dialog box appears:

P5.jpg

This may run for a long time, and all the while your SQLDeveloper is not available.  But you can click on “Run in Backgound” and use your SQLDeveloper again (you can then monitor the progress of Diff Database in View/Task Progress).

 

When that process finishes, the following “Diff Report” tab appears:

P6.jpg

The objects with a green plus sign are the new objects: those that are in one schema but not in the other.  The objects with an orange warning sign are the objects that are in both schemas but do not have the same definition.

 

If you click on one of those objects, a block appears below with the details of the difference:

P7.jpg

Here the “REPEAT_COUNT” column exists in one schema but not in the other.

 

In the “Script” tab, you will find the necessary DDL to implement the difference:

P8.JPG

And if you click on the “SQL” icon at the top left-hand corner:

p9.jpg

you are given the possibility to create one or several DDL scripts to implement those differences:

P10.jpg

Single File: all the necessary DDL code will be saved to one file of your choice

Separate Files: will create one file per object

Type Files: will create one file for all the indexes, another files for all the tables, another file for all the views etc.

Separate Directories: will create one file per object and sort those files per object type in sub-directories, and create a global script that runs all the others:

--  File created - Wednesday-May-25-2016  

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

@D:\hello\SEQUENCES\SEQ_PROCESS_ID_SEQ.sql

@D:\hello\SEQUENCES\SEQ_ACTIVITY_ID_SEQ.sql

@D:\hello\TABLES\DS001_QZ_SIMPLE_TRIGGERS.sql

@D:\hello\TABLES\DS001_PROCESS_INSTANCE_IN_USE.sql

@D:\hello\TABLES\DS001_ACTIVITY.sql

@D:\hello\INDEXES\IDX_QZ_FT_JG.sql

Clipboard: will copy all the necessary DDL to the clipboard

Worksheet: will copy all the necessary DDL to a new SQLDeveloper worksheet, like this:

P11.jpg

so you can run the DDL straight away from that new worksheet.

 

That was an overview of what Database Diff can do for you.  Please leave comments or questions below.

LIMIT the Duration of your Nightly Batch Job: implement a session maximum duration


The issue at hand

In your production environment, the nightly batch job spilled over the morning activity. How can you limit the duration of your batch job (or any Oracle session for that matter) ? Let's do it with the Oracle Profile CONNECT_TIME feature because it's easy to implement.


The feature we are going to use: CONNECT_TIME in a PROFILE

A profile is an Oracle feature that you ascribe to Oracle users to limit what they can do. It's typically used to manage the fact that an account (ie an Oracle user) will lock after a set number of failed logon attempts.

Here is what a profile can manage:

For passwords:

  • Expire in (days)         
  • Lock (days past expiration)  
  • Number of passwords to keep          
  • Number of days to keep for 
  • Complexity function 
  • No. failed logins       
  • No. days to lock for

For resources:

  • Concurrent Sessions (Per User)        
  • Reads/Session (Blocks)        
  • Reads/Call (Blocks)  
  • Private SGA (KBytes)          
  • Composite Limit (Srvc. Units)
  • Profile CPU/Session (Sec./100)        
  • CPU/Call (Sec./100) 
  • Idle Time (Minutes)
  • Connect Time (Minutes)

DEFPROFILE.PNGlimibatch.JPG

How to setup a PROFILE

To benefit from a profile's features, you must

  1. set the resource_limit parameter to TRUE
  2. create a profile with the desired limitations
  3. ascribe that profile to the relevant Oracle users

 

Example of the implementation of a PROFILE named "limitebatch", with connect_time=180

ALTER SYSTEM SET RESOURCE_LIMIT=TRUE ;  --1

-- DROP PROFILE limitebatch cascade;

show parameter resource_limit ;

CREATE PROFILE limitebatch LIMIT CONNECT_TIME 180 ;  -- 2

alter user utilisateurbatch PROFILE limitebatch;            -- 3

Select username,profile from dba_users where lower(username) ='utilisateurbatch' ;

 

NAME           TYPE    VALUE

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

resource_limit boolean TRUE 

 

Profile LIMITEBATCH créé(e).

 

 

User UTILISATEURBATCH modifié(e).

 

 

USERNAME                       PROFILE                     

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

UTILISATEURBATCH                      LIMITEBATCH                  

UPROPRIETAIRE                      DEFAULT

 

Once this profile has been ascribed to that user, no session from that user will be able to last more than 3H.  If the 3H limit is reached,here is what you will find in the batch log:

 

ERROR at line 1:
ORA-02399:,temps max. de connexion dépassé, vous êtes déconnecté
ORA-06512: à ligne 2272
ORA-02399: temps max. de connexion dépassé, vous êtes déconnecté
Disconnected from Oracle Database 11g Enterprise Edition Release

To disable that feature, do not drop the profile (so as to keep it for future use), but ascribe the DEFAULT profile to your user:

ALTER USER UTILISATEURBATCH PROFILE DEFAULT ; 

SELECT USERNAME,PROFILE FROM DBA_USERS WHERE lower(username) ='utilisateurbatch';

 

What really happens when a session is disconnected because the CONNECT_TIME limit has been reached?

The ORA-02399 error is generated of course, but in concrete terms, according to Oracle documentation

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6010.htm

If a user exceeds the CONNECT_TIME or IDLE_TIME session resource limit, then the database rolls back the current transaction and ends the session. When the user process next issues a call, the database returns an error.