Recover Database with allow corruption

Here is an example of block corruption faced during database recovery:

recover database;

RA-00600: internal error code, arguments: [3020], [48], [41103361], [41103361], [], [], [], [], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 48, block# 41103361)
ORA-10564: tablespace MYTS
ORA-01110: data file 48: '+MYDISC/mydb/datafile/myts.14714.699899641'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 323837
Errors in file /mytracepath/mydb_pr06_4728.trc:

You can skip recovery of this corrupted block by running following command:

recover database datafile '+MYDISC/mydb/datafile/myts.14714.699899641' allow 1 corruption;

If there are more then 1 corruption, you may repeat this step or run command with allow many corruption. However, dont forget to note corrupted file and block numbers. After opening database, we will use following query to find out to which segment corrupted block belogns to:

SELECT tablespace_name, segment_type, owner, segment_name

FROM dba_extents WHERE file_id = 48 and 41103361 between block_id AND block_id + blocks - 1;

generate move rebuild script to migrate partitioned tables from one tablespace to other

declare
cflag number;
oldDataTablespaceName varchar2(100);
newDataTablespaceName varchar2(100);
oldIndexTablespaceName varchar2(100);
newIndexTablespaceName varchar2(100);
parallel_level varchar2(2);
begin
DBMS_OUTPUT.ENABLE(1000000);
-------------------- SET VARIABLES ----------------------------
oldDataTablespaceName:=OLD_TABLESPACE';
newDataTablespaceName:='NEW_TABLESPACE';
newIndexTablespaceName:='NEW_INDEX_TABLESPACE';
parallel_level:='8';
---------------------------------------------------------------
dbms_output.put_line('alter session enable parallel ddl;');
for l in ( select table_owner, table_name, partition_name, partition_position from dba_tab_partitions where tablespace_name=oldDataTablespaceName order by table_owner, table_name, partition_position )
loop
cflag := 0;
dbms_output.put_line('alter table '||l.table_owner||'.'||l.table_name||' move partition "'||l.partition_name||'" tablespace '||newDataTablespaceName||' parallel '||parallel_level||';');
for k in (select dip.index_owner, dip.index_name, dip.partition_name from dba_ind_partitions dip, dba_indexes di where di.table_owner=l.table_owner and di.table_name=l.table_name and di.owner=dip.index_owner and di.index_name=dip.index_name and dip.partition_position=l.partition_position)
loop
dbms_output.put_line('alter index '||k.index_owner||'.'||k.index_name||' rebuild partition "'||k.partition_name||'" tablespace '||newIndexTablespaceName||' parallel '||parallel_level||';');
end loop;
end loop;
END;
/

Reason: Archivelog Generation Huge

There are many reasons for it and need to check out.

>Heavy DML statements.

>DML with Lobs and xml datatypes.

>Investigate the SQL by awr report.

> check the size of the redolog file it might be small.

> how many time log switch in an hour.

> there is specific time of archive log generations.

Resizing Recreating RedoLogs / Increase Redo log / Archivelog generation fast size

--------------------------------------------final steps--------------------------

select group#, status from v$log;

ALTER SYSTEM CHECKPOINT GLOBAL;

select group#, status from v$log;

alter database drop logfile group 1;

alter database add logfile group 1 ('F:\NBSD1112\REDO01.LOG') size 200M reuse ;

alter system switch logfile;

alter system switch logfile;

select group#, status from v$log;

1)

SELECT a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;

Make the last redo log CURRENT

select group#, status from v$log;

alter system switch logfile;

select group#, status from v$log;

ALTER SYSTEM CHECKPOINT GLOBAL;

ALTER DATABASE DROP LOGFILE GROUP 1;

2) Re-create dropped online redo log group

alter database add logfile group 1 ('F:\NBSD1112\REDO01.LOG' ) size 200m reuse;

3)

select group#, status from v$log;

GROUP# STATUS

---------- ----------------

1 UNUSED

2 INACTIVE

3 CURRENT

Force another log switch

alter system switch logfile;

select group#, status from v$log;

GROUP# STATUS

---------- ----------------

1 CURRENT

2 INACTIVE

3 ACTIVE

4)

Loop back to Step 2 until all logs are rebuilt

alter database add logfile group 2 ('F:\NBSD1112\REDO02.LOG' ) size 200m reuse;

-----------------------------------SECOND METHOD-------------------

SELECT a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;

GROUP# MEMBER BYTES

1 F:\NBSD1112\REDO01.LOG 52428800

2 F:\NBSD1112\REDO02.LOG 52428800

3 F:\NBSD1112\REDO03.LOG 52428800

Here is how i changed this to five 200M redo logs:

SQL> alter database add logfile group 4 ('F:\NBSD1112\REDO04.LOG') size 200M;

SQL> alter database add logfile group 5 ('F:\NBSD1112\REDO05.LOG') size 200M;

while running following sql commands, if you hit an error like this:

ORA-01623: log 3 is current log for instance RPTDB (thread 1) - cannot drop

ORA-00312: online log 3 thread 1: 'F:\NBSD1112\REDO03.LOG'

you should run " alter system switch logfile;" until current log is 4 or 5.

Then execute "alter system checkpoint;"

SQL> alter database drop logfile group 1;

SQL> alter database drop logfile group 2;

SQL> alter database drop logfile group 3;

then move (or maybe drop) old redo logs

RENAME F:\NBSD1112\REDO01.LOG F:\NBSD1112\REDO01_OLD.LOG

RENAME F:\NBSD1112\REDO02.LOG F:\NBSD1112\REDO02_OLD.LOG

RENAME F:\NBSD1112\REDO03.LOG F:\NBSD1112\REDO03_OLD.LOG

finally

SQL> alter database add logfile group 1 ('F:\NBSD1112\REDO01.LOG') size 200M;

SQL> alter database add logfile group 2 ('F:\NBSD1112\REDO02.LOG') size 200M;

SQL> alter database add logfile group 3 ('F:\NBSD1112\REDO03.LOG') size 200M;

Archivelog Frequency

A script to check the frequency of log switches

col MidN format 999

col 1AM format 999

col 2AM format 999

col 3AM format 999

col 4AM format 999

col 5AM format 999

col 6AM format 999

col 7AM format 999

col 8AM format 999

col 9AM format 999

col 10AM format 999

col 11AM format 999

col Noon format 999

col 1PM format 999

col 2PM format 999

col 3PM format 999

col 4PM format 999

col 5PM format 999

col 6PM format 999

col 7PM format 999

col 8PM format 999

col 9PM format 999

col 10PM format 999

col 11PM format 999

select to_char(first_time,'mm/dd/yy') logdate,

sum(decode(to_char(first_time,'hh24'),'00',1,0)) "MidN",

sum(decode(to_char(first_time,'hh24'),'01',1,0)) "1AM",

sum(decode(to_char(first_time,'hh24'),'02',1,0)) "2AM",

sum(decode(to_char(first_time,'hh24'),'03',1,0)) "3AM",

sum(decode(to_char(first_time,'hh24'),'04',1,0)) "4AM",

sum(decode(to_char(first_time,'hh24'),'05',1,0)) "5AM",

sum(decode(to_char(first_time,'hh24'),'06',1,0)) "6AM",

sum(decode(to_char(first_time,'hh24'),'07',1,0)) "7AM",

sum(decode(to_char(first_time,'hh24'),'08',1,0)) "8AM",

sum(decode(to_char(first_time,'hh24'),'09',1,0)) "9AM",

sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10AM",

sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11AM",

sum(decode(to_char(first_time,'hh24'),'12',1,0)) "Noon",

sum(decode(to_char(first_time,'hh24'),'13',1,0)) "1PM",

sum(decode(to_char(first_time,'hh24'),'14',1,0)) "2PM",

sum(decode(to_char(first_time,'hh24'),'15',1,0)) "3PM",

sum(decode(to_char(first_time,'hh24'),'16',1,0)) "4PM",

sum(decode(to_char(first_time,'hh24'),'17',1,0)) "5PM",

sum(decode(to_char(first_time,'hh24'),'18',1,0)) "6PM",

sum(decode(to_char(first_time,'hh24'),'19',1,0)) "7PM",

sum(decode(to_char(first_time,'hh24'),'20',1,0)) "8PM",

sum(decode(to_char(first_time,'hh24'),'21',1,0)) "9PM",

sum(decode(to_char(first_time,'hh24'),'22',1,0)) "10PM",

sum(decode(to_char(first_time,'hh24'),'23',1,0)) "11PM"

from v$log_history

group by to_char(first_time,'mm/dd/yy')

order by 1

/

sqlserver to oracle 11g connection

-----------odbcad32---------------------

datasource name (DSN) : dg4odbc

description: dg4odbc

sqlserver : 172.168.0.6

database: msajag

username: lduser

password: kshitij

test connection successfully

--------------------oracle------

Select * from v$parameter where name like 'global_names%';

alter system set global_names=false scope = both;

create public database link lnk_rkserver connect to lduser identified by kshitij using 'dg4odbc';

if ora 28500 error then username, password in double quotes

create public database link lnk_rkserver connect to "lduser" identified by "kshitij" using 'dg4odbc';

---D:\app\Administrator\product\11.2.0\dbhome_1\hs\admin\initdg4odbc.ora------

HS_FDS_CONNECT_INFO = dg4odbc

HS_FDS_TRACE_LEVEL = off

-----------listener-----------

(SID_DESC=

(SID_NAME=dg4odbc)

(ORACLE_HOME=D:\app\Administrator\product\11.2.0\dbhome_1)

(PROGRAM=dg4odbc)

)

-----------------tnsnames.ora-------------

dg4odbc =

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=172.168.0.11)(PORT=1521))

(CONNECT_DATA=(SID=dg4odbc))

(HS=OK)

)

EXPLAIN PLAN

SQL> explain plan for

2 SELECT * FROM TBLBANKDETAIL;

Explained.

SQL> set linesize 160

SQL> set pages 300

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------------------------------

Plan hash value: 3594628191

-----------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 100K| 10M| 253 (1)| 00:00:04 |

| 1 | TABLE ACCESS FULL| TBLBANKDETAIL | 100K| 10M| 253 (1)| 00:00:04 |

-----------------------------------------------------------------------------------

8 rows selected.

SQL>

Ora-29257 UTL_INADDR.get_host_address

Ora-29257 When Trying to Use UTL_INADDR to Get IP Address or Host Name of a Server

Problem Description

When using either the get_host_address() or get_host_name() function of the UTL_INADDR package to get host information, an ORA-29257 error is returned like below:

ORA-29257: host notebook unknown

ORA-06512: at "SYS.UTL_INADDR", line 19

ORA-06512: at "SYS.UTL_INADDR", line 40

For example while installation Audit Vault Agent it fails with following error:

Error1: SQL Error: ORA-29257: host notebook unknown

ORA-06512: at "SYS.UTL_INADDR", line 19

ORA-06512: at "SYS.UTL_INADDR", line 40

ORA-06512: at "AVSYS.DBMS_AUDIT_VAULT_AGENT_INSTALL" line 55

ORA-06512: at line 1

Cause of the Problem

The functions in the UTL_INADDR package are essentially using the 'nslookup' command behind the scenes and the nslookup command uses the specified dns server to get the information. If you test the nslookup command and it fails then there is a configuration issue on the host machine and it has nothing to do with the the Oracle Database server. On unix machines, the /etc/resolv.conf identifies the DNS server to be used.

Solution of the Problem

Modify the /etc/resolv.conf to configure the DNS server resolution and once nslookup works, so will the UTL_INADDR package. Note that you must have DNS server in this case and the corresponding hostname must resolve DNS.

If you don't have DNS server you can use /etc/hosts file and add the entry of the host and IP address which is unknown.

Index DeFragmentation / Rebuild / Index Performance Tuning

Index Defragmentation Steps

Analyzing statistics

Validating the index

Checking PCT_USED

Dropping and rebuilding (or coalescing) the index

-----The following INDEX_STATS columns are especially useful:

height----- refers to the maximum number of levels encountered within the index.

lf_rows------- refers to the total number of leafs nodes in the index.

del_lf_rows --------refers to the number of leaf rows that have been marked deleted as a result of table DELETEs.

SQL>analyze index INDEX_NAME validate structure;

Then query INDEX_STATS view

1. If del_lf_rows/lf_rows is > .2 then index should be rebuild.

2. If height is 4 then index should be rebuild.

3. If lf_rows is lower than lf_blks then index should be rebuild.

----------validate index------

spool c:\index_validate.sql

select 'Analyze Index '||index_name||' validate structure;' from user_indexes;

spool off

@c:\index_validate.sql

-------------following query should be run after all analyze statement

Select Name,(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 as index_usage From index_stats Where LF_ROWS_LEN!=0 order by Name ;

select DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS) PCT_DELETED,(LF_ROWS-DISTINCT_KEYS)*100/ decode(LF_ROWS,0,1,LF_ROWS) DISTINCTIVENESS

from index_stats;

If the PCT_DELETED is 20% or higher, the index is candidate for rebuilding

-------following scripts validate the indexes and find to rebuild and rebuild them-------

set serveroutput on size 100000

DECLARE

vOwner dba_indexes.owner%TYPE; /* Index Owner */

vIdxName dba_indexes.index_name%TYPE; /* Index Name */

vAnalyze VARCHAR2(100); /* String of Analyze Stmt */

vCursor NUMBER; /* DBMS_SQL cursor */

vNumRows INTEGER; /* DBMS_SQL return rows */

vHeight index_stats.height%TYPE; /* Height of index tree */

vLfRows index_stats.lf_rows%TYPE; /* Index Leaf Rows */

vDLfRows index_stats.del_lf_rows%TYPE; /* Deleted Leaf Rows */

vDLfPerc NUMBER; /* Del lf Percentage */

vMaxHeight NUMBER; /* Max tree height */

vMaxDel NUMBER; /* Max del lf percentage */

CURSOR cGetIdx IS SELECT owner,index_name

FROM dba_indexes WHERE OWNER NOT LIKE 'SYS%';

BEGIN

/* Define maximums. This section can be customized. */

vMaxHeight := 3;

vMaxDel := 20;

/* For every index, validate structure */

OPEN cGetIdx;

LOOP

FETCH cGetIdx INTO vOwner,vIdxName;

EXIT WHEN cGetIdx%NOTFOUND;

/* Open DBMS_SQL cursor */

vCursor := DBMS_SQL.OPEN_CURSOR;

/* Set up dynamic string to validate structure */

vAnalyze := 'ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE';

DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.V7);

vNumRows := DBMS_SQL.EXECUTE(vCursor);

/* Close DBMS_SQL cursor */

DBMS_SQL.CLOSE_CURSOR(vCursor);

/* Does index need rebuilding? */

/* If so, then generate command */

SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows

FROM INDEX_STATS;

IF vDLfRows = 0 THEN /* handle case where div by zero */

vDLfPerc := 0;

ELSE

vDLfPerc := (vDLfRows / vLfRows) * 100;

END IF;

IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN

DBMS_OUTPUT.PUT_LINE('ALTER INDEX ' || vOwner || '.' || vIdxName || ' REBUILD;');

END IF;

END LOOP;

CLOSE cGetIdx;

END;

/

win error error 0x80070522

This issue might occur permissions are not set properly for the C Drive.

Make sure that you are logged in as Administrator.

To take ownership of C Drive, follow these steps:

1. Right-click the C Drive and then click Properties.

2. Click the Security tab.

3. Click Advanced, and then click the Owner tab.

4. In the Name list, click your user name, or click Administrator if you are logged in as Administrator, or click the Administrators group.

5. Click on Edit and then put a check mark against "Replace all existing inheritable permissions on all descendants with inheritable permissions from this object".

6. Click OK, and then click Yes when you receive the following message:

This will replace explicitly defined permissions on all descendants of this object with inheritable permissions from C-Drive (C:).

Do you wish to continue?

7. Once the permissions are replaced, click on Ok.

Restart computer

8. Check if you are able to copy, paste or delete any documents now.

If the above steps fail then you may also want to disable UAC and check if that helps.

1. Click Start, type msconfig in the start search box, and then press Enter.

If you are prompted for an administrator password or for a confirmation, type the password, or click Continue.

2. Click the Tools tab.

3. Click Disable UAC and then click Launch.

4. A command window appears, and shortly thereafter, a notification bubble appears informing you that UAC is disabled.

5. Click Ok and restart your computer to apply the change.

tns 12535 operation timed out

ping serverip

tnsping serverip

telnet serverip 1521

solution

allow port 1521 on server

ORA-24005 must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

I was getting this error when I was trying to drop a schema .

Sql> drop user test cascade;

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

Logged in as Test user and checked for queue tables .

SQL> select * from user_queue_tables;

no rows selected

SQL> select OWNER, NAME, QUEUE_TABLE, ENQUEUE_ENABLED, DEQUEUE_ENABLED

from DBA_QUEUES where OWNER='TEST';

no rows selected

However I was able to locate few queue tables in the schema when I used

Sql> select table_name from user_tables;

Got few Tables starting with AQ$_ *******

Tried to delete these tables using the DBMS_AQADM.DROP_QUEUE_TABLE procedure . However ended with the following error message .

SQL> begin

2 DBMS_AQADM.DROP_QUEUE_TABLE(‘AQ$_test');

3 end;

4

5 /

begin

*

ERROR at line 1:

ORA-24002: QUEUE_TABLE SCOTT.AQ$_test does not exist

ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4084

ORA-06512: at "SYS.DBMS_AQADM", line 197

ORA-06512: at line 2

Working solution :

Logged in as sys and issued the following command :

alter session set events '10851 trace name context forever, level 2';

and then dropped all the AQ$.***** tables from sys successfully.

Finally dropped the particular schema .

Buffer Cache Size

High CPU_COUNT and increased granule size can cause ORA-0431 error.

------CPU_COUNT specifies the number of CPUs available to Oracle. On single-CPU computers, the value of CPU_COUNT is 1.

Memory sizing depends on CPU_COUNT (No of processor groups).

Please use below formulas to calculate min buffer cache size

--Minimum Buffer Cache Size

10g : max(CPU_COUNT) * max(Granule size)

11g : max(4MB * CPU_COUNT)

Please note that If SGA_MAX_SIZE < 1GB then use Granule size = 4mb, SGA_MAX_SIZE > 1G then use Granule size = 8MB.

-- _PARALLEL_MIN_MESSAGE_POOL Size

If PARALLEL_AUTOMATIC_TUNING =TRUE then large pool is used for this area otherwise shared pool is used.

CPU_COUNT*PARALLEL_MAX_SERVERS*1.5*(OS msg bufferr size) OR CPU_COUNT*5*1.5*(OS message size)

-- Add extra 2MB per CPU_COUNT for shared pool.

Here is the example:-

Sun Solaris server has threaded CPUs. 2 physical CPUs has 8 cores, and each core has 8 threads, then Oracle evaluates CPU_COUNT = 2*8*8=128.

When SGA_MAX_SIZE=900MB,

Minimum Buffer Cache = CPU_COUNT *Granule size = 128*4M = 512MB

Shared Pool can use 338MB

When SGA_MAX_SIZE=1200MB,

Minimum Buffer Cache = CPU_COUNT *Granule size = 128*8M = 1024MB

Shared Pool can use 176 MB, so ORA-4031 occurs despite larger SGA_MAX_SIZE.

You need to manually tune CPU_COUNT parameter to resolve this error.

ORA-02049: timeout: distributed transaction waiting for lock

ORA-02049: timeout: distributed transaction waiting for lock

simoutaneously transactions

Make sure that there were no any activities on the remote database

1) select * from v$parameter where upper(name)='DISTRIBUTED_LOCK_TIMEOUT'; ----60 to 300sec

alter system set distributed_lock_timeout=300 scope=spfile;

shut immediate

startup

2) The connection remains open until you end your local session or until the number of database links for your session exceeds the value of the initialization parameter OPEN_LINKS. If you want to reduce the network overhead associated with keeping the link open, then use this clause to close the link explicitly if you do not plan to use it again in your session.

3) increase SHARED_POOL_SIZE

or free space

ALTER SYSTEM FLUSH SHARED_POOL;

Oracle Internal Code Error

-----------------------------

ERROR:

ORA-06553: PLS-801: internal error [56319]

ERROR:

ORA-06553: PLS-801: internal error [56319]

Error accessing package DBMS_APPLICATION_INFO

--------------

shutdown immediate;

startup migrate;

@$ORACLE_HOME/rdbms/admin/utlirp.sql

shutdown immediate;

startup

@$ORACLE_HOME/rdbms/admin/utlrp.sql

connect system/password

execute dbms_stats.delete_database_stats

execute dbms_registry_sys.validate_components

select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type;

---------------------

SQL> create table ldtest as select * from ldfibs;

create table ldtest as select * from ldfibs

*

ERROR at line 1:

ORA-00600: internal error code, arguments: [15735], [2160], [2152], [], [], [], [],

SQL>

SQL> alter system set parallel_execution_message_size = 4096 scope=spfile;

-----------------------

oracle operation table size details could not be determined

--------------------

ORA-25153: Temporary Tablespace is Empty

1)

CREATE TEMPORARY TABLESPACE temp2

TEMPFILE 'E:\SNSD1011\TEMP02.ORA' SIZE 5M REUSE

AUTOEXTEND ON NEXT 1M MAXSIZE unlimited

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

2)

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

3)

DROP TABLESPACE temporary INCLUDING CONTENTS AND DATAFILES;

4)

cREATE TEMPORARY TABLESPACE temporary

TEMPFILE 'E:\SNSD1011\TEMP01.ORA' SIZE 500M REUSE

AUTOEXTEND ON NEXT 100M MAXSIZE unlimited

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

5)

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temporary;

6)

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

7)

SELECT tablespace_name, file_name, bytes

FROM dba_temp_files WHERE tablespace_name = 'temporary';

--------------

SQL> EXEC dbms_stats.gather_schema_stats('LDBO',cascade=>TRUE);

BEGIN dbms_stats.gather_schema_stats('LDBO',cascade=>TRUE); END;

*

ERROR at line 1:

ORA-00600: internal error code, arguments: [qernsRowP], [1], [], [], [], [],

[], []

-----------------

Connectivity error: [Microsoft][ODBC driver for Oracle][Oracle]

ORA-01578: ORACLE data block corrupted (file # 1, block # 16382) ORA-01110: data file 1: 'D:\LMSD0304\SYSTEM01.ORA'

select * from V$DATABASE_BLOCK_CORRUPTION;

SELECT SEGMENT_TYPE,OWNER||'.'||SEGMENT_NAME

FROM DBA_EXTENTS

WHERE FILE_ID = 1 AND 16382 BETWEEN BLOCK_ID

AND BLOCK_ID+BLOCKS -1;

analyze table SYS.IDL_UB1$ validate structure;

BEGIN

DBMS_REPAIR.ADMIN_TABLES

(

TABLE_NAME => 'IDL_UB1$',

TABLE_TYPE => DBMS_REPAIR.REPAIR_TABLE,

ACTION => DBMS_REPAIR.CREATE_ACTION,

TABLESPACE => 'SYSTEM'

);

END;

/

10g to 11g migration

http://debaatobiee.wordpress.com/2009/10/17/step-by-step-upgrading-oracle-10g-to-oracle-11g/

expdp

http://www.dba-oracle.com/t_export_import_different_releases_of_oracle.htm

Data Pump EXPDP

expdp userid="'sys/oracle@ari1112srv as sysdba'" FULL=y directory=dpump_dir2 dumpfile=ari1112.DMP LOGFILE=ari1112.LOG



expdp ldbo/ldbo$123@ari1112srv directory=dpump_dir1 schemas=ldbo dumpfile=ari1112_tab1.DMP LOGFILE=ari1112_tab.LOG INCLUDE=TABLE:\"IN \(\'BILLCHARGESFILE2\',\'BILLINCLUSIVECHARGESFILE2\',\'CBILLFILE2\',\'DBILLFILE2\',\'FALEDDRCR2\',\'FALEDGERDESCRIPTION2\',\'FOCBILLFILE2\',\'FODBILLFILE2\',\'FOTRANSACTIONS2\',\'TRANSACTION2\'\)\"



expdp ldbo/ldbo$123@ari1112srv full=Y directory=dpump_dir1 dumpfile=ari1112_meta.DMP LOGFILE=ari1112_meta.LOG content=metadata_only EXCLUDE=TABLE:\"IN \(\'CLIENTSCANNEDIMAGE\',\'BILLCHARGESFILE\',\'BILLINCLUSIVECHARGESFILE\',\'CBILLFILE\',\'DBILLFILE\',\'FALEDDRCR\',\'FALEDGERDESCRIPTION\',\'FOCBILLFILE\',\'FODBILLFILE\',\'FOTRANSACTIONS\',\'TRANSACTIONS\',\'MV_CAPABILITIES_TABLE\',\'MV_RKCASHCLISCRIPTRANSACTION\',\'MV_RKCASHTRANSACTION\',\'MV_RKCLIENT\',\'MV_RKEXCHANGETURNOVER\',\'MV_RKFOCLISCRIPTRANSACTION\',\'MV_RKFOTRANSACTION\'\)\"



expdp ldbo/ldbo$123@ari1112srv directory=dpump_dir1 dumpfile=ari1112_package.DMP LOGFILE=ari1112_package.LOG INCLUDE=PACKAGE_BODIES,PACKAGE,PROCEDURE,FUNCTION

Email Notification for System Event

Eventtriggers /Create /ru Administrator /rp afl07839 /TR "Event Triggers for unexptected shutdown" /EID "6008" /TK d:\Email_event.vbs


Eventtriggers /Create /TR "Event Triggers for Disk Space full" /EID "2013" /TK d:\Email_event_disk.vbs


Eventtriggers /Create /ru Administrator /rp afl078391 /TR "Event Triggers for network disconnected" /EID "4202" /TK d:\Email_event.vbs

Eventtriggers /Create /ru Administrator /rp afl078391 /TR "Event Triggers for network connected" /EID "4201" /TK d:\Email_event.vbs


Eventtriggers /Create /ru Administrator /rp afl07839 /TR "Event Triggers for restart server" /EID "1074" /TK d:\Email_event.vbs


Eventtriggers /Create /ru Administrator /rp afl07839 /TR "Event Triggers for shutdown server" /EID "1076" /TK d:\Email_event.vbs


Eventtriggers /Create /ru Administrator /rp afl07839 /TR "Event Triggers for All error" /L SYSTEM /T ERROR /TK d:\Email_event_ERR.vbs


Eventtriggers /?
Eventtriggers /create /?
Eventtriggers
Eventtriggers /delete /tid 1

Eventtriggers /Create /ru Administrator /rp Afl07839 /TR "Event Logger Login" /EID "540" /TK d:\Email_event_audit.vbs



Eventtriggers /Create /ru Administrator /TR "Event iisstop" /L APPLICATION /EID "4" /TK d:\Email_event_iisstop.vbs

Eventtriggers /Create /ru Administrator /TR "Event iisstart" /L APPLICATION /EID "3" /TK d:\Email_event_iisstart.vbs

Remove partition without delete data

1)

ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1000, BR1050 INTO PARTITION BR1050 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1050, BR1100 INTO PARTITION BR1100 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1100, BR1150 INTO PARTITION BR1150 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1150, BR1200 INTO PARTITION BR1200 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1200, BR1250 INTO PARTITION BR1250 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1250, BR1300 INTO PARTITION BR1300 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1300, BR1350 INTO PARTITION BR1350 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1350, BR1400 INTO PARTITION BR1400 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1400, BR1450 INTO PARTITION BR1450 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1450, BR1500 INTO PARTITION BR1500 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1500, BR1550 INTO PARTITION BR1550 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1550, BR1600 INTO PARTITION BR1600 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1600, BR1650 INTO PARTITION BR1650 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1650, BR1700 INTO PARTITION BR1700 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1700, BR1750 INTO PARTITION BR1750 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1750, BR1800 INTO PARTITION BR1800 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1800, BR1850 INTO PARTITION BR1850 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1850, BR1900 INTO PARTITION BR1900 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1900, BR1950 INTO PARTITION BR1950 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1950, BR2000 INTO PARTITION BR2000 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2000, BR2050 INTO PARTITION BR2050 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2050, BR2100 INTO PARTITION BR2100 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2100, BR2150 INTO PARTITION BR2150 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2150, BR2200 INTO PARTITION BR2200 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2200, BR2250 INTO PARTITION BR2250 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2250, BR2300 INTO PARTITION BR2300 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2300, BR2350 INTO PARTITION BR2350 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2350, BR2400 INTO PARTITION BR2400 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2400, BR2450 INTO PARTITION BR2450 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2450, BR2500 INTO PARTITION BR2500 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2500, BR2550 INTO PARTITION BR2550 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2550, BR2600 INTO PARTITION BR2600 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2600, BR2650 INTO PARTITION BR2650 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2650, BR2700 INTO PARTITION BR2700 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2700, BR2750 INTO PARTITION BR2750 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2750, BR2800 INTO PARTITION BR2800 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2800, BR2850 INTO PARTITION BR2850 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2850, BR2900 INTO PARTITION BR2900 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2900, BR2950 INTO PARTITION BR2950 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2950, BR3000 INTO PARTITION BR3000 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR3000, BR3050 INTO PARTITION BR3050 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR3050, BR3100 INTO PARTITION BR3100 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR3100, BR3200 INTO PARTITION BR3200 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR3200, BR3250 INTO PARTITION BR3250 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR3250, BR3350 INTO PARTITION BR3350 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR3350, BR3450 INTO PARTITION BR3450 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR3450, BR3550 INTO PARTITION BR3550 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR3550, BR3650 INTO PARTITION BR3650 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR3650, BR3750 INTO PARTITION BR3750 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR3750, BR3850 INTO PARTITION BR3850 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR3850, BR3950 INTO PARTITION BR3950 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR3950, BR4000 INTO PARTITION BR4000 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR4000, BR4050 INTO PARTITION BR4050 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR4050, BR4100 INTO PARTITION BR4100 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR4100, BR4200 INTO PARTITION BR4200 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR4200, BR4250 INTO PARTITION BR4250 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR4250, BR4350 INTO PARTITION BR4350 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR4350, BR4450 INTO PARTITION BR4450 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR4450, BR4550 INTO PARTITION BR4550 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR4550, BR4650 INTO PARTITION BR4650 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR4650, BR4750 INTO PARTITION BR4750 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR4750, BR4850 INTO PARTITION BR4850 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR4850, BR4950 INTO PARTITION BR4950 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR4950, BR5050 INTO PARTITION BR5050 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR5050, BR5100 INTO PARTITION BR5100 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR5100, BR5200 INTO PARTITION BR5200 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR5200, BR5250 INTO PARTITION BR5250 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR5250, BR5350 INTO PARTITION BR5350 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR5350, BR5450 INTO PARTITION BR5450 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR5450, BR5550 INTO PARTITION BR5550 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR5550, BR5650 INTO PARTITION BR5650 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR5650, BR5750 INTO PARTITION BR5750 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR5750, BR5850 INTO PARTITION BR5850 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR5850, BR5950 INTO PARTITION BR5950 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR5950, BR6000 INTO PARTITION BR6000 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR6000, BR6250 INTO PARTITION BR6250 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR6250, BR6500 INTO PARTITION BR6500 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR6500, BR6750 INTO PARTITION BR6750 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR6750, BR7000 INTO PARTITION BR7000 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR7000, BR7250 INTO PARTITION BR7250 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR7250, BR7500 INTO PARTITION BR7500 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR7500, BR7750 INTO PARTITION BR7750 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR7750, BR8000 INTO PARTITION BR8000 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR8000, BR8250 INTO PARTITION BR8250 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR8250, BR8500 INTO PARTITION BR8500 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR8500, BR8750 INTO PARTITION BR8750 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR8750, BR9000 INTO PARTITION BR9000 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR9000, BR9250 INTO PARTITION BR9250 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR9250, BR9500 INTO PARTITION BR9500 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR9500, BRXXXX INTO PARTITION BRXXXX tablespace USR;


2)

select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where table_name='FALEDDRCR';


3)

Create table faledrcr1 as select * from faleddrcr where 1=2;

or

CREATE TABLE "LDBO"."FALEDDRCR1"
(
"FIRMNUMBER" CHAR(10 BYTE) NOT NULL ENABLE,
"OOWNCODE" CHAR(10 BYTE) NOT NULL ENABLE,
"DTOFTRAN" DATE NOT NULL ENABLE,
"VOUCHER" CHAR(14 BYTE) NOT NULL ENABLE,
"VCNUMBER" NUMBER(5,0) NOT NULL ENABLE,
"DRCR" CHAR(1 BYTE) NOT NULL ENABLE,
"DAMOUNT" NUMBER(14,2) NOT NULL ENABLE,
"CAMOUNT" NUMBER(14,2) NOT NULL ENABLE,
"ENTRYCODE" NUMBER(2,0) NOT NULL ENABLE,
"BRANCHCODE" CHAR(4 BYTE) NOT NULL ENABLE,
"FAMILYCODE" CHAR(10 BYTE),
"SUBCODE" CHAR(10 BYTE),
"EXCHANGE" CHAR(4 BYTE) NOT NULL ENABLE,
"GROUPCODE" CHAR(10 BYTE),
"SPECIAL" CHAR(1 BYTE) DEFAULT 'N'
/* Contains Y or N indicating whether it is Special Entry or Normal */
,
"BOOKTYPE" CHAR(2 BYTE) DEFAULT ' '
/* Contains the Booktype Code */
,
"VALLAN" NUMBER(7,0) DEFAULT 0
/* Contains the Vallan Number */
,
"CHEQUE" CHAR(10 BYTE) DEFAULT ' '
/* Contains the Cheque Number */
,
"BANKCODE" CHAR(10 BYTE) DEFAULT ' '
/* Contains the Bank Code */
,
"BANKRECO" NUMBER(8,0) DEFAULT 0
/* Contains the Entry on Which Bank Reco is done */
,
"NPRODUCTCODE" NUMBER(3,0) DEFAULT 0
/* Contains the Product Code on Which the Ledger Entry is based */
,
"NORACLEFINANCIAL" NUMBER(1,0) DEFAULT 0
/* Contains the Flag for E-Financial Transfer */
,
"NENTRYTYPE" NUMBER(2,0) DEFAULT 0
/* Contains the Flag for Entry Type */
,
"NSUBPRODUCTCODE" NUMBER(3,0) DEFAULT 0
/* Contains the Sub-Product code */
,
CONSTRAINT "LEDGERPRIMARY" PRIMARY KEY ("FIRMNUMBER", "OOWNCODE", "VOUCHER", "VCNUMBER") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 838860800 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "INDX" ALTER INDEX "LDBO"."LEDGERPRIMARY" UNUSABLE;
ENABLE,
CONSTRAINT "CK_LEDGERDRCR" CHECK (Drcr IN ('D','C',' ')) ENABLE,
CONSTRAINT "CK_LEDGERENTRY" CHECK (Entrycode IN (1,2,3,4,5,6,7)) ENABLE,
CONSTRAINT "CK_LEDGERAMOUNT" CHECK ((Drcr ='D' AND Damount>0 AND Camount=0) OR (Drcr='C' AND Camount>0 AND Damount=0) OR (Drcr=' ' AND Camount=0 AND Damount=0)) ENABLE NOVALIDATE,
CONSTRAINT "LEDGERCODE" FOREIGN KEY ("FIRMNUMBER", "OOWNCODE") REFERENCES "LDBO"."ACCOUNTS" ("FIRMNUMBER", "OOWNCODE") ENABLE,
CONSTRAINT "LEDGERGROUP" FOREIGN KEY ("FIRMNUMBER", "GROUPCODE") REFERENCES "LDBO"."ACCOUNTGROUP" ("FIRMNUMBER", "OOWNCODE") ENABLE,
CONSTRAINT "LEDGEREXCHANGE" FOREIGN KEY ("FIRMNUMBER", "EXCHANGE") REFERENCES "LDBO"."ALLEXCH" ("FIRMNUMBER", "CODE") ENABLE,
CONSTRAINT "LEDGERBRANCH" FOREIGN KEY ("FIRMNUMBER", "BRANCHCODE") REFERENCES "LDBO"."BRANCHHO" ("FIRMNUMBER", "BRANCHCODE") ENABLE,
CONSTRAINT "LEDGERPRODUCT" FOREIGN KEY ("FIRMNUMBER", "NPRODUCTCODE") REFERENCES "LDBO"."TBLPRODUCTINFORMATION" ("CFIRMNUMBER", "NPRODUCTCODE") ENABLE ) PCTFREE 20 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 1048576000 NEXT 1048576000 BUFFER_POOL DEFAULT) TABLESPACE "USR"
) ;
CREATE UNIQUE INDEX "LDBO"."LEDGERPRIMARY" ON "LDBO"."FALEDDRCR1"
(
"FIRMNUMBER", "OOWNCODE", "VOUCHER", "VCNUMBER"
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
(
INITIAL 838860800 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "INDX" ;
ALTER INDEX "LDBO"."LEDGERPRIMARY" UNUSABLE;
CREATE INDEX "LDBO"."VOUCHER" ON "LDBO"."FALEDDRCR1"
(
"FIRMNUMBER",
"VOUCHER"
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
(
INITIAL 838860800 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "INDX" ;
ALTER INDEX "LDBO"."VOUCHER" UNUSABLE;
CREATE OR REPLACE TRIGGER "LDBO"."TG_FINANCE" AFTER
INSERT ON FALEDDRCR1 FOR EACH row DECLARE counter NUMBER;
BEGIN
SELECT COUNT(*)
INTO counter
FROM tblFintable
WHERE cFirmnumber =:new.firmnumber
AND cClientcode =:new.oowncode
AND cBranchcode =:new.Branchcode ;
IF NVL(counter,0) = 0 THEN
INSERT
INTO tblFintable
(
cFirmnumber,
cClientcode,
cBranchcode,
cDailychange
)
VALUES
(
:new.firmnumber,
:new.Oowncode,
:new.Branchcode,
'Y'
) ;
END IF ;
END ;
/
ALTER TRIGGER "LDBO"."TG_FINANCE" ENABLE;
CREATE OR REPLACE TRIGGER "LDBO"."TG_RKRECOUPDATE" AFTER
UPDATE OF BankReco ON FALEDDRCR1 FOR EACH row BEGIN Sp_RkBankreco(:Old.Firmnumber,:Old.Oowncode,:New.Bankreco,:Old.Bankreco,:Old.Voucher,:Old.Damount,:Old.Camount,:Old.Exchange,:New.Exchange) ;
END ;
/
ALTER TRIGGER "LDBO"."TG_RKRECOUPDATE" ENABLE;
CREATE OR REPLACE TRIGGER "LDBO"."TG_RKFINUPDATE" AFTER
INSERT OR
UPDATE OF Firmnumber,Oowncode,Damount,Camount,Exchange OR
DELETE ON FALEDDRCR1 FOR EACH row DECLARE lcOperationtype CHAR(1) ;
ldTransactiondate DATE ;
BEGIN
SELECT to_date(TO_CHAR(sysdate,'dd/mm/yyyy'),'dd/mm/yyyy hh:mi:ssam')
INTO ldTransactiondate
FROM dual ;
IF (((:Old.Vcnumber<10000 OR :New.Vcnumber<10000) AND (:Old.Entrycode=5 OR :New.Entrycode=5)) OR :New.Entrycode!=5 OR :Old.Entrycode!=5) THEN
IF Inserting THEN
lcOperationtype:='I' ;
INSERT
INTO tblFinentries
(
cOperationtype,
dTransactiondate,
cOldFirmnumber,
cNewFirmnumber,
cOldAccountcode,
cNewAccountcode,
cNewVoucher,
nOldDramount,
nNewDramount,
nOldCramount,
nNewCramount,
cOldExchange,
cNewExchange
)
VALUES
(
lcOperationtype,
ldTransactiondate,
:Old.Firmnumber,
:New.Firmnumber,
:Old.Oowncode,
:New.Oowncode,
:New.Voucher,
:Old.Damount,
:New.Damount,
:Old.Camount,
:New.Camount,
:Old.Exchange,
:New.Exchange
) ;
ELSE
IF Updating THEN
lcOperationtype:='U' ;
INSERT
INTO tblFinentries
(
cOperationtype,
dTransactiondate,
cOldFirmnumber,
cNewFirmnumber,
cOldAccountcode,
cNewAccountcode,
cNewVoucher,
nOldDramount,
nNewDramount,
nOldCramount,
nNewCramount,
cOldExchange,
cNewExchange
)
VALUES
(
lcOperationtype,
ldTransactiondate,
:Old.Firmnumber,
:New.Firmnumber,
:Old.Oowncode,
:New.Oowncode,
:New.Voucher,
:Old.Damount,
:New.Damount,
:Old.Camount,
:New.Camount,
:Old.Exchange,
:New.Exchange
) ;
ELSE
lcOperationtype:='D' ;
INSERT
INTO tblFinentries
(
cOperationtype,
dTransactiondate,
cOldFirmnumber,
cNewFirmnumber,
cOldAccountcode,
cNewAccountcode,
cNewVoucher,
nOldDramount,
nNewDramount,
nOldCramount,
nNewCramount,
cOldExchange,
cNewExchange
)
VALUES
(
lcOperationtype,
ldTransactiondate,
:Old.Firmnumber,
:New.Firmnumber,
:Old.Oowncode,
:New.Oowncode,
:Old.Voucher,
:Old.Damount,
:New.Damount,
:Old.Camount,
:New.Camount,
:Old.Exchange,
:New.Exchange
) ;
END IF ;
END IF ;
END IF ;
END tg_Rkfinbal;
/
ALTER TRIGGER "LDBO"."TG_RKFINUPDATE" ENABLE;



4)

select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where table_name='FALEDDRCR1';



5)

ALTER TABLE FALEDDRCR EXCHANGE PARTITION BRXXXX WITH TABLE FALEDDRCR1;


6)

Drop table FALEDDRCR;

7)

ALTER TABLE FALEDDRCR1 RENAME TO FALEDDRCR;



8)

ALTER TABLE org_table_name
RENAME CONSTRAINT new_constraint_name TO org_constraint_name;



9)

select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where table_name='FALEDDRCR';

-------------------------second method-----------------

create table transactions2 as select * from transactions;


drop table transactions;


@c:\transaction_struct.sql


alter table transactions disable all triggers;


select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where table_name='TRANSACTIONS';


insert into transactions select * from transactions2;


alter table transactions ENABLE all triggers;


select 'alter table '||table_name||' ENABLE constraint '||constraint_name||';' from user_constraints where table_name='TRANSACTIONS';


--------------------------