create group users

net localgroup LD_user /add

net user kshitij password /add /fullname:"Kshitij Rakesh" /expires:never /passwordchg:no

net localgroup LD_user kshitij /add

Replace String in text file

c:\> BatchSubstitute.bat "&Yourname" kshitij up78896.sql > up78896_new.sql

==========

@echo off
REM -- Prepare the Command Processor --
SETLOCAL ENABLEEXTENSIONS
SETLOCAL DISABLEDELAYEDEXPANSION

::BatchSubstitude - parses a File line by line and replaces a substring"
::syntax: BatchSubstitude.bat OldStr NewStr File
:: OldStr [in] - string to be replaced
:: NewStr [in] - string to replace with
:: File [in] - file to be parsed
:$changed 20100115
:$source http://www.dostips.com
if "%~1"=="" findstr "^::" "%~f0"&GOTO:EOF
for /f "tokens=1,* delims=]" %%A in ('"type %3|find /n /v """') do (
set "line=%%B"
if defined line (
call set "line=echo.%%line:%~1=%~2%%"
for /f "delims=" %%X in ('"echo."%%line%%""') do %%~X
) ELSE echo.
)


======================

LD Billing Porcess Email Notification

CREATE OR REPLACE TRIGGER email_nsef_bill
AFTER insert on TBLLOCKTABLE
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(50) := 'NSEF Bill Process Done';
l_message VARCHAR2(500);

BEGIN

if (USER in ('RITESHR')) then
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ss' ) || b;
l_message :=
l_message || 'User: ' || SYS_CONTEXT( 'USERENV', 'CURRENT_USER' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;

l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;

l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;

BEGIN
utl_mail.send
( sender => 'kshitij.rakesh@arihantcapital.com',
recipients => 'kshitij.rakesh@arihantcapital.com',
subject => l_subject,
message => l_message );

EXCEPTION
WHEN others THEN
RAISE;

END;
end if;
END email_nsef_bill;
/

Bulk Client Code Change prg

Set Exclusive OFF
Set Date BRITISH
Set Century on
Set Talk OFF
Set Safety OFF

Close ALL
Clear All
Clear

lcFirmnum='NBS-000001'
gnQryhandle=sqlconnect("NBS1011odbc","ldbo","ldbo")

lnSqlanswer=SQLPrepare(gnQryhandle,"select * from Ldfibs where firmnumber=?lcFirmnum ","ldfibs")
If lnSqlanswer!=-1
lnSqlanswer=SQLEXEC(gnQryhandle)
lnsqlanswer=sqlcommit(gnQryhandle)
EndIf
IF lnSqlanswer=-1
=AError(myErr)
=MessageBox(myErr(2)+" "+myErr(3),64,"Odbc Error")
Return
EndIf
Index on Oowncode tag oownin

Select 0
USE c:\temp\cldetail

Create Cursor erroroowncode(OldOowncode c(10),NewOowncode c(10),cErrortext char(150))
Create Cursor errortermcode(OldTermcode c(10),NewTermcode c(10),cErrortext char(150))

Create Cursor correctoowncode(OldOowncode c(10),NewOowncode c(10),cErrortext char(150))
Create Cursor correcttermcode(OldTermcode c(10),NewTermcode c(10),cErrortext char(150))

=messagebox(str(gnQryhandle))

lnCtr=0
Select Cldetail
Scan
lcOldcode=Cldetail.Oldcode
lcNewcode=Cldetail.Newcode
Select Ldfibs
Seek lcNewcode
If ! Found()
lnSqlanswer=SQLPREPARE(gnQryhandle,"{Call sp_Clientcodechange(?lcFirmnum,?lcOldcode,?lcNewcode,'N')}")
If lnSqlanswer!=-1
lnSqlanswer=SQLEXEC(gnQryhandle)
Insert into Correctoowncode values (lcOldcode,lcNewcode,'Successfully Changed')
EndIf
If lnSqlanswer=-1
=AError(myErr)
** =messagebox(myErr(2)+" "+myErr(3),64,"Odbc Error")
Select erroroowncode
Insert into erroroowncode values (lcOldcode,lcNewcode,x(2)+" "+x(3))
EndIf
lnCtr=lnCtr+1
@10,10 Say lnCtr
Else
Select erroroowncode
Insert into erroroowncode values (lcOldcode,lcNewcode,'Oowncode Already Exists')
EndIf
EndScan

=SQLCommit(gnQryhandle)

Select errorOowncode
Copy To c:\temp\erroroown.dbf

Select errorTermcode
Copy To c:\temp\errorterm.dbf

Close All
Return

LD AR > always release to AF > as per formula query

Client Master > Der+ Gen VI > Demat > Payout Status Quo
Client Master > Der+ Gen VI > Default product > Status Quo

AR > always release
AF > as per formula

Select T1.Oowncode From Accountdetail T1, Ldfibs T2
Where T1.Oowncode=T2.Oowncode And T1.Firmnumber=T2.Firmnumber
And T1.Firmnumber='ACML-00001'
And T1.Cpayoutstatusquo='AR' And T2.Brcode In(

)
;


Select distinct T1.Cclientcode From Tblclientproductactivation T1, Ldfibs T2
Where T1.Cclientcode=T2.Oowncode And T1.Cfirmnumber=T2.Firmnumber
And T1.Cfirmnumber='ACML-00001'
And T1.Cpayoutstatusquo='AR' And T2.Brcode In (

);


UPDATE ACCOUNTDETAIL SET Cpayoutstatusquo= 'AF' WHERE FIRMNUMBER='ACML-00001' and Cpayoutstatusquo= 'AR' AND oowncode= '20100A005';



UPDATE Tblclientproductactivation SET Cpayoutstatusquo='AF' Where Cfirmnumber='ACML-00001' And Nproductcode='0' And Cpayoutstatusquo='AR' and Cclientcode= '20100A005';

LD LOCKTABLE Email Notification

Select * From Tbllocktable Where Dcashbankfinancialentry Not Like ' ';
------------


CREATE OR REPLACE TRIGGER email_lock_table
AFTER UPDATE of Dcashbankreceiptentry,Dcashbankfinancialentry,Djournalentry on TBLLOCKTABLE
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'ALERT: UNLOCK Data Files FY1112';
l_message VARCHAR2(500);

BEGIN

if (SYS_CONTEXT( 'USERENV', 'CURRENT_USER' ) not in ('LDBO')) then

l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ss' ) || b;
l_message :=
l_message || 'User: ' || SYS_CONTEXT( 'USERENV', 'CURRENT_USER' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;

l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;

l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;

BEGIN
utl_mail.send
( sender => 'kshitij.rakesh@arihantcapital.com',
recipients => 'kshitij.rakesh@arihantcapital.com',
subject => l_subject,
message => l_message );

EXCEPTION
WHEN others THEN
RAISE;

END;
END IF;
END email_lock_table;
/

Create Control File Manually

CREATE CONTROLFILE REUSE DATABASE "ARI1112" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 671
LOGFILE
GROUP 1 'C:\ARID1112\REDO01.TXT' SIZE 50M,
GROUP 2 'C:\ARID1112\REDO02.TXT' SIZE 50M,
GROUP 3 'C:\ARID1112\REDO03.TXT' SIZE 50M
DATAFILE
'C:\ARID1112\SYSTEM01.ORA',
'C:\ARID1112\UNDOTBS01.ORA',
'C:\ARID1112\SYSAUX01.ORA',
'C:\ARID1112\INDX01.ORA',
'C:\ARID1112\USERS01.ORA'
CHARACTER SET WE8MSWIN1252;

Memory management SGA PGA

RAM=16 GB
WINDOW PROCESS=16*20%=3.2 GB

ORACLE SERVICES=12 GB

PGA_AGGREGATE_TARGET==(RAM*80%)*50% FOR DSS
PGA_AGGREGATE_TARGET==(RAM*80%)*20% FOR OLTP


SGA_TARGET + PGA_AGGREGATE_TARGET determine how much memory oracle is going to use.

SGA_MAX_SIZE is only upper limit. Not how much oracle is going to allocate.


PGA_AGGREGATE_TARGET is not being allocated at the startup it is allocated on as needed basis, so when some server process need memory from pga_aggregate _target area this memory is being allocated by setting 1GB to PGA_AGGREGATE_TARGET you set max size for this region, so even if some process will need memory but you have already reached 1GB Oracle will not allocate more.

INSTANCE =3



SGA PGA TOTAL
YR 0910 1 0.5 1.5
YR 1011 1 0.5 1.5
YR 1112 6 3 9

SGA Tuning

There are two parameters
SGA_TARGET: RAM SPACE IS ASSIGNED TO ORACLE SERVICES, CAN SEE ON TASK MANAGER PROCESS

This parameter is new with Oracle 10g. It specifies the total amaount of SGA memory available to an instance. Setting this parameter makes Oracle distribute the available memory among various components - such as shared pool (for SQL and PL/SQL), Java pool, large_pool and buffer cache - as required.

sga_target cannot be higher than sga_max_size.


If sga_max_size is less than the sum of db_cache_size + log_buffer + shared_pool_size + large_pool_size at initialization time, then the value of sga_max_size is ignored.



SGA_MAX_SIZE

This parameter sets the hard limit up to which sga_target can dynamically adjust sizes. Usually, sga_max_size and sga_target will be the same value, but there may be times when you want to have the capability to adjust for peak loads. By setting this parameter higher than sga_target, you allow dynamic adjustment of the sga_target parameter.

SGA Sizing on a dedicated server
OS Reserved RAM – This is RAM required to run the OS kernel and system functions, 20% of total RAM for MS-Windows, and 10% of total RAM for UNIX/Linux

Oracle Database Connections RAM – Each Oracle connection requires OS RAM regions for sorting and hash joins. (This does not apply when using the Oracle multi-threaded server or pga_aggregate_target .) The maximum amount of RAM required for a session is as follows:

2 MB RAM session overhead + sort_area_size + hash_area_size

Oracle SGA Sizing for RAM – This is determined by the Oracle parameter settings. The total is easily found by either the show sga command or the value of the sga_max_size parameter.


Eg. RAM=16 GB

for windows processes = 16*20%=3.5 GB reserved for windows

2MB + 64 KB + 128 KB




ALTER SYSTEM SET SGA_TARGET=6512M;

ALTER SYSTEM SET SGA_MAX_SIZE=8152M SCOPE=SPFILE;

Oracle error 14402 : ORA-14402: updating partition KEY COLUMN would cause a partition CHANGE

The above error was caused because when you updated the record, it now belongs to a different partition but since row movement between partitions is not enabled on the table, you got that error.

It's easy to solve this problem. Just enable row movement on the table by executing the following command. You need to be logged in as the owner schema of that table or have enough privileges to do so.

ALTER TABLE enable row movement;

PGA Tuning

Select Name,Value/1024/1024 From V$parameter where name like '%pga%';

Make a first estimate for PGA_AGGREGATE_TARGET, based on a rule of thumb. By default, Oracle uses 20% of the SGA size. However, this initial setting may be too low for a large DSS system.

You must then divide the resulting memory between the SGA and the PGA.

• For OLTP systems, the PGA memory typically accounts for a small fraction of the total memory available (for example, 20%), leaving 80% for the SGA.

• For DSS systems running large, memory-intensive queries, PGA memory can typically use up to 70% of that total (up to 2.2 GB in this example).

Good initial values for the parameter PGA_AGGREGATE_TARGET might be:

• For OLTP: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%

• For DSS: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%

  where total_mem is the total amount of physical memory available on the system.

The PGA_AGGREGATE_TARGET should be set to attempt to keep the ESTD_PGA_CACHE_HIT_PERCENTAGE greater than 95 percent. By setting this appropriately, more data will be sorted in memory that may have been sorted on disk. The next query returns the minimum value for the PGA_AGGREGATE_TARGET that is projected to yield a 95 percent or greater cache hit ratio:



Select Min(Pga_Target_For_Estimate/1024/1024) "recommended_pga"
from v$pga_target_advice
Where Estd_Pga_Cache_Hit_Percentage > 95;




alter system set pga_aggregate_target= "recommended_pga";

Kill Blocking Session

SELECT 'alter system kill session ''||ss.sid||','||ss.serial#||'';'
FROM v$process pr, v$session ss, v$sqlarea sqa
WHERE pr.addr=ss.paddr
AND ss.username is not null
AND ss.sql_address=sqa.address(+)
AND ss.sql_hash_value=sqa.hash_value(+)
AND ss.status='ACTIVE'
AND ss.blocking_session_status='VALID';

Contra Entry Zero Token

Cheque is expired after 6 months
31 Mar 2011 20110331
update cashmain set bankreco=20110331 where firmnumber='ACML-00001' AND CBNUMBER= 'PYAUTOP1285007';

Shrink datafile space from dropped table

Shrink datafile space from dropped table
==================================================

select sum(bytes) / 1024 / 1024 / 1024 from dba_segments where tablespace_name='USR';
114.127 GB

Select Sum(Bytes) / 1024 / 1024 / 1024 From V$datafile Where Name Like '%USERS01%';

251.13 GB

Select Sum(Bytes) / 1024 / 1024 / 1024 From Dba_Free_Space where Tablespace_Name='USR';
137 GB

137 GB free space, how to shrink it.



==================================================

ALTER TABLE XYZ ENABLE ROW MOVEMENT;
ALTER TABLE XYZ SHRINK SPACE CASCADE;

alter database datafile 'D:\ARID0910\USERS01.ORA' resize 102400M;


===================
create a temperory table space.
move all three user to the new tablespace
move all the tables to new tablespace.
now drop the old tablespace and
create a new table space with same name and
restore all ur user and tables.



====================================================



-- Enable row movement.
ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

-- Recover space and amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE;

-- Recover space, but don't amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE COMPACT;

-- Recover space for the object and all dependant objects.
ALTER TABLE scott.emp SHRINK SPACE CASCADE;


====================================================

you have two options besides import/export. Basically, only the first step differs:
1. As Douglas Paiva de Sousa stated, you can use DBMS_REDEFINITION to re-create the objects left in the tablespace to the same tablespace or a new one;
2. Without DBMS_REDEFINITION you can create an old-fashioned script based on the dictionary views that moves all the tables (and indexes if there is any in there) left in that tablespace to another one with ALTER TABLE MOVE TABLESPACE. The indexes must be rebuilt afterwards as they become invalid in the process.

If You moved the objects to a new tablespace, then the next step is to rename the new tablespace to the old one.

As a last step the datafile size should be changed to a lower value; at that point will only the Oracle DB release physical disc space for the OS.

The first one is better from the point of view of system accessibility as this can be done while the system using the given tables is online;
the second possibility may render that system useless so it requires them to be offline.


=======================================================


You can use the free space in the tablespace for new extents, but if You want to release it to the Operating System You must do some kind

of migration of the data.
Either while the system(s) using the tablespace are online or while offline.
The second is much more easier, as You can generate the script to do that from the data dictionary-but You must have enough free space in

the OS to hold one more copy of them and the accessing system(s) must be offline.
So this will be a planned downtime for them...

The steps in more detail:
create tablespace users2 ...

run the following query, then execute its results:

select 'ALTER TABLE ' || o.owner || '.' || o.TABLE_NAME || ' enable row movement; '
from dba_tables o
where o.TABLESPACE_NAME = 'USERS'

then execute the following query's results:

select 'ALTER TABLE ' || o.owner || '.' || o.TABLE_NAME || ' move tablespace users2; '
from dba_tables o
where o.TABLESPACE_NAME = 'USERS'

in case there are indexes in the TS also, run query and execute results:
select 'ALTER INDEX ' || o.owner || '.' || o.INDEX_NAME || ' rebuild tablespace users2; '
from dba_indexes o
where o.TABLESPACE_NAME = 'USERS'

then run the last two queries changed for the original users TS in order table, index to move them back (OR simply rename the new

tablespace to the old one if You checked aand it contains nothing).
After that You can do the datafile resize if it is necessary.

As a last step You should check the SPs, packages and functions for invalidity.


==============================================================

If YES, when you drop a table, its goes to "recyclebin" for "flash-back transactions", then you need to PURGE the table from recyclebin.

to list tables on recyclebin:
select object_name from recyclebin;

to clear users area(ALL objects on recyclebin):
PURGE RECYCLEBIN; (need sysdba privs)

to clear only a table:
PURGE TABLE TABLE_NAME;

==============================================================


To resize a datafile, you need to free the last blocks on the file, lik this:
A=table A
B=Table B
C=Index C
F=Free space
Datafile_blocks= AAABBBBBAABBCFFFFCCB

Droping table "A" you get:
FFFBBBBBFFBBCFFFFCCB

Then you get free space on Database, but not on Filesystem.

Moving segments "C" and "B" to another tablespace or to first blocks you can resize de datafile.

use this scritp to MAP the datafile segmets:
select file_id, block_id first_block, block_id+blocks-1 last_block,substr(segment_name,1,20) SegName
from dba_extents
where tablespace_name = 'USR' /*tablespace name*/
and file_id=5 /*id of datafile, see on dba_data_files table*/
union all
select file_id, block_id, block_id+blocks-1, 'FREE'
from dba_free_space
where tablespace_name = 'USR'
and file_id= /*Id of datafile*/
order by file_id, first_block

==============================================================

Primary key Constraints Enable

SQL> alter table CLJOBB disable constraint CLJOBB;

associated index (CLJOBB) will be dropped automatically.



SQL> alter table CLJOBB enable constraint CLJOBB;
alter table CLJOBB enable constraint CLJOBB
*
ERROR at line 1:
ORA-02437: cannot validate (LDBO.CLJOBB) - primary key violated




Enabling of the PK constraint requires association with index.
If we now try to enable the PK constraint again, it will pick up the first index it found on that column and will get associated with it. In case there is no index to get associated, oracle will create a new index with the name same as that of PK constraint.



CREATE UNIQUE INDEX "LDBO"."CLJOBB" ON "LDBO"."CLJOBB" ("FIRMNUMBER", "NFINANCIALYEAR", "CODE", "EXCHANGE", "BOOKCODE", "DVALIDUPTO", "NPRODUCTCODE") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 104857600 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "INDX" ;



ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

CREATE INDEX "LDBO"."CLJOBB" ON "LDBO"."CLJOBB" ("FIRMNUMBER", "NFINANCIALYEAR", "CODE", "EXCHANGE", "BOOKCODE", "DVALIDUPTO", "NPRODUCTCODE") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 104857600 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "INDX" ;






SQL> alter table CLJOBB enable constraint CLJOBB;
alter table CLJOBB enable constraint CLJOBB
*
ERROR at line 1:
ORA-02437: cannot validate (LDBO.CLJOBB) - primary key violated


SQL> alter table CLJOBB MODIFY CONSTRAINTS CLJOBB ENABLE noVALIDATE;

Table altered.

SQL>


alter table CLJOBB enable VALIDATE primary key;

ERROR at line 1:
ORA-02437: cannot validate (LDBO.CLJOBB) - primary key violated

Cause: attempted to validate a primary key with duplicate values or null values.
Action: remove the duplicates and null values before enabling a primary key.


-----------Primary Key- Firmnumber,Nfinancialyear,Code,Exchange,Bookcode,Dvalidupto,Nproductcode-----


Select A.Firmnumber, A.Nfinancialyear, A.Code, A.Exchange, A.Bookcode, A.Dvalidupto, A.Nproductcode From Cljobb A
Where Rowid > ( Select Min(Rowid) From Cljobb B
Where A.Firmnumber=B.Firmnumber And A.Nfinancialyear=B.Nfinancialyear And A.Code=B.Code And A.Exchange=B.Exchange
and A.Bookcode=b.Bookcode and a.Dvalidupto=b.Dvalidupto and A.Nproductcode=b.Nproductcode
);

BRANCH HO roles

GRANT BRANCHCHANGE TO USER;
GRANT BRANCHMASTER TO USER;
GRANT BRANCHCHANGEREQUEST TO USER;
GRANT EXECUTE ON SP_BRANCHMASTER TO USER;

LD YEP YEAR END DATABASE CREATION 2011-12

LD YEAR END DATABASE CREATION FY 1112


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 SGA_TARGET=1521M;
ALTER SYSTEM SET pga_aggregate_target=1200M;
ALTER SYSTEM SET SGA_MAX_SIZE=1521MB SCOPE=SFILE;



---FY11-12----

ALTER SYSTEM SET SGA_TARGET=8152M;

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 CLIENTSCANNEDIMAGE;

impdp ldbo/ldbo@ari1112srv DIRECTORY=dpump_dir1 DUMPFILE=SCANTABLE1011.dmp TABLES=CLIENTSCANNEDIMAGE

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----
@echo off
expdp ldbo/ldbo@ari1112srv directory=dpump_dir1 dumpfile=ari1112.DMP schemas=ldbo LOGFILE=ari1112.LOG EXCLUDE=TABLE:\"LIKE \'%SCANNEDIMAGE%\'\"

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




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');



STEP 21:

PROCEDURE splogininformation
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;
/

Premium Brokerage Update

CLient Master > Der+Gen VI > option brokerage on >

UPDATE Cldetail SET Cderbroktype='P' ,CDERSBROKTYPE='P' WHERE FIRMNUMBER='ACML-00001' AND oowncode= '952000001';

Constraint Check

SPOOL C:\CONS.TXT

select 'select '||cc.column_name-
||' from '||c.owner||'.'||c.table_name-
||' a where not exists (select ''x'' from '-
||r.owner||'.'||r.table_name-
||' where '||rc.column_name||' = a.'||cc.column_name||')'
from dba_constraints c,
dba_constraints r,
dba_cons_columns cc,
dba_cons_columns rc
where c.constraint_type = 'R'
and c.owner not in ('SYS','SYSTEM')
and c.r_owner = r.owner
and c.owner = cc.owner
and r.owner = rc.owner
and c.constraint_name = cc.constraint_name
and r.constraint_name = rc.constraint_name
and c.r_constraint_name = r.constraint_name
and cc.position = rc.position
and c.owner = 'LDBO'
and c.table_name = 'CEXIST'
and c.constraint_name = 'CEXISTBOOK';


SPOOL OFF

Check Unused Space

SQL> set serveroutput on

SQL> set pages 1000

SQL> set lines 160

SQL> DECLARE

2 alc_bks NUMBER;

3 alc_bts NUMBER;

4 unsd_bks NUMBER;

5 unsd_bts NUMBER;

6 luefi NUMBER;

7 luebi NUMBER;

8 lub NUMBER;

9 BEGIN

10 DBMS_SPACE.UNUSED_SPACE (

11 segment_owner => 'RNCRY'

12 , segment_name => 'COMS'

13 , segment_type => 'TABLE'

14 , total_blocks => alc_bks

15 , total_bytes => alc_bts

16 , unused_blocks => unsd_bks

17 , unused_bytes => unsd_bts

18 , last_used_extent_file_id => luefi

19 , last_used_extent_block_id => luebi

20 , last_used_block => lub

21 );

22

23 DBMS_OUTPUT.PUT_LINE('Allocated space = '|| alc_bts );

24 DBMS_OUTPUT.PUT_LINE('Actual used space = '|| unsd_bts );

25 EXCEPTION

26 WHEN OTHERS THEN

27 DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,250));

28 END;

29 /

Allocated space = 8534360064

Actual used space = 46874624



PL/SQL procedure successfully completed.