STEPS FOR CREATING NEW YEAR DATABASE ‘1112
Step 1 : Create the Database ARI1112 using template LDLARGE.
(Before that create folder d:\archive1112 )
Database templates should be copied in d:\oracle\product\10.2.0\db_1\assistants\dbca\templates
After database creation, Check the database folder all the datafiles are available or not.
* Only you have to change Database Name,SID,Sys Password & Database path
* Select Use Database File locations from Template.
Step 2 : Configure the Net Manager as ARI1112SRV and test it.
-------TNSNAMES.ORA-------
ARI1112SRV =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.84)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ari1112)
)
)
------------LISTENER.ORA----------
(SID_DESC =
(GLOBAL_DBNAME = ari1112)
(SID_NAME = ari1112)
)
C:\>LSNRCTL STOP
C:\>LSNRCTL START
Step 3 : Create the User LDBO and assign the roles dba,connect,exp_full_database,imp_full_database,
Resource. Assign all System privileges except two viz.,( Freeze_any_workspace,
global Query rewrite).
------------
CREATE USER "LDBO" PROFILE "DEFAULT" IDENTIFIED BY "LDBO" DEFAULT TABLESPACE "USR" TEMPORARY TABLESPACE "TEMPORARY" ACCOUNT UNLOCK ;
BEGIN
DBMS_WM.GrantSystemPriv('ACCESS_ANY_WORKSPACE', 'LDBO', 'NO');
END;
GRANT ADMINISTER ANY SQL TUNING SET TO "LDBO" ;
GRANT ADMINISTER DATABASE TRIGGER TO "LDBO" ;
BEGIN
dbms_resource_manager_privs.grant_system_privilege(privilege_name=>'ADMINISTER_RESOURCE_MANAGER', grantee_name=>'LDBO', admin_option=>FALSE);
END;
/
GRANT ADMINISTER SQL TUNING SET TO "LDBO" ;
GRANT ADVISOR TO "LDBO" ;
GRANT ALTER ANY CLUSTER TO "LDBO" ;
GRANT ALTER ANY DIMENSION TO "LDBO" ;
GRANT ALTER ANY EVALUATION CONTEXT TO "LDBO" ;
GRANT ALTER ANY INDEX TO "LDBO" ;
GRANT ALTER ANY INDEXTYPE TO "LDBO" ;
GRANT ALTER ANY LIBRARY TO "LDBO" ;
GRANT ALTER ANY MATERIALIZED VIEW TO "LDBO" ;
GRANT ALTER ANY OUTLINE TO "LDBO" ;
GRANT ALTER ANY PROCEDURE TO "LDBO" ;
GRANT ALTER ANY ROLE TO "LDBO" ;
GRANT ALTER ANY RULE TO "LDBO" ;
GRANT ALTER ANY RULE SET TO "LDBO" ;
GRANT ALTER ANY SEQUENCE TO "LDBO" ;
GRANT ALTER ANY SQL PROFILE TO "LDBO" ;
GRANT ALTER ANY TABLE TO "LDBO" ;
GRANT ALTER ANY TRIGGER TO "LDBO" ;
GRANT ALTER ANY TYPE TO "LDBO" ;
GRANT ALTER DATABASE TO "LDBO" ;
GRANT ALTER PROFILE TO "LDBO" ;
GRANT ALTER RESOURCE COST TO "LDBO" ;
GRANT ALTER ROLLBACK SEGMENT TO "LDBO" ;
GRANT ALTER SESSION TO "LDBO" ;
GRANT ALTER SYSTEM TO "LDBO" ;
GRANT ALTER TABLESPACE TO "LDBO" ;
GRANT ALTER USER TO "LDBO" ;
GRANT ANALYZE ANY TO "LDBO" ;
GRANT ANALYZE ANY DICTIONARY TO "LDBO" ;
GRANT AUDIT ANY TO "LDBO" ;
GRANT AUDIT SYSTEM TO "LDBO" ;
GRANT BACKUP ANY TABLE TO "LDBO" ;
GRANT BECOME USER TO "LDBO" ;
GRANT CHANGE NOTIFICATION TO "LDBO" ;
GRANT COMMENT ANY TABLE TO "LDBO" ;
GRANT CREATE ANY CLUSTER TO "LDBO" ;
GRANT CREATE ANY CONTEXT TO "LDBO" ;
GRANT CREATE ANY DIMENSION TO "LDBO" ;
GRANT CREATE ANY DIRECTORY TO "LDBO" ;
GRANT CREATE ANY EVALUATION CONTEXT TO "LDBO" ;
GRANT CREATE ANY INDEX TO "LDBO" ;
GRANT CREATE ANY INDEXTYPE TO "LDBO" ;
GRANT CREATE ANY JOB TO "LDBO" ;
GRANT CREATE ANY LIBRARY TO "LDBO" ;
GRANT CREATE ANY MATERIALIZED VIEW TO "LDBO" ;
GRANT CREATE ANY OPERATOR TO "LDBO" ;
GRANT CREATE ANY OUTLINE TO "LDBO" ;
GRANT CREATE ANY PROCEDURE TO "LDBO" ;
GRANT CREATE ANY RULE TO "LDBO" ;
GRANT CREATE ANY RULE SET TO "LDBO" ;
GRANT CREATE ANY SEQUENCE TO "LDBO" ;
GRANT CREATE ANY SQL PROFILE TO "LDBO" ;
GRANT CREATE ANY SYNONYM TO "LDBO" ;
GRANT CREATE ANY TABLE TO "LDBO" ;
GRANT CREATE ANY TRIGGER TO "LDBO" ;
GRANT CREATE ANY TYPE TO "LDBO" ;
GRANT CREATE ANY VIEW TO "LDBO" ;
GRANT CREATE CLUSTER TO "LDBO" ;
GRANT CREATE DATABASE LINK TO "LDBO" ;
GRANT CREATE DIMENSION TO "LDBO" ;
GRANT CREATE EVALUATION CONTEXT TO "LDBO" ;
GRANT CREATE EXTERNAL JOB TO "LDBO" ;
GRANT CREATE INDEXTYPE TO "LDBO" ;
GRANT CREATE JOB TO "LDBO" ;
GRANT CREATE LIBRARY TO "LDBO" ;
GRANT CREATE MATERIALIZED VIEW TO "LDBO" ;
GRANT CREATE OPERATOR TO "LDBO" ;
GRANT CREATE PROCEDURE TO "LDBO" ;
GRANT CREATE PROFILE TO "LDBO" ;
GRANT CREATE PUBLIC DATABASE LINK TO "LDBO" ;
GRANT CREATE PUBLIC SYNONYM TO "LDBO" ;
GRANT CREATE ROLE TO "LDBO" ;
GRANT CREATE ROLLBACK SEGMENT TO "LDBO" ;
GRANT CREATE RULE TO "LDBO" ;
GRANT CREATE RULE SET TO "LDBO" ;
GRANT CREATE SEQUENCE TO "LDBO" ;
GRANT CREATE SESSION TO "LDBO" ;
GRANT CREATE SYNONYM TO "LDBO" ;
GRANT CREATE TABLE TO "LDBO" ;
GRANT CREATE TABLESPACE TO "LDBO" ;
GRANT CREATE TRIGGER TO "LDBO" ;
GRANT CREATE TYPE TO "LDBO" ;
GRANT CREATE USER TO "LDBO" ;
GRANT CREATE VIEW TO "LDBO" ;
BEGIN
DBMS_WM.GrantSystemPriv('CREATE_ANY_WORKSPACE', 'LDBO', 'NO');
END;
/
GRANT DEBUG ANY PROCEDURE TO "LDBO" ;
GRANT DEBUG CONNECT SESSION TO "LDBO" ;
GRANT DELETE ANY TABLE TO "LDBO" ;
BEGIN
dbms_aqadm.grant_system_privilege(privilege=>'DEQUEUE_ANY', grantee=>'LDBO', admin_option=>FALSE);
COMMIT;
END;
/
GRANT DROP ANY CLUSTER TO "LDBO" ;
GRANT DROP ANY CONTEXT TO "LDBO" ;
GRANT DROP ANY DIMENSION TO "LDBO" ;
GRANT DROP ANY DIRECTORY TO "LDBO" ;
GRANT DROP ANY EVALUATION CONTEXT TO "LDBO" ;
GRANT DROP ANY INDEX TO "LDBO" ;
GRANT DROP ANY INDEXTYPE TO "LDBO" ;
GRANT DROP ANY LIBRARY TO "LDBO" ;
GRANT DROP ANY MATERIALIZED VIEW TO "LDBO" ;
GRANT DROP ANY OPERATOR TO "LDBO" ;
GRANT DROP ANY OUTLINE TO "LDBO" ;
GRANT DROP ANY PROCEDURE TO "LDBO" ;
GRANT DROP ANY ROLE TO "LDBO" ;
GRANT DROP ANY RULE TO "LDBO" ;
GRANT DROP ANY RULE SET TO "LDBO" ;
GRANT DROP ANY SEQUENCE TO "LDBO" ;
GRANT DROP ANY SQL PROFILE TO "LDBO" ;
GRANT DROP ANY SYNONYM TO "LDBO" ;
GRANT DROP ANY TABLE TO "LDBO" ;
GRANT DROP ANY TRIGGER TO "LDBO" ;
GRANT DROP ANY TYPE TO "LDBO" ;
GRANT DROP ANY VIEW TO "LDBO" ;
GRANT DROP PROFILE TO "LDBO" ;
GRANT DROP PUBLIC DATABASE LINK TO "LDBO" ;
GRANT DROP PUBLIC SYNONYM TO "LDBO" ;
GRANT DROP ROLLBACK SEGMENT TO "LDBO" ;
GRANT DROP TABLESPACE TO "LDBO" ;
GRANT DROP USER TO "LDBO" ;
BEGIN
dbms_aqadm.grant_system_privilege(privilege=>'ENQUEUE_ANY', grantee=>'LDBO', admin_option=>FALSE);
COMMIT;
END;
/
GRANT EXECUTE ANY CLASS TO "LDBO" ;
GRANT EXECUTE ANY EVALUATION CONTEXT TO "LDBO" ;
GRANT EXECUTE ANY INDEXTYPE TO "LDBO" ;
GRANT EXECUTE ANY LIBRARY TO "LDBO" ;
GRANT EXECUTE ANY OPERATOR TO "LDBO" ;
GRANT EXECUTE ANY PROCEDURE TO "LDBO" ;
GRANT EXECUTE ANY PROGRAM TO "LDBO" ;
GRANT EXECUTE ANY RULE TO "LDBO" ;
GRANT EXECUTE ANY RULE SET TO "LDBO" ;
GRANT EXECUTE ANY TYPE TO "LDBO" ;
GRANT EXPORT FULL DATABASE TO "LDBO" ;
GRANT FLASHBACK ANY TABLE TO "LDBO" ;
GRANT FORCE ANY TRANSACTION TO "LDBO" ;
GRANT FORCE TRANSACTION TO "LDBO" ;
GRANT GRANT ANY OBJECT PRIVILEGE TO "LDBO" ;
GRANT GRANT ANY PRIVILEGE TO "LDBO" ;
GRANT GRANT ANY ROLE TO "LDBO" ;
GRANT IMPORT FULL DATABASE TO "LDBO" ;
GRANT INSERT ANY TABLE TO "LDBO" ;
GRANT LOCK ANY TABLE TO "LDBO" ;
GRANT MANAGE ANY FILE GROUP TO "LDBO" ;
BEGIN
dbms_aqadm.grant_system_privilege(privilege=>'MANAGE_ANY', grantee=>'LDBO', admin_option=>FALSE);
COMMIT;
END;
/
GRANT MANAGE FILE GROUP TO "LDBO" ;
GRANT MANAGE SCHEDULER TO "LDBO" ;
GRANT MANAGE TABLESPACE TO "LDBO" ;
GRANT MERGE ANY VIEW TO "LDBO" ;
BEGIN
DBMS_WM.GrantSystemPriv('MERGE_ANY_WORKSPACE', 'LDBO', 'NO');
END;
/
GRANT ON COMMIT REFRESH TO "LDBO" ;
GRANT QUERY REWRITE TO "LDBO" ;
GRANT READ ANY FILE GROUP TO "LDBO" ;
BEGIN
DBMS_WM.GrantSystemPriv('REMOVE_ANY_WORKSPACE', 'LDBO', 'NO');
END;
/
GRANT RESTRICTED SESSION TO "LDBO" ;
GRANT RESUMABLE TO "LDBO" ;
BEGIN
DBMS_WM.GrantSystemPriv('ROLLBACK_ANY_WORKSPACE', 'LDBO', 'NO');
END;
/
GRANT SELECT ANY DICTIONARY TO "LDBO" ;
GRANT SELECT ANY SEQUENCE TO "LDBO" ;
GRANT SELECT ANY TABLE TO "LDBO" ;
GRANT SELECT ANY TRANSACTION TO "LDBO" ;
GRANT SYSDBA TO "LDBO" ;
GRANT SYSOPER TO "LDBO" ;
GRANT UNDER ANY TABLE TO "LDBO" ;
GRANT UNDER ANY TYPE TO "LDBO" ;
GRANT UNDER ANY VIEW TO "LDBO" ;
GRANT UNLIMITED TABLESPACE TO "LDBO" ;
GRANT UPDATE ANY TABLE TO "LDBO" ;
GRANT "AQ_ADMINISTRATOR_ROLE" TO "LDBO" ;
GRANT "CONNECT" TO "LDBO" ;
GRANT "DBA" TO "LDBO" ;
GRANT "EXP_FULL_DATABASE" TO "LDBO" ;
GRANT "IMP_FULL_DATABASE" TO "LDBO" ;
-------------------------------
Step 4 : Run the Optable in LDBO Schema & Run the four sqls in SYS Schema.
Step 5 : Run the Roles.sql(EXIST IN OPTABLE) in LDBO.
REVOKE LDBO ROLES EXCEPT IMPORTANT ROLES.
Step 6 : Create database link (1112 database) and test it.
CREATE DATABASE LINK "LNK_PREVIOUSYEARBALANCE"
CONNECT TO "LDBO" IDENTIFIED BY LDBO USING 'ari1112srv';
Select count(*) from ldfibs@lnk_previousyearbalance;
Step 7 : Analyze the database using old Analyze method.This is for one time (Analyze.sql)
---------ONE TIME-------------
spool c:\temp\analyze.sql
select 'ANALYZE TABLE '||Owner||'.'||table_name||' compute statistics;'
from sys.all_tables where table_name!='_default_auditing_options_'
/
select 'ANALYZE INDEX '||Owner||'.'||index_name||' compute statistics;'
from sys.all_indexes
/
spool off
set feed on
@c:\temp\analyze.sql
-----------REBUILD INDEX---------
SPOOL C:\temp.sql
SELECT 'ALTER INDEX ' || a.index_name || ' REBUILD;'
FROM all_indexes a
WHERE table_owner = 'LDBO'
ORDER BY 1
/
SPOOL OFF
-- Comment out following line to prevent immediate run
@temp.sql
---------
Step 8 : Schedule analyze (newAnalyze.sql) and backup for 1112 database.
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('LDBO',cascade=>TRUE);
Step 9 : Run users.sql in old year database(1011) and then run Recreate_users.sql in 1112 database.
set heading off verify off feedback off echo off term off linesize 200 wrap on
spool c:\temp\Recreate_Users.sql
SELECT distinct 'create profile '|| profile ||' Limit Sessions_per_user Unlimited;' from dba_profiles where profile!='DEFAULT' ;
Select 'Alter profile '|| profile ||' Limit '|| Resource_name ||' '|| Limit||';' from dba_profiles where profile!='DEFAULT' and Limit!='DEFAULT' ;
SELECT 'create user ' || username ||
' identified ' ||
DECODE(password, NULL, 'EXTERNALLY', ' by values ' || '''' || password || '''') ||
' default tablespace ' || default_tablespace ||
' temporary tablespace ' || temporary_tablespace ||
' profile ' || profile || ';'
FROM dba_users
where username!='SYSTEM' and Username!='SYS' and Username!='DBSNMP' and Username!='REPADMIN' ORDER BY username ;
SELECT 'Grant '|| Granted_role ||' to '|| Grantee||';' from dba_role_privs Where Grantee!='SYSTEM' and
Grantee!='SYS' and Grantee!='DBSNML' and Grantee!='REPADMIN' ;
spool off
Step 10 : Reduce the Previous year(1011) Memory size.(This should be done at 31/03/2011
before transmitting year end transfer procedure.)
---FY10-11----
ALTER SYSTEM SET pga_aggregate_target=1200M;
ALTER SYSTEM SET SGA_MAX_SIZE=8152M SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_target=2447M; ----workarea_size_policy ='AUTO'
ALTER SYSTEM SET parallel_execution_message_size=4096 SCOPE=SPFILE;
Step 11: compileinvalidobjects.SQL
pool c:\temp\invalid.sql ;
select OBJECT_NAME from dba_objects where owner='LDBO' AND STATUS='INVALID'
select
'ALTER ' || OBJECT_TYPE || ' ' ||
OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from
dba_objects
where
status = 'INVALID'
and
object_type in ('PACKAGE','FUNCTION','PROCEDURE','VIEW','TRIGGER')
;
spool out ;
@ c:\temp\invalid.sql
STEP 12: FIRM VFP BACKUP
@ECHO OFF
copy d:\AKJ1112\*.* F:\VFP_BACKUP1112\LAKJ1112\*.* /v /y
copy d:\ACML1112\*.* F:\VFP_BACKUP1112\LACML1112\*.* /v /y
copy d:\AFLC1112\*.* F:\VFP_BACKUP1112\LAFLC1112\*.* /v /y
copy d:\AFSL1112\*.* F:\VFP_BACKUP1112\LAFSL1112\*.* /v /y
copy d:\Aric1112\*.* F:\VFP_BACKUP1112\LARIC1112\*.* /v /y
copy d:\AIBS1112\*.* F:\VFP_BACKUP1112\LAIBS1112\*.* /v /y
xcopy d:\ld\*.* F:\VFP_BACKUP1112\Lld\*.* /s /i /v /y
EXIT
STEP 13: EXPORT SCANIMAGE FROM PREVIOUS YEAR AND IMPORT INTO CURRENT FY
---------FY10-11-----------
expdp ldbo/ldbo@ari1011srv directory=dpump_dir1 tables=CLIENTSCANNEDIMAGE dumpfile=SCANTABLE1011.dmp LOGFILE=SCANTABLE1011.LOG
drop table CLIENTSCANNEDIMAGE;
-----create table---------otherwise clientmaster will not open----
CREATE TABLE "LDBO"."CLIENTSCANNEDIMAGE"
(
"FIRMNUMBER" CHAR(10 BYTE) NOT NULL ENABLE,
"CODE" CHAR(10 BYTE) NOT NULL ENABLE,
"PSCANNEDIMAGE" LONG RAW,
"NFINANCIALYEAR" NUMBER(4,0) NOT NULL ENABLE
)
PCTFREE 15 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
(
INITIAL 10485760 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "USR" ;
CREATE UNIQUE INDEX "LDBO"."SCANNEDIMAGEINDEX" ON "LDBO"."CLIENTSCANNEDIMAGE"
(
"FIRMNUMBER", "NFINANCIALYEAR", "CODE"
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
(
INITIAL 3145728 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "INDX" ;
grant INSERT on CLIENTSCANNEDIMAGE to CLIENTADD ;
grant SELECT on CLIENTSCANNEDIMAGE to CLIENTADD ;
grant DELETE on CLIENTSCANNEDIMAGE to CLIENTEDT ;
grant INSERT on CLIENTSCANNEDIMAGE to CLIENTEDT ;
grant SELECT on CLIENTSCANNEDIMAGE to CLIENTEDT ;
grant UPDATE on CLIENTSCANNEDIMAGE to CLIENTEDT ;
--------FY 11-12--------
drop table
update clientscannedimage set nfinancialyear=2011;
STEP 14:
set oracle_sid=ari1112
RMAN TARGET sys/oracle@ari1112srv
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO F:\rman1112\%F';
SHOW ALL;
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT 'F:\rman1112\%U';
set oracle_sid=ari1112
RMAN TARGET sys/oracle@ari1112srv cmdfile='c:\ksh\rman1112.sql'
STEP 15:
SQL> CREATE DIRECTORY dpump_dir1 AS 'F:\EXPDP1112';
SQL> grant read, write on directory dpump_dir1 to ldbo;
@echo off
expdp ldbo/ldbo@ari1112srv directory=dpump_dir1 schemas=ldbo dumpfile=ari1112.DMP schemas=ldbo LOGFILE=ari1112.LOG
ren F:\EXPDP1112\ARI1112.DMP ARI1112_%date:~0,2%%date:~3,2%%date:~6,4%.DMP
ren F:\EXPDP1112\ARI1112log ARI1112_%date:~0,2%%date:~3,2%%date:~6,4%log
exit
----after some months----
ren F:\EXPDP1112\ARI1112log ARI1112_%date:~0,2%%date:~3,2%%date:~6,4%log
exit
STEP 16:
--------------TNSNAMES.ORA FOR ASP-----------------
ARI1112SRV =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.84)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ARI1112)
)
)
DIG1112SRV =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.84)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ARI1112)
)
)
STEP 17:
ASP STANDARD VERSION ROLES REVOKE
revoke insert,update on TBLUSERPROFILES from Remoteuser ;
revoke insert,update on TBLUSERPROFILES from firmpermission ;
create role userprofiles ;
grant insert,update on TBLUSERPROFILES to userprofiles ;
revoke execute on SP_ASIANEFFBAL from FIRMPERMISSION;
revoke insert on TBLFIRMCREATION from FIRMPERMISSION ;
revoke insert,update,delete on TBLMAILERDETAIL from FIRMPERMISSION ;
revoke execute on SP_FIRMCREATION from FIRMPERMISSION;
revoke execute on PK_JOURNALIMPORT from Remoteuser ;
revoke execute on PK_JOURNAL from remoteuser ;
revoke execute on ldbo.SP_LIENFUNDTRANSFER from Remoteuser ;
revoke insert on TBLBILLTAGGING from cashbankadd ;
REVOKE UPDATE ON ESETTLE FROM CASHBANKADD;
STEP 18:
CLIENTLEVEL ROLES
Create Role ClientPassEdit ;
Grant update on Accountemaildetail to ClientPassEdit ;
Grant clientpassedit to cllvl ;
STEP 19:
LD DIGITAL
CREATE DIRECTORY LDDGITAL AS 'd:\ldoutput\Lddigital';
grant read, write on directory LDDGITAL to ldbo;
STEP 20:
Disable the LD RK Rakshak job for Last year FY 1011
Select Owner,Job_Name,Job_Type,Job_Action,Enabled,State From Dba_Scheduler_Jobs Where Owner='LDBO' Order By State;
Exec DBMS_SCHEDULER.DISABLE('JLDRKBFTRADEUPDATION');
AS
V_PROG SYS.V_$SESSION.PROGRAM%TYPE;
V_MODULE SYS.V_$SESSION.PROGRAM%TYPE ;
LNUSERCODE NUMBER ;
LCUSERDETAILS VARCHAR2(100) ;
BEGIN
SELECT PROGRAM,MODULE INTO V_PROG,V_MODULE FROM SYS.V_$SESSION
WHERE AUDSID = USERENV('SESSIONID')
AND AUDSID != 0
AND ROWNUM = 1;
IF UPPER(V_PROG) LIKE '%TOAD%' OR UPPER(V_PROG) LIKE '%T.O.A.D%' OR
UPPER(V_PROG) LIKE '%SQLNAV%' OR
UPPER(V_PROG) LIKE '%PLSQLDEV%' OR
UPPER(V_PROG) LIKE '%BUSOBJ%' OR
UPPER(V_PROG) LIKE '%EXCEL%'
THEN
RAISE_APPLICATION_ERROR(-20000, 'On LD Database Development tools are Restricted and is violation of agreement entered into between 2 companies');
END IF;
LNUSERCODE:=-1;
IF ((UPPER(V_PROG)='LD.EXE' AND UPPER(V_MODULE)='LD.EXE') OR
(UPPER(V_PROG)='DLLHOST.EXE' AND UPPER(V_MODULE)='DLLHOST.EXE')) THEN
LCUSERDETAILS:=ORA_CLIENT_IP_ADDRESS ;
INSERT INTO TBLTEMPOPERATIONSTATISTICS (NOPERCODE,COPERATIONNAME) VALUES (LNUSERCODE,LCUSERDETAILS) ;
ELSE
LCUSERDETAILS:='USER LOGGED ON' ;
INSERT INTO TBLTEMPOPERATIONSTATISTICS (NOPERCODE,COPERATIONNAME) VALUES (LNUSERCODE,LCUSERDETAILS) ;
END IF ;
COMMIT ;
END;
Create or Replace trigger tglogininformation
after logon on Database
Begin
splogininformation() ;
End;
/
No comments:
Post a Comment