Tuesday, March 25, 2008

Ports used by ORACLE

One can know the list of ports used by various ORACLE components in the portlist.ini file.
You can find the file in the location specified below,

UNIX : $ORACLE_HOME/install
Windows : %ORACLE_HOME/instal
l

Tuesday, March 18, 2008

SMON_SCN_TIME - SCN

It is a table owned by SYS User and is filled by SMON every 5 minutes with a timestamp and the current SCN. However, it only counts 1440 records (=5 days). This makes it possible to roughly find an SCN for a point in time in the last 5 days.

Restrict user to export its own objects

One of my Senior DBA's - Bhupinder scripted the below trigger...
Prevent the schema owner to take export on its own objects:

SQL> conn / as sysdba

create or replace trigger prevent_exp_imp
after logon on database
begin
if user='USER1' and upper(sys_context('userenv','module')) Like ('%EXP%') then
raise_application_error (-20001, 'You are not allowed to export data.');
end if;
end;
/


From my client machine:

F:\>exp USER1/USER1@ora file=exp.dmp
Export: Release 10.2.0.2.0 - Production on Tue Mar 18 18:02:09 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
EXP-00056: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: You are not allowed to export or import data.
ORA-06512: at line 3
Username:
EXP-00030:

Similarly it can be configured for Import activity too.