Jump to content

richchen65

Members
  • Content count

    48
  • Joined

  • Last visited

Community Reputation

0 Neutral

About richchen65

  • Rank
    Advanced Member
  • Birthday 10/17/1965

Profile Information

  • Gender
    Male
  • Location
    IL 60559
  1. Dear all, Recently we were asked to find out a way to prevent Oracle db users to drop their own tables. AFAIK, Oracle users can always drop their own tables by default. Possible workaround is to create a trigger to raise error to prevent dropping a table by the user and the code works fine. CREATE or replace TRIGGER rich.trigger_prevent_drop BEFORE DROP ON rich.SCHEMA DECLARE v_username varchar2(30); BEGIN SELECT user INTO v_username FROM dual; IF ora_dict_obj_type = 'TABLE' AND ora_dict_obj_owner = v_username AND ora_login_user = v_username THEN raise_application_error (-20000, 'YOU CAN NOT DROP YOUR OWN TABLE!'); END IF; END; HOWEVER, Orale users can easily drop the trigger. (PS: The code can not prevent oracle users to drop their triggers.) CREATE or replace TRIGGER rich.prevent_drop_trigger BEFORE DROP ON rich.SCHEMA DECLARE v_username varchar2(30); BEGIN SELECT user INTO v_username FROM dual; IF ora_dict_obj_type = 'TRIGGER' AND ora_dict_obj_owner = v_username AND ora_login_user = v_username THEN raise_application_error (-20000, 'YOU CAN NOT DROP YOUR OWN TRIGGER!'); END IF; END; Is there any way i can prevent a oracle user from dropping triggers even if it is the owner ? Rich
  2. Dear Gurus, I tried to install Oracle 11gR2 Grid Instrastructure with Advanced installation Option and got error at Step6 (Specify Network Interface Usage) [iNS-40927] Interfaces with common subnets that have different names on different nodes. I think something wrong with the 2 nodes network setting. Not sure it is caused by incorrect network interface or IP setting. The following information is the setting for the 2 nodes: ----- 127.0.0.1 localhost #Public 10.6.201.138 r1 10.6.201.139 r2 #Private 10.6.201.140 r1-priv 10.6.201.141 r2-priv #Virtual IP 10.6.201.142 r1-vip 10.6.201.143 r2-vip # SCAN 10.6.201.144 r-cluster-scan 10.6.201.145 r-cluster-scan The Network Information: r1: [r1:root]# ifconfig -a lo0: flags=2001000849<UP,LOOPBACK,RUNNING,MULTICAST,IPv4,VIRTUAL> mtu 8232 index 1 inet 127.0.0.1 netmask ff000000 igb1: flags=1000843<UP,BROADCAST,RUNNING,MULTICAST,IPv4> mtu 1500 index 2 inet 10.6.201.138 netmask ffffff00 broadcast 10.6.201.255 ether a0:36:9f:43:7c:55 igb1:1: flags=1000843<UP,BROADCAST,RUNNING,MULTICAST,IPv4> mtu 1500 index 2 inet 10.6.201.140 netmask ffffff00 broadcast 10.6.201.255 r2: [r2:root]# ifconfig -a lo0: flags=2001000849<UP,LOOPBACK,RUNNING,MULTICAST,IPv4,VIRTUAL> mtu 8232 index 1 inet 127.0.0.1 netmask ff000000 igb2: flags=1000843<UP,BROADCAST,RUNNING,MULTICAST,IPv4> mtu 1500 index 2 inet 10.6.201.139 netmask ffffff00 broadcast 10.6.201.255 ether a0:36:9f:43:7c:56 igb2:1: flags=1000843<UP,BROADCAST,RUNNING,MULTICAST,IPv4> mtu 1500 index 2 inet 10.6.201.141 netmask ffffff00 broadcast 10.6.201.255 ----- Any idea ? Rich
  3. Hi, I think GOOGLE or WIKI can answer your question. BTW, Please Check ISO-8859 Codepage layout from http://en.wikipedia.org/wiki/ISO/IEC_8859-1 Rich
  4. Hi, The Hex value of "Bullet symbol" is B7 in ISO-8859-1. The Hex value of "Bullet symbol" is 25cf in UTF8. Which one is the hex value of "bullet symbol" you want ? Just like other characters , "bullet symbol" can have different hex values in different code pages. The method I suggested can help you to store any string data into Oracle DB as what is should be. But you have to know how to display correctly. Please keep in mind: Storing characters in Oracle DB without converting is one thing, displaying them correctly is another. Rich
  5. Hi, I think you miss my point. You should find out the ACTUAL HEX VALUE of the string you want to insert. No way the two special characters will be the same in HEX. I think something wrong with your environment /tool . If so, try some other ways to find the hex value like check code table in CONTROL PANEL. Rich
  6. Hi, You can insert any special characters into your Oracle DB with any character set : (1) Using RAWTOHEX() function to find out the Hex code of the string: select rawtohex('<whaterver you can type in>') from dual; (2) insert Oracle DB with the following command: say the Hex value is 'F5A0FFAA' insert into TEST_TABLE values (utl_raw.cast_to_varchar2( 'F5A0FFAA' )); COMMIT; Hope this helps, Rich
  7. richchen65

    Running extproc

    Hi, Try to change the command: oracmd.exec('dir'); to oracmd.exec('cmd.exe /c dir > c:\junk.txt'); To check if c:\junk.txt can be created successfully. Rich
  8. richchen65

    Running extproc

    Hi, The following steps were what I did. It works in my site. hope it helps. --------------------------------------------------------------------------------------------- 1. Compile a DLL file .. (I assume the DLL is well-compiled.) 2. Check the exported functions of DLL can be loaded by Oracle DB (or ask who wrote the DLL) E:>dumpbin /exports actnoMask.dll (VS 2008 ) where actnoMask.dll is the DLL. 3. Listener.ora add : .... (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = D:\oradata\oracle10) (PROGRAM = extproc) (ENVS="EXTPROC_DLLS=E:\dlltest\actnoMask.dll") .. ... then restart. where E:\dlltest\actnoMask.dll is the DLL path/filename. tnsnames.ora add: .... EXTPROC_CONNECTION = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO)) ) ... 4. Create library/external functions: SQL> create or replace library actnomask as 'E:\dlltest\actnoMask.dll; SQL> --- GenID and ReverseID are the exported functions of DLL actnoMask.dll SQL> --- GenACTNO and RevACTNO are the function name used in Oracle DB SQL> CREATE OR REPLACE FUNCTION GenACTNO (x char) RETURN CHAR AS LANGUAGE C LIBRARY actnomask NAME "GenID"; SQL> CREATE OR REPLACE FUNCTION RevACTNO(x char) RETURN CHAR AS LANGUAGE C LIBRARY actnomask NAME "ReverseID"; --------------------------------------------------------------------------------------------- Then you are good to use the functions provided by DLL. Rich
  9. richchen65

    Improve insert and update statement using parallel

    Hi, Check this one : Create a new table test_bis with CTAS : create table test_bis as select id, TO_CLOB(key) key from test .. Rich
  10. richchen65

    Oracle RAC for Datawarehouse

    Hi, Datawarehouse DBs usually serve internal users and allow reasonable down time. If that is the case in your site, RAC is not a necessity. Rich
  11. richchen65

    11gR2 RAC Questions

    Dear all, I hope gurus here can help me to clear some confusions regarding 11gR2 RAC environment. (1) Can I use the same owner (ie oracle) for both the Oracle Grid Infrastructure installation and the Oracle RAC installation ? Like : ORACLE_BASE=/oracle/11.2 GRID_HOME=/oracle/11.2/grid ## Grid Infrastructure binary ORACLE_HOME=/oracle/11.2/db ## Oracle RAC binary (2) Whats the difference between CSR_HOME and GRID_HOME ?? (3) If not using ASM in RAC environment, should I still need to create "storage administration role" (i.e. SYSASM/OSASM..) ? (4) Is password file necessary for RAC instances in each node ? Answer with detailed explanation is highly appreciated. Thank you in advance. Rich
  12. richchen65

    ORA-24756 in Distributed Transactions

    Hi Don, >> This does not always happen, right? If it is sporadic, it may not be a bug. No, it does not. Last time occurred is about 1 year ago and this DB was 9.2.0.8. We upgraded this DB to 10.2.0.5 recently. >> I made this page just for you to document this ORA-24756 error: >> http://www.dba-oracle.com/t_ora_24756.htm That is my privilege. Thank you and this document tells it all. >> Also, open an SR on MOSC, this could be a bug: We already did. Last year opened the same SR and ended up with nothing conclusive because it was a 9i DB. Wish me luck this time. Rich
  13. Hi, We found out an error from alert log of our Oracle 10.2.0.5 DB : ==================================== .. Wed Jan 30 16:45:01 EAT 2013 DISTRIB TRAN bea1.67AA54355C4A74ECDEE0 is local tran 6.42.332492 (hex=06.2a.512cc) insert pending prepared tran, scn=8151148567799 (hex=769.d6509cf7) Wed Jan 30 16:45:02 EAT 2013 Errors in file /oradata/sfapdb/bdump/sfapdb_reco_2739.trc: ORA-24756: transaction does not exist Wed Jan 30 16:45:02 EAT 2013 Errors in file /oradata/sfapdb/bdump/sfapdb_reco_2739.trc: ORA-24756: transaction does not exist .. ==================================== There is no useful information from the trace log as shown below: ==================================== Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, Data Mining and Real Application Testing options ORACLE_HOME = /ap/oracle10 System name: HP-UX Node name: scvap2 Release: B.11.23 Version: U Machine: 9000/800 Instance name: sfapdb Redo thread mounted by this instance: 1 Oracle process number: 18 Unix process pid: 2739, image: oracle@scvap2 (RECO) *** SERVICE NAME:(SYS$BACKGROUND) 2013-01-30 16:45:01.941 *** SESSION ID:(1749.1) 2013-01-30 16:45:01.941 *** 2013-01-30 16:45:01.940 ERROR, tran=6.42.332492, ose=0: ORA-24756: transaction does not exist *** 2013-01-30 16:45:02.059 ERROR, tran=6.42.332492, session#=1, ose=0: ORA-24756: transaction does not exist ==================================== I also found out there are some records (trans_id = "6.42.332492") in SYS.PENDING_TRANS$/ SYS.PENDING_SESSION$/dba_2pc_pending with "prepare" status. This transcation is launched from a Weblogic Server via JDBC. Since it is abnormal so I have no choice to force commit/purge this transaction. Is that a bug of Oracle DB ? or Weblogic coding problem ? Any suggestions ? Rich
  14. richchen65

    Sequence Nextval storage information

    Hi Rashmita, please check this: http://psoug.org/definition/NEXTVAL.htm Rich
  15. richchen65

    ASM VS Veritas Storage Foundation for Oracle RAC

    Hi, Oracle ASM plays a role like Logical Volme Manager (LVM). ASM files are controlled by an Oracle ASM Instance and can only be managed through Oracle applications. That could be a concern for system administrators. Also, consult the vendors first if you would like to use any 3th-party softwares (like backup/disk clone) in your project. Rich
×