ORA-00600 signalled while mounting the database from the second instance of Oracle9i RAC
Title: ORA-00600 signalled while mounting the database from the second instance of Oracle9i RAC Hi there, I have set up two-node Oracle9i RAC on Windows 2000 Advanced Server with OCFS (Oracle Clustered file system). The Oracle version is 9.2.0.1.0 and I have also applied the 9.2.0.3.0 patch on top of it. I am getting the following error while mounting the database or starting up the database: ORA-00600: internal error code, arguments: [kccsbck_first], [1], [3898663947], [], [], [], [], [] The relevant portion from the alertsid.log file is pasted below: Tue Dec 23 17:34:58 2003 Restarting dead background process QMN0 QMN0 started with pid=18 Tue Dec 23 17:35:29 2003 ORA-600 signalled during: alter database mount... Tue Dec 23 17:40:13 2003 Restarting dead background process QMN0 QMN0 started with pid=18 Tue Dec 23 17:45:22 2003 Restarting dead background process QMN0 QMN0 started with pid=18 Tue Dec 23 17:50:38 2003 Restarting dead background process QMN0 QMN0 started with pid=18 Tue Dec 23 17:55:53 2003 Restarting dead background process QMN0 QMN0 started with pid=18 Tue Dec 23 17:59:15 2003 alter database mount Tue Dec 23 17:59:15 2003 This instance was first to mount Tue Dec 23 17:59:19 2003 Errors in file c:\oracle\admin\clust\udump\clust2_ora_2148.trc: ORA-00600: internal error code, arguments: [kccsbck_first], [1], [3898663947], [], [], [], [], [] I tried changing the aq_tm_processes parameter for both instances from 1 to 0. But the error still recurs. I am also attaching the relevant trace file for your reference. Can anybody let me know the reason for the above errors and how to counter them? Any help in this regard is very much appreciated. Thanks and Regards, Ranganath clust2_ora_2148.zip www.mailfiler.com [RK-KI8G4W] clust2_ora_2148.zip Description: Zip compressed data
ORA-00600 signalled while mounting the database from the second instance of Oracle9i RAC
Title: ORA-00600 signalled while mounting the database from the second instance of Oracle9i RAC Hi there, I have set up two-node Oracle9i RAC on Windows 2000 Advanced Server with OCFS (Oracle Clustered file system). The Oracle version is 9.2.0.1.0 and I have also applied the 9.2.0.3.0 patch on top of it. I am getting the following error while mounting the database or starting up the database: ORA-00600: internal error code, arguments: [kccsbck_first], [1], [3898663947], [], [], [], [], [] The relevant portion from the alertsid.log file is pasted below: Tue Dec 23 17:34:58 2003 Restarting dead background process QMN0 QMN0 started with pid=18 Tue Dec 23 17:35:29 2003 ORA-600 signalled during: alter database mount... Tue Dec 23 17:40:13 2003 Restarting dead background process QMN0 QMN0 started with pid=18 Tue Dec 23 17:45:22 2003 Restarting dead background process QMN0 QMN0 started with pid=18 Tue Dec 23 17:50:38 2003 Restarting dead background process QMN0 QMN0 started with pid=18 Tue Dec 23 17:55:53 2003 Restarting dead background process QMN0 QMN0 started with pid=18 Tue Dec 23 17:59:15 2003 alter database mount Tue Dec 23 17:59:15 2003 This instance was first to mount Tue Dec 23 17:59:19 2003 Errors in file c:\oracle\admin\clust\udump\clust2_ora_2148.trc: ORA-00600: internal error code, arguments: [kccsbck_first], [1], [3898663947], [], [], [], [], [] I tried changing the aq_tm_processes parameter for both instances from 1 to 0. But the error still recurs. I am also attaching the relevant trace file for your reference. Can anybody let me know the reason for the above errors and how to counter them? Any help in this regard is very much appreciated. Please send your replies to my mail id also as I am unable to receive Fatcity mails. Thanks and Regards, Ranganath clust2_ora_2148.zip www.mailfiler.com [RK-P7P1D12] clust2_ora_2148.zip Description: Zip compressed data
SQL and PL/SQL tuning template document required urgently
Hi there, Does any body have a template for proactive and reactive query tuning which can be used as a guideline/report while tuning simple, medium complex and complex SQL queries and PL/SQL stored procedures? If so, can you please forward the same to me please? If not, can anybody suggest as to how to go about doing one? Any help in this regard is very much appreciated. Thanks and Regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ORA-14403 error detected during bulk updates
Hi there, We are running Oracle 9.0.2.3 on Sun Solaris 8. We have two fact tables - fct1 and fct2 and two PL SQL stored procedures - P1 and P2 fct1 - range partitioned on fct1_field1 and hash partitioned on fct1_field2 fct2 - range partitioned on fct2_field1 and hash partitioned on fct2_field2 Program P1 is updating fct1. Program P2 is selecting from fct1 and updating fct2. Both P1 and P2 are processing different sets of records. P1 - history records and P2 - current records In program P2 we are having multiple bulk updates (approx. 30) on fct2 (using fct1). Earlier we had acursor in this program for updating around 20 fields but opening and closing of cursor was giving bad performance so we changed it to individual bulk updates. Table statistics - fct1 - 300 million records and fct2 - around 200 million records P2 has to update approximately 0.8 million records everyday. Is there a possibility of oracle throwing a DML partition lock error? Or is it that oracle throws DML lock error only if same set of records are updated and selected by P1 and P2 respectively? We have such a scenario in one of the programs and are getting the error ORA-14403: 14403, 0, cursor invalidation detected after getting DML partition lock // *Cause: cursor invalidation was detected after acquiring a partition lock // during an INSERT, UPDATE, DELETE statement. This error is never // returned to user, because is caught in opiexe() and the DML // statement is retried. // *Action: nothing to be done, error should never be returned to user Program P1 is updating some set of records in the table T1. Program P2 is running in parallel and using a different set of records from table T1 for some processing. Is there a possibility of oracle throwing a DML partition lock error? Any help in this regard is very much appreciated. Thanks and Regards, Ranganath www.mailfiler.com [RK-K521CR3] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How to invoke stored procedures from another instance?
Hi listers, Assume that there are two instances in Oracle. Both instances are on different machines and different Oracle versions. There is a table on first instance. Any update on this table should invoke stored procedures on the second instance. This should be real time based. Options we looked at are 1. Trigger on the table invoking the procedures of the other instance 2. Using dbms_alert 3. Some kind of polling mechanism Triggers we would like to avoid. Options we are left with are dbms_alert and polling mechanism. Is it possible to use dbms_alert in this case? If yes how? Can you think of some kind of polling mechanism which will satisfy the need of real time communication? Updates on the table is done at a very fast rate, hence processing should also be at a fast rate. Any help in this regard is very much appreciated. Thanks and Regards, Ranganath MailFiler [RK-FM8B9B3]
RAC DBCA hangs while creating cluster database
Hi Listers, I am trying to set up a two-node RAC on Windows 2000 Advanced Server on Proliant DL 580 Servers. I am following the metalink document 178882.1 and Harrison B. Redhouses document DIY Multi-Node RAC on a single Windows PC downloaded from www.oaktable.net. I am having two separate nodes and hence there was no need to use VMvare to simulate two nodes as mentioned in Harrison B. Redhouses document. I ran through all the steps as mentioned in both the documents. But when I am using DBCA to install cluster database on the first node I am getting the following error when I click FINISH button: The filepath S:\Oradata\CLUST\CWMLITE01.DBF is invalid. Please check the permissions. I have stopped and restarted the GSD service. I have also installed the Oracle 9.2.0.3 patch and created the listener successfully. I am trying to install the cluster database through the command Dbca datafiledestination S:\oradata. I am able to see the shared disk from both the disk manager and Windows explorer. I am really stuck up with this problem. Any pointers as to what am I missing would be highly appreciated. Looking forward to hear from any of you soon. Thanks and Regards, Ranganath MailFiler [RK-7ARFH54]
Slightly OT: Unable to recognize shared disk on Red Hat Linux AS 2.1
Hi listers, I am trying to setup Oracle9i RAC on RedHat Linux AS 2.1 on DL580 server. The storage system is MSA 1000 Controller with FCA2101 Fibre Channel Host Bus Adapter. I have installed dual operating systems viz; Windows 2000 Advanced Server and RedHat Linux AS 2.1. I am unable to see the shared disk from either of the nodes when I boot through RedHat Linux AS 2.1. Where as when I boot through Windows 2000 AS 2.1, I am able to see the same. Please help me on the same. I am really stuck up with this and cannot proceed further without configuring the shared disk. Can anybody point me to the exact location of HBA drivers to be installed on this server so that the shared disk is recognized? Any help in this regard is very much appreciated. Thanks and Regards, Ranganath MailFiler [RK-V9NU4W4]
Clarification sought regarding configuring two-node Oracle9i RAC on RH Linux AS 2.1
Hi Listers, I am trying to configure a two-node Oracle9i RAC on Redhat Linux AS 2.1. I have 2 network cards in each node. Oracle doc. says that one of the network cards has to be on a public network and the other network card on a private network each configured with static ips. Does this mean that the first network card is configured with a static ip which is resolved by the DNS Server and on a network and the other network card is configured with any junk ip which need not be resolved by DNS Server and need not be on the network? Can I configure the RAC with this understanding? Please clarify. Does anybody have an exhaustive document on configuring Oracle9i RAC on RH Linux AS 2.1 on a two-node/four-node cluster apart from the RAC install-tips document at technet.oracle.com? If so, can they pass it on to me please? If not, can anybody please let me know any URL from where I can download the same? Thanks and Regards, Ranganath MailFiler [RK-ANH8LQ2]
Bug in Execute Immediate clause???
Hi Listers,
The below procedure gets created successfully in TEST
Schema. But when I execute the
procedure by starting a fresh session connecting as TEST schema I get the below
error and when I execute the procedure for the second time it executes
successfully. I have granted the dba privileges and explicit granted select on TEMP1 to TEST
Schema. Inspite
of that I am getting the below errors.
I tried this 8.1.7 and 9.2.1.0.
Is it a bug in the code or the database?
CREATE OR REPLACE procedure test authid
current_user is
c number;
n number;
a varchar2(1000);
Begin
Execute Immediate 'Alter Session Set Current_Schema
= SCOTT';
Dbms_output.put_line(sys_context('userenv', 'Current_schema'));
Select count(*) into c >From temp1;
Dbms_output.put_line(c);
End;
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at TEST.TEST, line 9
ORA-06512: at line 1
Can anybody help me out? Any help in this regard is very much
appreciated.
Thanks and Regards,
Ranganath
Standard or Enterprise edition without installing Oracle9i software
Hi there, I have 3 CDs of Oracle9i database Release2 (9.2.0.1.0) for Linux Intel with part numbers as A99339-01, A99340-01 and A99341-01. I want to set up RAC on Linux machine. I believe RAC option only comes from Oracle9i Enterprise Edition CDs. Is it possible to know whether RAC option in any of the CDs? Is it possible to know without installing Oracle Software as to whether the CDs are of enterprise edition or standard edition from the Part numbers? Thanks and Regards, Ranganath
RE: Deleting Statspack tables.
-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to select and display 10 records at a time from a table using sql query - Solved
Dear all, Here is the query to do the same: select outer.* from(select inner.*,ROWNUM as Query_Rownum from (select * from tablename)inner where Rownum =to) outer where Outer.Query_Rownum=from / Hope this helps! Regards, Ranganath DISCLAIMER: This correspondence is confidential and intended for the named recipient(s) only. If you are not the named recipient and receive this correspondence in error, you must not copy, distribute or take any action in reliance on it and you should delete it from your system and notify the sender immediately. Unless otherwise stated, any views or opinions expressed are solely those of the author and do not represent those of Subex Systems Limited. www.subexgroup.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ora - 4031 while inserting into a parent table
Dear DBA Gurus, I am getting the following error while inserting into a schema's parent table which is being referred by 42 tables of another schema. ORA-04031: unable to allocate 2196 bytes of shared memory (shared pool,RA_VOLUME_PLAN_POOL,KQLS heap,KQLS MEM BLOCK); How do I resolve this? I increased the shared_pool_size from 15mb to 30mb and shared_pool_reserved_size from 1mb to 5mb but it didn't solve the problem. I also set the _db_cached_cursors parameter to 0 and bounced the database but in vain. BTW I am using oracle 8.1.6 enterprise edition on Sun Solaris 2.7. Any help in this regard will be very much appreciated. TIA and Regards, Ranganath DISCLAIMER: This correspondence is confidential and intended for the named recipient(s) only. If you are not the named recipient and receive this correspondence in error, you must not copy, distribute or take any action in reliance on it and you should delete it from your system and notify the sender immediately. Unless otherwise stated, any views or opinions expressed are solely those of the author and do not represent those of Subex Systems Limited. www.subexgroup.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How to select and display 10 records at a time from a table using sql query
Dear DBA Gurus, How do I select and display 10 records at a time from a table using sql query only? Thanks and Regards, Ranganath DISCLAIMER: This correspondence is confidential and intended for the named recipient(s) only. If you are not the named recipient and receive this correspondence in error, you must not copy, distribute or take any action in reliance on it and you should delete it from your system and notify the sender immediately. Unless otherwise stated, any views or opinions expressed are solely those of the author and do not represent those of Subex Systems Limited. www.subexgroup.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Spooling output of a select statement to a file through stored procedure
Dear DBA Gurus, How do I spool output of a select statement to a file through stored procedure. The stored procedure should take the directory and path of the file to be spooled to on the client machine. I know we can do this using UTL_FILE package. But I don't want to write to a file in Oracle Database server machine rather I want to write the output to a file in client machine. Please note that I want to do this through a Pl/Sql procedure or a pl/sql program so that the procedure can be called from the front end which is Java/C++. How do I do this? Any help in this regard will be duly appreciated. Thanks and Regards, Ranganath DISCLAIMER: This correspondence is confidential and intended for the named recipient(s) only. If you are not the named recipient and receive this correspondence in error, you must not copy, distribute or take any action in reliance on it and you should delete it from your system and notify the sender immediately. Unless otherwise stated, any views or opinions expressed are solely those of the author and do not represent those of Subex Systems Limited. www.subexgroup.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Running sql script from stored procedure
Dear DBA Gurus, I have a .sql file which contains a series of insert statements. I want to call this .sql file and run it from a stored procedure. Is it possible? If so, how? Please post your answers to me directly as I am not receiving messages directly from the list rather I am able to send messages to the list. Thanks and Regards, Ranganath DISCLAIMER: This correspondence is confidential and intended for the named recipient(s) only. If you are not the named recipient and receive this correspondence in error, you must not copy, distribute or take any action in reliance on it and you should delete it from your system and notify the sender immediately. Unless otherwise stated, any views or opinions expressed are solely those of the author and do not represent those of Subex Systems Limited. www.subexgroup.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Not receiving messages from the list but able to send it
Hi List, My messages are received by list members but I am not receiving messages from the list since past 3 days. I am wondering as to what might be the problem? Can anybody shed light on this? Thanks and Regards, Ranganath DISCLAIMER: This correspondence is confidential and intended for the named recipient(s) only. If you are not the named recipient and receive this correspondence in error, you must not copy, distribute or take any action in reliance on it and you should delete it from your system and notify the sender immediately. Unless otherwise stated, any views or opinions expressed are solely those of the author and do not represent those of Subex Systems Limited. www.subexgroup.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
No messages since yesterday evening
Hi all, I have not received any messages since yesterday evening. Is it the same with all or any of you? Regards, Ranganath DISCLAIMER: This correspondence is confidential and intended for the named recipient(s) only. If you are not the named recipient and receive this correspondence in error, you must not copy, distribute or take any action in reliance on it and you should delete it from your system and notify the sender immediately. Unless otherwise stated, any views or opinions expressed are solely those of the author and do not represent those of Subex Systems Limited. www.subexgroup.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
stored procedure errors
Hi Gurus,
I am creating a stored procedure using the below code. The procedure gets
created successfully. But while executing the procedure I am getting below
errors:
ERROR at line 1:
ORA-00907: missing right parenthesis
ORA-06512: at SCOTT.CREATE_RATEDOUTPUT, line 5
ORA-06512: at line 1
CREATE OR REPLACE procedure create_ratedOutput(tablename IN varchar2)
authid current_user
IS
BEGIN
EXECUTE IMMEDIATE 'create table '|| tablename ||'(
version char(1) not null,
ne_id number(5) not null,
ne_sequence number(9) not null,
ne_date date not null,
device_id_indictor char(1) not null,
device_id varchar2(40) not null,
service_type_1_rule char(1) not null,
raw_service_type1_id varchar2(20) not null,
override1_id varchar2(20),
service_type_2_rule char(1) not null,
raw_service_type2_id varchar2(20),
override2_id varchar2(20),
call_date date not null,
call_completion number(3) not null,
call_termination number(3) not null,
call_direction char(1) not null,
mobile_to_mobile_indicator char(1) not null,
electronic_serial_number varchar2(11),
initial_cell_site varchar2(10),
second_number varchar2(20) not null,
dest_address varchar2(64),
special_cases varchar2(5) not null,
call_start_time_1 date not null,
call_duration_1 number(6) not null,
call_start_time_2 date,
call_duration_2 number(6),
home_zone duration number(6),
roaming_indicator char(1) not null,
roaming_direction char(1),
service_provider number(9),
service_provider_batch number(9),
service_provider_location varchar2(12),
ip_address varchar2(15),
data_size number(9),
user_defined1 varchar2(40),
user_defined2 varchar2(40),
user_defined3 varchar2(40),
user_defined4 varchar2(40),
user_defined5 varchar2(40),
user_defined6 varchar2(40),
user_defined7 varchar2(40),
user_defined8 varchar2(40),
us_call_type number(2),
day_type number(2),
origin_city varchar2(15),
origin_state char(2),
origin_country_abbr char(4),
destination_city varchar2(15),
destination_state char(2),
destination_country_abbr char(4),
destination_lata char(4),
destination_ocn char(4),
destination_rocn char(4),
destination_ioc_boc char(1),
distance_dialed number(9),
billed_duration number(6),
rate_periods_used number(1),
rate_period_description varchar2(30),
st1_rate_period_1 number(2),
st1_billed_duration_1 number(6),
st1_charge_1 number(18, 4),
st1_rate_period_id_2 number(2),
st1_billed_duration_2 number(6),
st1_charge_2 number(18, 4),
st1_billed_duration_rest number(6),
st1_charge_rest number(18, 4),
st2_rate_period_1 number(2),
st2_billed_duration_1 number(6),
st2_charge_1 number(18, 4),
st2_rate_period_id_2 number(2),
st2_billed_duration_2 number(6),
st2_charge_2 number(18, 4),
st2_billed_duration_rest number(6),
st2_charge_rest number(18, 4),
state_taxes number(18, 4),
local_taxes number(18, 4),
status char(1),
status_date date,
billed_id varchar2(20) not null,
customer char(6) not null,
billed_start_date date not null,
duplicate_key_sequence char(1) not null,
company char(3),
actual_service_type1 varchar2(20),
actual_service_type2 varchar2(20),
customer_cycle number(2),
usage_file_end_day number(2),
sort_method char(1),
rate_plan varchar2(20),
rate_plan_effective_date date,
revenue_code_by_plan char(4),
revenue_code_by_service1 char(4),
revenue_code_by_service2 char(4),
billing_item char(4),
billing_seq char(2),
billing_po char(2),
billing_cancel_date date,
free_usage_method char(1),
volume_discount_method char(1),
invoice_summary1 char(1),
invoice_summary2 char(1),
free_usage_amount1 number(18, 4),
free_usage_amount2 number(18, 4),
free_usage_details varchar2(40),
vol_disc_amount_applied1 number(18, 4),
vol_disc_amount_applied2 number(18, 4),
vol_disc_details varchar2(40),
carrier1 varchar2(20),
action_type1 char(1),
carrier2 varchar2(20),
action_type2 char(1),
actual_st1_charge_total number(18, 4),
actual_st2_charge_total number(18, 4),
alternate_amounts_calculated char(1),
alternate_amount_1 number(18, 4),
alternate_amount_2 number(18, 4),
CONSTRAINT RatedCDROut_PK
PRIMARY KEY (billed_id, customer, billed_start_date,
duplicate_key_sequence))';
execute immediate 'insert into outputstatus
values('||chr(39)||tablename||chr(39)||','||chr(39)||1||chr(39)||')';
commit;
end;
/
Can anyone help me in this regard?
Regards,
Ranganath
DISCLAIMER: This correspondence is confidential and intended for the named
recipient(s) only. If you are not the named recipient and receive this
correspondence in error, you must not copy, distribute or take any action in
reliance on it and you should delete it from your system and notify the
sender immediately. Unless otherwise stated, any views or opinions expressed
are solely those of the author and do not represent those of Subex Systems
Limited.
www.subexgroup.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ranganath K
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
Question on on delete cascade functionality
Dear DBA Gurus, How to add the functionality of on delete cascade to an existing foreign key constraint without dropping the constraint and recreating it using Alter command? Thanks and Regards, Ranganath DISCLAIMER: This correspondence is confidential and intended for the named recipient(s) only. If you are not the named recipient and receive this correspondence in error, you must not copy, distribute or take any action in reliance on it and you should delete it from your system and notify the sender immediately. Unless otherwise stated, any views or opinions expressed are solely those of the author and do not represent those of Subex Systems Limited. www.subexgroup.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Trigger help
Dear DBA Gurus, I have a table with two fields name and status. What I want to do is once the current system date rolls over from 30th to 31st the status should be entered as closed other wise from 1st to 30th the status should be entered as open. How do I do this using trigger or any dbms packages? Any help in this regard will be very much appreciated. TIA and Regards, Ranganath DISCLAIMER: This correspondence is confidential and intended for the named recipient(s) only. If you are not the named recipient and receive this correspondence in error, you must not copy, distribute or take any action in reliance on it and you should delete it from your system and notify the sender immediately. Unless otherwise stated, any views or opinions expressed are solely those of the author and do not represent those of Subex Systems Limited. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How to find the size of all the tables under a particular schema using vsize
Dear DBA Gurus, I need to find the size of one row of all the tables under a particular schema using vsize clause. How do I do this? Please help. TIA and Regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How to find the table size?
Dear DBA Gurus, Assume I have an emp table under Scott Schema with the following structure. Name Null?Type - - --- EMPNO NOT NULL NUMBER(4) ENAMEVARCHAR2(10) JOBVARCHAR2(9) MGRNUMBER(4) HIREDATE DATE SALNUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) I need to calculate the size of the table assuming one of the rows is having all the columns maxsize possible. Say, 4+10+9+4+7+7+7+2=50. I need to do this for all the tables under a particular schema. How do I do this using Sql or Pl/Sql? Any help in this regard will be very much appreciated. TIA and Regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ora - 900 error while using copy command in a stored procedure
Hi DBA Gurus, Is it possible to use the copy command in a stored procedure? I am trying to use the copy command to copy one of the table data from user A to user B in the same database in a stored procedure with source database, username and password and destination database, username and password being used as input parameters. But I am getting ora - 900 error - invalid sql statement error. Any ideas? Here is my code: create or replace procedure copy_proc as sql1 varchar2(2000); begin sql1:= 'copy from primal/primal@primal to jstage2/jstage2@primal append carrier using select * from carrier'; execute immediate sql1; end; Any help in this regard will be very much appreciated. TIA and Regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Ora - 1008 - Not all variables bound error
Dear DBA Gurus,
I am getting the above error while running the below JDBC program.
Can anyone help me in solving the problem?
TIA and Regards,
Ranganath
import java.sql.*;
import java.io.*;
public class Alert
{
public static void main(String args[]){
int status = 1;
int timeout = 5;
String message = new String();
try
{
String username=SCOTT;
String password=TIGER;
Class.forName(oracle.jdbc.driver.OracleDriver);
String dburl = jdbc:oracle:oci8:@orcl1;
Connection dbcon =
DriverManager.getConnection(dburl,username,password);
CallableStatement cstmt1 = dbcon.prepareCall(begin
sys.dbms_alert.register('I_GOT_IT'); end;);
cstmt1.execute();
System.out.println(We are waiting for waitone
signal);
CallableStatement cstmt2 = dbcon.prepareCall(begin
sys.dbms_alert.waitany('I_GOT_IT',:message,:status,5); end;);
cstmt2.execute();
if (status == 0) {
System.out.println(We have received the signal);
System.out.println(The message that has been
received is + message);
System.out.println(Status and time are + status +
timeout);
} else
System.out.println(No message has been received.
EXit Session);
CallableStatement cstmt3 = dbcon.prepareCall(begin
sys.dbms_alert.remove('I_GOT_IT'); end;);
cstmt3.execute();
dbcon.close();
}
catch(SQLException nExp)
{
System.out.println(Connection Failed, SQLException = +
nExp.toString());
}
catch(Exception e)
{
System.out.println(Connection Failed, Exception = +
e.toString());
}
}
}
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ranganath K
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
How to prevent oracle from committing a transaction?
Dear DBA Gurus, Can you tell me how to prevent oracle from committing a transaction unless I explicitly commit it. I have observed that when I insert a record into a table and type exit from the sql prompt without commiting the transaction and again open a new sqlplus session and select from that particular table the record is inserted. How do I prevent oracle from inserting into a table unless I explicitly specify commit? Please note that I have set my sqlplus session as autocommit off. I tried with alter table tablename nologging but it didn't do what I wanted. Can anybody help me in this regard? TIA and Regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How to notify the firing a database trigger to the java application
Dear DBA Gurus, I have written an after update database trigger on a particular table. The trigger gets fired and does what I want. However I want to know as to how do I notify my java application that the trigger has fired? Anybody can let me know about this? Any help in this regard will be very much appreciated. TIA and Regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to prevent oracle from committing a transaction - Solved
Hi DBA Gurus, The following soultion worked for me: Exit Rollback. My special thanks to Marco Rooy van and Ray O'Brien for providing the solution. Regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How to pass the connect clause to connect as a database user in a stored procedure?
Dear DBA Gurus, I want to create a stored procedure in which a name is passed as input parameter. The procedure should take the name as input parameter and create a user with the username and password same as name. Then I want to connect as the newly created user and create a set of tables. I know how to create a user and create tables in Pl/Sql using Execute immediate statement but I don't know how to pass the connect clause in stored procedure so that I can connect as the newly created user. Anybody knows how to to this? If so, please let me know. Any help in this regard will be very much appreciated. TIA and Regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Error while executing stored procedure as system user
Dear DBA Gurus,
I am able to execute the below code as an anonymous Pl/Sql block but when I
incorporate it in a stored procedure the procedure gets created but I am
getting the below errors while executing the procedure as system user:
Anonymous Pl/Sql block
Declare
name varchar2(4):='test';
BEGIN
EXECUTE IMMEDIATE 'create user ' ||name||' '||'identified by '||name||'
'||
'default tablespace users temporary tablespace temp';
EXECUTE IMMEDIATE 'grant connect, resource to ' ||name;
EXECUTE IMMEDIATE 'create table '||name||'.aaa(a number)';
EXECUTE IMMEDIATE 'create table '||name||'.bbb(b number)';
END;
Stored Procedure
create or replace procedure create_user (name IN VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE 'create user ' ||name||' '||'identified by '||name||'
'||
'default tablespace users temporary tablespace temp';
EXECUTE IMMEDIATE 'grant connect, resource to ' ||name;
EXECUTE IMMEDIATE 'create table '||name||'.aaa(a number)';
EXECUTE IMMEDIATE 'create table '||name||'.bbb(b number)';
END;
/
Procedure Created.
Errors while executing the procedure
exec create_user('test');
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at SYSTEM.CREATE_USER, line 4
ORA-06512: at line 1
What might be the reason for the errors? Can anyone help me?
TIA and Regards,
Ranganath
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ranganath K
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Error while executing stored procedure as system user - Solved
Dear DBA Gurus, I solved the problem by incorporating the authid current_user clause. I didn't explicitly grant any roles or privileges to system user. The modified code is as below: create or replace procedure create_user (name IN VARCHAR2) authid current_user IS BEGIN EXECUTE IMMEDIATE 'create user ' ||name||' '||'identified by '||name||' '|| 'default tablespace users temporary tablespace temp'; EXECUTE IMMEDIATE 'grant connect, resource to ' ||name; EXECUTE IMMEDIATE 'create table '||name||'.aaa(a number)'; EXECUTE IMMEDIATE 'create table '||name||'.bbb(b number)'; END; Thanks Amol Joshi for giving the suggestion to use auth_id current_user clause in the stored procedure code. Regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Imedia query tuning
( PK_CATEGORY_ID ) );
2. To create site table.
CREATE TABLE SITE
PK_SITE_ID NUMBER NOT NULL,
FK_CATEGORY NUMBER NOT NULL,
TITLECLOB,
URL VARCHAR2 (4000) NOT NULL,
DESCRIPTION CLOB,
STATUS NUMBER NOT NULL,
PAGE_HITSNUMBER NOT NULL,
EDITOR_CHOICE VARCHAR2 (10),
PRIMARY KEY ( PK_SITE_ID ));
ALTER TABLE SITE ADD CONSTRAINT FKSITE
FOREIGN KEY (FK_CATEGORY)
REFERENCES VCPLNEW.CATEGORY (PK_CATEGORY_ID) ;
3. creating an index after inserting the data(Datebase Updation) in both the
tables.
a) Execute this script to create a preference.
begin
ctx_ddl.create_preference('sitelexer', 'BASIC_LEXER');
ctx_ddl.set_attribute('sitelexer', 'printjoins', '_-''');
ctx_ddl.set_attribute('sitelexer', 'endjoins', '%');
ctx_ddl.set_attribute ( 'sitelexer', 'index_text', 'YES');
ctx_ddl.set_attribute ( 'sitelexer', 'mixed_case', 'NO');
end;
b) Execute this script to create an indexes.
CREATE INDEX site1descidx ON
site(description)
indextype is ctxsys.context
parameters ( 'LEXER sitelexer' );
CREATE INDEX site2titleidex ON
site(title)
indextype is ctxsys.context
parameters ( 'LEXER sitelexer' );
4.Deleting a preference
begin
ctx_ddl.drop_preference('sitelexer');
end
Thanks in advance and Regards,
Ranganath
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ranganath K
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Imedia query tuning
Dear DBA Gurus, I have the following two queries along with execution plan and statistics. The first one is taking a long time to execute. The second query is taking a long time to execute when I use the order by clause. Is there any way I can reduce the execution time as these queries will be used by a search engine? Any help in this regard will be greatly appreciated. SQL select depth, count(*) a from category, site 2 where (site.fk_category in (select pk_category_id from category 3 where category.status = 0)) and site.status = 0 4 and ((contains (title,'box') 0) or 5 (contains (description, 'box') 0)) 6 and pk_category_id = fk_category group by depth order by a desc; 467 rows selected. Elapsed: 00:00:16.43 Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=214936 Card=22470 By tes=136572660) 10 SORT (ORDER BY) (Cost=214936 Card=22470 Bytes=136572660) 21 SORT (GROUP BY) (Cost=214936 Card=22470 Bytes=136572660) 32 NESTED LOOPS (Cost=19 Card=22470 Bytes=136572660) 43 NESTED LOOPS (Cost=4 Card=15 Bytes=60945) 54 TABLE ACCESS (BY INDEX ROWID) OF 'SITE' (Cost=2 Ca rd=1 Bytes=4037) 65 BITMAP CONVERSION (TO ROWIDS) 76 BITMAP OR 87 BITMAP CONVERSION (FROM ROWIDS) 98 SORT (ORDER BY) 109 DOMAIN INDEX OF 'SITE2TITLEIDEX' (Cost=1 ) 117 BITMAP CONVERSION (FROM ROWIDS) 12 11 SORT (ORDER BY) 13 12 DOMAIN INDEX OF 'SITE1DESCIDX' (Cost=1) 144 TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY' (Cost= 1 Card=1499 Bytes=38974) 15 14 INDEX (UNIQUE SCAN) OF 'SYS_C001314' (UNIQUE) 163 TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY' (Cost=1 Card=149802 Bytes=301851030) 17 16 INDEX (UNIQUE SCAN) OF 'SYS_C001314' (UNIQUE) Statistics -- 786 recursive calls 40 db block gets 5919 consistent gets 1389 physical reads 0 redo size 1829532 bytes sent via SQL*Net to client 69737 bytes received via SQL*Net from client 920 SQL*Net roundtrips to/from client 13 sorts (memory) 0 sorts (disk) 467 rows processed SQL select depth, title, description, url, editor_choice from category,site 2 where (site.fk_category in (select pk_category_id from category 3 where category.status = 0)) and site.status = 0 4 and site.fk_category = category.pk_category_id 5 and ((contains (title, 'box') 0) or 6 (contains (description, 'box' ) 0)) order by editor_choice desc; 552 rows selected. Elapsed: 00:00:16.94 Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=142941 Card=22470 By tes=181714890) 10 SORT (ORDER BY) (Cost=142941 Card=22470 Bytes=181714890) 21 NESTED LOOPS (Cost=19 Card=22470 Bytes=181714890) 32 NESTED LOOPS (Cost=4 Card=15 Bytes=91080) 43 TABLE ACCESS (BY INDEX ROWID) OF 'SITE' (Cost=2 Card =1 Bytes=6046) 54 BITMAP CONVERSION (TO ROWIDS) 65 BITMAP OR 76 BITMAP CONVERSION (FROM ROWIDS) 87 SORT (ORDER BY) 98 DOMAIN INDEX OF 'SITE2TITLEIDEX' (Cost=1) 106 BITMAP CONVERSION (FROM ROWIDS) 11 10 SORT (ORDER BY) 12 11 DOMAIN INDEX OF 'SITE1DESCIDX' (Cost=1) 133 TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY' (Cost=1 Card=1499 Bytes=38974) 14 13 INDEX (UNIQUE SCAN) OF 'SYS_C001314' (UNIQUE) 152 TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY' (Cost=1 Ca rd=149802 Bytes=301851030) 16 15 INDEX (UNIQUE SCAN) OF 'SYS_C001314' (UNIQUE) Statistics -- 781 recursive calls 66 db block gets 6930 consistent gets 1708 physical reads 0 redo size 2244834 bytes sent via SQL*Net to client 252240 bytes received via SQL*Net from client 2265 SQL*Net roundtrips to/from client 11 sorts (memory) 1 sorts (disk) 552 rows processed TIA and Regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message
How to avoid sorting in the select statement?
Dear DBA Gurus, I have some static data in two of my tables which contains lakhs of records. Is there any way I can sort the data in the two tables and put the sorted data in the same table or some temporary table without creating a new one. Why I want this is because when I write a search query based on two tables with the order by clause it is taking a long time to execute the query but without the order by clause it is executing fast. I tried to create a view based on the tables with the order by clause and then try to run my search based query on the view. Then also the execution time is more. Is there any way I can create a clustered index like we do in SQL server in Oracle which sorts the data and stores in the table? Any workaround? Any help in this regard will be greatly appreciated. TIA and Warm Regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Ora - 4091 error while trying to insert a record into a table
Dear DBA Gurus, I have Table A and Table B whose structures are almost similar except Table B has a date-time field column. Whenever I insert a new record into Table A I want to insert the same record into the Table B along with the date-time stamp at which the new record is inserted into Table A. For achieving this I wrote an after insert trigger on Table A for each row. The trigger got created successfully. But whenever I try to insert a new record into Table A I get the Ora - 4091, mutating table error. How do I achieve this without any errors? Any workarounds? Any help in this regard will be greatly appreciated. TIA and Warm Regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Ora - 4091 error while trying to insert a record into a table - RESOLVED
Hi Gurus, I got the solution. Here is the modified code: create or replace trigger trig_dept1 after insert on dept for each row begin insert into dept1(deptno, dname, loc, date_time) values(:new.deptno, :new.dname, :new.loc, sysdate); end; Thanks Rob Fegan and Amol Joshi for correcting me. Regards, Ranganath -Original Message- Sent: Friday, May 11, 2001 2:35 PM To: Multiple recipients of list ORACLE-L Dear DBA Gurus, I have Table A and Table B whose structures are almost similar except Table B has a date-time field column. Whenever I insert a new record into Table A I want to insert the same record into the Table B along with the date-time stamp at which the new record is inserted into Table A. For achieving this I wrote an after insert trigger on Table A for each row. The trigger got created successfully. But whenever I try to insert a new record into Table A I get the Ora - 4091, mutating table error. How do I achieve this without any errors? Any workarounds? Any help in this regard will be greatly appreciated. TIA and Warm Regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ORA - 1008 error when trying to insert data into a table
)
{
System.out.println(method=setSiteData() FOCUS: +
e.toString());
}
}
}
public static void main(String args[])
{
try
{
int pk_site_id = 0;
int fk_category =1;
String title = Sample Title;
String url = Sample URL;
String description = Sample Description;
int siteStatus = 0;
int page_hits = 0;
String editor_choice = Y;
setSiteData(pk_site_id, fk_category, title, url, description,
siteStatus,
page_hits, editor_choice);
}
catch(Exception e)
{
System.out.println(Exception in Main : + e.toString());
}
}
private static Connection getConnection() throws Exception
{
try
{
Class.forName(oracle.jdbc.driver.OracleDriver);
String dburl = jdbc:oracle:thin:@172.19.24.123:1521:DMOZ;
Connection dbcon =
DriverManager.getConnection(dburl,system,manager);
return dbcon;
}
catch(SQLException nExp)
{
throw new Exception(nExp.toString());
}
}
}
Any help in this regard will be greatly appreciated.
TIA and Regards,
Ranganath
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ranganath K
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Memory leak problem
Dear DBA Gurus, OS: Solaris 8 (SUN OS 5.8) Oracle Ver: 8.1.5 and 8.1.6 Nature of problem: There is a problem reported from our application that there is a memory leak problem in OCI related queries. I posted the problem in OTN and got the response that, these versions require patches. But exact patch details are not available. Memory is not getting released even after completion of the query. Once the available memory gets used, we need to restart the system to get the free memory. This is happening in Oracle 8.0.5 and Oracle 8.1.5 on Red Hat Linux 6.2 too. Initially we have suspected the problem in our application and the same was debugged and closing the handle after completion of the query has been taken care. Now it is very clear that the oracle setting may be causing the problem. This is very urgent and please get back to me with accurate solution as early as possible. TIA and Regards Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Conventions for naming indexes, constraints etc.
Hi listers, A couple of days back somebody in the list had sent an URL about the convention to be followed for naming indexes, constraints etc. I have lost it somewhere. Could any of you who have saved and have it now send it to me? I am in need of it very badly. TIA and regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Conventions for naming indexes, constraints etc.
Hi Gints Plivna, Thanks for the URL. BTW where are you now? Whom are you working for? Regards, Ranganath -Original Message- [EMAIL PROTECTED] Sent: Wednesday, April 18, 2001 4:06 PM To: Multiple recipients of list ORACLE-L Hi! How it is in our case http://www.itsystems.lv/gints/eng/naming_conventions.htm But this was not mentioned earlier because I created it some minutes ago ;) Gints Plivna "Ranganath K" ranganathk@subexTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] group.com cc: Sent by: Subject: Conventions for naming indexes, constraints etc. [EMAIL PROTECTED] 01.04.18 12:20 Please respond to ORACLE-L Hi listers, A couple of days back somebody in the list had sent an URL about the convention to be followed for naming indexes, constraints etc. I have lost it somewhere. Could any of you who have saved and have it now send it to me? I am in need of it very badly. TIA and regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Entity Relationship diagrams
Dear DBA Gurus, I have around 30 tables for which the columns, datatypes, size of each datatype, referential integrity constraints is decided. I need to draw E-R diagram linking each table with one or many tables. I am looking for a tool which will draw the diagram for me if I provide the column names, its datatypes, referential integrity constraints etc. Is there any free tool which will do this for me? If not, how do I do this? Any URLs or links or documents will be very much helpful. Any help in this regard will be very much appreciated. TIA and Regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle Intermedia Search Query
Dear DBA Gurus, I have the following records in Oracle 8.1.6 database (CLOB fields). idtitle 1 kid's 2 kid and teen 3 kid's 4 kid's and teens If I write a query as select * from site where contains(title,'kid''s') 0 then it displays all the 4 records where are I want only records 1, 3 and 4 only. If I try with: select * from site where contains(title, 'kid||chr(39)||s') 0 then it is returning error ORA - 29902. How do I write the query in order to return the correct result? Any help in this regard will be very much appreciated. TIA and Regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Intermedia text table design
Dear DBA Gurus,
I have given below the procedure as how we are creating the tables,
preference and indexing for the same. Could you please check if there is any
other preference need to be set for CLOB Datatypes which I missed?
1) To create category table.
CREATE TABLE CATEGORY
PK_CATEGORY_IDNUMBER NOT NULL,
PARENT_CATEGORY NUMBER NOT NULL,
NAME VARCHAR2 (1000) NOT NULL,
DEPTH VARCHAR2 (4000) NOT NULL,
STATUSNUMBER NOT NULL,
UPDATED_DATETIME DATE,
PRIMARY KEY ( PK_CATEGORY_ID ) );
2. To create site table.
CREATE TABLE SITE
PK_SITE_ID NUMBER NOT NULL,
FK_CATEGORY NUMBER NOT NULL,
TITLECLOB,
URL VARCHAR2 (4000) NOT NULL,
DESCRIPTION CLOB,
STATUS NUMBER NOT NULL,
PAGE_HITSNUMBER NOT NULL,
EDITOR_CHOICE VARCHAR2 (10),
PRIMARY KEY ( PK_SITE_ID ));
ALTER TABLE SITE ADD CONSTRAINT FKSITE
FOREIGN KEY (FK_CATEGORY)
REFERENCES VCPLNEW.CATEGORY (PK_CATEGORY_ID) ;
3) Creating an index after inserting the data(Datebase Updation) in both
the tables.
a) Execute this script to create a preference.
begin
ctx_ddl.create_preference('sitelexer', 'BASIC_LEXER');
ctx_ddl.set_attribute('sitelexer', 'printjoins', '_-');
ctx_ddl.set_attribute('sitelexer', 'endjoins', '%');
ctx_ddl.set_attribute ( 'sitelexer', 'index_text', 'YES');
ctx_ddl.set_attribute ( 'sitelexer', 'mixed_case', 'NO');
end;
b) Execute this script to create an indexes.
CREATE INDEX site1descidx ON
site(description)
indextype is ctxsys.context
parameters ( 'LEXER sitelexer' );
CREATE INDEX site2titleidex ON
site(title)
indextype is ctxsys.context
parameters ( 'LEXER sitelexer' );
TIA and Regards,
Ranganath
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ranganath K
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
How to call a stored procedure from Java
Dear DBA Gurus, Can you give me some examples as to how do I create a Pl/Sql block or Stored Procedure which will have multiple queries returning multiple records from ResultSet and call that from Java? Any help in this regard will be greatly appreciated. TIA and Regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle Masters program
Dear Mr. Robert, Hi! Do you have the ILT Study material for any of the masters' modules? If so, can you lend it to me? By the way, in which countries is the masters certification program conducted? Please reply. Thanks and Regards, Ranganath -Original Message- Robert D SSgt AFIT/SCA Sent: Tuesday, March 06, 2001 11:52 PM To: Multiple recipients of list ORACLE-L As to whether or not it still exists...this should answer your question: http://education.oracle.com/masters/index.html -Original Message- Sent: Tuesday, 06 March, 2001 9:05 AM To: Multiple recipients of list ORACLE-L Does it still exist? I can't find reference to it anywhere in the new www.oracle.com http://www.oracle.com layout. I am just curious - I had major problems with it when they informed me that taking Microsoft courses on NT wasn't good enough, to be certifiable as an Oracle on NT Master you had to have learned NT from... Oracle. Even though their course names were identical to the MS ones. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Rgion des Maritimes, MPO E-Mail: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yexley Robert D SSgt AFIT/SCA INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How to increase the number of inserts into a table to 2000 per second
Dear DBA Gurus I am using Oracle 8.1.6 Work group server on Sun Solaris 2.8. I want to increase the number of inserts into a particular table per second to 2000 per second. Sun Microsystems claims that with the current hardware configuration the number of inserts can go upto 2800 per second. Currently it is only 600 per second. The insertion is done through a C program using OCI with an embedded SQL statement. I am using a SunE4500 machine with 130 gb hard disk, 4gb RAM and 4 Sparc CPU machine. Please let me know as to how do I increase the number of inserts into that table as we require to implement the same into our application. Any help in this regard will be higly appreciated. TIA and regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
