Re: commit for triggers
://wine.msn.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd 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.net -- Author: John Flack 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.net -- Author: Bobak, Mark 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.net -- Author: Arup Nanda 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: (Non)Unique Index Vs Unique Constraint
; create table index_test(c1 number,c2 varchar2(20)); create index i1 on index_test(c1); alter table index_test add constraint index_test_uk1 UNIQUE(c1); 2)UNIQUE index Vs Unique Constraint drop table index_test; create table index_test(c1 number,c2 varchar2(20)); create UNIQUE index i1 on index_test(c1); alter table index_test add constraint index_test_uk1 UNIQUE(c1); Thanks in advance, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay 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.net -- Author: Jonathan Lewis 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.net -- Author: Jay 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.net -- Author: Arup Nanda 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_OBFUSCATION_TOOLKIT
Yes. As long as your KEY_STRING is same, the encrypted value will be the same. Consider: SQL exec :i := rawtohex(utl_raw.cast_to_raw (dbms_obfuscation_toolkit.DES3Encrypt (input_string='12345678', key_string='123456789012345678901234'))) PL/SQL procedure successfully completed. SQL print i I--FB90F134036ABD29 Do it again. SQL exec :i := rawtohex(utl_raw.cast_to_raw (dbms_obfuscation_toolkit.DES3Encrypt (input_string='12345678', key_string='123456789012345678901234'))) PL/SQL procedure successfully completed. SQL print i IFB90F134036ABD29 The encrypted version is the same. This also brings up another good point - the key plays a vital role in the encryption process and should be as randomized as possible. In Oracle 9i you have the DESGetKey function. The other question is why do you want to do this? Perhaps you are trying to build a integrity assurance system that calculates an encrypted value before delivery, and then after delivery to confirm that data has not been tampered with in transit. If so, I would receommend using a hash, not encryption, i.e. MD5 procedure in the same package. It's less taxing on resources and does exactly what you want to do. Key management becomes easier in hashing, too. Hope this helps. Arup - Original Message - From: Paula Winkler To: Multiple recipients of list ORACLE-L Sent: Friday, January 16, 2004 11:24 AM Subject: DBMS_OBFUSCATION_TOOLKIT Hi Listers, Does the Oracle dbms_obfuscation_toolkit (DOT) support generating a non-randomized encrypted result? In other words, can we encrypt something like international and get the same encrypted result each time we pass international to the DOT? - Paula W. Do you Yahoo!?Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
Re: Suggestions Needed: Latch free - library cache
the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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.net -- Author: Ryan 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). = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan 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.net -- Author: Ryan 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.net -- Author: Arup Nanda 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: Books on rac
Title: RE: Books on rac "all the other non-Ault books from Rampant are total trash." That's a pretty strong statement, Ryan! Mine is a non-Ault Rampant book; does it count, too? Before you trash the book and commit the statement, can I interest you in at least taking a look at it? http://www.amazon.com/exec/obidos/ASIN/0972751394/qid%3D1073685823/sr%3D11-1/ref%3Dsr%5F11%5F1/104-5146136-6379164 Six months of hard work gotta deserve something better than the recyclebin, I sincerely hope. Regards, Arup Nanda - Original Message - From: April Wells To: Multiple recipients of list ORACLE-L Sent: Friday, January 09, 2004 4:14 PM Subject: RE: Books on rac wait... mine wasn't THAT bad... isn't out yet... but isn't that bad. April Wells Oracle DBA/Oracle Apps DBA Corporate Systems Amarillo Texas @-- Few people really enjoy the simple pleasure of flying a kite Adam Wells age 11 -Original Message- From: Ryan [mailto:[EMAIL PROTECTED]] Sent: Friday, January 09, 2004 2:59 PM To: Multiple recipients of list ORACLE-L Subject: Re: Books on rac beware the rampant press books. Most of them seem to be total garbage. All in large print with little detail. I didnt think much of the Ault Internals book from Rampant... its basically stuff you can copy and paste from metalink. Dont know about his RAC book. However, all the other non-Ault books from Rampant are total trash. There is another RAC book with some stuff on 10g by a guy who monitors this listserv(Murali Vallath). I have a copy of it, but have not read it yet. Anyone read either of those RAC books? - Original Message - To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: Friday, January 09, 2004 3:19 PM Joe, Last year at the midaltantic Oracle users group seminars there was a presentation by Mike Ault what was very informative on RAC with a budget. I believe that he has some decent information available. You might check www.rampant-books.com for his works. Ron [EMAIL PROTECTED] 01/09/2004 2:59:26 PM any recommendations? of course besides the oracle docs and technet, which i think i downloaded all that i need. joe -- Joseph S Testa Chief Technology Officer Data Management Consulting p: 614-791-9000 f: 614-791-9001 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joe Testa 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.net -- Author: Ron Rogers 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.net -- Author: Ryan 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). The information contained in this communication, including attachments, is strictly confidential and for the intended use of the addressee only; it may also contain proprietary, price sensitive, or legally privileged information. Notice is hereby given that any disclosure, distribution, dissemination, use, or copying of the information by anyone other than the intended recipient is strictly prohibited and may be illegal. If you have received this communication in error, please notify the sender immedia
Re: Obtain SQL Statement from audit
You haven't specified the Oracle version. If it's 9i, you could use Fine Grained Auditing (FGA) to get the exact SQLs. Hope this helps. Arup - Original Message - From: Mauricio Vélez To: Multiple recipients of list ORACLE-L Sent: Monday, December 29, 2003 1:39 PM Subject: Obtain SQL Statement from audit Hello everybody I am auditing select statements on one table, so I put the initialization parameter audit_trail = DB and I query the dba_audit_trail and sys.aud$ views and I can get information but I can't get the sql statement. The question is how can I retrieve the sql statement used to select on my table? Regards, Mauricio Vélez Do you Yahoo!?Yahoo! Photos - Get your photo on the big screen in Times Square
Re: Exporting a partition with transport tablespace
Hope it improved your hit ratio :) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 29, 2003 1:59 PM Yes, that's why I went to a memory improvement training few weeks ago ;) Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 29, 2003 6:59 PM Your brain is getting full. You should stop studying so much. See what it does to you? -Original Message- Yep, I didn't remember the exact clause in the exchange partition syntax. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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.net -- Author: Tanel Poder 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.net -- Author: Arup Nanda 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: A performance problem
) NEW10,SUBSTR(GLCC.SEGMENT6,1,6) PRODUCT,SUBSTR(GLCC.SEGMENT5,1,4) TRANSTYPE,NVL(SUBSTR(MGNAL.GL11PROD_ACCOUNT,1,5 13 9737644 749049.54 1419451399 30.18 SELECT DISTINCT ENTITY,ACCOUNT,COST_CENTER,INTERCOMPANY,TRANSACTION_TYPE,PRODUC T,LOCATION,CHANNEL,FUTURE,PERIOD_NAME,SUM(BAL) BALAMOUNT,SUM(MTD) MTDAMOUNT FROM (SELECT DISTINCT ENTITY,ACCOUNT,COST_CENTER,INTERCOMPANY,TRANSACTION_TYPE,PRODUC T,LOCATION,CHANNEL,FUTURE,PERIOD_NAME,0 BAL,(ABS(NVL(MTD_TRANSACTION_DR_AMOUNT 30 5839191 194639.70 2733501134 48.27 I am not sure on how to interpret the SQL*Net message from dblink wait event. Obviously we have a db link on this database pointing to another production database into which the data is being fed. Does this wait event indicate a network issue more so than a database issue? What else jumps out here? Thanks. Venu Potluri Oracle Financials DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Potluri, Venu (CT Appl Suppt) 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.net -- Author: John Kanagaraj 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.net -- Author: Potluri, Venu (CT Appl Suppt) 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.net -- Author: Arup Nanda 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: Risk of knowing password hash value (Was: OEM permissions)
to DBARTISAN_USER_ROLE; grant SELECT on SYS.PRODUCT_COMPONENT_VERSION to DBARTISAN_USER_ROLE; grant SELECT on SYS.DBA_EXTENTS to DBARTISAN_USER_ROLE; grant DBARTISAN_USER_ROLE to USER_WE_DONT_LIKE; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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.net -- Author: Jared Still 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.net -- Author: Norris, Gregory T [ITS] 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). This information in this e-mail is intended solely for the addressee and may contain information which is confidential or privileged. Access to this e-mail by anyone else is unauthorized. If you are not the intended recipient, or believe that you have received this communication in error, please do not print, copy, retransmit, disseminate, or otherwise use the information. Also, please notify the sender that you have received this e-mail in error, and delete the copy you received. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Davey, Alan 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.net -- Author: Arup Nanda 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: RMAN - the time has come
Title: RMAN - the time has come In addition to the Freeman book, I would also suggest the RMAN Pocket Reference from O'Reilly. It predates the RF book and certainly comes handy for learning - I learned from there. HTH. Arup Nanda - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Monday, December 22, 2003 10:34 AM Subject: RMAN - the time has come Okay, its time to bite the bullet ... time to learn RMAN. Outside of TFM (which I just started reading), are there any good books/articles on RMAN? I know there is one by RFreeman, and it is for 9i (Robert, will there be a 10g version?) Yeah, I am also google'ing, taeoma'ing and in general stfw'ing whenever time permits. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! **This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.**4
Re: Exporting a partition with transport tablespace
Title: Exporting a partition with transport tablespace
No it's not. However, you could simply exchange the
partition with a table and then export the tablespace.
For instance,
ALTER TABLE HISTO_DOSSIEREXCHANGE PARTITION
part1 WITH TABLE HISTO_DOSSIER_part1;
Then export the tablespace. Make sure you do the
same for any local indexes, too.
HTH.
Arup Nanda
- Original Message -
From:
NGUYEN Philippe (Cetelem)
To: Multiple recipients of list ORACLE-L
Sent: Monday, December 22, 2003 10:34
AM
Subject: Exporting a partition with
transport tablespace
Hi list, is it
possible to export a partition with the transportable tablespace feature
? My partition is over 8 Go.
Here my statements , thank you in advance !
SQLexec
sys.dbms_tts.transport_set_check('HISTO_DOSSIER_P1_MD_TAB',FALSE);
SQL select * from
sys.transport_set_violations;
VIOLATIONS
Partitioned table TOPASE.HISTO_DOSSIER is
partially contained in the transportab le
set: check table partitions by querying sys.dba_tab_partitions
Default Partition (Table) Tablespace
HISTO_DOSSIER_P2_MD_TAB for HISTO_DOSSIER n ot contained in transportable set
Default Partition (Table) Tablespace
HISTO_DOSSIER_P3_MD_TAB for HISTO_DOSSIER n ot contained in transportable set
Default Partition (Table) Tablespace
HISTO_DOSSIER_P4_MD_TAB for HISTO_DOSSIER n ot contained in transportable set
Re: Oracle and Firewall
Seema, This is a typical misconception on the workings of Net8. Port 1521 is only used to contact the listener, after that the listener might: a) create a server process which listens on a port other than 1521 OR b) pass the connection to a prespawned server process, again on a different port. The new port could be 1034, for example. The client process is then notified that the server process is listening on port 1034 and the client process then starts communicating through the new port. Therefore what you see is normal. In fact it is the biggest proble in building a firewall around the database server; it just have to have too many ports (and mostly unpredictable) open. Here are a few options: (1) use firewall around the subnet where both app/web server and db server exist; not a firewall between them. (2) Use TCP Node checking to restrict Net8 traffic to the db server only from the app server. (3) Use Connection Manager. USing CM, known ports are used for communication, typically 1630 and 1631 (or is it 1634?) and only those can be opened up for connection. (4) Use Shared Servers. The connectiosn pass through the dispatchers. Since the ports used by them can be known, those ports can be opened up. (5) Use SSH redirection. (6) Use a commercial firewall product that can perform proxy-redirection, which preserves the port number in all established connections, even though actual ports used may be different. If anyone has any more options, I would love to know. HTH. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 21, 2003 10:59 AM Hi, We are using Oracle817 on Windows with netscreen firewall.I have been noticing after some times applications start connecting form 1521 to 1034 and so.IS this normal ?I want port 1521 Only in use. How to fix this problem? thx -Seema _ Gift-shop online from the comfort of home at MSN Shopping! No crowds, free parking. http://shopping.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh 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.net -- Author: Arup Nanda 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: html output
Have you tried using SET MARKUP HTML SPOOL ON from SQL*plus itself? It creates a neat HTML report and you canplace most of the formatting. HTH. Arup - Original Message - To: Multiple recipients of list ORACLE-L Sent: Wednesday, November 19, 2003 6:50 PM I am looking for a utility package for throwing output in html format from a query . This should use utl_file to write the file ( no sqlplus markup ). Is there any package /procedure oracle has to do this job ? This is just a html report and it will be sent to users by email. ( this is not a OAS/IAS report ). Thanks -ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda 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: Multimaster replication as alternative backup
VirVit, You haven't specified Oracle version and your tolerance for data loss. If you can afford to lose the data in the most current redo log file, then I would suggest 9i Data Guard in maximum performance mode (or Standby in 8i) solution over the MM replication. The DG solution does not affect the primary database performance. You can backup the standby database and use it for recovery of the primary, too. If you can't afford to lose any data, then you can still use the DG Maximum Protection (in 9i) mode, but it will affect the performance to some extent. However, in this case (no data loss) you have to enable MM Synchronous Replication as opposed to the default Asynchronous mode. In the Synch mode, the performance is worse compared to the Max Protection DG solution. Here is a summary of the options: Oracle 8i | +- Last redo data loss ok = Standby Database ~~ Performance=BEST +- LAst redo data los NOT ok = MM Synchronous Replication ~~ Performance=VERY BAD Oracle 9i | +- Last redo data loss ok = Data Guard Max Performance Mode ~~ Performance=BEST +- LAst redo data los NOT ok = | +- Option 1: MM Synchronous Replication ~~ Performance=VERY BAD +- Option 2: Data Guard Max Protection Mode ~~ Performance=BAD Therefore, you are better off using DG (or standby in 8i). Pros: (1) You can use standby datafiles to recover primary database (2) You can take RMAN backups from the standby, reducing the CPU cycle requirements in primary Cons: (1) In Max Protection Mode, the primary also halts if the standby has a problem; not truly a HA solution. Ideally you need three or more servers - one primary and two standbys to work perfectly = high cost Summary: If your management can live with the loss of the last redo, your best option is DG Max Performance (or Standby, in 8i), IMHO. HTH. Arup Nanda www.proligence.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 11:54 PM Hello! What do you think of additional backup method as multimaster replication? Isn't it a way to continue working normal, if main database crash and I move all connections to another master site? -- Oracle 9i DBA beginner -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VirVit 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.net -- Author: Arup Nanda 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: Re[2]: Deleting partitioned data
Jonathan,
You are welcome. Another time for the syntax table (partitoion) comes handy
is while dropping, merging or doing some other partition maintenance work.
This will quickly check is the partition is empty or not, othewise you have
to ge the hig values of the partition and one prior to it and then do a
query based on these two; possible but rather difficult.
HTH.
Arup
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, November 14, 2003 7:40 AM
Arup, thanks for taking the time to put that together,
that's a great explanation.
Best regards,
Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED]
Join the Oracle-article list and receive one
article on Oracle technologies per month by
email. To join, visit
http://four.pairlist.net/mailman/listinfo/oracle-article,
or send email to [EMAIL PROTECTED] and
include the word subscribe in either the subject or body.
Friday, November 14, 2003, 1:04:25 AM, Arup Nanda ([EMAIL PROTECTED])
wrote:
AN Jonathan,
AN This is explained by a quark in the way partitioning works, especially
in the case of range partitioned tables on date columns. If the partitioning
key is not given clearly in the dsame format as
AN it has been initially defined on, the partition elimination is done at
runtime, not at the parse phase. Consider the following example:
AN 1 create table sales
AN 2 (sales_dt date,
AN 3 product varchar2(20)
AN 4 )
AN 5 partition by range (sales_dt)
AN 6 (
AN 7 partition p1 values less than
(to_date('14-nov-2003','dd-mon-')),
AN 8 partition p2 values less than
(to_date('15-nov-2003','dd-mon-')),
AN 9 partition p3 values less than
(to_date('16-nov-2003','dd-mon-'))
AN 10* )
SQL /
AN Table created.
SQL insert into sales values (sysdate - 1, 'Yesterday');
AN 1 row created.
SQL insert into sales values (sysdate, 'Today');
AN 1 row created.
SQL insert into sales values (sysdate+1, 'Tomorrow');
AN 1 row created.
SQL commit;
AN Commit complete.
SQL analyze table sales compute statistics;
AN Table analyzed.
AN Now let's examine the various access methods.
SQL explain plan for select * from sales where sales_dt =
to_date('14-nov-2003'
AN ,'dd-mon-');
AN Explained.
SQL select * from table(dbms_xplan.display);
AN | Id | Operation| Name | Rows | Bytes | Cost |
Pstart| Pstop |
AN
AN | 0 | SELECT STATEMENT | | 1 |21 | 2 |
| |
AN |* 1 | TABLE ACCESS FULL | SALES | 1 |21 | 2 |
2 | 2 |
AN
AN Predicate Information (identified by operation id):
AN ---
AN 1 - filter(SALES.SALES_DT=TO_DATE('2003-11-14 00:00:00',
'-mm-dd hh24:mi:ss'))
AN Note: cpu costing is off
AN 15 rows selected.
AN As expected, the optimizer decided to look into partiotion p2 only
(partition start=2 and partition stop=2). The choice was made by the
optimizer at step 1, indicated by an asterix and the
AN predicate information is shown below in the filter section.
SQL explain plan for select * from sales partition (p2);
AN Explained.
SQL select * from table(dbms_xplan.display);
AN | Id | Operation | Name | Rows | Bytes | Cost |
Pstart| Pstop |
AN
--
AN | 0 | SELECT STATEMENT | |1 | 12 | 2 |
| |
AN | 1 | TABLE ACCESS FULL | SALES |1 | 12 | 2 |
2 | 2 |
AN
--
AN Note: cpu costing is off
AN 9 rows selected.
AN It still selected from partition p2 only, as expected. The only
difference is there is no predicate section, as none is required; we
selected from partition directly. So far, so good. Let's see
AN the third selection option.
SQL explain plan for select * from sales where sales_dt = '14-nov-03';
AN Explained.
SQL select * from table(dbms_xplan.display);
AN | Id | Operation | Name | Rows | Bytes | Cost |
Pstart| Pstop |
AN
--
AN | 0 | SELECT STATEMENT | | 1 |21 | 2 |
| |
AN | 1 | PARTITION RANGE SINGLE| | | | |
KEY | KEY |
AN |* 2 | TABLE ACCESS FULL| SALES | 1 |21 | 2 |
KEY | KEY |
AN
--
AN Predicate Information (identified by operation id
Re: RE: Re: Stop using SYS, SYSTEM?
Nuno Pinto do Souto [EMAIL PROTECTED] wrote: And that's why I feel disabling SYS or SYSTEM purely on security grounds makes no sense whatsoever I'm not sure that's what the OP wanted. He wanted to know if stopping use of SYS and SYSTEM on a regular basis will be acceptable, not disable them. It sure is. Besides, how does one disable the account? Lock it? SYSTEM can be locked but SYS can't be; hence the whole concept of disabling does not make sense. I feel the auditors merely wanted the OP to stop using SYS and SYSTEM on a regular basis in operations that require a DBA access - such as full exports and selecting from disctionary tables. IMHO this is a very valid advisory and not difficult to follow. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 13, 2003 12:49 AM Jacques Kilchoer [EMAIL PROTECTED] wrote: In my case I also enforce the don't sign on as SYS/SYSTEM rule. The reasons I do that: - The default tablespace for SYS is SYSTEM, and I don't like to change that. There are probably reasons why you wouldn't want to change that. But when I sign on to do my DBA work to try something I don't want to have to specify a tablespace name every time I create a test object like CREATE TABLE TEST (X NUMBER) STORAGE (INITIAL 1000M) It has nothing to do with the dba role itself and its security. Oracle just happens to associate user SYS with the SYSTEM tablespace. Fair enough that you may not want that association by default. - If each DBA has a named account, it's easy to tell who's logged in to the database by saying SELECT USERNAME FROM V$SESSION ; otherwise I would have to figure out who could be logged on as SYSTEM to call them and ask them if it's OK to shutdown the database. That is a pure audit requirement: you want to know who is using DBA access. Nothing to do with SYSTEM. If you remove SYS and SYSTEM, there is nothing in USERNAME in V$SESSION that will tell you username BLOGGSJ is using DBA rights. Other than your own prior knowledge that is the case. In a way, you're worse off. Telling all the DBAs sign on as SYSTEM would be (IMHO) like telling all the programmers You can all sign on as user 'coder' and all users you can all sign on in the database as user 'data_entry_person'. Don't they always? G Quite frankly, the problem as I see it is that I want to know WHO dropped the tablespace and WHEN and from WHERE. That whoever did it had DBA access rights is a given, I don't need it clarified! It's the who, when and where that is the province of auditing. And have nothing to do with SYS, SYSTEM or whatever, other than as information. Using or not using SYS or SYSTEM adds nothing to this knowledge or its implicit security. And that's why I feel disabling SYS or SYSTEM purely on security grounds makes no sense whatsoever. Of course, one may want to reduce the risk of accidents and therefore lock those out. Even then, debatable if that is the best way of doing it: accidentaly dropping the tablespace produces the same chaotic results regardless of what account one does it from. Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Pinto do Souto 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.net -- Author: Arup Nanda 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: wakeup time manager
Have you enabled Advanced Queue Manager process in the init file? aq_tm_processes? If so, this wait event occurs when the process just waits to see some messages in the queue. Yes, it is an idle event and can be ignored. Fortunately this is listed as idle event in STATSPACK, which does not report these waits. HTH. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 13, 2003 10:24 AM Hi, I cannot find reference to the wakeup time manager wait event in the FM. Can anyone explain it to me? Getting lots of waits for this in a 9.2.0.4 database - but given that both processors and disks are pretty idle at the moment I guess it falls under the category of idle events. Therefore I'm not going to worry about it too much - but would be nice to know what it is. Thanks for any info - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bill Buchan 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.net -- Author: Arup Nanda 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: HTML DB
We use it. I have been using it ever since it was Project Marvel under Oracle 9i. No, it's not part of 10g per se; you could use HTMLDB along with Oracle 9i database. The coolest thing about it is you don't need expensive software such as Oracle App Server; an Apache webserver is all that is needed. My biggest complaint is installation - the manuals available now are not upto mark. However, the product management team of HTMLDB has promised that the final manual will be a lot more user-friendly. Why don't you just use Oracle's own HTMLDB installation and test your sample apps there to get a feel for it? If you like it then you can think about installing it in house. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 13, 2003 10:44 AM List: I have been asked by management to investigate HTML DB. Although this is an Oracle 10g development tool, it has been pre-released for 9i. Some of my questions are: is anyone currently using this? Are there any issues with installation? Any advice or information concerning this subject would be appreciated! ~~~ Denise Rossette Southwest Florida Water Management District ~~~ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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.net -- Author: Arup Nanda 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: HTML DB
htmldb.oracle.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 13, 2003 11:34 AM Ask tom has a link. I think it's marvel.orcle.com. ...installation is easy. ...it looks to be a pretty cool tool. -Original Message- Sent: Thursday, November 13, 2003 10:15 AM To: Multiple recipients of list ORACLE-L On 11/13/2003 10:54:25 AM, Jamadagni, Rajendra wrote: Although we are getting our feet wet ... the installation on a 9202/4 is a breeze. Raj I don't see it on OTN. Where can I get it? Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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.net -- Author: Chris Stephens 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.net -- Author: Arup Nanda 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: Deleting partitioned data
Jonathan,
This is explained by a quark in the way
partitioning works, especially in the case of range partitioned tables on date
columns. If the partitioning key is not given clearly in the dsame format as it
has been initially defined on, the partition elimination is done at runtime, not
at the parse phase. Consider the following example:
1 create table sales
2 (sales_dt date, 3 product varchar2(20) 4
) 5 partition by range (sales_dt) 6 (
7 partition p1 values less than
(to_date('14-nov-2003','dd-mon-')), 8 partition p2 values
less than (to_date('15-nov-2003','dd-mon-')), 9 partition p3
values less than (to_date('16-nov-2003','dd-mon-'))10*
)SQL /
Table created.
SQL insert into sales values (sysdate -
1, 'Yesterday');
1 row created.
SQL insert into sales values (sysdate,
'Today');
1 row created.
SQL insert into sales values
(sysdate+1, 'Tomorrow');
1 row created.
SQL commit;
Commit complete.
SQL analyze table sales compute
statistics;
Table analyzed.
Now let's examine the various access
methods.
SQL explain plan for select * from
sales where sales_dt = to_date('14-nov-2003','dd-mon-');
Explained.
SQL select * from
table(dbms_xplan.display);
| Id |
Operation
| Name | Rows | Bytes |
Cost | Pstart| Pstop |
| 0 | SELECT
STATEMENT
|
| 1 | 21 | 2
| | |
|* 1 | TABLE ACCESS
FULL | SALES
| 1 | 21 | 2
| 2 | 2 |
Predicate Information (identified by
operation
id):---
1 -
filter("SALES"."SALES_DT"=TO_DATE('2003-11-14 00:00:00', '-mm-dd
hh24:mi:ss'))
Note: cpu costing is off
15 rows selected.
As expected, the optimizer decided to look
into partiotion p2 only (partition start=2 and partition stop=2). The choice was
made by the optimizer at step 1, indicated by an asterix and the predicate
information is shown below in the filter section.
SQL explain plan for select * from
sales partition (p2);
Explained.
SQL select * from
table(dbms_xplan.display);
| Id |
Operation
| Name | Rows | Bytes |
Cost | Pstart| Pstop |
--
| 0 | SELECT
STATEMENT
|
|1 | 12
| 2 |
|
|
| 1 | TABLE ACCESS
FULL | SALES
|1 | 12
| 2 | 2 |
2 |
--
Note: cpu costing
is off
9 rows selected.
It still selected from partition p2 only,
as expected. The only difference is there is no predicate section, as none is
required; we selected from partitiondirectly. So far, so good. Let's see
the third selection option.
SQL explain plan for select * from
sales where sales_dt = '14-nov-03';
Explained.
SQL select * from
table(dbms_xplan.display);
| Id |
Operation
| Name | Rows | Bytes |
Cost | Pstart| Pstop |
--
| 0 | SELECT
STATEMENT
|
| 1 | 21 | 2
| | |
| 1 | PARTITION RANGE
SINGLE|
| |
| | KEY | KEY
|
|* 2 | TABLE ACCESS
FULL | SALES
| 1 | 21 | 2
| KEY | KEY |
--
Predicate Information (identified by
operation
id):---
2 -
filter("SALES"."SALES_DT"='14-nov-03')
Note: cpu costing is off
15 rows selected.
Well, what happened here? The optimizer
couldn't decide the partition at the parse time, hence it shows KEY as the
values of partition start and stop keys. This occurred since we specified "where
sales_date = '14-nov-03'" as opposed to "where sales_dt =
to_date('14-nov-2003','dd-mon-')". The former is not in the same format as
the partition definition, i.e. "(to_date('14-nov-2003','dd-mon-'))", the
latter is; hence the optimizer made a smart choice. When the patterns mentioned
in the query and the partition definition don't match, the optimizer can't
decide at parse time which partition to use; it uses a KEY
iterator.
I am not sure if the facility provided by
Oracle to query a partition directly is due to the above situation, but it helps
there, nevertheless.
HTH.
Arup Nanda
- Original Message -
From: "Jonathan Gennick" [EMAIL PROTECTED]
To: "Multiple recipients of list ORACLE-L"
[EMAIL PROTECTED]
Sent: Thursday, November 13, 2003 7:34
PM
Subject: Deleting partitioned
data
I'd like to ask a question. Consider the two statements
below: DELETE FROM county PARTITION (michigan)
WHERE county_name = 'Alger'; DELETE FROM county
WHERE county_name = 'Alger' AND state = 'MI'; Is
there ever a case where the first option is preferable? Is there ever a
case where Oracle wouldn't be able to isolate the partition of interes
Re: Stop using SYS, SYSTEM?
Ron, It is a good practice, in general, to stop using SYS and SYSTEM accounts for everyday use. The simplest rule of thumb is accountability somehow increases many times over when you link a database named user to a physical person, not a ethereal entity like SYS. This is especially true if you use auditing and turn on SYSDBA auditing; but even if you don't sometimes the use of specific named users put people on the alert when they do something potentially dangerous and can avoid accidents. The other reason of not using SYS is to avoid accidental creation of objects in SYS and SYSTEM schema. The best option is to lock SYSTEM user and never let SYS user. Unfortunately you can't lock the SYS user. Third, you can create default tablespaces for all these DBA users to hold their objects, specifically temporary/occasional tables (not the global temporary tables), test tables, etc. and all those will not get into SYSTEM tablespace. Perhaps I should mention here is that I also conduct database security audits for corporations. But unlike your auditors, I tend to follow the advice up with more detailed information :) Arup Nanda www.proligence.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 3:04 PM We are being asked by Auditing to stop using the SYS, and SYSTEM accounts. They would like for us to create an Oracle Role with the same permissions a SYS and SYSTEM, then grant the role to each of the DBA's. Don't ask me why. Nothing is being audited in 99% of the databases. They just say it in a paper some where so they said we shouldn't use it. This seems like it would cause lots of problems with exports, imports, installs, etc... Has anyone had to deal with this type of request? Any potential problems with making the change? Thanks! Ron Smith -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. 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.net -- Author: Arup Nanda 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).
Fw: Stop using SYS, SYSTEM?
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.net -- Author: Arup Nanda 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: Stop using SYS, SYSTEM?
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.net -- Author: Arup Nanda 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).
Getting Number of Rows in CTAS across DBLink
List, When I create a table as select * from another table across a dblink, how do I find out how many rows were created in the table? Is there a statistic somewhere, documented or otherwise, that tells me how many rows were fetched? Currently I am using a rather convoluted approach - using the statistic, bytes received via SQL*Net to dblink, and dividing that by the average row size to get an approximate idea of the number of rows. However, this approximation is far from even reasonably accurate; and since the rowsize can change radically, it can be way off the mark. Any help or pointers will be highly appreciated. Thanks. Arup Nanda
Re: Getting Number of Rows in CTAS across DBLink
Waleed, Thanks a bunch; it worked like a charm. The list rules! Arup - Original Message - From: Khedr, Waleed To: Multiple recipients of list ORACLE-L Sent: Friday, November 07, 2003 3:49 PM Subject: RE: Getting Number of Rows in CTAS across DBLink Use pl/sql block with execute immediate 'create table as ...' Number of rows should be in sql%rowcount (immediately after execute immediate). Waleed -Original Message-From: Arup Nanda [mailto:[EMAIL PROTECTED]Sent: Friday, November 07, 2003 3:34 PMTo: Multiple recipients of list ORACLE-LSubject: Getting Number of Rows in CTAS across DBLink List, When I create a table as select * from another table across a dblink, how do I find out how many rows were created in the table? Is there a statistic somewhere, documented or otherwise, that tells me how many rows were fetched? Currently I am using a rather convoluted approach - using the statistic, bytes received via SQL*Net to dblink, and dividing that by the average row size to get an approximate idea of the number of rows. However, this approximation is far from even reasonably accurate; and since the rowsize can change radically, it can be way off the mark. Any help or pointers will be highly appreciated. Thanks. Arup Nanda
Re: Getting Number of Rows in CTAS across DBLink
Dennis, Thanks. Sorry for not being explicit about it. Since the table created is huge, I want to avoid the count(*) if I can get the number in some other way. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 07, 2003 3:44 PM Arup select count(*) from table? What is your goal? Corruption detection? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, November 07, 2003 2:34 PM To: Multiple recipients of list ORACLE-L List, When I create a table as select * from another table across a dblink, how do I find out how many rows were created in the table? Is there a statistic somewhere, documented or otherwise, that tells me how many rows were fetched? Currently I am using a rather convoluted approach - using the statistic, bytes received via SQL*Net to dblink, and dividing that by the average row size to get an approximate idea of the number of rows. However, this approximation is far from even reasonably accurate; and since the rowsize can change radically, it can be way off the mark. Any help or pointers will be highly appreciated. Thanks. Arup Nanda -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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.net -- Author: Arup Nanda 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: Getting Number of Rows in CTAS across DBLink
Sami, This is inside a PL/SQL procedure; hence SQL*Plus commands like COPY are not available. The trick is to use SQL%ROWCOUNT as mentioned byWaleed and Mladen.Thanks for the help though. Regards, Arup - Original Message - From: Sami To: Multiple recipients of list ORACLE-L Sent: Friday, November 07, 2003 10:24 PM Subject: RE: Getting Number of Rows in CTAS across DBLink Arup, connot you use COPY command? -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Daniel FinkSent: Friday, November 07, 2003 4:20 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Getting Number of Rows in CTAS across DBLinkArup, Any chance there will be an index on the table? Daniel "Arup Nanda" [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/07/2003 12:34 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject: Getting Number of Rows in CTAS across DBLink List, When I create a table as select * from another table across a dblink, how do I find out how many rows were created in the table? Is there a statistic somewhere, documented or otherwise, that tells me how many rows were fetched? Currently I am using a rather convoluted approach - using the statistic, bytes received via SQL*Net to dblink, and dividing that by the average row size to get an approximate idea of the number of rows. However, this approximation is far from even reasonably accurate; and since the rowsize can change radically, it can be way off the mark. Any help or pointers will be highly appreciated. Thanks. Arup Nanda
Re: Getting Number of Rows in CTAS across DBLink
Dan, Are you referring to an index on the source table (which is remote)? Since I'm creating the table on the destination side, there is no index. However, I am creating several indexes after the table created. The answer is to use SQL%ROWCOUNT. I'm curious - how will an index help? Thanks for the help. Arup - Original Message - From: Daniel Fink To: Multiple recipients of list ORACLE-L Sent: Friday, November 07, 2003 4:19 PM Subject: Re: Getting Number of Rows in CTAS across DBLink Arup, Any chance there will be an index on the table? Daniel "Arup Nanda" [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/07/2003 12:34 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject: Getting Number of Rows in CTAS across DBLink List, When I create a table as select * from another table across a dblink, how do I find out how many rows were created in the table? Is there a statistic somewhere, documented or otherwise, that tells me how many rows were fetched? Currently I am using a rather convoluted approach - using the statistic, bytes received via SQL*Net to dblink, and dividing that by the average row size to get an approximate idea of the number of rows. However, this approximation is far from even reasonably accurate; and since the rowsize can change radically, it can be way off the mark. Any help or pointers will be highly appreciated. Thanks. Arup Nanda
Re: Refresh option for Materialized view , want to use it during refresh - for
David, Glad to be of help. Looking at your list of steps, I see that your questions is whether building an MV on the table T is beneficial. Tables and MVs, on prebuilt table or not, are stored as segments in the database; so space-sise there is no difference, nor there is any change in the way the MV/Table is accessed. There are a few situations where you may want to convert a table to MV. They are: * Building an MV enables Query Rewrite, where Oracle smartly decides to rewrite a user query to select from the MV instead of the main tables. This is not possible on a regular table. The user must explicitly select from it. * If you want to refresh FAST, then MVs are required. You can do a fast refresh on a table, but you have to write your own procedures for that. DBMS_MVIEW package does it for you on MVs. * Your designer software will recognize MV as one and will report it to all users, who are aware of the fact that it's an MV, useful for queries. A mere table will not be clear on that regard. Converting a table to MV does not cost any resource, as the change is done inside the data dictionary only. So, if you are in doubt, you may just convert the table to MV anyway. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 12:04 PM refresh - for Arup, I really appreciate your answer in great details. I got on prebuilt table work. Thanks a lot for your help. Here is another question: Do you see any advantage to use materialized view on prebuilt table for my data loading over just simple renaming tables as steps below: 1. create table t that is always accessed by applications 2. create table t1 that is a temp table for loading 3. load data into table t1 4. rename table t to table t2 5. rename table t1 to t 6. rename table t2 to t1 7. truncate table t1 for next day loading David From: Arup Nanda [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Refresh option for Materialized view , want to use it during refresh - for Date: Fri, 24 Oct 2003 18:04:33 -0800 David, Answers to your questions: (1) Without knowing your exact needs, I wil offer a few different scenarios. I am assuming that you are doing a complete refresh every time. The following pertain to that. Say, your name of the MV is MV1. Here are the steps the first time. 1. Create table MV1 2. Create MV MV1 on that table. When you want to refresh complete: 1. Create table MV1_TEMP. Choose a suitable method: CTAS across dblink (with NOLOGGING), SQL*Loader, Direct Load Insert, or simpley exp/imp. 2. Drop MV MV1. This drops the MV but doesn't drop the table. 3. Drop table MV1. 4. Rename table MV1_TEMP to MV1. 5. Recreate MV MV1. 6. Allow users to proceed as usual. Note the time consumed between Steps 2 and 6 are in the order of a few seconds. And it's the only time the users will not have access to the MV, as opposed to a full refresh using dbms_mview.refresh approach., which will lock the MV for the entire duration and generate tons of redo and rollback. Even if you do a incremental refresh, this is still a better approach. In that case, you don't drop the table during the refresh. (2) Yes, the option is available in 8i, at least in 8.1.7. Make sure the syntax is correct. create materialized view MV1 on prebuilt table refresh fast as select ... from In the article I mentioned, you can find the complete syntax. www.proligence.com/downloads.html is the site. It also dscribes a step by step solution to the issue and compares the common solution with this new one. Hope this helps. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 12:34 PM refresh - for Hi Arup, This is a very good method. I would like to use it to modify some of my data loading procedures. Here are my questions: 1. Do I need to create the table on the step 1 every time when I refresh the data If I refresh data once per day? 2. Is ON PREBUILT TABLE available on Oracle 8i? When I was trying the method on Oracle 8i, I got missing keyword error on PREBUILT. Dave Siddharth, I will offer a slightly out-of-the-box solution. Please read it through till the end to determine its applicability in your case. It seems yours refresh interval is once a day and you don't mind stale data for a max of 24 hours. You also refresh is complete, not incremental. So, I would suggest the follwoing approach. (1) Create a table first CREATE TABLE CT_PRODUCTID_VW TABLESPACE NOLOGGING AS SELECT . (2) When you are ready to refresh, drop the MV DROP MATERIALIZED VIEW CT_PRODUCTID_VW; (3) Create the MV with the PREBUILT TABLE option. CREATE MATERIALIZED VIEW
Re: Solved - RE: UTL_RAW and slowness
Awesome, Raj! And thank you for sharing this with us. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 27, 2003 4:29 PM Read if you are interested ... Finally I got some time and luckily the largest message to use with dbms_profiler. And the results shocked me dbms_profiler showed me that instead of utl_raw, substr() was the culprit. Remember my operation is character by character. After some long thinking and evaluating different techniques, I finally decided that the delay was due to the fact that substr() operation was way too slow to be included in my processing. As for alternatives, only thing I could think was parallel processing and pipelined came to my mind ... So, I implemented a pipelined function that does nothing, but takes a CLOB and returns me 1K chunks (using dbms_lob.read()) of it. I do this operation in a cursor loop, so by the time I process 1k characters, next 1k is waiting at the doorstep. Thus by parallelising the delay, I finally resolved the problem. Thanks you all for your help, suggestion and hints which really got me thinking. Plus having 9202 helped too otherwise it would have been difficult (without pipelined function). Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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.net -- Author: Arup Nanda 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: Materialized view and index
Chuan, You can crteate indexes on MVs if you want to - there is nothing against it; in fact it may be specifically desirable to do so. MVs are designed to help in query optimization by selecting against a materialized collection f data as opposed to selecting from a set of tables as in case of normal views and hence you can create as many indexes as you like to improve access plan. The only downside I can think of, and it is pretty minimal, is increased time spent on index maintenance - be it in a fast refresh or complete one. But the cost is marginal compared to the benefit. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 27, 2003 10:29 PM Hi, All Wondering whether anyone created indexes on materialized view to further improve the performance? What's the pros and cons of this method? Thanks in advance. Chuan Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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.net -- Author: Arup Nanda 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: UTL_RAW and slowness
Another thing you can try is using PL/SQL native compilation. It's certainly faster in CPU intensive operations and encryption is a great candidate for it. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, October 26, 2003 12:29 PM Profiling is on the cards for Monday if I don't flush, sometimes the Cisco router doesn't send the information quickly enough. For a ticker tape that is running at 1220 bauds, if characters do not come at set intervals, it appears as if there has been a network hiccup ... and we like to avoid that. BTW do you think Java would be a good idea? I am no good at java stored procs, but will try to hack it next week. This seems to be an better alternative than going for external procs (and then security gets involved due to concerns). BTW in line with US Congress resolution I had initially set the connection be flushed every 1.6K characters G, but soon realized that it was ummm ... inefficient for our needs. Oh Well ... But thanks for pointing out the Java stored proc idea, I'll definitely try it out. Raj -Original Message- Sent: Saturday, October 25, 2003 10:39 PM To: Multiple recipients of list ORACLE-L Raj, When I was writing the PL/SQL implementation of Blowfish, I also wrote a version as a Java Stored Procedure so I could compare the performance of the two implementations. For CPU intensive work (like encryption), the Java Stored Procedure performed orders of magnitude better than the PL/SQL version. I was using 8.1.7 at the time. I am wondering why you need to flush the TCP connection after 128 bytes? Have you profiled your code using DBMS_PROFILER to see where the time it being spent? Cheers, Craig. ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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.net -- Author: Arup Nanda 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: Refresh option for Materialized view , want to use it during refresh - for
David, Answers to your questions: (1) Without knowing your exact needs, I wil offer a few different scenarios. I am assuming that you are doing a complete refresh every time. The following pertain to that. Say, your name of the MV is MV1. Here are the steps the first time. 1. Create table MV1 2. Create MV MV1 on that table. When you want to refresh complete: 1. Create table MV1_TEMP. Choose a suitable method: CTAS across dblink (with NOLOGGING), SQL*Loader, Direct Load Insert, or simpley exp/imp. 2. Drop MV MV1. This drops the MV but doesn't drop the table. 3. Drop table MV1. 4. Rename table MV1_TEMP to MV1. 5. Recreate MV MV1. 6. Allow users to proceed as usual. Note the time consumed between Steps 2 and 6 are in the order of a few seconds. And it's the only time the users will not have access to the MV, as opposed to a full refresh using dbms_mview.refresh approach., which will lock the MV for the entire duration and generate tons of redo and rollback. Even if you do a incremental refresh, this is still a better approach. In that case, you don't drop the table during the refresh. (2) Yes, the option is available in 8i, at least in 8.1.7. Make sure the syntax is correct. create materialized view MV1 on prebuilt table refresh fast as select ... from In the article I mentioned, you can find the complete syntax. www.proligence.com/downloads.html is the site. It also dscribes a step by step solution to the issue and compares the common solution with this new one. Hope this helps. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 12:34 PM refresh - for Hi Arup, This is a very good method. I would like to use it to modify some of my data loading procedures. Here are my questions: 1. Do I need to create the table on the step 1 every time when I refresh the data If I refresh data once per day? 2. Is ON PREBUILT TABLE available on Oracle 8i? When I was trying the method on Oracle 8i, I got missing keyword error on PREBUILT. Dave Siddharth, I will offer a slightly out-of-the-box solution. Please read it through till the end to determine its applicability in your case. It seems yours refresh interval is once a day and you don't mind stale data for a max of 24 hours. You also refresh is complete, not incremental. So, I would suggest the follwoing approach. (1) Create a table first CREATE TABLE CT_PRODUCTID_VW TABLESPACE NOLOGGING AS SELECT . (2) When you are ready to refresh, drop the MV DROP MATERIALIZED VIEW CT_PRODUCTID_VW; (3) Create the MV with the PREBUILT TABLE option. CREATE MATERIALIZED VIEW CT_PRODUCTID_VW BUILD IMMEDIATE REFRESH START WITH SYSDATE NEXT (SYSDATE + 1) ON PREBUILT TABLE AS SELECT msi.segment1productid, ... Your MV is not accessible between STEP 2 and STEP3, which is really a dictionary update and takes about a second or so. So the outage is really 1 second, not 1/2 hr. A few explanations are in order here. (1) Creating an MV on a Prebuilt Table does not consume more space. The segment that used to be a table simply becomes an MV. (2) When you drop the MV, the MV is gone, but the table remains instact. (3) The table can be create by any means - export/import, SQL*Loader, INSERT APPEND, etc. (4) IT places less strain on the system comapred to the MV refresh option, simply because the MV refresh truncates the segment and then builds it. I presented a paper to the same effect at IOUG Live 2003. You can download a modified version of the same from my website www.proligence.com/downlaods.html, titled Painless Master Table Alter from the Presentations Section. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L Sent: Tuesday, October 21, 2003 3:59 AM refresh Hi Gurus, I have a materialized view, which is based on Oracle Apps tables and on remote database. The view refresh takes around ½ hour, during this time period I cannot see any records in the materialized view and therefore my application faces errors. The following is the view definition CREATE MATERIALIZED VIEW CT_PRODUCTID_VW BUILD IMMEDIATE REFRESH START WITH SYSDATE NEXT (SYSDATE + 1) AS SELECT msi.segment1productid, msi.description description, msi.inventory_item_id inventory_item_id, mc.segment1 product_family, mc.segment2 product_type FROM [EMAIL PROTECTED] mcs, [EMAIL PROTECTED] mc, [EMAIL PROTECTED]mic, [EMAIL PROTECTED] msi where 1=1 and mc.structure_id = 50112 and mc.segment3 != 'SPARE' and mc.global_name= 'US' and mc.enabled_flag = 'Y' and mcs.global_name = mc.global_name and mcs.category_set_name = 'PROD GROUP' and mic.category_set_id = mcs.category_set_id
Re: RMAN Incremental
Michael, I hope you have the tables partitioned on some date column. You can make some of the older partitions read only and back them up only once. Another solution is to exchange the partitions with a table to convert your old partitions to tables, transporting them to a tape and keep it on the tape. If the database crashed, you will plug these TSs back to the DB and exchange the partitions with the tables created earlier. I presented a session at Oracle World this year describing a case study of a datawarehouse where I have described the backup and recovery approach. You can download it from OTN or from my website (www.proligence.com/downloads.html). Be sure to download both the paper and the presentation. HTH. Arup Nanda - Original Message - From: Michael Kline To: Multiple recipients of list ORACLE-L Sent: Friday, October 24, 2003 7:34 PM Subject: RMAN Incremental I'm working at a Data Warehouse and they are looking for backup possibilities... This is almost a TB, a baby,and it may be that RMAN with incremental could be a good solution. If say for instance there is a single tablespace of some 100 gig and they add 200,000,000 records to one of the tables and we do an incremental backup. Is the whole tablespace slated for backup? Also if this tablespace was lost, and we recover.. What happens during that process? Does RMAN basically have to filter through two completecopies of that tablespace or just once and then get changed blocks? What solutions have some doing this found to be "best practice"? Thanks. Michael Kline, Principal ConsultantBusiness To Business Solutions, LLCRichmond, VA804-744-1545
Re: Refresh option for Materialized view , want to use it during refresh - for
No, you didn't read it completely. Create table and create MV do the same thing - produce a copy of the data on a different location (or a different segment) that can be queried independently. However, I proposed a different way of doing the MV creating and refreshing, not using the dbms_mview.refresh procedure as documented, but by using prebuilt table and using other faster methods such as CTAS and Direct Path load to do a complete refresh. It offers severa advantages such as faster execution, much less outage window and low resource utilization. As an added bonus, you don't have to drop and recreate the read only MV when you add/alter a column to the master table. In your case, you might want to consider converting the tables to MV if MVs are used in such a way. One example is if you see some benefit from Query Rewrite, you may want to create the MVs on the tables using the ON PREBUILT TABLE clause for Oracle to use QR. HTH. Arup Nanda www.proligence.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 9:24 PM refresh - for did i read that correctly that create table as is superior to a materialized view for nightly loads? We drop all the tables in some of our schemas and rebuild them with create table as statements. I was going to try out materialized views to see if they were faster. guess they are not? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 12:34 PM refresh - for Hi Arup, This is a very good method. I would like to use it to modify some of my data loading procedures. Here are my questions: 1. Do I need to create the table on the step 1 every time when I refresh the data If I refresh data once per day? 2. Is ON PREBUILT TABLE available on Oracle 8i? When I was trying the method on Oracle 8i, I got missing keyword error on PREBUILT. Dave Siddharth, I will offer a slightly out-of-the-box solution. Please read it through till the end to determine its applicability in your case. It seems yours refresh interval is once a day and you don't mind stale data for a max of 24 hours. You also refresh is complete, not incremental. So, I would suggest the follwoing approach. (1) Create a table first CREATE TABLE CT_PRODUCTID_VW TABLESPACE NOLOGGING AS SELECT . (2) When you are ready to refresh, drop the MV DROP MATERIALIZED VIEW CT_PRODUCTID_VW; (3) Create the MV with the PREBUILT TABLE option. CREATE MATERIALIZED VIEW CT_PRODUCTID_VW BUILD IMMEDIATE REFRESH START WITH SYSDATE NEXT (SYSDATE + 1) ON PREBUILT TABLE AS SELECT msi.segment1productid, ... Your MV is not accessible between STEP 2 and STEP3, which is really a dictionary update and takes about a second or so. So the outage is really 1 second, not 1/2 hr. A few explanations are in order here. (1) Creating an MV on a Prebuilt Table does not consume more space. The segment that used to be a table simply becomes an MV. (2) When you drop the MV, the MV is gone, but the table remains instact. (3) The table can be create by any means - export/import, SQL*Loader, INSERT APPEND, etc. (4) IT places less strain on the system comapred to the MV refresh option, simply because the MV refresh truncates the segment and then builds it. I presented a paper to the same effect at IOUG Live 2003. You can download a modified version of the same from my website www.proligence.com/downlaods.html, titled Painless Master Table Alter from the Presentations Section. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L Sent: Tuesday, October 21, 2003 3:59 AM refresh Hi Gurus, I have a materialized view, which is based on Oracle Apps tables and on remote database. The view refresh takes around ½ hour, during this time period I cannot see any records in the materialized view and therefore my application faces errors. The following is the view definition CREATE MATERIALIZED VIEW CT_PRODUCTID_VW BUILD IMMEDIATE REFRESH START WITH SYSDATE NEXT (SYSDATE + 1) AS SELECT msi.segment1productid, msi.description description, msi.inventory_item_id inventory_item_id, mc.segment1 product_family, mc.segment2 product_type FROM [EMAIL PROTECTED] mcs, [EMAIL PROTECTED] mc, [EMAIL PROTECTED]mic, [EMAIL PROTECTED] msi where 1=1 and mc.structure_id = 50112 and mc.segment3 != 'SPARE' and mc.global_name= 'US' and mc.enabled_flag = 'Y' and mcs.global_name = mc.global_name and mcs.category_set_name = 'PROD GROUP' and mic.category_set_id = mcs.category_set_id
Re: VPN to database?
Paul, We use Advanced Security. the product is pricey and difficult to setup; but once in place it's in solid footing. Advanced security does not replace VPN per se; it's purpose is slightly different and broader in scope. If you take VPN away, how do you suppose you will connect to the DB server, directly? Hardly. So, VPN _may_ be required regardless. Some of the uses of AS are (not exhaustive) 1. Encryption and Checksumming of Net8 connection between the db server and the app servers (and any other users connected to the db server directly). This is the bare minimum security manadated by HIPAA and unfortunately Oracle does not provide a solution as a part of the base product. You may not need it, though; since using intelligent subnets and using firewalls around the db servers can limit threats to an acceptable degree. 2. Single signon. We use it in our app servers (running IIS) where the authentication is done using certificates. Again, this is necessary due to the refusal of the Development group to introduce database userids and eliminate the application authentication. The second part can be addressed in a different way. Using an application user security model where the users supply their userid and password to the database for authentication will eliminate the need to have a Windows user to be authenticated. A simple mechanism will be to authenticate the user agaist the database as the very first step. If authentication fails, the app will not proceed further. This will eliminate the authentication of the user by Windows. This model has been in use on a different app here and works great; but on the other app, the manager insists on one authentication on Windows and then another on the database, hence single signon. HTH. Arup Nanda www.proligence.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 10:09 PM Thanks, everyone, for your helpful responses. A talk with our Oracle sales droid has pointed me in the direction of Oracle Advanced Security for authentication, encryption, and integrity. Anyone have experience using this? We are considering using Entrust SSL authentication as we already use Entrust to authenticate users of our app. Would Advanced Security replace a VPN, or coexist with it? = Paul Baumgartel Transcentive, Inc. www.transcentive.com __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel 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.net -- Author: Arup Nanda 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: Refresh option for Materialized view , want to use it during refresh - for
i thought they might load faster because of fast refresh. If you can do fast refresh, then MVs may be better. However, here are a few considerations before you do so: 1. Fast refresh requires creation of mv logs on the master tables, which are populated by triggers. This will affect performance on your source db, which might not be acceptable. 2. Fast refresh is transactional, and it may be slower. If the number of changes are large between two refreshes, it might be actually faster to do a full refresh. not sure if we can do that across a db link. Of course you can across db links. Fast refreshable MVs are also created in replication environments; how do you suppose they work between two different databases? HTH. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 11:49 PM refresh - for no we dont need query rewrite. we load data every night across a database link. we drop and recreate all the tables from scratch. I thought about using materialized views. i thought they might load faster because of fast refresh. not sure if we can do that across a db link. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 10:14 PM refresh - for No, you didn't read it completely. Create table and create MV do the same thing - produce a copy of the data on a different location (or a different segment) that can be queried independently. However, I proposed a different way of doing the MV creating and refreshing, not using the dbms_mview.refresh procedure as documented, but by using prebuilt table and using other faster methods such as CTAS and Direct Path load to do a complete refresh. It offers severa advantages such as faster execution, much less outage window and low resource utilization. As an added bonus, you don't have to drop and recreate the read only MV when you add/alter a column to the master table. In your case, you might want to consider converting the tables to MV if MVs are used in such a way. One example is if you see some benefit from Query Rewrite, you may want to create the MVs on the tables using the ON PREBUILT TABLE clause for Oracle to use QR. HTH. Arup Nanda www.proligence.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 9:24 PM refresh - for did i read that correctly that create table as is superior to a materialized view for nightly loads? We drop all the tables in some of our schemas and rebuild them with create table as statements. I was going to try out materialized views to see if they were faster. guess they are not? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 12:34 PM refresh - for Hi Arup, This is a very good method. I would like to use it to modify some of my data loading procedures. Here are my questions: 1. Do I need to create the table on the step 1 every time when I refresh the data If I refresh data once per day? 2. Is ON PREBUILT TABLE available on Oracle 8i? When I was trying the method on Oracle 8i, I got missing keyword error on PREBUILT. Dave Siddharth, I will offer a slightly out-of-the-box solution. Please read it through till the end to determine its applicability in your case. It seems yours refresh interval is once a day and you don't mind stale data for a max of 24 hours. You also refresh is complete, not incremental. So, I would suggest the follwoing approach. (1) Create a table first CREATE TABLE CT_PRODUCTID_VW TABLESPACE NOLOGGING AS SELECT . (2) When you are ready to refresh, drop the MV DROP MATERIALIZED VIEW CT_PRODUCTID_VW; (3) Create the MV with the PREBUILT TABLE option. CREATE MATERIALIZED VIEW CT_PRODUCTID_VW BUILD IMMEDIATE REFRESH START WITH SYSDATE NEXT (SYSDATE + 1) ON PREBUILT TABLE AS SELECT msi.segment1productid, ... Your MV is not accessible between STEP 2 and STEP3, which is really a dictionary update and takes about a second or so. So the outage is really 1 second, not 1/2 hr. A few explanations are in order here. (1) Creating an MV on a Prebuilt Table does not consume more space. The segment that used to be a table simply becomes an MV. (2) When you drop the MV, the MV is gone, but the table remains instact. (3) The table can be create by any means - export/import, SQL*Loader, INSERT APPEND, etc. (4) IT places less strain on the system comapred to the MV refresh option, simply because the MV refresh truncates the segment and then builds it. I presented a paper
Re: how is it possible
Could it have some special characters in the name? Try "cat *" instead. - Original Message - From: AK To: Multiple recipients of list ORACLE-L Sent: Thursday, October 23, 2003 6:49 PM Subject: how is it possible This happening with me 3 rd time on this hp box . When I do ls -alt I can see a file in current directory but when I try to open it (vi/cat ) it says no such file or directory . I am same user who created the file . ( no permission problem ) Any idea ?? -ak /home/ak/myscripts/shell_scr $ ls -alt total 4drwxrwxrwx 2 ak dba 96 Oct 23 14:40 .-rwxrwxrwx 1 ak dba 412 Oct 23 14:40 mon_scrdrwxr-x--- 10 ak dba 1024 Oct 13 16:07 .. /home/ak/myscripts/shell_scr $ cat mon_scrcat: Cannot open mon_scr: No such file or directory /home/ak/myscripts/shell_scr $ cat ./mon_scrcat: Cannot open ./mon_scr: No such file or directory /home/ak/myscripts/shell_scr $ /home/ak/shell_scr $ whoamiak
Re: Using oracle together with PHP and redhat
Johan, Perhaps Sean Hull's article on Oracle Technology Network will help. Awesome article! http://otn.oracle.com/oramag/webcolumns/2003/techarticles/hull_php.html Hope this helps. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 22, 2003 6:54 AM I've been trying to get Linux+apache+php+oracle working here with no success, so I've to ask for some help. Versions: RedHat 9.0 PHP 4.3.4RC1 Oracle Enterprise Server 9.2.x Oracle client 8.1.7 The machine has to run RedHat, and newest version would be good. Oracle9.x doesn't seem to be certified for RedHat9 but I've found guides on how to get it running and it works. PHP is compiled with --with-oci8 against the Oracle9.x server, the compilation works but PHP does not. I've set env values in httpd.conf together with the mod_env module without any success, probably because there are no oracle 9 support. I've read that you can use the Oracle 8.x client against a 9.x database and compile PHP against that client instead, but the installation just hangs with a gray window and 100% CPU usage. I've read that it could help to change window manager to fvwm but no success. Any help to either get PHP using Oracle9 or installing Oracle8 on RedHat9 would be appreciated. It would be good if any answers where cced/sent directly to my e-mail address. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johan Kruger-Haglert 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.net -- Author: Arup Nanda 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: using temp tables for staging databases?
Richard, I will repeat my statement made earlier about this. A packed block does not _cause_ BBW, it increases _possibility_ of it. There are several causes of BBW, some are related to application design. The perplexing question is how performance can be improved without changing the app. On the same line of thought - do you see BBWs occuring? If not, these suggestions may not be even relevant. The original poster wanted to know if a PCTUSED 99 and PCTFREE 0 (or something around that) was alright and then another poster inquired under which circumstances those values are acceptable. Tim Gorman correctly pointed out that the 99/0 combination in OLTP will result is a super-packed block resulting in row migration. I further qualified his advise by stating that (in an OLTP database) they will also increase likelihood of buffer busy waits. Note - not PCTUSED alone, but PCTFREE and PCTUSED. Somehow it was misconstrued as pctused only. No, PCTFREE will ensure that the block is completely filled to capacity, pctused will not be relevant then; but if you happen to have a smaller row which will grow to a longer row after update, you may want to play with these values to get a better packing, completely based on your specific situation. I repeat, not PCTUSED alone, but PCTFREE and PCTUSED. You are correct in stating that FTS cost will increase if more blocks are read for the same number of blocks. However, in OLTP, what is the likelihood of FTS as opposed to index scans? It just might be better to accept the FTS cost increase while substantially reducing the chance of BBWs. I also mentioned that in Datawarehouses, the setting of a smaller value of PCTFREE might be acceptable. Why? Three reasons: (1) The likelihood that two rows from the same block by the two sessions is rare = packed blocks are ok (2) The space requirement in DW is usually large and we want to save space = packed blocks are needed (3) As you yourself pointed out - FTS cost will increase and most DW queries use FTS =? packed blocks are desirable. Therefore in DW this combination may be desirable. This thread encouraged me to draw up a test bed to test the effects of combinations of pctfree/initrans/blockssize on various types of apps and the cost of FTS as well as chances of BBWs. I will report the findings to the list. Hope this clears up any confusion. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 22, 2003 10:44 AM Hi Arup, Having a low PCTUSED will achieve nothing with regard to row density per block if there are no deletes or really really significant updates that reduce row lengths. Therefore, using it to reduce BBW is a doubtful method. That said, using say PCTFREE (for which a high value might be effective) to artificially reduce row density per block and hence possibly reduce BBW is also dangerous. Wasted space below the HWM and the extra LIOs associated with FTS can cause more issues than the BBWs you're trying to avoid. Especially if FTS are common and the BBWs are as a result of poor freelist management during inserts ... In my opinion, the reason for the default settings for PCTFREE and PCTUSED is that a row must be greater than 50% of the block size for the insertion to fail whist the block is currently *under* the PCTUSED value (for which a new block is required). This would be the worst case scenario. IMHO, a PCTUSED of 40 is dangerous when tables have sparse/random deletions as this could again result in wasted space below the HWM. FTS would just hate you for it. Ideally PCTFREE should be sized to accommodate average row growths, PCTUSED should be sized to efficiently reclaim deleted space without excessive freelist overheads. It's easier said than done ;) Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 10:29 PM Mark, While waiting for Tim, I can offer another situation - in datawarehouses, where the subsequent updates are not likely to occur. Also, space is a premium and packing the blocks as densly as populated might be necessary. I will also add to Tim's response of justifying a smaller PCTUSED. In addition to the freelist problem he mentioned, there is also a greater chance of buffer busy waits occuring when a block contains too many rows. In an OLTP database that is certainly likely to happen - another case for the default 40 setting for the parameter. In DW, however, the chances of BBW are low, hence a higher setting may be possible. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 5:19 AM Tim, Can you sum up a few situations when the need *has* arisen to change these values? Cheers Mark -Original Message- Tim Gorman Sent: 21 October 2003 06
Re: questions regarding nologging
In addition to operations provided in Mladen's explanation, all partitioning operations such as exchange/split.. partition, etc. do not generate data related redo for segments marked nologging. HTH. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 22, 2003 4:54 PM That's because nologging attribute only affects the direct operations, i.e. the the operations that prebuild blocks and add them below the flood watermark. That includes sqlloader with direct=y, inserts with /*+ append */ hint and CTAS. Normal SQL based operations are not affected. On 10/22/2003 04:39:34 PM, Roger Xu wrote: Hi Gurus, I have a couple of questions regarding nologging. 1) alter table tabname move tablespace tbsname nologging; How come this sql still generated same amount of redo logs equal to the size of the table? 2) alter index idxname rebuild tablespace tbsname nologging; This sql only generate minimum redo logs. But the index ends up LOGGING=NO in dba_indexes view. How do I turn the logging on for this index? Thanks, Roger Xu Database Administrator Dr Pepper Bottling Company of Texas (972)721-8337 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Roger Xu 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). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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.net -- Author: Arup Nanda 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: Cache a table
Never. Altering the table to cache does not gurantee that it will be always be available in the cache. It simply means the table will be placed in the Least recently used end of the LRU list and it will age away as time goes by, just like any other table. A better approach is to use KEEP pool and place teh table (and all other tables that are accessed frequently) there. This is particualrly true for datawarehouses wherethe lookup tables or small dimension tables can be placed in KEEP pool. Ah, come tho think about it, actually there is one situation where I will consider the CACHE option, when I restart the instance and want the hit ratio to look good :) HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 3:39 AM Hi all, when you would consider to put a table a cache... rgds gb Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Gunnar=20Berglund?= 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.net -- Author: Arup Nanda 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: using temp tables for staging databases?
Mark, While waiting for Tim, I can offer another situation - in datawarehouses, where the subsequent updates are not likely to occur. Also, space is a premium and packing the blocks as densly as populated might be necessary. I will also add to Tim's response of justifying a smaller PCTUSED. In addition to the freelist problem he mentioned, there is also a greater chance of buffer busy waits occuring when a block contains too many rows. In an OLTP database that is certainly likely to happen - another case for the default 40 setting for the parameter. In DW, however, the chances of BBW are low, hence a higher setting may be possible. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 5:19 AM Tim, Can you sum up a few situations when the need *has* arisen to change these values? Cheers Mark -Original Message- Tim Gorman Sent: 21 October 2003 06:09 To: Multiple recipients of list ORACLE-L Unless you typo'd, there are some serious problems here... Setting PCTFREE to 99 is not likely to pack in the blocks. Rather the opposite; you are instead leaving blocks 99% empty. Quite a bit of wasted I/O in performing a FULL table scan here... :-) Anyway, it is not a good idea to have PCTFREE and PCTUSED sum to a value greater than 70 or 80 or so, just as a rule of thumb. Having them sum to a value near 100 ensures that each insert, delete, or even update will potentially cause the block to be removed or reinserted to one of the segment's free list. Think about it: the width of a single row crossing the boundary from off the free list to on the free list. Better to leave a bit of a no man's land between the two values. The default settings of PCTFREE=10 and PCTUSED=40 are one of the few default settings that need little manipulation for most situations. on 10/20/03 7:34 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: we drop and recreate the temp tables every night. We also use PCTFREE PCTUSED at 99 and 1 to pack in the blocks and we use very small extent sizes. then we analyze with an estimate size of 20 percent which is quite fast. All of them are used for full table scans and do not have indexes. Ive found that a 'create table as' is MUCH faster than inserting into global temporary tables when you do not have to worry about latch contention(ie 1-3 users logged in at a time). anyone else notice this? Seems to go against conventional wisdom which says never use them. So I want to make sure Im not missing something. From: Tim Gorman [EMAIL PROTECTED] Date: 2003/10/20 Mon AM 10:19:33 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: using temp tables for staging databases? All the time. Oracle Apps's open interfaces are built this way, for example. However, the guys here covered their bases by specifying smaller temporary tables, as if they could prevent them from becoming large. I suppose they might feel that they indemnify themselves if the tables should ever become large? As with OraApps open interface tables, it is when a large volume of data is pushed through that the trouble starts. The high-water marks on all the tables are pushed to a high level, thereafter causing full table scans on the interface/temporary tables to run slowly. The only way to bring the HWM back down is quiesce the interface/app and then truncate the tables. on 10/20/03 6:39 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: This is for non-transactional data load instances. The guys here sware that by using smaller temporary tables(not global temp tables) they can increase the speed of the data loads. Not worried about latch contention because its just for bulk loads. I know this bad in transactional instances. Has anyone used these in non-transactional data load instances? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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.net -- Author: Tim Gorman 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
Re: using temp tables for staging databases?
Binley,The cause of Buffer Busy Waits (BBW) is not exclusively the setting of PCTUSED and PCTFREE; they just two of the causes. To understand the connection, let me explain a little bit on the cause of BBWs.When a session requests some data element from a table, the server process of the session gets the block from the disk to the cache (assume the block is not present in the cache). The event of the block coming from the disk to occupy a buffer in the caceh is pretty straight forward. Now, imagaine, at the exact same time another session selects a row from the same block. A *different* row but from the *same* block. That session will search the cache buffer chain and see that the buffer is not present and will attempt the same maneuevre, i.e. get the buffer from the disk. However, the first session is currently moving the buffer; the second session has to *wait* till the process is complete. This wait is known as buffer busy wait (BBW); but I guess you already knew that. The two sessions are not in conflict over the same row, but the same buffer; so it's not locking contention.How can we eliminate BBWs? Unfortunately we can't bring it to zero. There is always a probability that two sessions will try to get the same block. The only exception is when a block contains only one row. In that case the sessions will select different blocks for different rows. Again, this is not practical. We can reduce BBW by reducing the *possibility* that two sessions will not try to access the same block. This can be done using several ways:(1) reducing the block size(2) making a block less compact, so that each block holds less number of rows. The fewer the number of rows in a block, the lesser the probability that two sessions will access rows in the same block.The first option is not a very practical one in most cases. The second option is. It can be effected by allocating less space in a block, which can be done by using a large value of PCTFREE, e.g. 40 and/or small value of PCTUSED, such as 40, instead of 99. Other ways to achieve the same result is using a higher value of INITRANS, or anything that will cause less number of rows to fill up a block. Less rows = less chance of BBW occuring.I wrote a paper in Select Journal a few months ago explaining this very situation. Although the article is on Segment Level Statistics, it has an example which you can simulate to see the effect of PCTFREE/PCTUSED/INITRANS on Buffer Busy Waits. It can be downlaoded from my website at www.proligence.com/downloads.html and choose New Tool on the Block - Segment Level Statistics. Please feel free to give it a whirl.Further qualifying the case for higher PCTUSED and lower PCTFREE in datawarehouse environments, the chance that two sessions will access the row in same block is much less in DW than in OLTP. Hence the values can be different in DW.HTH.Arup Nanda- Original Message - From: "Binley Lim" [EMAIL PROTECTED]To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED]Sent: Tuesday, October 21, 2003 10:24 AMSubject: Re: using temp tables for staging databases? I'm unclear how BBW is related to PCTUSED. PCTUSED is used to control when blocks are returned to the freelist due to deletions. Blocks already-off the freelist, and above PCTUSED, remain unavailable for inserts. PCTUSED does not prevent a "block contains too many rows" -since a low PCTFREE will pack the rows tightly anyway. If BBW wait is a problem, then there are other causes. PCTUSED is not one of them, or at least should not be an attempted solution. I will also add to Tim's response of justifying a smaller PCTUSED. In addition to the freelist problem he mentioned, there is also a greater chance of buffer busy waits occuring when a block contains too many rows. In an OLTP database that is certainly likely to happen - another case for the default 40 setting for the parameter. In DW, however, the chances of BBW are low, hence a higher setting may be possible. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Binley Lim 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: WHERE 1 = 1 (any info on this)
Or just use NULL as a return predicate in FGAC; it will be the same effect, but may be slightly better. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 9:49 AM I'm using that predicate in FGAC functions so the function always return something. An FGAC metalink note advise to this . Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Hately, Mike (LogicaCMG) Sent: 21 octobre, 2003 07:45 To: Multiple recipients of list ORACLE-L Morning folks, the developers here are looking at a view with a where clause which specifies : WHERE 1=1 AND ... AND ... etc. I'd seen this used before as a way of tweaking the RBO into certain behaviours but it was years ago and my recollection is very hazy. The only explanation I've found so far is : the 1=1 is in there to avoid doing repetitive index scans for single rowids, when the app knows the result set is going to be manipulated rowid's for a large subset of the table. I would guess your DUAL/CBO example had some similar effect. Is anyone familiar enough with this tweak to explain it ? Cheers, Mike PS Maybe it'll head off some replies if I make it clear that this view hasn't been generated by code so the 1=1 isn't an accidental artifact It was custom written and is definitely supposed to have exactly this structure. E mail Disclaimer You agree that you have read and understood this disclaimer and you agree to be bound by its terms. The information contained in this e-mail and any files transmitted with it (if any) are confidential and intended for the addressee only. If you have received this e-mail in error please notify the originator. This e-mail and any attachments have been scanned for certain viruses prior to sending but CE Electric UK Funding Company nor any of its associated companies from whom this e-mail originates shall be liable for any losses as a result of any viruses being passed on. No warranty of any kind is given in respect of any information contained in this e-mail and you should be aware that that it might be incomplete, out of date or incorrect. It is therefore essential that you verify all such information with us before placing any reliance upon it. CE Electric UK Funding Company Lloyds Court 78 Grey Street Newcastle upon Tyne NE1 6AF Registered in England and Wales: Number 3476201 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (LogicaCMG) 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.net -- Author: Stephane Paquette 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.net -- Author: Arup Nanda 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: Cache a table
OOPS! Fat finger it is, indeed. It should be MRU end of the LRU list, not LRU end. Thanks for the correction, Mike. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 8:39 AM Hi, I'm sure you know this but you had some finger trouble there. Just to clarify it for others; Tables with the CACHE option are placed at the Most Recently Used end of the LRU list. Cheers, Mike Hately -Original Message- Sent: 21 October 2003 12:21 To: Multiple recipients of list ORACLE-L Never. Altering the table to cache does not gurantee that it will be always be available in the cache. It simply means the table will be placed in the Least recently used end of the LRU list and it will age away as time goes by, just like any other table. A better approach is to use KEEP pool and place teh table (and all other tables that are accessed frequently) there. This is particualrly true for datawarehouses wherethe lookup tables or small dimension tables can be placed in KEEP pool. Ah, come tho think about it, actually there is one situation where I will consider the CACHE option, when I restart the instance and want the hit ratio to look good :) HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 3:39 AM Hi all, when you would consider to put a table a cache... rgds gb E mail Disclaimer You agree that you have read and understood this disclaimer and you agree to be bound by its terms. The information contained in this e-mail and any files transmitted with it (if any) are confidential and intended for the addressee only. If you have received this e-mail in error please notify the originator. This e-mail and any attachments have been scanned for certain viruses prior to sending but CE Electric UK Funding Company nor any of its associated companies from whom this e-mail originates shall be liable for any losses as a result of any viruses being passed on. No warranty of any kind is given in respect of any information contained in this e-mail and you should be aware that that it might be incomplete, out of date or incorrect. It is therefore essential that you verify all such information with us before placing any reliance upon it. CE Electric UK Funding Company Lloyds Court 78 Grey Street Newcastle upon Tyne NE1 6AF Registered in England and Wales: Number 3476201 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (LogicaCMG) 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.net -- Author: Arup Nanda 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: using temp tables for staging databases?
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.net -- Author: Tim Gorman 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). --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.524 / Virus Database: 321 - Release Date: 06/10/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.524 / Virus Database: 321 - Release Date: 06/10/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting === message truncated === __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker 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.net -- Author: Arup Nanda 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: Refresh option for Materialized view , want to use it during refresh
Siddharth,I will offer a slightly out-of-the-box
solution. Please read it through till the end to determine its applicability in
your case.It seems yours refresh interval is once a day and you don't
mind stale data for a max of 24 hours. You also refresh is complete, not
incremental. So, I would suggest the follwoing approach.(1) Create a
table firstCREATE TABLE CT_PRODUCTID_VW
TABLESPACE NOLOGGINGASSELECT .(2) When
you are ready to "refresh", drop the MVDROP
MATERIALIZED VIEW CT_PRODUCTID_VW;(3) Create the MV with the
PREBUILT TABLE option.CREATE MATERIALIZED VIEW
CT_PRODUCTID_VW BUILD IMMEDIATEREFRESH START WITH SYSDATENEXT
(SYSDATE + 1)ON PREBUILT TABLEAS SELECT
msi.segment1
productid, Your MV is not
accessible between STEP 2 and STEP3, which is really a dictionary update and
takes about a second or so. So the "outage" is really 1 second, not 1/2
hr.A few explanations are in order here.(1) Creating an MV on a
Prebuilt Table does not consume more space. The segment that used to be a table
simply becomes an MV.(2) When you drop the MV, the MV is gone, but the table
remains instact.(3) The table can be create by any means - export/import,
SQL*Loader, INSERT APPEND, etc.(4) IT places less strain on the system
comapred to the MV refresh option, simply because the MV refresh truncates the
segment and then builds it.I presented a paper to the same effect at
IOUG Live 2003. You can download a modified versionof the same from my
website www.proligence.com/downlaods.html,
titled "Painless Master Table Alter" from the Presentations
Section.HTH.Arup Nanda- Original
Message - From: Siddharth Haldankar To: Multiple recipients of list
ORACLE-L Sent: Tuesday, October 21, 2003 3:59 AMSubject: Refresh option
for Materialized view , want to use it during refreshHi
Gurus,I have a materialized view, which is based on Oracle Apps tables
and on remote database. The view refresh takes around ½ hour, during this time
period I cannot see any records in the materialized view and therefore my
application faces errors.The following is the view definitionCREATE
MATERIALIZED VIEW CT_PRODUCTID_VW BUILD IMMEDIATEREFRESH START WITH
SYSDATENEXT (SYSDATE + 1)AS SELECT
msi.segment1
productid,
msi.description
description,
msi.inventory_item_id
inventory_item_id,
mc.segment1
product_family,
mc.segment2
product_typeFROM [EMAIL PROTECTED]
mcs,
[EMAIL PROTECTED]
mc,
[EMAIL PROTECTED]
mic,
[EMAIL PROTECTED] msiwhere
1=1and mc.structure_id
= 50112and
mc.segment3 !=
'SPARE'and
mc.global_name =
'US'and mc.enabled_flag =
'Y'and mcs.global_name =
mc.global_nameand mcs.category_set_name = 'PROD
GROUP'and mic.category_set_id =
mcs.category_set_idand
mic.category_id = mc.category_id
and mic.global_name =
mc.global_nameand mic.organization_id =
1and mic.inventory_item_id =
msi.inventory_item_idand msi.organization_id =
mic.organization_idand
msi.global_name =
mc.global_nameAND msi.auto_created_config_flag =
'N'AND
msi.item_type IN ('ATO
MODEL','CONFIG SPARE','CONFIG SUB','FEATURE PACK','PRODUCT LIST$0','PTO
MODEL','SPARE')and msi.inventory_item_status_code IN
('ENABLE-MAJ','ENABLE-NON','ENABLE-OPT','NONORD')Please note that the
tables referenced are remote tables and Oracle Apps tables and not logging on it
is possible.Please suggest an appropriate refresh mechanism to see the
records even during refresh period.Thanks in advance.With Warm
RegardsSiddharth HaldankarZensar Technologies Ltd.Cisco
Systems Inc. (Offshore Development Center)# : 091 020
4128394[EMAIL PROTECTED][EMAIL PROTECTED]
Re: ORA-02049: timeout: distributed transaction waiting for lock
David, Take a look at Note 19332.1, which explains the error and what to do next. In short, the essence of the note is: The error comes if the time waited is mor than the value of the distributed_lock_timeout parameter. Even if you do a select from the remote database, it acquires a TX lock and that can wait. Increase the value of the timeout or, just use an exception handler on the commit statement to retry. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 11:49 AM Hi List, We have a job that copies data in a table on a remote database to a local database through a database link. Here are the steps in the job: 1. truncate the table of t1 on the local database 2. insert into t1 select * from [EMAIL PROTECTED] 3. commit There are only 847 records in the table. The job completes in 1 sec normally. However, last Sunday we got ORA-02049: timeout: distributed transaction waiting for lock during commit process. As my understanding, the error comes from a DML statement that requires locks on a remote database can be blocked if another transaction own locks on the requested data. I'm pretty sure that there were no any activities on the remote database since the application was not open. Also I can see from the log file (see below) that 847 records were inserted into the t1 table on the local database. The error was generated during the commit process. Does any one have any comments? Thanks for any input. Here is the job log file: 847 rows created. commit * ERROR at line 1: ORA-02049: timeout: distributed transaction waiting for lock We are in Oracle 8.1.7.4 and SunOS 5.8. We take the default value for DISTRIBUTED_LOCK_TIMEOUT . Dave _ Get a FREE computer virus scan online from McAfee. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd 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.net -- Author: Arup Nanda 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: SQL Plus
SQL*Plus windoz version is removed, not the ubiquitous command line tool. How can they remove SQL*Plus, the proverbial cockroach tool of the Oracle world? Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 22, 2003 12:19 PM Hello: Was watching Larry Ellison's key note and he mentioned that they still have SQL Plus in 10g. I heard rumor that it was removed. Since it seems as though some from this list have seen it does it still have SQL Plus or has it been removed? Thanks In Advance, Jay _ High-speed Internet access as low as $29.95/month (depending on the local service providers in your area). Click here. https://broadband.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade 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.net -- Author: Arup Nanda 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: SQLPLUS on UNIX
Rajesh, On the SQL*Plus prompt type the follwoing define _editor = vi Note the underscore before editor. This will make the editor vi for the SQL*Plus session. To make it permanent, put the line in $ORACLE_HOME/sqlplus/admin/glogin.sql, so that it will be executed evey time the SQL*Plus sessions starts. HTH. Arup Nanda www.proligence.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, September 13, 2003 3:09 AM Dear Friends, ed command in SQLPLUS( on UNIX ports )not working. I hope there is some EDITOR setting to use VI editor, but forgot where to do that. Can somebody give a thought. Thanks Rajuveera ** This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Veeraraju_Mareddi 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.net -- Author: Arup Nanda 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: Oracle Standby Database Backups.
Vivek, The origianl poster inquired on Physical Standby (in 8i and 9i) as opposed to logical standby (only in 9i). In physical standby, you don't have a choice of running the standby in noarchivelog mode. The control file is created from the primary as "standby controlfile" which is then implanted at the standby site. Therefore the LOGMODE is V$DATABASE is always ARCHIVELOG and the CONTROLFILE_TYPE is always "STANDBY". I guess you are confused on the potential issue - when the logmode is archivelog, whether the standby generates archived log files. No, the standby does not generate archived logs since it does not excute transactions; it just applies the logs shipped from the primary. When you activate the standby to make it the primary, however, the archived logs are generated. Hope this clears any confusion. Do let us know if you have more questions on this. Arup Nanda www.proligence.com - Original Message - From: VIVEK_SHARMA To: [EMAIL PROTECTED] ; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, September 05, 2003 3:09 AM Subject: RE: Oracle Standby Database Backups. Arup,Indy, List Some Clarifications please If the Primary Database is in ARCHIVELOG Mode (Physical Standby) archived files there from are being shipped applied to the Standby Database, What is the need to run the Standby Database in ARCHIVELOG Mode? Are you implying 9i Dataguard with a Standby which works on a mechanism Other than Log-shipping? Please give detail Thanks -Original Message-From: Arup Nanda [mailto:[EMAIL PROTECTED] Sent: Thursday, September 04, 2003 12:35 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Oracle Standby Database Backups. Tom, You should perform backups from the Standby database, regular RMAN backups, no need to shutdown the database. Make sure you backup the archived log files from there too. Contrary to what the docs might _imply_, I use the word "imply" rather than "state", since the docs have been kind of ambiguous, the archivedlogbackups from the standby are perfectly alright to be used for recoveries.. You could use the RMAN backup on the primary, but why? You would rather want to offload the CPU cycles for RMAN to the standby database. In case of a failure in the primary, your first option is to get the files from standby and recover them. If standby is down too (as in case of a complete disaster), you would reinstate the standby backup files to primary and you will be ok. We are using it to backup out 7 TB OLTP database. HTH. Arup - Original Message - From: Mercadante, Thomas F To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 03, 2003 2:29 PM Subject: Oracle Standby Database Backups. All, We are in the beginning stages of designing a database with Oracle Standby capability. The initial size of the database will be 600-800 Gig. The proposed database will be run on a IBM P690 with a mirrored fail-over machine. Two separate machines with separate disk.We are considering using Oracle Standby to have the database available as much as possible. Do I need to perform regular backups of the Standby database? Sounds like a silly question, but how do I do this? Using Rman? Or do I shut it down and perform a cold backup? I will definitely use Rman on the primary database. Just curious what you all would suggest. Thanks in advance! Tom Mercadante Oracle Certified Professional
Re: alter system reset
ALTER SYSTEM RESET applies to RACs only and you need to give the SID parameter. When you said I find that shared_pool_size exists there, did it exist as the follwoing? *.shared_pool_size=... I bet it did. Note how you used the SID in setting the value alter system set shared_pool_size=40M scope=spfile sid='lahiri' ; This properly set the value for the SID lahiri, not globally. That is why the RESET operation was successful after the SET operation. The same is true for any other parameters that is reset. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 04, 2003 3:29 AM Yes , opened up spfile in a text editor and undo_suppress_errors exists. But I get the error , no matter which parameter I try to reset. ex: undo_management, undo_tablespace, undo_retention, pga_aggregate_target, workarea_size_policy, shared_pool_size, sort...etc, etc However when I open the spfile I find that shared_pool_size exists there , then I try resetting it (just to test it out) SQL alter system reset shared_pool_size scope=spfile sid='lahiri' ; alter system reset shared_pool_size scope=spfile sid='lahiri' * ERROR at line 1: ORA-32010: cannot find entry to delete in SPFILE SQL alter system set shared_pool_size=40M scope=spfile sid='lahiri' ; System altered. SQL alter system reset shared_pool_size scope=spfile sid='lahiri' ; System altered. Why does this happen ? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 00:44 Does the entry even exist in the SPFILE? Open up the spfile in notepad and check the existence of the parameter in there. Do the following: SQL alter system set undo_suppress_errors = false scope=spfile sid='ananda'; System altered. SQL ALTER SYSTEM RESET undo_suppress_errors scope=spfile sid='ananda'; System altered. HTH. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 01, 2003 2:14 PM Hello list I am using Oracle 9.2.0.1.0 enterprise on windows. When I run the following ( connected as sysdba , and using an spfile called %oracle_home%\database\spfilelahiri.ora ) SQL show user USER is SYS SQL host echo %oracle_sid% LAHIRI SQL sho parameter db_name NAME TYPE VALUE db_namestring lahiri SQL ALTER SYSTEM RESET undo_suppress_errors SCOPE=BOTH SID='lahiri'; ALTER SYSTEM RESET undo_suppress_errors SCOPE=BOTH SID='lahiri' * ERROR at line 1: ORA-32010: cannot find entry to delete in SPFILE Any ideas ? I get the same problem irrespective of the parameter I try to reset. .. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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.net -- Author: Arup Nanda 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.net -- Author: [EMAIL PROTECTED] 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.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City
Re: A basic replication question
Walt, I presented a paper at IOUG Live 2003 and wrote an article on DBAZine on an issue similar to this. Although the issue addressed was something much more complex; the article does have scripts to set up a basic readonly snapshot (or MV) replication. The article is at http://www.dbazine.com/nanda2.html. Hope you will find it useful. Best reagrds, Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 03, 2003 12:29 PM Okay, we have a request for quite a few of our customers for read-only copies of their databases they can do their ad-hoc queries on. These read-only databases need to as closely matched to the production database as possible, i.e., exact, from their point of view. I've looked into some options to do this. Since we're going to 9i fairly soon I was thinking of setting up logical standbys, but I've read some pretty bad things about logical standbys -- typical new buggy Oracle product. I've also started into looking at basic replication: maybe just simple updateable snapshots refreshed every now and then. So, for the past couple of days I've gone through Metalink, Technet, and the mail archives on Oracle-l trying to learn about simple, basic, readonly replication. The problem is, all of the manuals, white papers, etc. I've found don't deal with how to set up and administer simple basic replication. It's all mixed in with multi-master replication, Advanced Replication, and stuff like that. I'm new to replication and would like to basically start learning the basics for basic replication, basically. Can anyone point me to a document that talks about basic read-only replication only? Or am I fooling myself into thinking there is such a thing? Thanks, --Walt Weaver Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Weaver, Walt 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.net -- Author: Arup Nanda 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: Listener Lockout Feature
Listener password failures do not lock the password, unfortunately; but it is possible to identify the failed logins. The listener log shows entries similar to the lines below 05-JUL-2003 21:14:39 * services * 1169 TNS-01169: The listener has not recognized the password Note the lines TNS-01169: The listener has not recognized the password , which could mean as simple as a fat fingered DBA or repeated attempts to break in. As a rule of thumb, I scan TNS-01169 errors and review them periodically. HTH. Arup Nanda www.proligence.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 03, 2003 12:39 PM Any idea if a listener lockout feature is going to be added to the listener utility, i.e. lock after x failed login attempts (this is on the listener password). Are failed login attempts logged in the log file? Being lazy on that last question. Thanks, Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan 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.net -- Author: Arup Nanda 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: alter system reset
One should not rely on either the init.ora or the spfile to determine which parameters are set, but should query the database True, but the original poster wanted to see why the ALTER SYSTEM RESET ... SCOPE=SPFILE failed and that would occur only if the entry is not found in the spfile, even if the value is set in the database with SCOPE=MEMORY. Hence you should _not_ check the database but should check the spfile. HTH. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 03, 2003 3:34 PM Is the want to read the spfile just a curiosity? One should not rely on either the init.ora or the spfile to determine which parameters are set, but should query the database. Now that we have both init.ora and an spfile someone is going to get caught. For instance a patch requirement requires 150 MB shared_pool and java_pool sizes and warns about an unrecoverable memory error if the pools are undersized. The person dutifully makes the changes to the init.ora and starts the database, but forgets that an spfile is being used with the database He then starts the patch and it fails with the unrecoverable memory error. I would have been caught by this if my patchset installation procedure did not include show parameter to verify their settings. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Monday, September 01, 2003 8:19 PM To: Multiple recipients of list ORACLE-L Prem, The SPFILE should never be opened to be modified; opening a file to see the contents are acceptable and that's what I said. Sometimes opening the file in an editor may not be needed; type in Windows command prompt or cat in unix will let us achive the same objective. Talking about the SPFILE modification, you can't effectively modify it with accuracy. The file is binary (actually binary in the beginning and then text towards the bottom part), so even if you open it in notepad and save it, there is no guarantee that the file will be accurately saved with all contents intact. By the way, I have edited the SPFILE in some cases, only in development, though; but I wouldn't advise it to be done that way; always use ALTER SYSTEM ... SCOPE=SPFILE to modify it or edit the init.ora file and then create the SPFILE from it. You can create the spfile from pfile even when the instance is down. HTH. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 01, 2003 10:49 PM Arup Bhai, i remember oracle docs saying NOT to open a spfile and that it may even corrupt the file. Is that always true ? Can the spfile be opened ? can you kindly explain me ? Thanks Regards, Prem Khanna J. 02-09-2003 04:14:26, Arup Nanda [EMAIL PROTECTED] wrote: Does the entry even exist in the SPFILE? Open up the spfile in notepad and check the existence of the parameter in there. Do the following: SQL alter system set undo_suppress_errors = false scope=spfile sid='ananda'; System altered. SQL ALTER SYSTEM RESET undo_suppress_errors scope=spfile SQL sid='ananda'; System altered. Arup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J 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.net -- Author: Arup Nanda 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.net -- Author: MacGregor, Ian A. 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
Re: Oracle Enhancement Request System
Title: Oracle Enhancement Request System Raj, The new enhancement system is inside Metalink now. When you want to open an ER, just open an iTAR, but in the drop down field named Type of TAR, choose "Enhancement Request". Thanks. Arup - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Tuesday, September 02, 2003 1:34 PM Subject: Oracle Enhancement Request System Does anyone have the URL handy ?? I have managed to misplace mine. TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !
Re: Oracle Standby Database Backups.
Tom, You should perform backups from the Standby database, regular RMAN backups, no need to shutdown the database. Make sure you backup the archived log files from there too. Contrary to what the docs might _imply_, I use the word "imply" rather than "state", since the docs have been kind of ambiguous, the archivedlogbackups from the standby are perfectly alright to be used for recoveries.. You could use the RMAN backup on the primary, but why? You would rather want to offload the CPU cycles for RMAN to the standby database. In case of a failure in the primary, your first option is to get the files from standby and recover them. If standby is down too (as in case of a complete disaster), you would reinstate the standby backup files to primary and you will be ok. We are using it to backup out 7 TB OLTP database. HTH. Arup - Original Message - From: Mercadante, Thomas F To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 03, 2003 2:29 PM Subject: Oracle Standby Database Backups. All, We are in the beginning stages of designing a database with Oracle Standby capability. The initial size of the database will be 600-800 Gig. The proposed database will be run on a IBM P690 with a mirrored fail-over machine. Two separate machines with separate disk.We are considering using Oracle Standby to have the database available as much as possible. Do I need to perform regular backups of the Standby database? Sounds like a silly question, but how do I do this? Using Rman? Or do I shut it down and perform a cold backup? I will definitely use Rman on the primary database. Just curious what you all would suggest. Thanks in advance! Tom Mercadante Oracle Certified Professional
Re: Oracle World - Listers get together (proposed Tuesday Sep 9)
John, Oracle Awards felicitation is a luncheon on Wednesday. So Dinner is free that day. By the way, Sean Hull also expressed his interest to join the party. Thanks. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 03, 2003 5:54 PM Hi all, We talked about this earlier and I wanted to get this mail out before everyone participating departs for OOW. I would propose a Lister's get-together on the evening of Tuesday Sep 9. Monday is the first day, Wed has the 'OracleWorld Appreciation day' in the evening and I am assuming there will be felicatations for Arup and Mogens at this time... (and Thu ends it all). I have the following that have responded (in no particular order): Arup Nanda, Jonathan Gennick, Matthew Adams, Brian McGraw, Ari Kaplan, Cary Millsap (+ other Gurus - Cary brought along Tom Kyte and Kyle Hailey last time?), Connor McDonald (all the way from Down under!), Greg Loughmiller, Matthew Zito, Molina Gerardo and self. We will meet over Dinner at a restaurant across the street from Moscone Center - probably from about 6:30PM? The address is: Chevy's 201 3rd Street (corner of 3rd and Howard) San Francisco, CA 94105 415-543-8060 I will send out a reminder email closer to that time (like Monday :) Let me know if there are additional numbers... John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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.net -- Author: Arup Nanda 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: alter system reset
Prem, The SPFILE should never be opened to be modified; opening a file to see the contents are acceptable and that's what I said. Sometimes opening the file in an editor may not be needed; type in Windows command prompt or cat in unix will let us achive the same objective. Talking about the SPFILE modification, you can't effectively modify it with accuracy. The file is binary (actually binary in the beginning and then text towards the bottom part), so even if you open it in notepad and save it, there is no guarantee that the file will be accurately saved with all contents intact. By the way, I have edited the SPFILE in some cases, only in development, though; but I wouldn't advise it to be done that way; always use ALTER SYSTEM ... SCOPE=SPFILE to modify it or edit the init.ora file and then create the SPFILE from it. You can create the spfile from pfile even when the instance is down. HTH. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 01, 2003 10:49 PM Arup Bhai, i remember oracle docs saying NOT to open a spfile and that it may even corrupt the file. Is that always true ? Can the spfile be opened ? can you kindly explain me ? Thanks Regards, Prem Khanna J. 02-09-2003 04:14:26, Arup Nanda [EMAIL PROTECTED] wrote: Does the entry even exist in the SPFILE? Open up the spfile in notepad and check the existence of the parameter in there. Do the following: SQL alter system set undo_suppress_errors = false scope=spfile sid='ananda'; System altered. SQL ALTER SYSTEM RESET undo_suppress_errors scope=spfile sid='ananda'; System altered. Arup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J 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.net -- Author: Arup Nanda 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: get sid (session id) and serial#?
Title: Message Oracle _officialy_doesn't support it even now. It is included in the sources with a caveat that it will be installed only under instructions from the Support Analysts only. That is why the package is not installed as a part of a normal installation. Think of it this way - even if it was not included "by mistake" in 8.1.5, there were two releases of 8i after that, 8.1.6 and 8.1.7, not to mention several patchkits. The mistake could have been easily fixed by including the source. However, that was not done. Similarly, if it was a mistake, Oracle support should havebeen more than happy to send out the source upon request; but they weren't. HTH. Arup - Original Message - From: Hitchman, Peter To: Multiple recipients of list ORACLE-L Sent: Monday, September 01, 2003 10:09 AM Subject: RE: get sid (session id) and serial#? Hi, It is my understanding that Oracle do support the dbms_support package and that it should have been in the 8i distribution, but was missed out by mistake. It's calls to sys.dbms_system.set_ev they do not supoprt since this enables you to set any debug event in the kernel. Regards Pete -Original Message-From: Arup Nanda [mailto:[EMAIL PROTECTED]Sent: 30 August 2003 00:30To: Multiple recipients of list ORACLE-LSubject: Re: get sid (session id) and serial#? Oracle officially "desupported" the package in 8i; but then included (by mistake?) in 9i. In 8i, however, you could pester your friendly neuighborhood Support Analyst to give you the package. You could try from 9i sources, too; but not sure if they work. Arup - Original Message - From: Igor Neyman To: Multiple recipients of list ORACLE-L Sent: Friday, August 29, 2003 5:29 PM Subject: RE: get sid (session id) and serial#? It's not in 8.1.5 Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of John KanagarajSent: Friday, August 29, 2003 3:20 PMTo: Multiple recipients of list ORACLE-LSubject: RE: get sid (session id) and serial#? Need to install @?/rdbms/admin/dbmssupp while connected as SYS. Available on all platforms 8.0.6+ I understand. John KanagarajDB Soft IncPhone: 408-970-7002 (W)Disappointment is inevitable, but Discouragement is optional!** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Murali_Pavuloori/[EMAIL PROTECTED] [mailto:Murali_Pavuloori/[EMAIL PROTECTED]] Sent: Friday, August 29, 2003 3:35 PM To: Multiple recipients of list ORACLE-L Subject: RE: get sid (session id) and serial#? Raj, Which version of db are you on? This is not available on 9.2.0.3 Murali. __The information contained in this email is confidential and intended only for the use of the individual or entity named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. Thomson Scientific will accept no responsibility or liability in respect to this email other than to the addressee. If you have received this communication in error, please notify us immediately via email: [EMAIL PROTECTED]__
Re: book
What exactly do you want to see in the book on internals and kernel? As some others have mentioned already, Steve Adam's book is excellent for undertanding the internal workings; but it may not be appropriate for a beginner. If you want the knowledge as a beginner, do check out Tom Kyte's book - Expert One-to-One; it has a clear explnation of the Oracle processes and file interaction. This book is for 8i, too; but will help you in understanding 9i stuff as well. If you are lookin fro internal latches, locks, waits information, then Steve Adam's book is unparalled. You have to supplement your learning with tid-bits of informaion from other sources such as MetaLink, articles, OTN, and more. HTH. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 01, 2003 5:44 AM Sorry to repost but forgot to mention, that I needed a book for Oracle 9i Hello list any recommendations about buying a book on oracle internal working , oracle kernel ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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.net -- Author: Arup Nanda 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: get sid (session id) and serial#?
It's hardly illogical. The purpose of this view is not to provide the SID of the current session, but to show statistics, some of which the DBA may not want a user to know in all cases. Therefore the view is granted to a role SELECT_CATALOG_ROLE, similar to most other dictionary views. Any user with that role can see the view. However, granting the role SELECT_CATALOG_ROLE to all users is not advisable at all. If a user needs it, a very simple grant select on v_$mystat to username can be issued which will resolve the problem. At a development site, or a site where the security for this view is not quite pronounced, you can issue the follwing which will make everyone see this view. grant select on v_$mystat to public; This will make the view visible to even the users created after this command. The decision to hide this view from normal users is deliberate; but breaking that lock is not hard either using a simple grant as shown. Going back to the discussion on why it has been so hard for a user to know his/her SID - why does a user need to know the SID? To alter the DBA for some monitoring action and seeing the SID will help the DBA see that in V$SESSION? In that case, the proper technique is to set the client identifier using DBMS_APPLICATION_INFOR.SET_IDENTIFIER procedure. The value shows up in v$session, no need to know the SID. What else? In a web app maintain state between pages using SID? this approach is fraight with errors. First, SID is not guaranteed to be unique; SID and SERIAL# combination is. To guarantee a unique identifier, use the function DBMS_SESSION.UNIQUE_SESSION_ID to get an id that is unique in a session and use it to maintain state. If it is necessary that the user must know the SID, then the V$MYSTAT can be queried. Hope this helps. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 01, 2003 4:54 AM It does seem illogical than Oracle provide a view called mystat which tells you about your own statistics, including SID and yet a normal user does not have access to that view unless specifically given. John -Original Message- Sent: 30 August 2003 15:04 To: Multiple recipients of list ORACLE-L But the user need select access to v$mystat connect as sysdba and grant select on v_$mystat to user; HTH GovindanK Thanks all for the input. the script is select sid,serial# from v$session where sid=(select sid from v$mystat where rownum=1) But the user need select access to v$mystat Chuan -Original Message- Sent: Friday, 29 August 2003 15:19 To: Multiple recipients of list ORACLE-L Hi Chuan, can v$session help you this view has information like machine, osuser, username, sid, program, and others SQL desc v$session SQL select columns,... from v$session where username = 'SCOTT' and machine = 'YOUR_HOSTNAME' if you do telnet you will get 2 rows (if scoot is only use by you) is not Sinardy -Original Message- Sent: 29 August 2003 12:34 To: Multiple recipients of list ORACLE-L DABs, Is there any way in my connection to get the sid and serial# for my own connection? Suppose I connect to Oracle db by sqlplus scott/[EMAIL PROTECTED] In this connection, SQL What shoud I input to get this sid and serial#? TIA Chuan Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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.net -- Author: Sinardy Xing 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
Re: alter system reset
Does the entry even exist in the SPFILE? Open up the spfile in notepad and check the existence of the parameter in there. Do the following: SQL alter system set undo_suppress_errors = false scope=spfile sid='ananda'; System altered. SQL ALTER SYSTEM RESET undo_suppress_errors scope=spfile sid='ananda'; System altered. HTH. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 01, 2003 2:14 PM Hello list I am using Oracle 9.2.0.1.0 enterprise on windows. When I run the following ( connected as sysdba , and using an spfile called %oracle_home%\database\spfilelahiri.ora ) SQL show user USER is SYS SQL host echo %oracle_sid% LAHIRI SQL sho parameter db_name NAME TYPE VALUE db_namestring lahiri SQL ALTER SYSTEM RESET undo_suppress_errors SCOPE=BOTH SID='lahiri'; ALTER SYSTEM RESET undo_suppress_errors SCOPE=BOTH SID='lahiri' * ERROR at line 1: ORA-32010: cannot find entry to delete in SPFILE Any ideas ? I get the same problem irrespective of the parameter I try to reset. .. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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.net -- Author: Arup Nanda 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: REPLICATION: ORA-23313: object group PUBLIC.HR_REPG is not mastered at ORC1.WORLD
Before you create the Materialized View Group, you must create the REPGROUP at the Master site, which in you case is ORC1.world. In the database ORC1, use the DBMS_REPCAT.CREATE_MASTER_REPGROUP and DBMS_REPCAT.CREATE_MASTER_REPOBJECT before calling the procedure DBMS_REPCAT.CREATE_MVIEW_REPGROUP. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 01, 2003 4:24 PM mastered at ORC1.WORLD Hi list I am using Oracle 9.2.0.1.0 enterprise on windows. I am going thru the examples provided in Replication Management API Reference-Part No. A96568-01. While creating MVIEW REPLICATION GROUP, getting the following error (page # 5-7) SQL CONNECT mviewadmin/[EMAIL PROTECTED] Connected. SQL BEGIN 2 DBMS_REPCAT.CREATE_MVIEW_REPGROUP ( 3 gname = 'hr_repg', 4 master = 'orc1.world', 5 propagation_mode = 'ASYNCHRONOUS'); 6 END; 7 / BEGIN * ERROR at line 1: ORA-23313: object group PUBLIC.HR_REPG is not mastered at ORC1.WORLD ORA-06512: at SYS.DBMS_SYS_ERROR, line 105 ORA-06512: at SYS.DBMS_REPCAT_SNA_UTL, line 2424 ORA-06512: at SYS.DBMS_REPCAT_SNA_UTL, line 1757 ORA-06512: at SYS.DBMS_REPCAT_SNA, line 64 ORA-06512: at SYS.DBMS_REPCAT, line 1262 ORA-06512: at line 2 Any help would be really greatful. Thanks Sami __ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Oracle DBA 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.net -- Author: Arup Nanda 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: REPLICATION: ORA-23313: object group PUBLIC.HR_REPG is not mastered at ORC1.WORLD
Sami, Setting up replication is pretty straight forward as long as you follow all the steps dilligently. Unfortunately if a step is missed, it's hard to diagnose the problem until one is at the terminal looking at the actual database. In this case there are several steps that my not have been correctly executed. Here are some: (1) Have you built the replication support for the master group? begin dbms_repcat.generate_replication_support( sname='HR', oname='DEPATMENTS', type='TABLE', min_communication=TRUE); end; (2) Have you started replication activity? begin dbms_repcat.resume_master_activity( gname='HR_REPG'); end; (3) Have you created the snaphsot group at the MV Site using dbms_refresh.make? You should do this steps before you create the MV RepGroup. You may find an article I wrote for DBAZine (http://www.dbazine.com/nanda2.html), a related but separate issue. And please let us know your progress. HTH. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 01, 2003 5:09 PM mastered at ORC1.WORLD Dear Arup, Thanks for your response. But i have done the same which is mentioned in page # 3-6(Replication API Reference Manual). Please see below. What else could be wrong? TIA CONNECT repadmin/[EMAIL PROTECTED] BEGIN DBMS_REPCAT.CREATE_MASTER_REPGROUP ( gname = 'hr_repg'); END; / BEGIN DBMS_REPCAT.CREATE_MASTER_REPOBJECT ( gname = 'hr_repg', type = 'TABLE', oname = 'departments', sname = 'hr', use_existing_object = TRUE, copy_rows = FALSE); END; / --- Arup Nanda [EMAIL PROTECTED] wrote: Before you create the Materialized View Group, you must create the REPGROUP at the Master site, which in you case is ORC1.world. In the database ORC1, use the DBMS_REPCAT.CREATE_MASTER_REPGROUP and DBMS_REPCAT.CREATE_MASTER_REPOBJECT before calling the procedure DBMS_REPCAT.CREATE_MVIEW_REPGROUP. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 01, 2003 4:24 PM mastered at ORC1.WORLD Hi list I am using Oracle 9.2.0.1.0 enterprise on windows. I am going thru the examples provided in Replication Management API Reference-Part No. A96568-01. While creating MVIEW REPLICATION GROUP, getting the following error (page # 5-7) SQL CONNECT mviewadmin/[EMAIL PROTECTED] Connected. SQL BEGIN 2 DBMS_REPCAT.CREATE_MVIEW_REPGROUP ( 3 gname = 'hr_repg', 4 master = 'orc1.world', 5 propagation_mode = 'ASYNCHRONOUS'); 6 END; 7 / BEGIN * ERROR at line 1: ORA-23313: object group PUBLIC.HR_REPG is not mastered at ORC1.WORLD ORA-06512: at SYS.DBMS_SYS_ERROR, line 105 ORA-06512: at SYS.DBMS_REPCAT_SNA_UTL, line 2424 ORA-06512: at SYS.DBMS_REPCAT_SNA_UTL, line 1757 ORA-06512: at SYS.DBMS_REPCAT_SNA, line 64 ORA-06512: at SYS.DBMS_REPCAT, line 1262 ORA-06512: at line 2 Any help would be really greatful. Thanks Sami __ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Oracle DBA 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.net -- Author: Arup Nanda 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). __ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Oracle DBA INET: [EMAIL PROTECTED] Fat City Network Services-- 858
Re: get sid (session id) and serial#?
select sid from v$mystat where rownum 2 - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, August 29, 2003 12:34 AM DABs, Is there any way in my connection to get the sid and serial# for my own connection? Suppose I connect to Oracle db by sqlplus scott/[EMAIL PROTECTED] In this connection, SQL What shoud I input to get this sid and serial#? TIA Chuan Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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.net -- Author: Arup Nanda 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).
Guild of DBAs
And how do you suppose you would conduct the intiation to this Guild? A touch of your scepter on the shoulders? A hard knock on the head? Being asked to hold on the TAR call for a dozen ORA-600 errors? Or negotiating with a bunch of developers for using bind variable? Arup, Wannabe founding member of this this Guild. By the way, you might have noticed, I changed the subject line. Much as I like to see my name up there, I think the topic has moved on :) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, August 29, 2003 4:04 PM DENNIS WILLIAMS scribbled on the wall in glitter crayon: Stephen To copy from Alcoholics Anonymous, I'm Dennis and I'm a DBA. I've been promoting an idea of a Guild of Oracle DBAs. Nobody would be allowed to call themselves an Oracle DBA without our permission. An organization where the Sargent of Arms is more than a ceremonial post. i do believe the term is Sergeant at Arms, and i can do that.;-) with or without equipment.;-) but then again i don't know if the Guild would admit me.;-) -- Bill Shrek Thater ORACLE DBA BAARF Party member #25 [EMAIL PROTECTED] Man was born to be rich, or grow rich by use of his faculties, by the union of thought with nature. Property is an intellectual production. The game requires coolness, right reasoning, promptness, and patience in the players. Cultivated labour drives out brute labour. - Ralph Waldo Emerson -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William 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.net -- Author: Arup Nanda 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: package errors
David, Could you post the few lines before and after the line the package comiplation is failing. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, August 29, 2003 6:39 PM I am getting this error from these packages. In one database they create just fine (8.1.7.0.0) and the other database they error out (8.1.7.1.0) does anybody have an idea? SQL @p_autoaud_api.pck CREATE PACKAGE BODY p_autoaud_api * ERROR at line 1: ORA-06553: PLS-103: Encountered the symbol when expecting one of the following: an identifier a double-quoted delimited-identifier check foreign primary unique ORA-06553: PLS-114: identifier ' VARCHAR2(BYTECOUNT=16) NOT N' too long ORA-06553: PLS-112: end-of-line in quoted identifier SQL @p_autoaud_utl.pck CREATE OR REPLACE PACKAGE BODY p_autoaud_utl * ERROR at line 1: ORA-06553: PLS-103: Encountered the symbol when expecting one of the following: an identifier a double-quoted delimited-identifier check foreign primary unique ORA-06553: PLS-114: identifier ' VARCHAR2(BYTECOUNT=16) NOT N' too long ORA-06553: PLS-112: end-of-line in quoted identifier thanks, David Ehresmann -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ehresmann, David 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.net -- Author: Arup Nanda 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: get sid (session id) and serial#?
Title: Message Oracle officially "desupported" the package in 8i; but then included (by mistake?) in 9i. In 8i, however, you could pester your friendly neuighborhood Support Analyst to give you the package. You could try from 9i sources, too; but not sure if they work. Arup - Original Message - From: Igor Neyman To: Multiple recipients of list ORACLE-L Sent: Friday, August 29, 2003 5:29 PM Subject: RE: get sid (session id) and serial#? Its not in 8.1.5 Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of John KanagarajSent: Friday, August 29, 2003 3:20 PMTo: Multiple recipients of list ORACLE-LSubject: RE: get sid (session id) and serial#? Need to install @?/rdbms/admin/dbmssupp while connected as SYS. Available on all platforms 8.0.6+ I understand. John KanagarajDB Soft IncPhone: 408-970-7002 (W)Disappointment is inevitable, but Discouragement is optional!** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Murali_Pavuloori/[EMAIL PROTECTED] [mailto:Murali_Pavuloori/[EMAIL PROTECTED]] Sent: Friday, August 29, 2003 3:35 PM To: Multiple recipients of list ORACLE-L Subject: RE: get sid (session id) and serial#? Raj, Which version of db are you on? This is not available on 9.2.0.3 Murali.
Re: How to keep root out?
Walter, Unfortunately, there is no way. You can prevent root from connecting as sysdba by removing the dba group from root userid; but hey, root can "root" it again; he is root, remember, omnipotent. Even if that is successful, he can connect toany dba account, such as "oracle" using "su -" and then connect as sysdba. Worse, they can connect to _any_ dba user, not necessarily "oracle", and your audit logs will show as if coming from that user. Therefore the issue is serious than it sounds like and you should approachat from the manegerial level. Take dba group out if the root userid and establish ground rules that dba group is never allowed to any user without the DBA's request. If they continue to do "su - oracle", make them aware that this operation is imporsonation, and may be deemed illegal. They will listen to that word! HTH. Arup - Original Message - From: Walter K To: Multiple recipients of list ORACLE-L Sent: Thursday, August 28, 2003 11:34 AM Subject: How to keep "root" out? Just for grins, I'll ask this question... Is there any way to keep the Unix "root" user from logging into the database (i.e. connect internal or / as sysdba)? Currently using 8.1.7.4 on Solaris 8 here. We have a couple people in our Unix admin group that feel the need to "help" by writing their own DB monitoring scripts. Of course, they don't know what they're talking about. They do not have formal logins for the database, but since they are root users they are connecting via "connect internal". This is not only counterproductive but actually a potential security issue--just because someone has root doesn't necessarily entitle them to see the data in the database. What if it is a payroll database? So, I'm curious,is there any way to prevent access via "connect internal" or "/ as sysdba"? Thanks in advance. W
Re: How to keep root out?
Title: Message Better yet, put the following lines echo ORA-600 [kgfdjjks] [scdcsc] [dssdcdcsdc] [45] [999] Unauthorized root access then print some garbage into a file named like the regular trace files in user_dump_dest directory. Open up a iTARand show this "trace" file to your SA's manager, along with the TAR number. Let the fun begin. - Original Message - From: Mladen Gogala To: Multiple recipients of list ORACLE-L Sent: Thursday, August 28, 2003 1:04 PM Subject: RE: How to keep "root" out? Put the following code snippet "if [ "$LOGNAME" = "root" ]; then init 0 fi; in your oraenv. I guarantee you that the SA will no longer be connecting as SYSDBA. --Mladen GogalaOracle DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Walter KSent: Thursday, August 28, 2003 11:34 AMTo: Multiple recipients of list ORACLE-LSubject: How to keep "root" out? Just for grins, I'll ask this question... Is there any way to keep the Unix "root" user from logging into the database (i.e. connect internal or / as sysdba)? Currently using 8.1.7.4 on Solaris 8 here. We have a couple people in our Unix admin group that feel the need to "help" by writing their own DB monitoring scripts. Of course, they don't know what they're talking about. They do not have formal logins for the database, but since they are root users they are connecting via "connect internal". This is not only counterproductive but actually a potential security issue--just because someone has root doesn't necessarily entitle them to see the data in the database. What if it is a payroll database? So, I'm curious,is there any way to prevent access via "connect internal" or "/ as sysdba"? Thanks in advance. W Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error,please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient.Wang Trading LLCand any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.
Re: How to keep root out?
Tanel, That's a cool tip! Thanks. Arup - Original Message - From: Tanel Poder To: Multiple recipients of list ORACLE-L Sent: Thursday, August 28, 2003 4:54 PM Subject: Re: How to keep "root" out? As an alternativefor setting sqlnet.authentication_services to none, you can also set event 10063 which disables usage of OPER DBA privileges in OSD layer. This one is probably harder to find out for a regular sysadmin (especially when you put it in a wrapped after startup trigger :) But be careful, I don't know how exactly this event works, but it seems that I can't log on to another test instance under the same user either using sysdba now. And setting this event to "off" didn't help either. Had to bounce (ver 8.1.7.1 on Solaris). Most of events are unsupported as well... See below, Tanel. bash-2.03$ sqlplus "/ as sysdba" SQL*Plus: Release 8.1.7.0.0 - Production on Thu Aug 28 22:38:51 2003 (c) Copyright 2000 Oracle Corporation. All rights reserved. Connected to:Oracle8i Enterprise Edition Release 8.1.7.1.0 - ProductionWith the Partitioning optionJServer Release 8.1.7.1.0 - Production SQL alter system set events '10063 trace name context forever, level 1'; System altered. SQL exitDisconnected from Oracle8i Enterprise Edition Release 8.1.7.1.0 - ProductionWith the Partitioning optionJServer Release 8.1.7.1.0 - Productionbash-2.03$ sqlplus "/ as sysdba" SQL*Plus: Release 8.1.7.0.0 - Production on Thu Aug 28 22:39:03 2003 (c) Copyright 2000 Oracle Corporation. All rights reserved. ERROR:ORA-01031: insufficient privileges
Re: materialized views
Sai, You may want to check bug# 1188948; it sounds like your case. HTH. Arup - Original Message - From: Sai Selvaganesan To: Multiple recipients of list ORACLE-L Sent: Tuesday, August 26, 2003 9:04 PM Subject: materialized views hi materialized views become invalid as soon as a complete or fast refresh of the mv is done. is this how it is supposed to work? the status is invalid in dba_objects and is set to 'N' in dba_mview_analysis. i have been struggling to understand this. i read thru few docs on metalink which talk about query rewrite enabled blah..blah.. but nothing seems to work. can some tell me what has to be done. it is mandatory for me to validate the mvs after every refresh.. thanks sai
Re: Nature of Oracle-l has changed
Part of the problem lies with the old human vices - jealousy comes into mind, first. The problem is mostly not with companies but immediate supervisers, who often struggle with the prospect that the subordinate will get more money - and they resent it to very core. They would rather hire someone off the street with more money than give the old failthful the due share. The other problem is the HR departments magic wand yardstick of salary and compensation which dictates, often incorrectly, how much a particular job's adequate compensation is. Never mind the fact that a regular HR joe doesn't understand DBAs from Developers - so the highly paid specialist boils it dall own to a simple yardstick - number of years of experience! Several years ago I rose to the postition of the lead DBA at a company when I was 24, but my salary was less than the lowest of the 15 DBAs in the team. Reason - my years of experience was simply didn't show high enough in the yardstick to warrant a higher salary. It was even more painful when I was the fail-over contact for all members of the team. When the pager goes off in the middle of the night, out I go to fix the problem in the HR database and just making sure all is well, especially in the salary table, where the indisposed team member's pay glares, almost mockingly! I left; the new person was almost myage, but the negotiated salary was higher. The HR department's magic yardstick was broken by the departmental manager. Similarly, the in the new place I went, there was no problem in getting a much fatter paycheck. Morale - when you stick around, you become hoi poloi; the knight in the shining armor is the one who comes from outside! Regards, Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, August 26, 2003 9:49 PM Partially true. I've seen the IT cutbacks at a company where people almost never leave. Many IT folk have been there 10+ year, a surprising number of them 20+ years. The flip side to the salary story is something of a paradox. As a person became more experienced, learned new technologies, and as the company embraced more technologies, the employees at times may not be paid commensurate with their abilities. I experienced that once. The only way to increase my earning power was to leave. My salary jumped 50% immediately. This has no doubt happened to a number of folks. The silly side of this is that the former employer then had to hire a replacement at the going rate, or get a contractor in. Bottom line, they lose an experienced employee, and end up paying as much or more as if they had tried to retain said employee. Jared Jared On Tue, 2003-08-26 at 18:04, [EMAIL PROTECTED] wrote: I think a lot of IT people abused the situation during the boom days. Company loyalty meant nothing ... we'll go wherever the biggest paychecks are. Don't stay anywhere too long. that's for losers. Change jobs if we felt the least bit abused and unappreciated. That'll teach them to screw with me! In general a holier-than-thou attitude. The times allowed us to do that. But it also means a lot of non-IT people developed an opinion of IT folk as not being team players, only out for themselves, not committed to the company, etc. So when the chance comes to cut back, where are you going to look? :-) Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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.net -- Author: Jared Still 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.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: column level privilege - resolved
Rahul,
Glad to know that it worked for you. Yes, I have implemented that and it
works quite well in production.
Just make sure you understand that the columns are not really hidden, just
masked - which means they may convey wrong impressions if not used
properly. In my case, I have avoided using it in number fields; since in
character fields it's easy to put a value like 'XXX', or 'NOT ALLOWED'.
In the number field, a value of 0 may be misconstrued, especially in
aggregation functions. Another options is to use NULLs, but they will lend a
hand in indexing.
So, use this with caution. By the way, it's Arup, not Arun :)
Arup Nanda
www.proligence.com
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, August 26, 2003 11:44 AM
Arun, thanks for suggesting this, this is what i did.
1. created a table to store table names associated column names
and a security level for a that column
EMP emp_name 3
EMP emp_sal 5
2. then i granted every users a security level also,
3. then i created a view on the name table
select decode(sec_func('EMP','EMP_NAME'),1,emp_name,'x' emp_name),
decode(sec_func('EMP','EMP_SAL'),1,emp_sal,0 emp_sal)
the sec_func is passed table name and the column name and it checks the
security_level of that column againet the security level of the user
quering the table. if the security level is equal or lower, then 1 is
returned, else 0 is returned, and the decode in the view will do the rest
this way i can show/hide columns based on the security level (or roles
granted to users) ...
ok, i have only tested it, not implemented it, will it work in a real
application environment ? has anyone used this type of column privs ?
-TIA
=
On Sun, 24 Aug 2003 16:24:36 -0800, Arup Nanda [EMAIL PROTECTED]
wrote :
This is a multi-part message in MIME format.
Rahul,
I'm not sure if this is too late; but here is a strategy you could
follow
to achieve what you want. True, VPD does not have a mechanism to suppress
columns; and using a view for each user is impractical. Someday, I hope,
VPD will have that capability; but until then you could try the following.
Suppose you have a table called SAVINGS, for savings account holders as
follows:
ACCTNO NUMBER
CLEARED_BALANCENUMBER
UNCLEARED_BALANCE NUMBER
The records in the table are as follows:
ACCTNO CLEARED_BALANCE UNCLEARED_BALANCE
-- --- -
11000 1100
21100 1200
31300 1500
Cleared balance is the amount the the customer can draw from the bank.
If
there are checks outstanding, the balance is shown in uncleared. Let's
start with a simple example - you have users who are allowed to see the
uncleared balance of the customers and the others are not. Intead of
hiding
the column completely, which how VPD operates, you would want to show then
as zero, if not authorized to see that; otherwise the actual value is
shown.
You would create a context as follows:
create context sec_ctx using sec_ctx_pkg;
The trusted function can be created as:
create procedure sec_ctx_pkg
(
p_attribute_name in varchar2,
p_attribute_value in varchar2
) is
begin
dbms_session.set_context(
'sec_ctx',
p_attribute_name,
p_attribute_value);
end;
/
In the after-login trigger, you would set the context value
automatically
for user using
set_Ctx_pkg ('cleared', 'yes'); or set_Ctx_pkg ('cleared', 'no');
depending on whether the user is cleared to see the balance or not. In
real
life, you may have a table that lists all users and whether or not they
are
cleared. The after-logon trigger could read that table and set the context
attribute properly.
Next, you would craete a view.
create or replace view vw_savings
as
select acctno, cleared_balance,
decode(sys_context('sec_ctx','cleared'),'yes',
uncleared_balance, 0) uncleared_balance
from savings
/
Note: there is only ONE view, not one per user. Regardless of how many
users you have, there will be only one view.
Now to test the setup. Assume user RAHUL is allowed to see the
uncleared_balance. The after-logon trigger will set the context
attribute cleared to yes when the user logs in. When the user selects:
select * from vw_savings;
He sees:
ACCTNO CLEARED_BALANCE UNCLEARED_BALANCE
- --- -
11000 1100
21100 1200
31300 1500
Which is the correct value. Now, user ARUP logs in, who does not have
the
authority to see the uncleared balance. The logon trigger will set
Re: column level privilege - resolved
Reginald,
No, FGAC (or VPD or RLS, depending on who you ask) is not about columns;
it's about restricting rows. Within the selected rows, all the columns are
visible. Rahul wanted to mask columns without creating views for each user.
This is the only way to do that.
Hope this helps.
Arup
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, August 26, 2003 12:29 PM
Rahul:
It sounds like your implementation is very similar, in theory, to Oracle's
Fine Grain Access. Are you re-inventing the wheel?
Reginald W. Bailey
IBM Global Services - ETS SW GDSD - Database Management
Your Friendly Neighborhood DBA
713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager)
[EMAIL PROTECTED]
[EMAIL PROTECTED]
[EMAIL PROTECTED]
.co.id To: [EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: column level
privilege - resolved
ity.com
08/26/2003
10:44 AM
Please respond
to ORACLE-L
Arun, thanks for suggesting this, this is what i did.
1. created a table to store table names associated column names
and a security level for a that column
EMP emp_name 3
EMP emp_sal 5
2. then i granted every users a security level also,
3. then i created a view on the name table
select decode(sec_func('EMP','EMP_NAME'),1,emp_name,'x' emp_name),
decode(sec_func('EMP','EMP_SAL'),1,emp_sal,0 emp_sal)
the sec_func is passed table name and the column name and it checks the
security_level of that column againet the security level of the user
quering the table. if the security level is equal or lower, then 1 is
returned, else 0 is returned, and the decode in the view will do the rest
this way i can show/hide columns based on the security level (or roles
granted to users) ...
ok, i have only tested it, not implemented it, will it work in a real
application environment ? has anyone used this type of column privs ?
-TIA
=
On Sun, 24 Aug 2003 16:24:36 -0800, Arup Nanda [EMAIL PROTECTED]
wrote :
This is a multi-part message in MIME format.
Rahul,
I'm not sure if this is too late; but here is a strategy you could
follow
to achieve what you want. True, VPD does not have a mechanism to suppress
columns; and using a view for each user is impractical. Someday, I hope,
VPD will have that capability; but until then you could try the following.
Suppose you have a table called SAVINGS, for savings account holders as
follows:
ACCTNO NUMBER
CLEARED_BALANCENUMBER
UNCLEARED_BALANCE NUMBER
The records in the table are as follows:
ACCTNO CLEARED_BALANCE UNCLEARED_BALANCE
-- --- -
11000 1100
21100 1200
31300 1500
Cleared balance is the amount the the customer can draw from the bank.
If
there are checks outstanding, the balance is shown in uncleared. Let's
start with a simple example - you have users who are allowed to see the
uncleared balance of the customers and the others are not. Intead of
hiding
the column completely, which how VPD operates, you would want to show then
as zero, if not authorized to see that; otherwise the actual value is
shown.
You would create a context as follows:
create context sec_ctx using sec_ctx_pkg;
The trusted function can be created as:
create procedure sec_ctx_pkg
(
p_attribute_name in varchar2,
p_attribute_value in varchar2
) is
begin
dbms_session.set_context(
'sec_ctx',
p_attribute_name,
p_attribute_value);
end;
/
In the after-login trigger, you would set the context value
automatically
for user using
set_Ctx_pkg ('cleared', 'yes'); or set_Ctx_pkg ('cleared', 'no');
depending on whether the user is cleared to see the balance or not. In
real
life, you may have a table that lists all users and whether or not they
are
cleared. The after-logon trigger could read that table and set the context
attribute properly.
Next, you would craete a view.
create or replace view vw_savings
as
select acctno, cleared_balance,
decode(sys_context('sec_ctx','cleared'),'yes',
uncleared_balance, 0) uncleared_balance
from savings
/
Note: there is only ONE view, not one per user. Regardless of how many
users you have, there will be only one view.
Now
Re: 9iR2, grant select on a column (without using views) using RL
Rahul,
I'm not sure if this is too late; but here
is a strategy you could follow to achieve what you want. True, VPD does not have
a mechanism to suppress columns; and using a view for each user is impractical.
Someday, I hope, VPD will have that capability; but until then you could try the
following.
Suppose you have a table called SAVINGS,
for savings account holders as follows:
ACCTNO
NUMBERCLEARED_BALANCE
NUMBERUNCLEARED_BALANCE
NUMBER
The records in the table are as
follows:
ACCTNO CLEARED_BALANCE
UNCLEARED_BALANCE-- ---
-
1
1000
1100
2
1100
1200
3
1300
1500
Cleared balance is the amount the the
customer can draw from the bank. If there are checks outstanding, the balance is
shown in uncleared. Let's start with a simple example - you have users who are
allowed to see the uncleared balance of the customers and the others are not.
Intead of hiding the column completely, which how VPD operates, you would want
to show then as zero, if not authorized to see that; otherwise the actual value
is shown.
You would create a context as
follows:
create context sec_ctx using
sec_ctx_pkg;
The trusted function can be created
as:
create procedure
sec_ctx_pkg( p_attribute_name in
varchar2, p_attribute_value in varchar2)
isbegin
dbms_session.set_context(
'sec_ctx',
p_attribute_name,
p_attribute_value);end;/
Inthe after-login trigger, you would
set the context value automatically for user using
set_Ctx_pkg ('cleared', 'yes'); or
set_Ctx_pkg ('cleared', 'no'); depending on whether the user is cleared to see
the balance or not. In real life, you may have a table that lists all users and
whether or not they are cleared. The after-logon trigger could read that table
and set the context attribute properly.
Next, you would craete a view.
create or replace view
vw_savingsasselect acctno,
cleared_balance,decode(sys_context('sec_ctx','cleared'),'yes',uncleared_balance,
0) uncleared_balancefrom savings
/
Note: there is only ONE view, not one per
user. Regardless of how many users you have, there will be only one
view.
Now to test the setup. Assume user RAHUL is
allowed to see the uncleared_balance. The after-logon trigger will set the
context attribute "cleared" to "yes" when the user logs in. When the user
selects:
select * from vw_savings;
He sees:
ACCTNO CLEARED_BALANCE
UNCLEARED_BALANCE- ---
-
1
1000
1100
2
1100
1200
3
1300
1500
Which is the correct value. Now, user ARUP
logs in, who does not have the authority to see the uncleared balance. The logon
trigger will set the attribute to "no" and the same select will now
produce:
ACCTNO CLEARED_BALANCE
UNCLEARED_BALANCE-- ---
-
1
1000
0
2
1100
0
3
1300
0
Note: How the uncleared balance is 0.
This model can be extended to any column
and any number of valuesfor theattribute "cleared". You could even
specify levels of users who are allowed to see the balances under certain
amount; not above that. In case of character values; it's even simpler; just
mask it by some value such as "", or "NOT CLEARED TO SEE".
All the users are granted select privileges
on the view, not the table. The context setting procedure is owned by a secured
user; SYS would do, but you should have a separate username, say, SECUSER, for
it. In doing so, you prevent the user from setting the context
directly.
This is not VPD and not supposed to be; but
I think it will work nice for your purpose. Please let us know the devlopment at
your side.
Hope this helps.
Arup Nanda
www.proligence.com
- Original Message -
From: "rahul" [EMAIL PROTECTED]
To: "Multiple recipients of list ORACLE-L"
[EMAIL PROTECTED]
Sent: Sunday, August 24, 2003 4:34 AM
Subject: RE: 9iR2, grant select on a column
(without using views) using RL
how would i write a policy which retuns selected columns if the user
has issued select * from tab ??? using views for each
user would work, but then.. i would end up with so many views in the
main schema !!! ;-( On Sat, 23 Aug 2003 12:24:39
-0800, "Jamadagni, Rajendra" [EMAIL PROTECTED] wrote
: This message is in MIME format. Since your mail reader
does not understand this format, some or all of this message may
not be legible. Use RLS ..
Raj
--
-- Rajendra dot Jamadagni at nospamespn dot
com All Views expressed in this email are strictly
personal. QOTD: Any clod can have facts, having an opinion is an
art ! -Original
Message- Sent: Saturday, August 23, 2003 2:34 AM
To: Multiple recipients of list ORACLE-L
list, i'm ikn the process of designing security for a highly sensitive
schema for a bank, plan:
have multiple oracle users, an
Re: 9iR2, grant select on a column (without using views) using RL
NO! Never move a data dictionary table out of the SYSTEM tablespace; or do any kind of operation on them. The only tables that can be operated on - particularly DELETEs and TRUNCATEs, are AUD$ and FGA_LOG$ (in 9i). Besides, how would moving SOURCE$ table from SYSTEM tablespace help? The probelm is not a smaller tablespace size. Most of the data dictionary views are well indexed, anyway; so performance impact may be low. Something I have monkeyed around earlier is to place my own indexes on these tables, if needed. Mind you, these are not supported by Oracle; so you are on your own if something happens. In most cases, however, that may not be necessary. Hope this helps. Arup - Original Message - From: A Joshi To: Multiple recipients of list ORACLE-L Sent: Monday, August 25, 2003 2:14 PM Subject: Re: 9iR2, grant select on a column (without using views) using RL Hi, Unrelated question : If the system tablespace is so big would it not hurt performance for queries to all_tables, v$session, dba_segments etc. In such a case : can tables like source$be moved out of system tablespace and would it make sense. Thank YouVladimir Begun [EMAIL PROTECTED] wrote: Tell me about it. :)Regards,-- Vladimir BegunThe statements and opinions expressed here are my own anddo not necessarily represent those of Oracle Corporation.Tanel Poder wrote: Hi! The views are small part. There are over 15 objects in whole database, of which over 22000 are packages. System TS is about 4GB. (source$ table is 1.2GB, total of IDL_ tables is also about 1.2G). Tanel.-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Vladimir BegunINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-! Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!?Yahoo! SiteBuilder - Free, easy-to-use web site design software
Re: Hey Jared
Now that's one place M$ is way ahead of Oracle, with easy do-it-yourself "become any user" privilege! - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Monday, August 25, 2003 1:55 PM Subject: Re: Hey Jared Wasn't from me. Our virus checker here at work would stop it. From home I use Linux, so I'm not propagating virusii from there. That would require that I manually forward a message with a virus. That is also not likely, since my ISP stops those. A virus will spoof someones email address when that address is found in the address book of the infected machine, typically a PC running Outlook. As SoBig has it's own SMTP engine, this is likely what happened. Jared "Dilip Patel" [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/25/2003 10:14 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Hey JaredJared, Did you send the list/me some mail with "Your Details" as subject line. It has some .pif file as extention. Maybe that is some virus, So wanted to make sure before i open it.Thanks.From: Jared Still [EMAIL PROTECTED]Reply-To: [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]Subject: Re: Nature of Oracle-l has changedDate: Mon, 25 Aug 2003 04:39:17 -0800Here's a perfect example of an email that shouldnever have been sent.Sigh... I'll learn one of these days.This does not characterize the people I work for,as they're a pretty good bunch and actually dounderstand technology.It's more of a generalized rant fueled by pastexperiences.Should have hit 'delete' on this one instead of 'send'.JaredOn Sun, 2003-08-24 at 11:19, Jared Still wrote: Mladen, My version of the explanation of this goes back to childhood. When you were in school, just which crowd were those execs in? The 'in' crowd, the jocks, the party hounds. If like me, you were one of the 'eggheads', you didn't fit in so well with their clique, and maybe you still don't. When in school, I was told I would be more popular if I "wasn't so smart". I was even told that once as an adult. After pondering that for a bit, I decided they could all bite the green weenie if they didn't like it. This is probably how I earned my Hawkeye Pierce like cynicism, which I do work hard at keeping in check, lest it cause me more problems with the former 'in' folk that I now work for. 'They' don't like it when people are smarter than they are, and understand things they don't understand, and can't hope to understand. Hmm, this is getting a but cynical, so I guess I'll stop before I provide too much fodder for an HR type that has finally learned how to use google. Jared On Sat, 2003-08-23 at 17:19, Mladen Gogala wrote: On 2003.08.23 18:34, Tim Gorman wrote: Six years ago, a CIO commented to me, waving down a corridor which hadoffices full of developers, "If I had my way, I'd get rid of all of them andreplace them with lawyers. We'd buy applications instead of building themand then sue the vendors." My response was something along the lines of "ifyou think developers are expensive, go price some lawyers", but it certainlybounced off him. At the time, I took it as just another colorful commentfrom a colorful guy. But he was dead serious, along with his CIO/CFObrethren, and the passing of Y2K and the dot-com bubble pop has expeditedhis prediction... I always wondered where does this prejudice against us, computer geeks (my apologies to anyone offended by that _expression_, but I'm a hard core computer geek) comes from? I must say that this prejudice is very hard to understand. IT people are very well educated, very hard working, regularly willing to work long hours and sacrifice their weekends for the benefit of the company. I found that very same attitude against the "darned geeks" at several executives and managers of several companies I worked for. Even if lawyers are much more expensive the programmers, system and database administrators, application designers, they are still very willing to make the switch. I'm not quite sure why are we so hated? Why would anyone want to kill a nice and seet little wabbit? -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services -
Re: system tablespace at 50 pct_increase in 9i?
Title: Re: system tablespace at 50 pct_increase in 9i? Tim, WebIV? I must have skipped a generation; I used the OraSupport forum on CompuServe before the MetaLink. You had to subscribe to CompuServe; and it was accessible only through dial-up; so we had dial-up lines at our desk, had to buy external modems, and all that. And I thought WebIV was and still is only for Oracle Consulting and Support Staff. Arup - Original Message - From: Tim Gorman To: Multiple recipients of list ORACLE-L Sent: Monday, August 18, 2003 9:44 AM Subject: Re: system tablespace at 50 pct_increase in 9i? Good point! Another war story...Some 6 years ago, during v7.3.3 timeframe, a DBA decided to modify INITIAL, NEXT, and PCTINCREASE of everything, including stuff in SYSTEM. Unfortunately, he chose first to do this in pre-PROD (to become PROD following week). Turns out he ran into a little-known bug (arent they all, at first?) whereby any ALTER TABLE to the table named SYS.BOOTSTRAP$ causes a single bit to be set in the segment header block. This single bit being set causes ORA-00600 on instance startup.You guessed it: the night before go live, they had a junior DBA stop and restart the instance at 12:30am. Poor guy stayed up all night, I got there around 6:00am, we found the cause and convinced Oracle Support to dial in and BBED the problem into submission by 1:00pm. MetaLink didnt exist in those days we had access to MetaLinks predecessor, called WebIV...Dont change the stuff in the SYSTEM tablespace, which includes the tablespace itself. Keep foreign stuff (i.e. not belonging to SYS, SYSTEM, MDSYS, ORDSYS, OUTLN, etc) out of it and just leave it alone.on 8/18/03 4:59 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: i thought you should leave the system table space to the defaults? Ive never touched System.you really should change system to locally managed tablespaces? From: Tim Gorman [EMAIL PROTECTED] Date: 2003/08/17 Sun PM 11:19:23 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: system tablespace at 50 pct_increase in 9i? Better yet, use locally-managed SYSTEM tablespace and dispense with the issue altogether? on 8/17/03 5:39 PM, Ryan at [EMAIL PROTECTED] wrote: any idea why oracle has the system tablespace using 50 pct_increase in 9i? I k now it did that in the past, but why not set it to zero?Ryan Better yet, use locally-managed SYSTEM tablespace and dispense with the issue altogether?on 8/17/03 5:39 PM, Ryan at [EMAIL PROTECTED] wrote: any idea why oracle has the system tablespace using 50 pct_increase in 9i? I k now it did that in the past, but why not set it to zero? Ryan
Re: 32 or 64?
A bigger SGA comes to mind as one immediate benefit. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, August 18, 2003 12:14 PM From a technical and business perspective, what are the reasons to migrate from 32-bit to 64-bit Oracle? Are there known bugs/problems with one version that are not present in the other? Daniel Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda 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: Oracle 9i and connect as sys
Babette, You can still achieve the objective using SYSTEM, which does not require a SYSDBA connection. Or for that matter any user with DBA role. The trick is a new privilege named grant any object privilege; any user with that privilege can grant anything on objects owned by other schema. If you don't want to use SYSTEM for this, you could create a special user and grant only that system privilege directly to it. Hope this helps. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, August 17, 2003 9:09 PM Tim / Peter / Michael Thanks for the information. I was afraid of that. We have a patching mechanism and need to logon as sys to grant access to sys objects for part of the process. (to grant select on sys.dba_free_space and execute on sys.dbms_util). However, the patching mechanism only does a regular connect and not as sysdba--- DARN! - Will have to change automation scripts if we upgrade ... and I was hoping this would be easy to slide in :-( - Babette -Original Message- Tim Gorman Sent: Sunday, August 17, 2003 1:09 AM To: Multiple recipients of list ORACLE-L It's a 9i thing, across all platforms. on 8/16/03 9:29 PM, Babette Turner-Underwood at [EMAIL PROTECTED] wrote: I have created my first 9i database on OS/390 v2.10. On my Oracle 8i instance, I can connect to the database using: sys/[EMAIL PROTECTED] HOWEVER, In Oracle 9i, I cannot do this. I am FORCED to connect using: sys/[EMAIL PROTECTED] as sysdba I was wondering if this was a new 9i feature or if it was configurable? Or just a weird thing because of the mainframe environment. Comments please. Thanks in Advance - Babette -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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.net -- Author: Babette Turner-Underwood 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.net -- Author: Arup Nanda 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: Oracle 9i and connect as sys
A manager who has to create public synonyms himself! Ordinarily I would have had nothing but pure respect for the man who is obviously such a grease monkey; but he proved to be just a monkey. Damagement title with sys password; boy how I hate to be DBA there! No, to be _anything_ there. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, August 17, 2003 11:39 PM Babette, This is how database security unravels. Pretty soon, the password to SYS is embedded everywhere, used everywhere, and everyone knows it. Thus, the DBA ends up with the pager and responsibility for fixing stuff, but everyone else can cause that pager to go off with a stupid goof at 3:00am where they shouldn't have been able to goof up. It sounds like the patching utility only needs a couple privileges, but instead all of the goddess-like privileges of SYS are provided. Pretty soon, it seems normal for people and programs to connect as SYS on a regular basis. And so it goes... A couple alternatives: * use 9i GRANT ANY OBJECT PRIVILEGE to let another account have an incredible amount of authority, which is OK if you don't know exactly what permissions will be needed ahead of time... * grant specific permissions WITH GRANT OPTION to another user, a more focused approach than the shotgun GRANT ANY OBJECT PRIVILEGE approach, provided you know what permissions will be needed ahead of time. This has been around forever... * encapsulate such actions within a stored procedure owed by SYS, which may seem cumbersome but allows all kinds of control. Not just who can do what (which is basically what permissions and roles provide), but also during what time, from where, from what program, from what location, etc... Just this Friday, I was wrapping up an installation engagement and one of the last things we did was change all the passwords. Standard practice. Immediately, one of the development managers comes boiling out of his office screaming Who changed the passwords to SYS and SYSTEM?. I 'fessed up and asked him why he thought he needed it. He turned red and snarled that he just needed it and never you mind, turned on his heel and went in the CIO's office, then came boiling back with approval. We turned it over, and within 5 minutes I logged back onto the system and saw SQL*Plus running with the SYS/SYSTEM password visible to anyone and everyone who can run the UNIX ps command. I looked at the scripts he was running, noticed that all he wanted SYS/SYSTEM for was to create PUBLIC SYNONYMs. I left to catch my plane... Hope this helps... -Tim on 8/17/03 6:09 PM, Babette Turner-Underwood at [EMAIL PROTECTED] wrote: Tim / Peter / Michael Thanks for the information. I was afraid of that. We have a patching mechanism and need to logon as sys to grant access to sys objects for part of the process. (to grant select on sys.dba_free_space and execute on sys.dbms_util). However, the patching mechanism only does a regular connect and not as sysdba--- DARN! - Will have to change automation scripts if we upgrade ... and I was hoping this would be easy to slide in :-( - Babette -Original Message- Tim Gorman Sent: Sunday, August 17, 2003 1:09 AM To: Multiple recipients of list ORACLE-L It's a 9i thing, across all platforms. on 8/16/03 9:29 PM, Babette Turner-Underwood at [EMAIL PROTECTED] wrote: I have created my first 9i database on OS/390 v2.10. On my Oracle 8i instance, I can connect to the database using: sys/[EMAIL PROTECTED] HOWEVER, In Oracle 9i, I cannot do this. I am FORCED to connect using: sys/[EMAIL PROTECTED] as sysdba I was wondering if this was a new 9i feature or if it was configurable? Or just a weird thing because of the mainframe environment. Comments please. Thanks in Advance - Babette -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: After Logon Trigger and Import
Prasad, You can use SKIP_UNUSABLE_INDEXES=Y as an import parameter. But then again, why do that? Why not just impirt with INDEXES=N and then rebuild the indexes in parallel and with NOLOGGING? HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, August 05, 2003 1:19 PM Sorry, I forgot to mention the OS and Oracle Version. It is Hp-UX v11 and Oracle 8.1.7.4 Thanks. Best Regards, Prasad 860 843 8377 Prasada R Gunda To: [EMAIL PROTECTED] 08/05/2003 12:21 cc: PM Subject: After Logon Trigger and Import(Document link: Prasada R Gunda) Hi, I put the 'alter session set skip_unusable_indexes=true' in the logon trigger of a particular user and tested it in the sql*plus session. It is working fine there. I tested it by making an index unusable and inserting the data into the table. But, when I tried to import (using the same user) the data into that table, It gives an error saying that 'Index is in unusable state'. Does logon trigger fire for the Import? Is there any way to verify that the skip_unusable_indexes is set to 'true' for a particular session. Thanks in advance for your help. Best Regards, Prasad 860 843 8377 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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.net -- Author: Arup Nanda 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: Resend : Question about EXTPROC and vulnerability
You are welcome. Another role that has the CREATE ANY DIRECTORY system priv is the IMP_FULL_DATABASE. In addition the users of Intermedial MDSYS, CTXSYS, etc, have that privilege too. you should watch out for those roles and users. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 30, 2003 11:14 AM Thanks Arup. It is a bit clearer now. I do not grant CREATE LIBRARY privileges explicitly but these would be part of the DBA role and I've seen the DBA role being granted all to easily. My guess is that some of the seeded demo schemas in 9i also have such privileges. Here, again, I never install the demo schemas. Regards Hemant At 07:29 AM 29-07-03 -0800, you wrote: I sent a reply on that day. Here it is, once again. Date: Fri, 25 Jul 2003 12:25:59 -0400 Subject: Re: Question about EXTPROC and vulnerability Hemant, You are right in wondering why there are three steps. 1. The lsitener must not be listening for the EXTPROC connections - that is the first line of defense. 2. There is no absolute need to remove from tnsnames.ora, but good to do so as you will see later. 3. The executabe has to be removed as it could be exploited in a different manner. Note, all security alerts are based on what is known _today_; not what is possible. Just because the listener is not listening for the extproc executable does not _necessarily_ indicate that it can't be used in an attack; an enterprising hacker may find a way. If your intention is to remove extproc, you did so by removing from listener.ora; so it is just prudent to remove the last potential hole by removing extproc executable, too. After all, it not useful. Now for the other question why the alter 57 does not talk about the listener.ora security. The alerts 29 and 57 are similar, yet different. The alert 29 talks about a buffer overflow using the external process. The alert 57 is about system privileges. The system privilege, create library will alow a hacker to create a library on any filesystem that the user oracle has privileges on, INCLUDING THE ORACLE_HOME/BIN and $OH/lib! Therefore, imagine a hacker breaks in, creates a library that uses the Oracle excutables and java libraries and executes them. This is a huge hole and should be plugged by simply disallowing any user to create a library. Take for instance, a user has to create a library to create a function for some complex mathemetical calculation, e.g. finding the prime numbers, which can't be done in PL/SQL. This can be done via a C++ program and the shared object can be made availabel to ORacle using a lbrary as: create library prime_num_lib as '/usr/ananda/lib/prime_num_lib.so'; When a user uses this library, the EXTPROC process will run the .so file on the user's behalf. Fair enough; what's wrong with that? What is the user (the hacker) creates a library to point to some .so file in $OH/lib directory? You get the picture what might happen. Another variation of the create library is create library prime_num_lib as '/usr/ananda/lib/prime_num_lib.so' AGENT 'dblink1' Here the Oracle server process uses the dblink to connect to another server's EXTPROC process to executes its task. Instead of using a dblink to another server, it may actually connect to the extproc of the same server using the connect string defined in the tnsnames.ora. It may not exist; but what if the hacker actually copied the exeutable to a different name, seemingly harmless. Removing extproc from tnsnames.ora wil lplug that hole too. BEsides, it is a good practice to remove it since the presence indicates the usage (albeit in the past) and may give a potential hacker a clue. Remember, securing is not just plugging the most obvious holes; but all potential ones. The alerts point that out. Another thing of note here is to plug a seprate potential problem - removing the CREATE ANY DIRECTORY privilege. This provilege creates a directory on any filesystem accessible by oracle user. Do not grant any one this privilege; and be very cautious while granting CREATE DIRECTORY privilege, too. HTH. Arup Nanda www.proligence.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 29, 2003 10:59 AM Resending this email, hoping for a reply this time. Date: Fri, 25 Jul 2003 07:49:24 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] X-Comment: Oracle RDBMS Community Forum X-Sender: Hemant K Chitale [EMAIL PROTECTED] Sender: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] From: Hemant K Chitale [EMAIL PROTECTED] Subject: Question about EXTPROC and vulnerability Organization: Fat City Network Services, San Diego, California Oracle's Security Alert #29 [Note 175429.1] on the EXTPROC recommends the workaround to disable EXTPROC as 1. Removing
Re: Resend : Question about EXTPROC and vulnerability
I sent a reply on that day. Here it is, once again. Date: Fri, 25 Jul 2003 12:25:59 -0400 Subject: Re: Question about EXTPROC and vulnerability Hemant, You are right in wondering why there are three steps. 1. The lsitener must not be listening for the EXTPROC connections - that is the first line of defense. 2. There is no absolute need to remove from tnsnames.ora, but good to do so as you will see later. 3. The executabe has to be removed as it could be exploited in a different manner. Note, all security alerts are based on what is known _today_; not what is possible. Just because the listener is not listening for the extproc executable does not _necessarily_ indicate that it can't be used in an attack; an enterprising hacker may find a way. If your intention is to remove extproc, you did so by removing from listener.ora; so it is just prudent to remove the last potential hole by removing extproc executable, too. After all, it not useful. Now for the other question why the alter 57 does not talk about the listener.ora security. The alerts 29 and 57 are similar, yet different. The alert 29 talks about a buffer overflow using the external process. The alert 57 is about system privileges. The system privilege, create library will alow a hacker to create a library on any filesystem that the user oracle has privileges on, INCLUDING THE ORACLE_HOME/BIN and $OH/lib! Therefore, imagine a hacker breaks in, creates a library that uses the Oracle excutables and java libraries and executes them. This is a huge hole and should be plugged by simply disallowing any user to create a library. Take for instance, a user has to create a library to create a function for some complex mathemetical calculation, e.g. finding the prime numbers, which can't be done in PL/SQL. This can be done via a C++ program and the shared object can be made availabel to ORacle using a lbrary as: create library prime_num_lib as '/usr/ananda/lib/prime_num_lib.so'; When a user uses this library, the EXTPROC process will run the .so file on the user's behalf. Fair enough; what's wrong with that? What is the user (the hacker) creates a library to point to some .so file in $OH/lib directory? You get the picture what might happen. Another variation of the create library is create library prime_num_lib as '/usr/ananda/lib/prime_num_lib.so' AGENT 'dblink1' Here the Oracle server process uses the dblink to connect to another server's EXTPROC process to executes its task. Instead of using a dblink to another server, it may actually connect to the extproc of the same server using the connect string defined in the tnsnames.ora. It may not exist; but what if the hacker actually copied the exeutable to a different name, seemingly harmless. Removing extproc from tnsnames.ora wil lplug that hole too. BEsides, it is a good practice to remove it since the presence indicates the usage (albeit in the past) and may give a potential hacker a clue. Remember, securing is not just plugging the most obvious holes; but all potential ones. The alerts point that out. Another thing of note here is to plug a seprate potential problem - removing the CREATE ANY DIRECTORY privilege. This provilege creates a directory on any filesystem accessible by oracle user. Do not grant any one this privilege; and be very cautious while granting CREATE DIRECTORY privilege, too. HTH. Arup Nanda www.proligence.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 29, 2003 10:59 AM Resending this email, hoping for a reply this time. Date: Fri, 25 Jul 2003 07:49:24 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] X-Comment: Oracle RDBMS Community Forum X-Sender: Hemant K Chitale [EMAIL PROTECTED] Sender: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] From: Hemant K Chitale [EMAIL PROTECTED] Subject: Question about EXTPROC and vulnerability Organization: Fat City Network Services, San Diego, California Oracle's Security Alert #29 [Note 175429.1] on the EXTPROC recommends the workaround to disable EXTPROC as 1. Removing the entry for extproc/PLSExtproc/icache_extproc from the listener.ora 2. Removing the entry from the tnsnames.ora 3. Renaming or removing the extproc executable Why should all three actions be necessary ? Why not just removing the entry from the listener.ora ? Can extproc be called without the listener configured ? Security Alert #57 just talks of the CREATE LIBRARY privilege and makes no mention of updating the listener.ora or tnsnames.ora or removing/renaming the extproc executable. Why ? Is it that Oracle wants people to use EXTPROC [or makes use of EXTPROC itself] so it does not specify how EXTPROC can be disabled ? Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author
Re: Please look at this imp command
Two things to check immediately: (1) Is the Oracle service defined as started automatically in the services? (2) If yes, is there any error reported in alert log of the database? Import does not shut the database down. HTH Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 29, 2003 11:04 AM imp tpocs/[EMAIL PROTECTED] file=c:\Latest_Reference_Files\pharm_jul_2003.dmp log= c:\Latest_Reference_Files\pharm_jul_2003.log fromuser=tpocs touser=tpocs tables=arc_ndc commit=y ignore=y buffer=8388608 Scenario: I ran this command (as instructed by the tier III tech), got a couple of warnings but it did import 28K plus rows into arc_ndc. Before I ran the command at the command NT command level, I truncated arc_ndc via SQL*Plus. After the command completed I rebooted the server, there was no direction to do this, I just did it for good measure figuring I would get a clean oracle instance going. When the server finished the reboot, oracle wasn't up -- or I should say the database wasn't opened. I had to open the database as SYSDBA. Did the imp command close the database? I don't see any directive to do so, but being new maybe there is something I don't understand. Thanks for your help and patience. v/r Stephen S. Wolfe, GS-11, DAFC Data Services Manager [EMAIL PROTECTED] (813) 827-9974 DSN 651-9974 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfe Stephen S GS-11 6 MDSS/SGSI 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.net -- Author: Arup Nanda 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: Set Role in Trigger
Jack, Question - why do you want to use System triggers to change roles? I don't see how you can define an event for this trigger to be fired. If the event is the updating of record in USERS table, who updates it? Obviously not the user himeself; then the whole system becomes insecure to the core. Your choice of using a role to allow access to a specific yesr is sound design. I assume each year is stored in separate tables. If that is not correct, i.e. the record for all years are stored in a single table, then roles will not help you, you need to enable FGAC using something called application context. Scenario 1: Each year's data is stored in a seprate table. You would create roles, but not normal roles. The roles should be identified by a procedure. This procedure is owned by SYS or some other secued schema. Inside this procedure you would assign a value to the application context attribute called user_role, which is set via dbms_Session.set_context. All the users are revoked execute priv on dbms_session; so they will never be able to call this procedure directly. The only way they can do it is by calling the trusted procedure you have defined for that role. Scenario 2: All the tables have data for all the years. Inthis case you will have to use FGAC; but the FGAC policy will have to depend on the application context you defined earlier. You wil define another context attribute called school_year, whic is again set by the trusted procedure of the role. Since the user does not have the privilege to call dbms_Session, he will not be able to set the value of this attrbute to any other year at will. Summary: You will define several roles ideintified by procedure. All these roles are granted to the user but none is a default role. When a user logs in, all roles assigned to him are disabled, since none is a default role. Then he calls the procedure set_role(), no arguments. Inside the procedure set_role(), you will read the users table, see the role the user is supposed to have, enable this role via dbms_Session.set_role and then set the application context, if any. Since the user does not have execute privs on dbms_session, he will not be able to set the app context. Since the role is identified by a procedure, i.e. set_role(), the user will not be able to set the role himself using "SET ROLE" command in sql*plus. The only way he can do that is by calling the set_role procedure. If the user does not call the procedure, none of the roles are enabled; therefore he will not be able to do anything. In other words, you have a secured system. As an added bonus: you will have the application context attribute you can use any way you want to use. Right now you can use it for FGAC; but later when you are in 9i, you can use it in other cooler features such as Fine Grained Auditing. HTH. Arup Nanda www.proligence.com - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Sunday, July 27, 2003 11:59 AM Subject: Set Role in Trigger Short form of my question: How can I enable a Role for a User within a database trigger (owned by another Schema) on a table owned by yet another Schema? - The M's I've RTF'd indicate that a trigger (and any procedure it calls) can never execute with Invoker's Rights. - I can't find a way to execute Set Role for a User as another User, say, System. - I'm stuck. - Environment: 8.1.7 on Win2k and HP-UX. Longer form of my question: I'm in the process of adding extra security features to our 3rd Party Student Information System, whose code I can't touch. I've successfully implemented FGAC to keep Users at a School from accessing info.at other schools. Now I need to limit which School Year's data they can update (Past, Current, Next). The Application grants Sel, Ins, Upd, Del on its tables via a Role, so I thought I'd just switch Roles when the User switched School Years (via updating her record in a Users table). Seemed like a good idea, but now I can't see how to implement it. SQL and PL/SQL commands like Set Role, Alter Session, DBMS_Session.Set_Role, etc. only apply to the current User, which would be the Trigger Owner. I've used DBMS_System.Set_SQL_Trace_In_Session, but can't find an equivalent procedure to Set Role for another User. BTW, the fact that there's no Invoker_Rights_Clause in the Create Trigger syntax and a section in the PL/SQL User's Guide and Reference (Ch. 7 Subprograms / Invoker Rights vs Definer Rights / Using Views and Database Triggers) are the basis for my being stuck. The only possible way I see to do this is to create the trigger as System, then use Dynamic SQL to issue the "Alter User ... Default Role " command. However, I don't know if that takes effect immediately (within the User's current Session) or would take effect at the User's n
Re: Set Role in Trigger
Technically possible, but prpbably not practical. You have to create several views for each of the users; possible - but may become unmaintainable. FGAC may be a btter maintanable option, IMHO. Arup Nanda www.proligence.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, July 27, 2003 2:44 PM This is probably too kludgy or simple-minded, or non-maintainable, but is it technically possible? 1) Create a series of views that subset the actual tables, according to the rules you've got about who the viewer is what year(s) they've selected in the Users table. 2) Redefine the public synonyms so that they point to your views rather than the base tables. Cheers, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Sunday, July 27, 2003 11:24 AM To: Multiple recipients of list ORACLE-L Jack, Question - why do you want to use System triggers to change roles? I don't see how you can define an event for this trigger to be fired. If the event is the updating of record in USERS table, who updates it? Obviously not the user himeself; then the whole system becomes insecure to the core. Your choice of using a role to allow access to a specific yesr is sound design. I assume each year is stored in separate tables. If that is not correct, i.e. the record for all years are stored in a single table, then roles will not help you, you need to enable FGAC using something called application context. Scenario 1: Each year's data is stored in a seprate table. You would create roles, but not normal roles. The roles should be identified by a procedure. This procedure is owned by SYS or some other secued schema. Inside this procedure you would assign a value to the application context attribute called user_role, which is set via dbms_Session.set_context. All the users are revoked execute priv on dbms_session; so they will never be able to call this procedure directly. The only way they can do it is by calling the trusted procedure you have defined for that role. Scenario 2: All the tables have data for all the years. Inthis case you will have to use FGAC; but the FGAC policy will have to depend on the application context you defined earlier. You wil define another context attribute called school_year, whic is again set by the trusted procedure of the role. Since the user does not have the privilege to call dbms_Session, he will not be able to set the value of this attrbute to any other year at will. Summary: You will define several roles ideintified by procedure. All these roles are granted to the user but none is a default role. When a user logs in, all roles assigned to him are disabled, since none is a default role. Then he calls the procedure set_role(), no arguments. Inside the procedure set_role(), you will read the users table, see the role the user is supposed to have, enable this role via dbms_Session.set_role and then set the application context, if any. Since the user does not have execute privs on dbms_session, he will not be able to set the app context. Since the role is identified by a procedure, i.e. set_role(), the user will not be able to set the role himself using SET ROLE command in sql*plus. The only way he can do that is by calling the set_role procedure. If the user does not call the procedure, none of the roles are enabled; therefore he will not be able to do anything. In other words, you have a secured system. As an added bonus: you will have the application context attribute you can use any way you want to use. Right now you can use it for FGAC; but later when you are in 9i, you can use it in other cooler features such as Fine Grained Auditing. HTH. Arup Nanda www.proligence.com - Original Message - To: Multiple recipients of list ORACLE-L Sent: Sunday, July 27, 2003 11:59 AM Short form of my question: How can I enable a Role for a User within a database trigger (owned by another Schema) on a table owned by yet another Schema? - The M's I've RTF'd indicate that a trigger (and any procedure it calls) can never execute with Invoker's Rights. - I can't find a way to execute Set Role for a User as another User, say, System. - I'm stuck. - Environment: 8.1.7 on Win2k and HP-UX. Longer form of my question: I'm in the process of adding extra security features to our 3rd Party Student Information System, whose code I can't touch. I've successfully implemented FGAC to keep Users at a School from accessing info.at other schools. Now I need to limit which School Year's data they can update (Past, Current, Next). The Application grants Sel, Ins, Upd, Del on its tables via a Role, so I thought I'd just switch Roles when the User switched School Years (via updating her record in a Users table). Seemed like a good idea, but now I can't see how to implement it. SQL and PL/SQL commands like Set Role
Re: Set Role in Trigger
I am not an expert on Oracle Apps, but those session environment variables are probably application context attributes I mentioned earlier. They can also be implemented by a package global variable; but there is no security in that; the user will be able to set the variable in anyway he wants. Yes, it is better from the performance point, too. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, July 27, 2003 4:19 PM Hi! This is probably too kludgy or simple-minded, or non-maintainable, but is it technically possible? 1) Create a series of views that subset the actual tables, according to the rules you've got about who the viewer is what year(s) they've selected in the Users table. 2) Redefine the public synonyms so that they point to your views rather than the base tables. Oracle Apps actually works that way, that a user gets assigned an organization id org_id when he logs on (not using trigger, from client side instead) and uses views which restrict queries dml by org_id. This is based on session environment variables, I believe it's better in performance point of view, if we would have to scan a privileges table during every select on any table, it could become the bottleneck... Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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.net -- Author: Arup Nanda 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: Set Role in Trigger
Jack, After theclarification, my recommendation actually holds more ground - I hope you can see that in the explanation here. Creating views are not practical due to the number of users and year combinations involved. Since you are using FGAC, that is precisely you should do. you wrote Actually, you should review the docs on Application Context. No User can set his Application Context Variables by calling DBMS_Session.Set_Context, since each Application Context is "registered" to its specific package, which should be inaccessible to the User. /you wrote I am aware of that; in fact that is what makes this process more secure. I am writing a book to be published inSeptember that describes exactly how this is done; asa matter of fact, the example described mimics your case. Unfortunately as this stage I will violate the contract with the publisher if I divulge too much details; so I have to be succinct! I am assuming you have 10 years of data in table such as year1, year2, etc. When a user logins, he may be given a role year1, which has select privileges on year1 only. 1. create a procedure set_role (p_user_id). this procedure selects from some table that stores the username and role allocated. from the role allocated, it calls dbms_session.set_role (p_role_name); you can also specify dbms_session.set_context 2. create a role year1 identified by set_role. Note "identified by set_role"; this is important. Similarly create all roles year2, year3, etc. 3. grant select on year1 to year1, etc. 4. create the user myuser 5.grant year1, year2 to myuser. 6. alter user myuser default role none; 7. create a procedure validate_me (p_username, p_password) that returns only YES or NO based on the username/password combination. Inside this procedure call the set_role and set_context procs. 8. When the user calls this validate_me procedure, the application context as well as role can be set. I wish itwere more descriptive; but .. you wrote Using an Application Context Variable won't work, since the App. Ctx. Var. User_Role is not really a Role to which I could grant object privileges /you wrote the app ctx attr value is simply for therole name. The actual role setting is done by set_role procedure. The only thing I don't understand is why this has to be called from a DML trigger on table. Which table's trigger will trigger this role setting event; could you elaborate on that? I really HTH. Arup Nanda www.proligence.com - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Sunday, July 27, 2003 8:59 PM Subject: Re: Set Role in Trigger Arup, Thanks for your reply. As I said in my memo, I really want the User's Role to be changed from within a Trigger on a table, not a System Trigger. Actually, it doesn't matter, since no database trigger can be defined with Invoker's Rights. That means that any trigger in which I issue execute DBMS_Session.Set_Role would fail, since Roles are disabled in any PL/SQL module executed with Definer's Rights - which is all triggers. Yes, this App. updates the User table when the User chooses to change to a different set of Schoolyear tables, but it creates no system insecurity. Why would it? Using an Applicatioin Context Variable won't work, since the App. Ctx. Var. User_Role is not really a Role to which I could grant object privileges. My situation is your Scenario #1. I understand Application Context and am using it in the current FGAC implementation I've created for this app. However, I can't see how I can substitute an App. Ctx. Var. for a "real" Role. Actually, you should review the docs on Application Context. No User can set his Application Context Variables by calling DBMS_Session.Set_Context, since each Application Context is "registered" to its specific package, which should be inaccessible to the User. Roy suggested views, which might be an option for a well-designed, low user-count app, but this app. (remember, 3rd Party, we didn't design it!) has over 50,000 tables. Also, we have several thousand teachers and administrators who access the system. Multiply the two and you can see that millions of views are not feasible. I've really got to find a way to change a User's "real" Role from within a Trigger. It can't be the After_Logon Trigger, since the User's Role must be changeable up to many times during a single Session. Also, remember that I can't make a single modification to the Application's code. Unless someone comes up with something else, I'll experiment with having the table Trigger owned by System and executing "Alter User... Default Role..." as a Dynamic SQL statement to see if that works to change the User's Role. Thanks.Jack C. ApplewhiteDatabase AdministratorAustin Independent School DistrictAustin, T
Re: domain vs local account
Joe, You can certainly use the doamin account; we do it all the time. I am not sure why the TAR said this - but I can take a guess - generally the domain users are not privileged enough. Oracle recommends using the Administrator account to install anything for that reason. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 22, 2003 11:04 AM During a recent tar I was told by an Oracle tech that Oracle windows installs done by a domain user (versus local user) would not be supported by Oracle. We used the domain acct (which is also an administrator on the machine)so that it could see the tns_admin directory on the network. We have not had any problems with the software as a result. The box is w2k, Oracle 8.1.7.4. Do most people use local accts? Are there any other reasons for using/not using a domain acct? Thanks. Joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joe Armstrong-Champ 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.net -- Author: Arup Nanda 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: possible to set continuation prompt in sql*plus?
Sure, just set the following (assuming 9i) SET SQLNUMBER OFF HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 22, 2003 12:44 PM Greetings all, I've set up my login.sql so that my prompt shows my username the sid of the db to which I'm connected. This works well, but has made it tough to drag-select text b/c now the first line sticks way out relative to the continuation lines, like so: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; Is it possible to specify the prompt that shows on continuation lines (or otherwise pad it out) in sql*plus? Thanks! -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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.net -- Author: Arup Nanda 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: possible to set continuation prompt in sql*plus?
I suggested using SET SQLNUMBER OFF. This sure works in SQL*Plus 8i and above; not sure if it does in 8.0.6 and I don't have a test executable to test it. But have you tried it? Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 22, 2003 3:14 PM That's right--I am looking for an analogue to PSx. I've been playing around w/sqlcontinue sqlnumber but so far no joy. I'm using sql*plus 8.0.6.0.0 (running against an 8.1.6 db). Many thanks to all who responded. Cheers, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Tuesday, July 22, 2003 11:15 AM To: Multiple recipients of list ORACLE-L I think so too. I think sqlcontinue and sqlnumber is what this guy is looking for. -Original Message- Sent: Tuesday, July 22, 2003 1:50 PM To: Multiple recipients of list ORACLE-L I got the impression that the question is if sql plus has the equivalent of the Unix PS1, PS2, PS3, PS4 prompts. -Original Message- The command is set sqlprompt. -Original Message- Greetings all, I've set up my login.sql so that my prompt shows my username the sid of the db to which I'm connected. This works well, but has made it tough to drag-select text b/c now the first line sticks way out relative to the continuation lines, like so: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; Is it possible to specify the prompt that shows on continuation lines (or otherwise pad it out) in sql*plus? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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.net -- Author: Richard Ji 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.net -- Author: Pardee, Roy E 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.net -- Author: Arup Nanda 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: possible to set continuation prompt in sql*plus?
Setting SQLNUMBER OFF will make the SQLPROMPT continuation prompt, not completely eliminate it. Isn't it what the OP wanted in the first place? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 22, 2003 3:59 PM sqlcontinue changes the prompt for the continuation of a SQL*Plus command, not a SQL command. sqlnumber off will mean that instead of having number prompts on the continuation of a SQL statement the SQL prompt will be continued (which I personally find annoying). set sqlnumber off should eliminate the problem of the indented first line, but you won't get a continuation prompt at all. Example (using SQL*Plus 8.1.7): SQL -- continuation of a SQL*Plus command. SQL prompt - Hello World Hello World SQL set sqlcontinue Next SQL prompt - Next Hello World Hello World SQL -- +++ SQL -- continuation of a SQL command SQL select * 2 from dual where 1 = 2 ; aucune ligne sélectionnée SQL set sqlnumber off SQL select * SQL from dual where 1 = 2 ; aucune ligne sélectionnée -Original Message- From: Arup Nanda [mailto:[EMAIL PROTECTED] Sent: mardi, 22. juillet 2003 12:44 To: Multiple recipients of list ORACLE-L Subject: Re: possible to set continuation prompt in sql*plus? I suggested using SET SQLNUMBER OFF. This sure works in SQL*Plus 8i and above; not sure if it does in 8.0.6 and I don't have a test executable to test it. But have you tried it? Arup Nanda - Original Message - That's right--I am looking for an analogue to PSx. I've been playing around w/sqlcontinue sqlnumber but so far no joy. I'm using sql*plus 8.0.6.0.0 (running against an 8.1.6 db). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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.net -- Author: Arup Nanda 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 make SPFILE in sync with INIT.ORA ?
Well, I thought I answered your question earlier. Here are the steps, in more detail. I assume your database instance name is ORCL; replace with the actual. (1) With your database open, issue a command as sys SQL create pfile from spfile. (2) Go to the directory $ORACLE_HOME/dbs and make sue the timestamp of teh file initORCL.ora file is now. (3) Open the file and place the line audit_trail=db, if not already there. (4) Shutdown the database. (5) Restart SQL startup pfile=$ORACLE_HOME/dbs/initORCL.ora (6) Make sure the audit trail is set. SQL show parameter audit_trail. (7) resynch the SPfile. SQL create spfile from pfile; (8) And, please let us know here if they worked. If any error occurs, copy and paste the error here. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, July 17, 2003 11:19 AM Hi, I really need some helps to configure my database with audit_trail option. I have tried some but fail so far. My database is Oracle 9.2. I have read all email related to spfile. Can somebody provides simple steps for me to update my specified initialization parameter in init.ora? (1) Manually change the initialization parameter, e.g. audit_trail = db in init.ora. How to sync it when re-start Oracle database? (2) Can I change the audit_trail = db initialization parameters in init.ora on-line? Any comments are appreciated! Many thanks! Don Arup Nanda wrote: The ability to change the system parameters without bouncing the system is not provided by spfile, neither in RAC nor single instance databas. It depends upon the the parameter that can be changed dynamically or not. I guess you wanted to convey the impression that the using spfiles the parameters can be changed and the changes can be persistent across shutdowns. But that is not just in RAC; it's true for single instance DBs, too. Now, suppose you want to set a parameter that can't be changed using ALTER SYSTEM, such as, say, java_pool_size. How do you plan to make the change? You have to open up the old favorite init.ora file and start the database with pfile=init.ora option. At that stage the spfile is not active and your issuing Alter system set db_cache_size = 800m scope = [ memory | spfile | both ] sid = * has no effect. You must create the spfile from the pfile and then use the newly created spfile to use this dynamic parameter persistent. Note the complexity involved - spfile allowed you to make the changes to some parmeters using alter system persistent; but for all other parametrs you are forced to use pfile. What happens if you ommit the pfile=init.ora clause? The database will pickup the spfile, which will _not_ have your changes. To fully appreciate the value of the spfile parameter, Oracle should have allowed editing spfile directly and completely done away with pfile. Splitting functionality across two different implementations adds to difficulties, does not resolve them. I hope future versions of Oracle do offer that functionality. It's not that difficult or unheard of, either. Listener.ora, tnsnames.ora are all editable and also read by Net Assistant. just my .02 Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 8:09 PM Spfile is shared and can be modified dynamically without bouncing the instances. Eg. Alter system set db_cache_size = 800m scope = [ memory | spfile | both ] sid = * Without spfile, you can still make this change dynamically in memory, but you would have to manually update init.ora file to reflect your change. Spfile allows you to automatically capture these dynamic changes. Gerardo -Original Message- Sent: Tuesday, July 15, 2003 4:45 PM To: Multiple recipients of list ORACLE-L And how, exactly? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 7:29 PM You'll appreciate spfiles if you're using RAC. -Original Message- Sent: Tuesday, July 15, 2003 2:09 PM To: Multiple recipients of list ORACLE-L And create pfile from spfile; does a similar job. I'm rapidly beginning to think that spfiles are just not worth it. Now how to reverse the policy decision having mandated them for 9i installs :( Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Arup Nanda Sent: 15 July 2003 04:44 To: Multiple recipients of list ORACLE-L Subject: Re: How to make SPFILE in sync with INIT.ORA ? As user sys, issue CREATE SPFILE FROM PFILE; This will create the spfile. You must have started the database using the pfile to use this command. HTH. Arup Nanda
Re: Security re: default and non-default roles
Leslie, First make sure they are indeed the default roles. select * from dba_role_privs where grantee = 'SCOTT' See the DEFAULT_ROLE column; it should be NO. Next, after logging in, before SET ROLE, issue select * from session_roles; to see the roles that are active now. Should be only the DEFAULT roles. If you see more, it's possible that one of those roles have default roles. For instance if DBA is a default role, select * from dba_role_privs where grantee = 'DBA'; See if any of these are default roles. I suspect you have a second level role as default which is different from Development. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, July 17, 2003 10:24 AM In an attempt to enhance security, we set up one role which is a default and has only select privileges on tables and another role, with is not default, which has update, insert, delete, as well as select privileges. Our application turns on the non-default role via set role. This all works just fine in our development database, but not in the test database (or production). The symptom is that the non-default role is enabled on login to sql*plus, even when it's not supposed to be. Can anybody give me a clue as to where to look to find out what's wrong and fix it? TIA, Leslie -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tierstein, Leslie 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.net -- Author: Arup Nanda 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: Here comes Oracle10G
How about G for Greedy! - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, July 17, 2003 3:44 PM OracleWorld web sites SFO/PARIS revealing the new name as: Oracle10G Gee, it's not 'i' anymore ? Nope!! It's Capital G. G for God? No. It's Grid :) Enjoy.. - Kirti __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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.net -- Author: Arup Nanda 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: Insert 8000 Byte into LONG column
Try using sql*loader. Put the input data string in a single file as the input file. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 16, 2003 9:54 AM Hi dear list members, we run a database 8.1.6.2 I've run into a problem. I've got a table that contains a LONG column (I know, I know). I have to insert a string into it that has a length 8500 Byte. Whenever I issue the insert statement SQL/PLUS throws the error 'ORA-01704: string literal too long'. Does anyone have an idea how I can get the data into the table? Any help (except: change the datatype ;-)) is appreciated. Greetings, Guido -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guido Konsolke 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.net -- Author: Arup Nanda 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).
