Jump to content

richchen65

Members
  • Content count

    53
  • Joined

  • Last visited

Community Reputation

0 Neutral

About richchen65

  • Rank
    Advanced Member
  • Birthday 10/17/1965

Profile Information

  • Gender
    Male
  • Location
    IL 60559
  1. richchen65

    alter user sys password question in 12cR2 database

    Hi Don, This problem will happen after applying 2018 JULY PSU (Bug 28538439) .. The solution: 1. Apply 2018 OCT PSU patch (Patch No# 28662603) 2. Apply Bug 28538439 hotfix 3. Setting hidden parameter _enable_ptime_update_for_sys=TRUE and restart the database. The solution works for me. Thank you for running this forum for all Oracle experts around the world. Rich
  2. richchen65

    alter user sys password question in 12cR2 database

    Hi Don, I think I didn't describe the question clearly. This question is from the internal auditors who will check the password changing policy is enforced or not. They rely on the timestamp in PTIME column of sys.user$ to determine if the password was changed periodically. Prior to 11gR2 , the timestamp in PTIME of SYS will be updated soon after the password was changed but not in 12cR2. In 11gR2: SQL> alter user sys identified by xxxx; --- Done without error SQL> select ctime, ptime From sys.user$ where name = 'SYS'; --- Ptime will be the password changed time In 12cR2: SQL> alter user sys identified by xxxx; --- Done without error SQL> select ctime, ptime From sys.user$ where name = 'SYS'; --- Ptime will NOT be changed ..in my case , same as ctime Rich
  3. Dear Oracle experts, The password changed time will be recorded in the column (Ptime ) in sys.user$ prior to 11gR2 . I found out the Ptime value won't be updated in 12cR2 after the sys password was changed. Any idea ? Rich
  4. richchen65

    ASM file system for non-RAC oracle 12cR2 database in AIX

    Hi Don, Yes , that is the right download. Thank you. It is sad to confirm the awkward change in 12cR2: For those who want to use ASM for non-RAC oracle databases, you should install extra Oracle Clusterware components which you don't actully need. For those who want to use 3th-party cluster file system (like VxFS) in Oracle RAC , you should install ASM anyway for storing OCR and Voting Disk files. Rich
  5. Dear Oracle gurus, I was told ASM now becomes part of Oracle Grid Infrastructure in 12cR2. Does that mean I have to download Oracle Grid anyway for implementing ASM even I just want to install non-RAC oracle database in AIX ? Is there any 12cR2 ASMLIB I can download for AIX ? Thank you, Rich
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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
  14. 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
  15. 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
×