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";
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';
Cheque is expired after 6 months 31 Mar 2011 20110331 update cashmain set bankreco=20110331 where firmnumber='ACML-00001' AND CBNUMBER= 'PYAUTOP1285007';
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.
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.
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
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.
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.
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 );
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.
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)
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
-----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;
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;
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;
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; /
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';
Alter table CEXIST DISABLE constraint CEXISTBROKBOOK;
alter table CEXIST disable constraint CEXISTBOOK; alter table CEXIST ENABLE constraint CK_COMPANYCATEGORY alter table CEXIST ENABLE constraint CEXISTPRIMARY ; alter table CEXIST ENABLE constraint COMPANYEXIST ;
SQL> alter table CEXIST ENABLE constraint CEXISTBOOK ; alter table CEXIST ENABLE constraint CEXISTBOOK * ERROR at line 1: ORA-02298: cannot validate (LDBO.CEXISTBOOK) - parent keys not found
alter table CEXIST MODIFY CONSTRAINTS CEXISTBOOK ENABLE VALIDATE;
ERROR at line 1: ORA-02298: cannot validate (LDBO.CEXISTBOOK) - parent keys not found
alter table CEXIST MODIFY CONSTRAINTS CEXISTBOOK ENABLE NOVALIDATE;
Constraint stats Here are the four type of constraint stats. These four constraint stats are applicable for all type of constraints(primary key, foreign key, check etc).
ENABLE VALIDATE is same as ENABLE. Constraint validate the data as soon as we entered in the table.
ENABLE NOVALIDATE is not same as ENABLE. Constraint validates the new data or modified data. It would not validate the existing data in table.
DISABLE NOVALIDATE is the same as DISABLE. The constraint is not checked so data may violate the constraint.
DISABLE VALIDATE means the constraint is not checked but disallows any modification of the constrained columns.
Note : Couple of things needs to be noted down here.
1. Converting NOVALIDATE constraint to VALIDATE would take longer time, depends on how big the data in the table. Although conversion in the other direction is not an issue
2. Disabling primary key constraint will drop the index associated with primary key. Again, when we enable the primary key constraint, it will create the index on the primary key column.
What is the ideal place to use ENABLE NOVALIDATE option?
In a busy environment, some one disabled the constraint accidently or intentionally, and we have already bad data in that table. Now business requested you to load the new set of data, but business wanted to make sure that new set of data should be validated during the load. At this circumstances, we can use ENABLE NOVALIDATE option. This option will validate the new data and old data will not be validated.
What is the ideal place to use DISABLE VALIDATE option?
We disabled the constraint for some reason. We do not want to load any data until we fix the issue and enable the constraint. We can use DISABLE VALIDATE option here. This option would not let you load any data when the constraint is disabled.
LD duplicate transaction number at time of billing
[Microsoft][ODBC driver for Oracle][Oracle]ORA-20014: Bill Financial Posting cannot be Run. ~-1~ORA-00001: unique constraint (LDBO.PK_BILLPROCPRIM) violated~ ORA-06512: at "LDBO.SP_CASHJOBFINPOST", li
Not enough storage is available to process this command
If you recieve multiple storage alerts within the event log, the below procedure should resolve.
1. Click on Start > Run > regedit and click OK 2. Locate HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\ LanmanServer\Parameters 3. Locate IRPStackSize. If this value does not exist, right click on Parameters key and Click on New > Dword Value and type in IRPStackSize under the name. 5. The name of the value must be exactly the same as the one in step 3. (Case sensitive) 6. Right click on IRPStackSize and click on modify 7. Select decimal and enter a value higher than 15 (Maximum Value is 50 decimal) 8. Click Ok 9. Exit from registry editor and restart your computer
Before processing for New Year financial process for ASP. We have to log into the local LD and follow the following link- o General Utilities-> General Utilities->Wan Enable o Select Firm, select the New Financial Year, Tick on Wan Enable & click on save button. o User has to do for all firm for which New Financial Year has to be change. These are following points which have to be done for the New Year financial Process for ASP o Click Start->Run->CMD 1) Stop the IIS Server by typing the following command – iisreset/stop o Click on the Control Panel -> Administrative Tools-> Component Services-> Select all components-> Right click and select Shut Down. o Copy the following files from m:\LD\sysuser folder system.*, setup.*, esetup.*, excode.*, directory.* ,firm.* to asp server i.e. (d:\LD\sysuser) o After copying the file Go to Fox Pro write the following command Set excl off use d:/ld/sysuser/directry.DBF Brow Change the Data_Drive (i.e. Drive letter for ld server) Change the Lwanenable =”T” (for the new firm if it is not been viewed at Branch level.) Clos all o Go to cmd -> type the following command – iisreset/start If there is problem in Pre-Printed file then do the following Points o Go Visual Foxpro and type the following text Set excl off Rest from m:\(firmfolder)\ldvar.mem Display memo _LDDDRIVE = ‘D:’ (Where the ld is stored on LD Server) _LDDRIVE = ‘D:’ (Where the ld is stored on LD Server Save all like *.* to m:\(firmfolder)\ldvar.mem Clea Close all
--------------------------------------------- Issue ROLES TO GRANTED CONTAIN EXTRA PRIVILEGES
REASON:BRANCH HAVE STANDARD VERSION ------revoke_std_ver.sql 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 ;
Create Role ClientPassEdit ; Grant update on Accountemaildetail to ClientPassEdit ; Grant clientpassedit to cllvl;
OTHERWISE
Technical Information (for support personnel)
Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC driver for Oracle][Oracle]ORA-01031: insufficient privileges /clientlevel/clientaccess/default.asp, line 257
Browser Type: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729)
Page: POST 334 bytes to /clientlevel/clientaccess/default.asp
make entry in tnsnames.ora otherwise ECN is not be accessible from clientlevel
1) export clientscannedimage table 2) drop table clientscannedimage; ---other master transmission take verc much time.. 3) impdb later 4) odbc connection 5) Create Firms (Financial Period) 6) Login to new year , run update the package , Reindex 7) Master Transmission 8) check sql 9) Transfer B/F transcation (No Delivery) SELECT * FROM ESETTLE; CHECK FROM PREVIOUS YEAR EXPORT FROM FY1011 30/03/11 BSE 2010254 SETTLE TO 2011001 31/03/11 BSE 2011001 SETTLE TO 2011002 01/04/11 BSE 2011002 SETTLE TO 2011003
30/03/11 NSE 2011060 SETTLE TO 2011061 31/03/11 NSE 2011061 SETTLE TO 2011062 01/04/11 NSE 2011062 SETTLE TO 2011063
01/04/11 NSEF 110401 SETTLE TO 110401
10) CREATE ABOVE SETTLEMENT INTO FY1112 IMPORT LIKE EXPORT SAME AS ABOVE
11) BEFORE DELETING FOLLOWINGS, TAKE BACKUP OF BILL SUMMARY AND TRANSACTION BOOK 12) DELETE BILL POSTING FOR ABOVE SEGMENTS AND SETTLEMENTS IN FY 1011 13) DELETE DAY DATA FOR ABOVE SEGMENTS AND SETTLEMENTS IN FY 1011
14) Transfer Demat Balance
SELECT * FROM ESETTLE; CHECK FROM PREVIOUS YEAR create zero 0 settlement in settlement master 01/04/2011 Login into FY 1112 do direct transmission
Note: if there is error in direct transmission then use export and import Note: there is some difference of INSIN in previous and new year, just update isin to isin master in both the years. Nobody can do anything wrong, change date in setup parameter to 31/03/2011
select * from demat;
15) Transfer Financial balances Create Profit loss account in in FY 1112 setup account option same like previous year FY1011 in FY1112, using direct transmission, Transfer Financial balances
select * from tblopeningbalance;
Note: this process will continue till some days.
Delete opening balances daily and run this option using internal code tick
if error in code then select * from ldfibs where oowncode=&code; in both years, internal code and oowncode should be in both firm and
same. it not same take backup and do it same and revert. internal code is same in all table for specific oowncode.
16) Margin Transfer will be same like point 9
17) Transfer Security files
18) Bill posting of point 9 settlement into new FY1112
19) Transfer Portfolio positions(global net position) to NEW Financial period (use it from old FY 1011)
SELECT * FROM ESETTLE; CHECK FROM PREVIOUS YEAR
Create Book type Opng. Stock
create 1 number settlement in in FY1112 in egroup Opening stock(OK)
please check dates in setup parameter – Global VI Note: It will take 1-2 days select * from sauda where egroup='OK';
20) Collateral Details select * from collateral;
21) Unreconciled Bank Entries
Problems: 1) constraints is voilated then disable it 2) trigger is voilated then disable it 3) datatype is not null, size, others 4) sp is missing 5) CPU usage is 100% then analyze schema.... 6) ORA-00600: internal error code, arguments: [15735], [2244], [2152], [], [], [], [], [] 7) roles exceed problem in ASP standard version, revoke roles from firmpermission and remoteuser 8) if direct transmission will not work or show some error then use export from oldFY and import into newFY ROLES TO GRANTED CONTAIN EXTRA PRIVILEGES
REASON:BRANCH HAVE STANDARD VERSION ------revoke_std_ver.sql
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 ;
8) for ld digital, create directory same as previous year. SELECT * FROM DBA_DIRECTORIES; CREATE DIRECTORY LDDIGITAL AS 'd:\ldoutput\Lddigital'; grant read, write on directory LDDIGITAL to ldbo;
9) for clientlevel, grant role which is exist in clienlevel folder Create Role ClientPassEdit ; Grant update on Accountemaildetail to ClientPassEdit ; Grant clientpassedit to cllvl;
make entry in tnsnames.ora otherwise ECN is not be accessible from clientlevel
DIG1112SRV = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.84)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ARI1112) ) ) 10) backoffice code and branch change should be done only after financial balance carry process will be finished in somedays.
alter system set sga_max_size=8192M scope=spfile; alter system set parallel_execution_message_size=4096 scope=spfile;
AFTER ALL PROCESS, RESTART SERVER & ANALYZE ARE MUST OTHERWISE CPU USAGE WILL BE 100%
alter system set parallel_execution_message_size=4096 scope=spfile; shut immediate startup
On most platforms, the default value is 2148 bytes if parallel_automatic_tuning is set to FALSE, and 4096 bytes if parallel_automatic_tuning = TRUE. The default value is adequate for most applications. Larger values require a larger shared pool. Larger values result in better performance at the cost of higher memory use. For this reason, replication gets no benefit from increasing the size.
