Posts Tagged ‘Oracle 9i’

Oracle – Restricted Database Session

November 17, 2010 Leave a comment

Sometimes it is necessary to do work on a database without any other users being logged in. It is possible to restrict the database session in such a case. When the database starts in restricted mode only users with restricted session privileges can get access to the database even though it is technically in open mode.

Enable / Disable Restricted Session

SQL> startup restrict ORACLE instance started.
Total System Global Area 504366872 bytes
Fixed Size 743192 bytes
Variable Size 285212672 bytes
Database Buffers 218103808 bytes
Redo Buffers 307200 bytes
Database mounted.
Database opened.

Startup the database in restricted mode

The alter system command can be used to put the database in and out of restricted session once it is open:

SQL> alter system enable restricted session;
system altered
SQL> alter system disable restricted session;
system altered

Find and disconnect users connected during restricted session

Any users connected to the database when restricted session is enabled will remain connected and need to be manually disconnected
To check which users are connected to the database run the following:

SQL> SELECT username, logon_time, process from v$session;
-------- --------- -------
17-NOV-10 606252
17-NOV-10 598054
17-NOV-10 540690
17-NOV-10 421948
17-NOV-10 561182
17-NOV-10 512046
17-NOV-10 1257542
SYS      17-NOV-10 1310796
8 rows selected.

By querying the process id you can then issue a kill -9 <process_id> at the operating system level to disconnect the connected user. The blank usernames in v$session refer to background database processes.

Check if database in restricted mode

If you are unsure whether the database is in restricted session or not you can run the following query to check:

SQL> SELECT logins from v$instance;


Oracle – Migrate database from Oracle 9i to Oracle 10g

October 18, 2010 1 comment

If you are migrating from Oracle 9i to Oracle 10g you must use the Oracle export/import tools.  The process for migrating from Oracle 9i to Oracle 10g is to export the data from the Oracle 9i system, the transfer the files across to the 10g system. On the 10g system you must then create a 10g database. Once the 10g database has been created you must then import the data from the 9i system. After the import has completed statistics can then be gathered.

9i Preparation

  1. Check how much disk space is available, either by checking the file system, or checking how much space can be created when backups have been cleared down
  2. Check how big the datafiles are to see how big the export is likely to be, remember the export will be compressed but it is better to have more space than necessary than not enough
  3. So as not to run out of undo tablespace create the following script and store it on the 9i system:

    create table tmp_users as select * from dba_users;
    delete tmp_users;

    Do not close the session or commit the transaction whilst the export is completing. If the database export is large then there is a possibility you will run out of undo tablespace during export. If this happens you will receive the following error:

    ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'

  4. For ease of use we can create an export parameter file. This file can be reused for further exports. Create the export parameter file on the 9i system:


  5. Create a shell script for running the export, this should include a nohupto allow the script to run in the background. If the script runs in the foreground then the export will fail if the server window times out.

    nohup exp parfile=export.par &

  6. Query tablespaces and sizes. Take a note of the sizes of the tablespaces and datafiles in order to recreate them for use on the 10g system.

10g Preparation

  1. On the 10g system create an import shell script. This will look similar to that of the export on the 9i system:

    nohup imp parfile=import.par &

  2. Create the export parameter file on the 10g system:


  3. Create a transactional database using DBCA in a VNC session
  4. Create a sql script for the creation of tablespaces and datafiles

Export Data from 9i

  1. Check backup to tape has been successful
  2. Cleardown any old backups if space is required before export
  3. Run the sql script for undo tablespace retention
  4. In a separate session start the export using the previously created export shell script
  5. Tail nohup.out to see when the export has completed

    $  tail -f nohup.out

FTP the files to the 10g server

  1. FTP the files across to the 10g server either using WinSCP (linux to linux) or using mget (windows to linux/windows)

Import to 10g

  1. In a VNC session run the import shell script created previously