Applying a delta in Progress

If you want to apply a delta to a Progress database, that is, a file that contains information to update objects of a Progress database in order to be equal to other Progress database's objects (like to a production database from a development one), then you could do this with mpro utility:

RHPRGPR1:/mnt0/new:> mpro /mydatabase/mydb.db









        @@@@@@   @@@@@@   @@@@@@@   @@@@@   @@@@@@   @@@@@@@   @@@@@    @@@@@
       @     @  @     @  @     @  @     @  @     @  @        @     @  @     @
      @     @  @     @  @     @  @        @     @  @        @        @
     @@@@@@   @@@@@@   @     @  @  @@@@  @@@@@@   @@@@@     @@@@@    @@@@@
    @        @   @    @     @  @     @  @   @    @              @        @
   @        @    @   @     @  @     @  @    @   @        @     @  @     @
  @        @     @  @@@@@@@   @@@@@   @     @  @@@@@@@   @@@@@    @@@@@

                           Progress Software Corporation
                                    14 Oak Park
                            Bedford, Massachusetts 01730
                                    781-280-4000

       PROGRESS is a registered trademark of Progress Software Corporation
                              Copyright 1984-2004
                        by Progress Software Corporation
                              All Rights Reserved

After this screen you will be prompted for an user and password; you have to login with an user with enough privileges:

PROGRESS Version 9.1E as of Tue Oct 12 14:40:19 EDT 2004

                ┌─────────────────── Login ───────────────────┐
                │                                             │
                │ Please enter a User Id and Password for     │
                │ database: mydb                              │
                │                                             │
                │    User Id: DBAUSER                         │
                │   Password:                                 │
                │                                             │
                │              (OK)   (Cancel)                │
                └─────────────────────────────────────────────┘












Enter data or press F4 to end.

Then in the next screen you have to press F3 and select the Tools menu, Data Dictionary option:

PROGRESS Version 9.1E as of Tue Oct 12 14:40:19 EDT 2004
File Edit Search Buffer Compile Tools Help
────────────────────────────────┌──────────────────────┐────────────────────────
                                │ Data Dictionary      │
                                │ OS Shell             │
                                │ Application Compiler │
                                └──────────────────────┘














─ File: Untitled:1 ─────────────────────────────────────────────────────────────


 F1=RUN  F3=MENUS  F5=OPEN  F6=SAVE  F8=CLOSE                            Insert

The next screen will be the Data Dictionary tool:

Database Schema Admin DataServer Utilities PRO/SQL Tools





      ┌──────────── Welcome to the PROGRESS Data Dictionary ─────────────┐
      │                                                                  │
      │ You may use these facilities to set up or alter the structure of │
      │ your database(s) or to perform various administrative functions. │
      │                                                                  │
      └──────────────────────────────────────────────────────────────────┘







 Main Menu                                                     Data Dictionary
Database: mydb (PROGRESS)            Table:

In the Data Dictionary screen you have to select the Admin menu, Load Data and Definitions submenu, Data Definitions (.df file) option:

Database Schema Admin DataServer Utilities PRO/SQL Tools
                ┌────────────────────────────────────────┐
                │ Dump Data and Definitions ->           │
                │ Load Data and Definitions ->           │
                │ ┌─────────────────────────────────┐    │
                │ │ Data Definitions (.df file)...  │    │
      ┌─────────│ │ Table Contents (.d file)...     │    │y ─────────────┐
      │         │ │ SQL Views...                    │... │               │
      │ You may └─│ User Table Contents...          │────┘e structure of │
      │ your datab│ Sequences Current Values...     │strative functions. │
      │           │ Reconstruct Bad Load Records... │                    │
      └───────────└─────────────────────────────────┘────────────────────┘







 Main Menu                                                     Data Dictionary
Database: mydb (PROGRESS)            Table:

In order to apply a delta file the database must not be locked by any user, but if so you will get an error message like this:

Database Schema Admin DataServer Utilities PRO/SQL Tools


















 Data Definitions (.df file)                                   Data Dictionary
Database: mydb (PROGRESS)            Table:


Database in use by SOMEUSER on w1c3xpr3. Wait or press CTRL-C to stop. (388)

If you have to apply this delta and you can safely restart your database or kill the users locking the database then do that, preferably in other terminal; if there are no users locking the database then you will get a screen where you have to select the delta file and a lot of other options that you can keep unchanged.

Finally, after selecting OK, if again there are no users locking the database the delta will be applied very fast and you will go back to the Data Dictionary menu.

Getting a profile’s DDL

If you want to copy a profile and you don’t have a frog, then you could use the DBMS_METADATA package to get the DLL you need to recreate it:

SQL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR',true);

PL/SQL procedure successfully completed.

SQL> SET LINESIZE 150
SQL> SET PAGESIZE 0
SQL> SET LONG 90000
SQL> SELECT DBMS_METADATA.GET_DDL('PROFILE',PROFILE) from dba_profiles
where PROFILE='MYPROFILE' group by PROFILE;

   CREATE PROFILE "MYPROFILE"
    LIMIT
         COMPOSITE_LIMIT DEFAULT
         SESSIONS_PER_USER DEFAULT
         CPU_PER_SESSION DEFAULT
         CPU_PER_CALL DEFAULT
         LOGICAL_READS_PER_SESSION DEFAULT
         LOGICAL_READS_PER_CALL DEFAULT
         IDLE_TIME UNLIMITED
         CONNECT_TIME DEFAULT
         PRIVATE_SGA DEFAULT
         FAILED_LOGIN_ATTEMPTS DEFAULT
         PASSWORD_LIFE_TIME UNLIMITED
         PASSWORD_REUSE_TIME DEFAULT
         PASSWORD_REUSE_MAX DEFAULT
         PASSWORD_VERIFY_FUNCTION "VERIFY_FUNCTION"
         PASSWORD_LOCK_TIME DEFAULT
         PASSWORD_GRACE_TIME DEFAULT ;

As you can see, there is a verify function for password checking purposes so you have to create the VERIFY_FUNCTION function first in order to create this profile. You can get the source code of this function querying the dba_source view:

SQL> select TEXT from dba_source where NAME='VERIFY_FUNCTION' and TYPE='FUNCTION' order by LINE;
FUNCTION verify_function
(username varchar2,
  password varchar2,
  old_password varchar2)
  RETURN boolean IS
   n boolean;
   m integer;

...

       IF differ < 3 THEN
         raise_application_error(-20004, 'Password should differ by at \
         least 3 characters');
       END IF;
     END IF;
   END IF;
   -- Everything is fine; return TRUE ;
   RETURN(TRUE);
END;

113 rows selected.

More information:

57 DBMS_METADATA

Getting a role's DDL

If you want to copy a role and you don't have a frog, then you could use the DBMS_METADATA package to get the DLL you need to recreate it:

SQL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);

PL/SQL procedure successfully completed.

SQL> SET PAGESIZE 0
SQL> SET LONG 90000
SQL> SELECT DBMS_METADATA.GET_DDL('ROLE', role) || ';' FROM dba_roles WHERE ROLE='MYROLE';

   CREATE ROLE "MYROLE";
 ;

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',role) FROM ROLE_ROLE_PRIVS
WHERE ROLE='MYROLE' group by ROLE;

   GRANT "CONNECT" TO "MYROLE";

   GRANT "EXP_FULL_DATABASE" TO "MYROLE";


SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',role) FROM ROLE_SYS_PRIVS
WHERE ROLE='MYROLE' group by ROLE;

no rows selected

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',role) FROM ROLE_TAB_PRIVS
WHERE ROLE='MYROLE' group by ROLE;

no rows selected

As you can see, there is an extra ; after the CREATE ROLE sentence that you can just ignore, and for this role there are no system grants nor object grants.

More information:

Oracle Roles
57 DBMS_METADATA

Getting a user's DDL

If you want to copy an user and you don't have a frog, then you could use the DBMS_METADATA package to get the DLL you need to recreate it:

SQL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);

PL/SQL procedure successfully completed.

SQL> SET PAGESIZE 0
SQL> SET LONG 90000
SQL> SELECT DBMS_METADATA.GET_DDL('USER',username) from dba_users where username='MYUSER';

   CREATE USER "MYUSER" IDENTIFIED BY VALUES '123456B563D86FE2'
      DEFAULT TABLESPACE "MYTABLESPACE"
      TEMPORARY TABLESPACE "TEMP"
      PROFILE "MYPROFILE";


SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',GRANTEE) FROM DBA_ROLE_PRIVS
WHERE GRANTEE='MYUSER' group by GRANTEE;

   GRANT "MYROLE" TO "MYUSER";


SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('DEFAULT_ROLE','MYUSER') from dual;

   ALTER USER "MYUSER" DEFAULT ROLE ALL;


SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',GRANTEE) FROM DBA_SYS_PRIVS
WHERE GRANTEE='MYUSER' group by GRANTEE;

  GRANT CREATE TABLE TO "MYUSER";


SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',GRANTEE) FROM DBA_TAB_PRIVS
WHERE GRANTEE='MXS320A' group by GRANTEE;

no rows selected

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA','MYUSER') from dual;

  DECLARE
  TEMP_COUNT NUMBER;
  SQLSTR VARCHAR2(200);
BEGIN
  SQLSTR := 'ALTER USER "MYUSER" QUOTA UNLIMITED ON "MYTABLESPACE"';
  EXECUTE IMMEDIATE SQLSTR;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -30041 THEN
      SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES
              WHERE TABLESPACE_NAME = ''MYTABLESPACE'' AND CONTENTS = ''TEMPORARY''';
      EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT;
      IF TEMP_COUNT = 1 THEN RETURN;
      ELSE RAISE;
      END IF;
    ELSE
      RAISE;
    END IF;
END;
/

  DECLARE
  TEMP_COUNT NUMBER;
  SQLSTR VARCHAR2(200);
BEGIN
  SQLSTR := 'ALTER USER "MYUSER" QUOTA 10485760 ON "SYSTEM"';
  EXECUTE IMMEDIATE SQLSTR;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -30041 THEN
      SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES
              WHERE TABLESPACE_NAME = ''SYSTEM'' AND CONTENTS = ''TEMPORARY''';
      EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT;
      IF TEMP_COUNT = 1 THEN RETURN;
      ELSE RAISE;
      END IF;
    ELSE
      RAISE;
    END IF;
END;
/

As you can see, you don't get simple ALTER USER QUOTA sentences with the TABLESPACE_QUOTA option so you can execute the entire PL/SQL block or just pick up the ALTER USER QUOTA sentences.

More information:

Copying Oracle Users
57 DBMS_METADATA

Mapping tablespace usage in Oracle

Lets say that you need to reduce the size of a datafile to reclaim some space (like in an urgency), then after checking the size and usage of one datafile you issue an ALTER DATABASE DATAFILE RESIZE just to get this error:

ORA-03297: file contains used data beyond requested RESIZE value

You may wonder why you cannot reduce the size of that datafile if there is enough free space, and more likely you cannot do that because some segment (table, index, etc.) is placed beyond the resize value. You have to move that segment with techniques like rebuilding indexes and moving tables, but before trying to resize a datafile you might like to map the tablespace usage with this script: Tablespace_map2.sql

Disclaimer: I DIDN'T WRITE THIS SCRIPT, I just modified it to split the maps by datafile as is stated in the comments at the beginning of the script. I was unable to find the original author so I cannot credit him properly.

After running this script you will get an HTML file with a map usage by datafile of the selected tablespace; this script is very handy if you can only run scripts at a SQL prompt. If you place the cursor over a block you will get information about the owner, segment type and name that block belongs to, or if it's a free block.

More information:

ORA-03297 Tips

How to kill a user in Progress

If you need to kill a user connected to a Progress database (because locking issues for example), you can get a list and then kill a user with the proshut command:

myserver:/myprogressdb:> proshut mydb -C list

usr pid time of login user id tty Limbo?

165 20352 Tue Jun 21 08:35:32 2009 SOMEUSER w1c3xpr3 no

 
myserver:/myprogressdb:> proshut mydb -C disconnect 165

User 165 disconnect initiated. (6796)

You can also disconnect users and shutdown databases with the promon menu, selecting the option 8:

myserver:/myprogressdb:> promon mydb.db

    PROGRESS MONITOR Version 9

    Database: /myprogressdb/mydb

    1.  User Control
    2.  Locking and Waiting Statistics
    3.  Block Access
    4.  Record Locking Table
    5.  Activity
    6.  Shared Resources
    7.  Database Status
    8.  Shut Down Database

    T.  Transactions Control
    L.  Resolve Limbo Transactions
    C.  Coordinator Information

    M.  Modify Defaults
    Q.  Quit

    Enter your selection: 8
usr    pid    time of login           user id     tty                  Limbo?
 11  24122 Sat May 21 10:23:29 2009   biw                              no
 12  24247 Sat May 21 10:23:30 2009   wdog                             no
 13  14898 Wed Jun 29 08:25:08 2009   MYUSER0001  /dev/pts/1           no
100  18648 Tue Jun 28 11:30:09 2009   MYUSER0002  w1c3xpr3             no
104  15128 Fri Jun 24 11:51:34 2009   MYUSER0003  w1c3xpr4             no
106  13556 Fri Jun 24 11:52:02 2009   MYUSER0004  w1c3xpr4             no
115  20344 Tue Jun 21 13:30:10 2009   MYUSER0003  w1c3xpr4             no
122  16948 Tue Jun 28 10:18:10 2009   MYUSER0005  w1c3xpr3             no
131  11128 Tue Jun 28 09:09:33 2009   MYUSER0006  w1c3xpr3             no
138  13252 Tue Jun 28 09:45:05 2009   MYUSER0007  w1c3xpr3             no
139   9828 Tue Jun 28 09:00:06 2009   MYUSER0008  w1c3xpr4             no
146  11708 Tue Jun 28 08:51:51 2009   MYUSER0009  w1c3xpr3             no
150  10640 Tue Jun 28 08:48:59 2009   MYUSER0010  w1c3xpr3             no
151  15464 Tue Jun 28 08:48:44 2009   MYUSER0001  w1c3xpr4             no

RETURN - show remaining, Q - quit:
usr    pid    time of login           user id     tty                  Limbo?
190  10708 Wed Jun 29 07:21:35 2009   MYUSER0011  w1c3xpr4             no

                1  Disconnect a User
                2  Unconditional Shutdown
                3  Emergency Shutdown (Kill All)
                x  Exit

   Enter choice>

If there is a lot of users you can see all pressing RETURN, and after showing all users you can choose to shutdown the database (option 2), or kill some user with his usr number (option 1).

Oracle exp and imp

Working with exp and imp, I prefer creating configuration files rather than writing everything at the command prompt because I can have a "template" and modify it as needed. Therefore, this is my template for exporting some schemas (not the full database) with data, putting the log file and dump file in /somedir:

BUFFER=1048576
FILE=/somedir/export.dmp
LOG=/somedir/export.log
STATISTICS=NONE
FULL=N
ROWS=Y
OWNER=SOMEUSER,OTHERUSER
DIRECT=Y

The template for exporting a full database is a bit shorter:

BUFFER=1048576
FILE=/somedir/export.dmp
LOG=/somedir/export.log
STATISTICS=NONE
FULL=Y
ROWS=Y
DIRECT=Y

On the other hand, for importing some schemas (from a full or partial export) I use this configuration file:

BUFFER=1048576
FILE=/somedir/export.dmp
LOG=/somedir/export.log
STATISTICS=NONE
FULL=N
ROWS=Y
feedback=10000
commit=n
IGNORE=Y
fromuser=SOMEUSER,OTHERUSER
touser=SOMEUSER,OTHERUSER
CONSTRAINTS=Y
grants=yes

And my template for importing a full database is the following:

BUFFER=1048576
FILE=/somedir/export.dmp
LOG=/somedir/export.log
STATISTICS=NONE
FULL=Y
ROWS=Y
feedback=10000
commit=n
IGNORE=Y

And just to not forget setting the target database name and character settings (very important), I use this little shell script:

#export ORACLE_SID=mydb
#export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

imp / parfile=/somedir/import.par

Having the more important parameters in your template you can easily change it without checking imp/exp help or documentation, that's why some parameters are included with their default values.

More information:

Import Export FAQ
Checking database character set