Re: how to hint a unique index scan?
You can define the exact index you want to use. /*+ INDEX ( table [index [index]...] ) */ HTH, Mike [EMAIL PROTECTED] wrote: Is there anyway to hint a specific type of scan? I know you can hint a fast full scan. I have something odd happening. I made a copy of a tablespace and transported that copy with in the same instance. So in the one instance. I have two copies of this tablespace. I have two tables that are 99% identical. IE one has about 1% more records and if you validate the indexes on each they are also less than 1% apart. Now in one tablespace an update does an Unique Scan and in the other the EXACT same update does a full scan and runs ALOT slower. I want to mess around. anyway to force a unique index scan? Both tables are analyzed. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michal Zaschke 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: Fixed_date and dbms_job
Hi, 1) check job_queue_processes and job_queue_interval parameters in your init.ora file 2) have a commit after dbms_job.submit 3) if you want the procedure to be executed every second, then the next date should be 'sysdate+1/86400' = dbms_job.submit(:job_num, 'myprocedure;',sysdate, 'sysdate+1/86400'); Mike Kader Ben wrote: Hi Listers, I'm simulating the date in future with fixed_date. I wrote procedure to be called every seconde through dbms_job to increment the fixed_date. I did that dbms_job.submit(:job_num, 'myprocedure;', sysdate, 'sysdate'); the dba_jobs table show me the right interval un next_date: BROKEN ST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL N 13-JUN-03 00:00:00 13-JUN-03 17:50:06 sysdate And the sysdate is: 13-JUN-2003 17:50:06 But the job never execute. Could you please give me hint how to resolve this problem? Thanks you, Ben __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Ing. Michal Zaschke DB Administrator Sokolovska uhelna, a.s. tel.: +420 352 465417 e-mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michal Zaschke 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: DBMS_JOB : Can submit but does not execute
Have you set the job_queue_processes parameter in you init.ora file?
CHAN Chor Ling Catherine (CSC) wrote:
Hi Gurus,
I have submitted a job but it does not execute. Why? Does the owner require any privileges ? However, I can use DBMS_JOB.CHANGE command to execute immediately. I would like the job to execute on a daily basis. Any advice ? TIA
--
-- Submit a job
--
1 DECLARE
2job BINARY_INTEGER;
3 BEGIN
4DBMS_JOB.SUBMIT(job,'PROCEDURE_NAME;',SYSDATE,'SYSDATE+1');
5DBMS_OUTPUT.PUT_LINE(TO_CHAR(JOB));
6* END;
SQL /
1
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Execute the job
--
1 begin
2 DBMS_JOB.CHANGE(1,
3 ' PROCEDURE_NAME;',to_date('1802200308:00:00','ddmmhh24:mi:ss'),'SYSDATE+1');
4* END;
SQL /
PL/SQL procedure successfully completed.
SQL COMMIT;
Commit complete.
--
-- Check whether the job is submitted
--
SQL SELECT * FROM DBA_JOBS
JOB LOG_USER PRIV_USER
- -- --
SCHEMA_USERLAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC TOTAL_TIME B
-- - - - -- -
INTERVAL
FAILURES
-
WHAT
NLS_ENV
MISC_ENV INSTANCE
-
1 USER1 USER1
USER1 17-FEB-03 20:01:2018-FEB-03 08:00:00 16 N
SYSDATE+1
0
PROCEDURE_NAME;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUME
RIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-YY' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
01020002 0
Regds,
Catherine
--
Ing. Michal Zaschke
DB Administrator
Sokolovska uhelna, a.s.
tel.: +420 352 465417
e-mail: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Michal Zaschke
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: NOT NULL constraint and long parse time
(or with /*+RULE*/ hint) my query is quick in both cases. If you have any info about this behaviour, tell me please. Thanks Mike -- Ing. Michal Zaschke DB Administrator Sokolovska uhelna, a.s. tel.: +420 352 465417 e-mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michal Zaschke 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).
NOT NULL constraint and long parse time
Hi all, I had a problem with long parse time on one of my queries and want to know if it is a bug or what. Suppose two tables: tab1 and tab2 on my database 9.0.1.3.0. These tables are both empty. Have a look at the column tab1.col1. The NOT NULL constraint on it can be defined in two ways: 1) in a create table definition 2) with alter table add constraint create table tab2 (id number(10) not null, col1 number(1) not null); create table tab1 (id number(10) not null, tab2id number(10) not null, col1 char(12) not null, col2 number(4) not null); --create table tab1 (id number(10) not null, tab2id number(10) not null, col1 char(12), col2 number(4) not null); --alter table tab1 add constraint cns_tab1_col1 check (col1 is not null); alter table tab1 add constraint pk_tab1 primary key (id); alter table tab2 add constraint pk_tab2 primary key (id); analyze table tab1 compute statistics; analyze table tab2 compute statistics; Now I want to execute SELECT: select tab2.col1, count(tab1.col1) from tab2, tab1 where (tab1.tab2id=tab2.id) and (tab1.col2 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25)) group by tab2.col1; In case 1) the parse time is too long and gets longer with more numbers in IN condition. While in case 2) the parse time doesn't depend on the number of values in IN condition. I traced the query in both cases and the only difference was in calling one more SELECT in case 2) just before executing my query. The query executed before was: select condition from cdef$ where rowid=:1 And a note: Without ANALYZE on the tables (or with /*+RULE*/ hint) my query is quick in both cases. If you have any info about this behaviour, tell me please. Thanks Mike -- Ing. Michal Zaschke DB Administrator Sokolovska uhelna, a.s. tel.: +420 352 465417 e-mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michal Zaschke 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: Location of Trace file
Look in $ORACLE_HOME/dbs for initSID.ora file. It will be the file or the link to the file there. HTH, Mike [EMAIL PROTECTED] wrote: Hi , I want to know the location of trace file.I do not know location of init.ora. How to get it. Thanks Manoj -- Ing. Michal Zaschke DB Administrator Sokolovska uhelna, a.s. phone: +420 352 465417 e-mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michal Zaschke 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: Unix script to stop pwd-protected listener
Hi, we have lsnrctl with 750 and listener.ora with 600 permissions and everything runs fine. For job stopping process you can then use the encrypted password wirtten in listener.ora. HTH, Mike [EMAIL PROTECTED] wrote: Hi, Solaris 8, Oracle 9i listener It seems that anyone who has a login on Solaris can shut the listener down. I have tried with a non-dba userid and could stop the listener. The default file permission for ORACLE_HOME/bin/lsnrctl is 751, and for ORACLE_HOME/network/admin/listener.ora file is 644. I asked Oracle if I could change them to 750 and 640 respectively and they said that you should not change the defaults since they are verified against the system. So I password protected it. However to stop a password-protect listener you need to do the following interactively: run lsnrctl, issue set password command, put in the password, issue stop, issue exit. I need to implement this stop in a Shell script so that I can call the script at the server reboot time. Do you know how to supply a password to lsnrctl set password command in a script? Have attempted with the script but have not got it worked yet. Thanks Long -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Ing. Michal Zaschke DB Administrator Sokolovska uhelna, a.s. phone: +420 352 465417 e-mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michal Zaschke 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: Unix script to stop password-protected listener
Yes, you can change your listener.ora file permission up to 600 on the server side. In a shell script you can then use encrypted password found in listener.ora on line PASSWORDS_listener. lsnrctl EOF set password C6C144CF750E3CA5 stop exit EOF If the password is not in the listener.ora file, run lsnrctl, set password manualy and execute SAVE_CONFIG. This will write a line into your listener.ora file with the encrypted password. HTH, Mike Choudhary Rajendra (TTL_LKO) pe: Hi , We can very well protect the Server side executables by changing the permission of the file . We had implemented this on all our database sites and normal user don't have access to lsnrctl,svrmgrl,namesctl etc .You can also protect the listener.ora file by changing the permission to 770 or 700 as applicable to your environment . The sqlnet.ora file need to be given read access to all and also the tnsnames.ora file (If you have not gone for nameserver) . Regards Rajendra -Original Message- Sent: Tuesday, October 08, 2002 11:13 AM To: Multiple recipients of list ORACLE-L Hi, Solaris 8, Oracle 9i listener It seems that anyone who has a login on Solaris can shut the listener down. I have tried with a non-dba userid and could stop the listener. The default file permission for ORACLE_HOME/bin/lsnrctl is 751, and for ORACLE_HOME/network/admin/listener.ora file is 644. I asked Oracle if I could change them to 750 and 640 respectively and they said that you should not change the defaults since they are verified against the system. So I password protected it. However to stop a password-protect listener you need to do the following interactively: run lsnrctl, issue set password command, put in the password, issue stop, issue exit. I need to implement this stop in a Shell script so that I can call the script at the server reboot time. Do you know how to supply a password to lsnrctl set password command in a script? Have attempted with the script but have not got it worked yet. Thanks Long -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Choudhary Rajendra (TTL_LKO) 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). -- Ing. Michal Zaschke DB Administrator Sokolovska uhelna, a.s. phone: +420 352 465417 e-mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michal Zaschke 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: Numeric comparison in DECODE statement
Because it is not possible to put 30 into format '9.99'. Increase your
format in to_char to '99.99'. ;-)
Steven Joshua wrote:
Hi, I tried this. not work well when in_value 60:
SELECT TO_CHAR( CASE WHEN 30 60 THEN 30 ELSE 30/60
END , '9.99') FROM DUAL;
it returns:
TO_CH
-
#
any idea why?
Thanks
--- Jan Pruner [EMAIL PROTECTED] wrote:
Don't use DECODE, but CASE
SELECT TO_CHAR(
CASE WHEN in_value 60 THEN in_value ELSE
in_value/60 END
, '9.99'
) FROM DUAL;
JP
Reply
Separator
Author: Yexley Robert D Contr Det 1 AFRL/WSI
[EMAIL PROTECTED]
Date: 5/20/2002 7:08 AM
I was wondering if anyone might have tried this
before, because I can't
seem to get it to work. I'd like to be able to
determine which unit of
measure to concatenate to a value by using a
decode statement in the query.
I have a column in the database that stores time
in minutes, and I'd like
to be able to show the output in minutes if the
value is less than 60, but
in hours (such as 3.27 hours) if the value is
greater than 60. So far I've
tried the following statement, but it seems to be
blowing up on the first
comparison operator:
SELECT decode(in_value,
to_char(to_number(in_value) = to_number('60')),
to_char(in_value)||' minutes',
to_char(to_number(in_value)
to_number('60')), to_char(in_value/60, '9.99')||'
hours')
FROM dual
/
I'm selecting from dual just until I can get the
query working at all. Is
what I'm trying to do even possible? Any help or
ideas would be greatly
appreciated. Thanks in advance.
_YEX_
/*
|| Robert D. Yexley
|| Oracle Programmer/Analyst
|| Easylink Services Corporation
|| Professional Services
|| Contractor - Wright Research Site MIS
|| Det-1 AFRL/WSI Bldg. 45 Rm. 062
|| (937) 255-1984
|| [EMAIL PROTECTED]
|| )))
*/
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Jan Pruner
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).
__
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steven Joshua
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).
--
Ing. Michal Zaschke
DB Administrator
Sokolovska uhelna, a.s.
phone: +420-168-465417
e-mail: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Michal Zaschke
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).
svrmgrl vs. sqlplus
Hi all, I have problem with my oracle client on linux. Using 'svrmgrl' I can connect to a database but if I use 'sqlplus', I get an ORA-01034 error. Can anybody say me why? And how to repair it? Server: Alpha with Tru64 Unix 4.0F: Oracle7 Server Release 7.3.4.0.0 with the 64-bit option - Production PL/SQL Release 2.3.4.0.0 - Production Client: Red Hat Linux release 6.1, Kernel 2.2.19-6.2.7enterprise on a 4-processor i686 Oracle8 Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.0.0 - Production If I try 'svrmgrl', it's OK: $ svrmgrl Oracle Server Manager Release 3.0.5.0.0 - Production (c) Copyright 1997, Oracle Corporation. All Rights Reserved. Oracle8 Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.0.0 - Production SVRMGR connect test/test@test Connected. SVRMGR But trying 'sqlplus' I get an error: $ sqlplus test/test@test SQL*Plus: Release 8.0.5.0.0 - Production on Thu Apr 11 16:35:46 2002 (c) Copyright 1998 Oracle Corporation. All rights reserved. ERROR: ORA-01034: ORACLE not available Help me, please. Mike P.S.: This error (1034) in sqlplus comes only with connecting to Oracle7. I have no problem with connecting to Oracle8 or higher. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michal Zaschke 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: svrmgrl vs. sqlplus
All my sqlnet.ora is: names.default_domain = suas.cz sqlnet.expire_time = 10 John Weatherman wrote: Michal, The only problem similar to that I have seen on 6.1 Red Hat was the result of a problem in the sqlnet.ora. Are you sure the names, TRACE_LEVEL, etc that should be the same for all the target instances (78) are the same? Good Luck! John -Original Message- Sent: Thursday, April 11, 2002 11:38 AM To: Multiple recipients of list ORACLE-L Hi all, I have problem with my oracle client on linux. Using 'svrmgrl' I can connect to a database but if I use 'sqlplus', I get an ORA-01034 error. Can anybody say me why? And how to repair it? Server: Alpha with Tru64 Unix 4.0F: Oracle7 Server Release 7.3.4.0.0 with the 64-bit option - Production PL/SQL Release 2.3.4.0.0 - Production Client: Red Hat Linux release 6.1, Kernel 2.2.19-6.2.7enterprise on a 4-processor i686 Oracle8 Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.0.0 - Production If I try 'svrmgrl', it's OK: $ svrmgrl Oracle Server Manager Release 3.0.5.0.0 - Production (c) Copyright 1997, Oracle Corporation. All Rights Reserved. Oracle8 Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.0.0 - Production SVRMGR connect test/test@test Connected. SVRMGR But trying 'sqlplus' I get an error: $ sqlplus test/test@test SQL*Plus: Release 8.0.5.0.0 - Production on Thu Apr 11 16:35:46 2002 (c) Copyright 1998 Oracle Corporation. All rights reserved. ERROR: ORA-01034: ORACLE not available Help me, please. Mike P.S.: This error (1034) in sqlplus comes only with connecting to Oracle7. I have no problem with connecting to Oracle8 or higher. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michal Zaschke 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: FW: insert privilege across db link
I think that it depends on type of database link you created. CONNECTED_USER versus CURRENT_USER. Imagine two users - user A in database A and user B in database B. User B wants to connect to database A using link and do something... Now: If the database link is of type CONNECTED_USER then user B connects to database A as /connected_/ user B so the rights must be done for user B. If the database link is of type CURRENT_USER then user B connects to database A as a user who owns the trigger (in this case as /current_/user A). If I understood you problem clearly it will be better to do database link of type CURRENT_USER (your public db link in the example is of type CONNECTED_USER). Try reading http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76960/ds_admin.htm#24406 Maybe it will be clearer. :-) HTH, Mike Kathy Duret wrote: Ok let's start over.hopefully this will be clearer. I created a public database link owned by system, system is the user I created the database link under. created in my Production database: create public database link ARCHIVE connect to system identified by repadmin_pass using 'ARCHIVE'; create public database link PROD connect to system identified by repadmin_pass using 'PROD'; User produser which owns the original tables is in the Production database User archiveuser owns the archive tables in the Archive database. Triggers were created under produser in the PROD database so when a delete takes place a row is inserted into archiveuser table in the Archive database. Triggers work fine when A schema tables are in Archive database. I need to grant some sort of insert permission but how. Grant insert on atable to A@PROD doesn't work tried to grant insert on all tables to A, didn't work either. I get a permission error when the triggers are fired SQL @cre_trig AFTER DELETE ON atable * ERROR at line 2: ORA-01031: insufficient privileges Thanks Kathy -Original Message- Sent: Thursday, January 03, 2002 2:25 PM To: Multiple recipients of list ORACLE-L what is the user in the db link. it it b or some other user? babu - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 03, 2002 4:30 PM the db link is a public one owned by system. User B is only in Bshema. User A with the trigger is only is Aschema. Kathy -Original Message- Sent: Thursday, January 03, 2002 1:16 PM To: Multiple recipients of list ORACLE-L in the db link are you using the same user as the table owner in the remote db. in your case are you using the user b in your dblink? babu - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 03, 2002 3:35 PM database is 8.1.7 how do I grant insert privilege to a user in a different database? Note: the database link in place in both database and I can describe tables in each just fine. In the trigger I already have the insert into b.address@bschema. When I try to execute the trigger I get insufficient privileges. I have tried the trigger locally and it works just fine it is when I try to insert a row across the database link I get insufficient privileges. What am I missing/forgetting? Thanks Kathy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michal Zaschke 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).
Oracle9i Error within Cursor
Hi all,
I wrote a script bellow. If it is run on Oracle 8 it runs fine and
gives me all the result but on Oracle 9i it returns the error ORA-06502.
Can anybody tell me please why?
I have found a workaround - instead of
select col||'x' colu from tbl union select col colu from tbl;
use
select col||'x' colu from tbl union select col||null colu from tbl;
But it doesn't tell me, why the first statement doesn't work on 9i.
Thak you for your responses.
Mike
script
set serveroutput on
create table tbl (col varchar2(1));
insert into tbl values ('a');
insert into tbl values ('b');
select * from tbl;
select col||'x' colu from tbl union select col colu from tbl;
declare
cursor cA is
select col||'x' colu from tbl union select col colu from tbl;
begin
for vA in cA loop
dbms_output.put_line(vA.colu);
end loop;
end;
/
drop table tbl;
/script
The error message:
SQLWKS declare
2 cursor cA is
3 select col||'x' colu from tbl union select col colu from
tbl;
4 begin
5 for vA in cA loop
6 dbms_output.put_line(vA.colu);
7 end loop;
8 end;
9 /
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5
--
Ing. Michal Zaschke
DB Administrator
Sokolovska uhelna, a.s.
phone: +420-168-465417
e-mail: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Michal Zaschke
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: Update table problem
Maybe: update tbl set col=replace(col,' ',''); Harvinder Singh pe: Hi, We inserted data into 1 of the column as test 1 abc while the actual value is test1abc without spaces. There are about 1 million rows in a table. Is there a way to update the table column and remove the extra space . Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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). -- Ing. Michal Zaschke DB Administrator Sokolovska uhelna, a.s. phone: +420-168-465417 e-mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michal Zaschke 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: select from dual?
Did you mean somethink like this:
select 30,'where name in (''NAME1'',''NAME2'')' || ' ;' from dual;
Tatireddy, Shrinivas (MED, Keane) pe:
Hi lists
Whats wrong with the following statement:-
select 30,'where name in ('NAME1','NAME2')' || ' ;' from dual;
where as the following is working fine:
select 30, 'where id in (10,20)' || ' ;' from dual;
can anybody throw some light?
thanx in adv
srinivas
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tatireddy, Shrinivas (MED, Keane)
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).
--
Ing. Michal Zaschke
DB Administrator
Sokolovska uhelna, a.s.
phone: +420-168-465417
e-mail: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Michal Zaschke
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: Sql question - please reply fast
create table AAA (NAME varchar2(30));
insert into AAA values ('Pelle T');
insert into AAA values ('Kalle S1000');
insert into AAA values ('Info missing');
insert into AAA values ('Pelle Svensson T3');
insert into AAA values ('Bad info');
select decode(name, 'Info missing',NAME,'Bad info',NAME,substr(NAME, 1, instr(NAME, '
', -1)-1)) NAME from AAA;
HTH,
Mike
[EMAIL PROTECTED] pe:
Hej
Would like to have som help with this. Please give me an example of sql statement.
I have field1.
Pelle T
Kalle S1000
Info missing
Pelle Svensson T3
Bad info
and want this result after the selectstatement.
Pelle
Kalle
Info missing
Pelle Svensson
Bad info
which means that I dont want to have the last word in this field except
'Info missing' and 'Bad info'. These two should remain unchanged, but all other
should not have the last word .
Thanksin advance
Roland S
--
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).
--
Ing. Michal Zaschke
DB Administrator
Sokolovska uhelna, a.s.
phone: +420-168-465417
e-mail: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Michal Zaschke
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: I forget the sight!!!
http://www.oraperf.com/analyze.html Murali Vallath wrote: I have not used it for a while and inbetween switching jobs I missed it. What's the site that formats your statspack and estat/bstat reports. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Murali Vallath 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). -- Ing. Michal Zaschke DB Administrator Sokolovska uhelna, a.s. phone: +420-168-465417 e-mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michal Zaschke 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: ODBC driver setup is not started
In my case it was because of bad PATH variable. It must have "c:\orant\bin" (or wherever you installed) in it. I changed it in (as I remember) Start/Control Panel, System, Environment variables HTH Mike "Nguyen, Long (ITS, Limestone Ave)" wrote: Hi, A user had just installed Oracle Client 8.0.5 on NT4 machine. When she got into Setting\Control Panel\ODBC admin and clicked on the Add button, selected the Oracle ODBC driver 8.0.5 and click Finish, the ODBC Driver Setup screen did not appear. Did anyone come across this problem and know what went wrong here? Thanks a lot. Long -- Ing. Michal Zaschke DB Administrator Sokolovska uhelna, a.s. phone: +420-168-645417 e-mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michal Zaschke 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: Problem with database link
AFAIK, parameter DB_DOMAIN is what appends to the database link name (which must be DB_NAME of your remote database). SQLWKS show parameter db_domain NAME TYPEVALUE -- --- -- db_domain string WORLD Helmut Daiminger wrote: Hi there! I'm having a problem with database links here. I am on my SPEEDY database and want to create a link that points to another database called SAM. create database link SAM connect to user identified by pasword using 'SAM'; Then I try and issue that statement: select view_name from user_views@SAM * ERROR at line 2: ORA-02085: database link SAM.VIVONET.COM connects to SAM Where does our domain name appendix (vivonet.com) come from I looked in all the possible config files (e.g. sqlnet.ora etc.) and couldn't find anything. In both databases the init.ora parameter is set to global_names = true. Any idea? This is 8.1.6 on Win2k. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Helmut Daiminger 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). -- Ing. Michal Zaschke DB Administrator Sokolovska uhelna, a.s. phone: +420-168-645417 e-mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michal Zaschke 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: *.dbf, *.ctl, redo*.log access rite
We have: -rw-r- for all of them Mike Sinardy Xing wrote: Hi guys, Can you tell me what is your access rite for those files in unix solaris 7 oracle 8.1.6, my current access rite are: rwxrwxr-x for all of them. Sinardy -- Ing. Michal Zaschke DB Administrator Sokolovska uhelna, a.s. phone: +420-168-645417 e-mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michal Zaschke 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: Tables and indexes on different disks
Thank you all for your responses. I'll be more thoughtful for then next time. Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michal Zaschke 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 Error!!!Help
!oerr ora 6512 06512, 0, "na %sline %s" // *Cause: Backtrace message as the stack is unwound by unhandled // exceptions. // *Action: Fix the problem causing the exception or write an exception // handler for this condition. Or you may need to contact your // application administrator or DBA. "Dash, Saroj (CAP,CEF)" pe: Hello all, I am getting an oracle Error ,But unable to understand . The error number is ;0ra-6512. But i did not found any pl/sql error message. Please help me. Regards, saroj. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dash, Saroj (CAP,CEF) 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). -- Ing. Michal Zaschke DB Administrator Sokolovska uhelna, a.s. phone: +420-168-645417 e-mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michal Zaschke 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).
Tables and indexes on different disks
Hi all, I've read an interesting section in Oracle Docu (Tuning I/O): --- Separating Tables and Indexes It is not necessary to separate a frequently used table from its index. During the course of a transaction, the index is read first, and then the table is read. Because these I/Os occur sequentially, the table and index can be stored on the same disk without contention. --- Why do you then recommend to separate tables and indexes to different disks? Thanks Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michal Zaschke 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: Conversion of datatype
use: select ltrim(to_char(w_seqline, '9')) into w_nseqline from dual; Eriovaldo do Carmo Andrietta pe: I have this sentence in a pl/sql block : w_nseqline := w_seqline; select lpad(to_char(w_seqline, '9'), '0') into w_nseqline from dual; w_nseqline := w_seqline; w_nseqline is char(6); w_seqline is number(6). I want the result '1' but the code above is giving me '1 ' How can I solve it ? Thanks Eriovaldo do Carmo Andrietta ICQ #102604225 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eriovaldo do Carmo Andrietta 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). -- Ing. Michal Zaschke DB Administrator Sokolovska uhelna, a.s. phone: +420-168-645417 e-mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michal Zaschke 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).
