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


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

Server Disk Space Notification

' Sample code for monitoring windows disk space



' Constants for drive types
Const Unknown = 0
Const Removable = 1
Const Fixed = 2
Const Remote = 3
Const CDROM = 4
Const RAMDisk = 5

' general constants – NEED TO BE MODIFIED FOR YOUR ENVIRONMENT
Const MailServer = "mail.arihantcapital.com"
Const MailServerPort = "25"

Const LOCAL_HARD_DISK = 3

' Send a mail message
Sub SendMail(Sender, Recipient, Subject, Message)
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = Subject
objMessage.From = Sender
objMessage.To = Recipient
objMessage.TextBody = Message

objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = MailServer

'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = MailServerPort

objMessage.Configuration.Fields.Update

objMessage.Send
End Sub

' get current computer name (from system environment variables)
Function GetCurrentComputerName
set oWsh = WScript.CreateObject("WScript.Shell")
set oWshSysEnv = oWsh.Environment("PROCESS")
GetCurrentComputerName = oWshSysEnv("COMPUTERNAME")
End Function

'==================================================================
' Begin main code
'==================================================================
str = ""

'Only enumerate physical disks (Not Network Drives)
Const HARD_DISK = 3

'====================================================================
' Server Server1
'===================================================================
strComputer = "192.168.0.7"
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colDisks = objWMIService.ExecQuery _
("Select * from Win32_LogicalDisk Where DriveType = " & HARD_DISK & "")

str = str & "Server: " & strComputer & vbcrlf
For Each objDisk in colDisks
str = str & "Disk: "& objDisk.DeviceID & vbTab
str = str & " Free Disk Space: "& FormatNumber(CLng(objDisk.FreeSpace / 1024 / 1024),0,,,-1) & " MB" & vbcrlf
Next

str = str & vbcrlf


'Send the email
SendMail "backoffice@arihantcapital.com", "kshitij.rakesh@arihantcapital.com", "Server" & strComputerName & ": Drive Space Report", str

Low Disk Space Window Notification

Set wshShell = WScript.CreateObject( "WScript.Shell" )
strComputerName = wshShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" )
'WScript.Echo "Computer Name: " & strComputerName
Set objMessage = CreateObject("CDO.Message")


objMessage.Subject = "Disk Space Alert: " &strComputerName
objMessage.From = "kshitij.rakesh@arihantcapital.com"
objMessage.To = "kshitij.rakesh@arihantcapital.com"

Dim objShell, space_value, Result
Set objShell = Wscript.CreateObject("WScript.Shell")

Set DiskSet = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery ("select * from Win32_LogicalDisk where DriveType=3")
For each Disk in DiskSet
If (Disk.Name="D:") Then
Disk.FreeSpace=Disk.FreeSpace/1024
Disk.FreeSpace=Disk.FreeSpace/1024
Disk.FreeSpace=Disk.FreeSpace/1024
Result = Disk.FreeSpace
End If
Next
space_value = Result
Wscript.echo space_value
if space_value < 150 then

objMessage.TextBody = "Server Disk Space Low, " & space_value & " GB"
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.arihantcapital.com"
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMessage.Configuration.Fields.Update
objMessage.Send

end if

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