Jump to content

All Activity

This stream auto-updates     

  1. Past hour
  2. Hi, I have question with following case where I have use the function_parameters => 'm12DYh11m11s11' but the value are not REDACTed as per the documentation. Where I am expecting something like '01-dec-4712 11:11:11 am bc'. Steps to reproduce: SQL> conn scott/tiger SQL> CREATE TABLE tab1(dt_range DATE); SQL> INSERT INTO tab1 values(to_date('01-jan-4712 01:23:45 am bc','dd-mon-yyyy hh:mi:ss am ad')); SQL> INSERT INTO tab1 values(to_date('31-dec-9999 02:34:56 pm ad','dd-mon-yyyy hh:mi:ss am ad')); SQL> CREATE USER user1 IDENTIFIED BY user1; SQL> GRANT CREATE SESSION TO user1; SQL> GRANT SELECT ON tab1 to user1; Values Before redaction: SQL> select TO_CHAR(DT_RANGE,'dd-mon-yyyy hh:mi:ss am ad') from tab1; TO_CHAR(DT_RANGE,'DD-MON-Y -------------------------- 01-jan-4712 01:23:45 am bc 31-dec-9999 02:34:56 pm ad SQL> conn scott/tiger SQL> BEGIN DBMS_REDACT.add_policy( object_schema => 'scott', object_name => 'tab1', policy_name => 'redactPolicy_001', policy_description => 'redactPolicy_001 for tab1 table', column_name => 'dt_range', column_description => 'dt_range value in tab1 table', function_type => DBMS_REDACT.PARTIAL, function_parameters => 'm12DYh11m11s11', expression => '1=1', enable => TRUE); END; / PL/SQL procedure successfully completed. SQL> conn user1/user1 -- Values After redaction: SQL> select TO_CHAR(DT_RANGE,'dd-mon-yyyy hh:mi:ss am ad') from scott.tab1; TO_CHAR(DT_RANGE,'DD-MON-Y -------------------------- 01-dec-4712 01:23:45 am bc 31-dec-9999 02:34:56 pm ad Is this expected? Or I am missing something here, Please suggest.
  3. Today
  4. Yesterday
  5. burleson

    Complex query - help needed :)

    Hi Romntkac, I would consider using the “with” clause: http://www.dba-oracle.com/t_with_clause.htm Good luck!
  6. romntkac

    Complex query - help needed :)

    Hello everyone, I need your advise to get the following output table: Team Scored Received GoalDiff Argentina 3 1 2 Germany 4 4 0 Nigeria 3 4 -1 Japan 1 2 -1 From this input table: HomeTeam AwayTeam HomeScore AwayScore Argentina Nigeria 2 0 Germany Japan 1 1 Japan Argentina 0 1 Germany Nigeria 2 3 Nigeria Japan 0 0 Germany Argentina 1 0 Any suggestions? Thanks for the help!
  7. Last week
  8. Hi again, To get an old plan, user the techniques above to get the plan, and then make a SQL profile for it. Then, swap the current plan with the older plan: http://www.dba-oracle.com/t_swapping_sql_profiles.htm You can also do this with Optimizer plan stability (stored outlines): http://www.remote-dba.net/t_op_sql_hinted_query.htm Have fun!
  9. Hi Pravin, and welcome to the forum! There are many ways to freeze SQL plans, read here: http://www.dba-oracle.com/oracle11g/oracle11g_sql_plan_management.htm And here: http://www.rampant-books.com/art_foot_plan_stability.htm And SQL profiles: http://www.dba-oracle.com/t_sql_profiles_replace_stored_outlines.htm Good luck!
  10. How to Force the Optimizer to choose Old Plan.please share the Steps.
  11. Swapnil

    Delete query very slow

    - DELETE Statement with One Condition If you run a DELETE statement with no conditions in the WHERE clause, all of the records from the table will be deleted. As a result, you will most often include a WHERE clause with at least one condition in your DELETE statement. Let's start with a simple example of a DELETE query that has one condition in the WHERE clause. In this example, we have a table called suppliers with the following data: supplier_id supplier_name city state 100 Microsoft Redmond Washington 200 Google Mountain View California 300 Oracle Redwood City California 400 Kimberly-Clark Irving Texas 500 Tyson Foods Springdale Arkansas 600 SC Johnson Racine Wisconsin 700 Dole Food Company Westlake Village California 800 Flowers Foods Thomasville Georgia 900 Electronic Arts Redwood City California Enter the following DELETE statement: Try It DELETE FROM suppliers WHERE supplier_name = 'Microsoft'; There will be 1 record deleted. Select the data from the suppliers table again: SELECT * FROM suppliers; These are the results that you should see: supplier_id supplier_name city state 200 Google Mountain View California 300 Oracle Redwood City California 400 Kimberly-Clark Irving Texas 500 Tyson Foods Springdale Arkansas 600 SC Johnson Racine Wisconsin 700 Dole Food Company Westlake Village California 800 Flowers Foods Thomasville Georgia 900 Electronic Arts Redwood City California This example would delete all records from the suppliers table where the supplier_name is 'Microsoft'
  12. burleson

    Database connection only by a specific user

    Hi Hugo, A simple sution would be to revoke the “connec”, “resource”, and “create session” roles from all users except the one that you want to connect. Let us know if this works for you.
  13. Hi Rich, Thanks for the update! Yake care . . .
  14. You has complete directions! This forum is for answering questions! This is not a question, it is a request for free support services. For that, you need to do a service request on MOSC: http://support.oracle.com If you do not have not purchased Oracle support, you can call (800) 766-1884, give your credit card number for $1,000, and we will assign a consultant to help you. http://www.dba-oracle.com/consulting_prices.htm In the future, only post specific questions. Good luck! Good luck!
  15. Earlier
  16. 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
  17. # This is a customized agent init file that contains the HS parameters # that are needed for the Database Gateway for Microsoft SQL Server # # HS init parameters # HS_FDS_CONNECT_INFO=[]:49698//sa # alternate connect format is hostname/serverinstance/databasename HS_FDS_TRACE_LEVEL=OFF HS_FDS_RECOVERY_ACCOUNT=RECOVER HS_FDS_RECOVERY_PWD=RECOVER tnsnames.ora locattion :/u01/app/oracle/test/product/12.1.0/tghome_4/network/admin/tnsnames.ora dg4msql = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=rite-oralinux-68-vm1.localdomain)(PORT=1523)) (CONNECT_DATA=(SID=dg4msql)) (HS=OK) ) # listener.ora Network Configuration File: /u01/app/oracle/test/product/12.1.0/tghome_4/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rite-oralinux-68-vm1.localdomain)(PORT = 1523)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523)) ) ) SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=dg4msql) (ORACLE_HOME=/u01/app/oracle/test/product/12.1.0/tghome_4) (PROGRAM=dg4msql) ) ) # listener.ora Network Configuration File: /u01/app/oracle/product/ # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rite-oralinux-68-vm1.localdomain)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=dg4msql) (ORACLE_HOME=/u01/app/oracle/test/product/12.1.0/tghome_4) (PROGRAM=dg4msql) ) ) # tnsnames.ora Network Configuration File: /u01/app/oracle/product/ # Generated by Oracle configuration tools. LISTENER_CDB1 = (ADDRESS = (PROTOCOL = TCP)(HOST = rite-oralinux-68-vm1.localdomain)(PORT = 1521)) CDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rite-oralinux-68-vm1.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cdb1) ) ) ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rite-oralinux-68-vm1.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.localdomain) ) ) dg4msql = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rite-oralinux-68-vm1.localdomain)(PORT = 1523)) (CONNECT_DATA = (SID = dg4msql) ) (HS= OK) ) [oracle@rite-oralinux-68-vm1 admin]$ tnsping dg4msql TNS Ping Utility for Linux: Version - Production on 11-OCT-2018 13:29:22 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/ Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rite-oralinux-68-vm1.localdomain)(PORT = 1523)) (CONNECT_DATA = (SID = dg4msql)) (HS= OK)) OK (0 msec) ...Create db_link create database link mytest connect to "sa" identified by "Html@3452" using 'dg4msql'; select * from all_users@dg4msql; ORA-28545: error diagnosed by Net8 when connecting to an agent Unable to retrieve text of NETWORK/NCR message 65535 ORA-02063: preceding 2 lines from MYTEST select * from all_users@mytest
  18. burleson

    Kerberos authentication using AD

    Hi Amit, i don’t know why the OS should matter, but you can check on MOSC and know for sure: http://support.oracle.com Did you collect a trace file as the error message suggested?
  19. burleson

    Database link from Oracle to SQL Server

    Hi Vipon, and welcome to the forum! I have complete instructions here: http://www.dba-oracle.com/t_database_link_sql_server_oracle.htm Step-by-step instructions here: http://www.dba-oracle.com/t_heterogeneous_database_connections_sql_server.htm Good luck!
  20. Amitsahame

    Kerberos authentication using AD

    Hi. Is it applicable to AIX box? As oracle recommends kerberos5 entry in the sqlnet.ora file for authentication protocol
  21. how to create dblink in oracle for connect sql server database i want to insert data from sql server to oracle
  22. burleson

    Export Issue

    Hi, I would start by using the full path when using the CREATE DIRECTORY statement: http://www.dba-oracle.com/t_oracle_create_directory.htm You also need to specify the dump_dir on the expdp invocation. See working example here: http://www.dba-oracle.com/t_rman_166_expdp_example.htm Good luck!
  23. burleson

    Kerberos authentication using AD

    Hi Amit, and welcome to the forum! First, try this change to SQLNET_AUTHENTICATION_SERVICES: http://www.dba-oracle.com/t_ora_12638_credential_retrieval_failed.htm Also, per the error, get a trace file. Good luck!
  24. We have configured kerberos authentication for oracle 12r release.2 database in aix lpar.but while connecting to database "sqlplus /@testdb", we are getting ora 12638:credential retreival failed error Then when asking for username and password,it shows ora 12170 : tns:connect timeout occured error
  25. Abhiprada

    Export Issue

    Hello Experts, I have seen a strange thing while taking the exports of the tables. I have created the Directory and execute the expdp command with same directory name,Export works fine and gets completed but the dumpfile name gets changed and location becomes different. Kindly suggest what mistake I have done.
  26. Hello Suhaasv, Eventhough you don't have to use an EXECUTE IMMEDIATE in a nested way, your script will work just fine. It makes more sense to not include the syntax as it's already a string. Thank you, Boobal Ganesan Author of Advanced PL/SQL Programming: The definitive reference
  27. Hi, and welcome to the forum! This is a good question, but I have never tried it. You should be able to build a simple test procedure of bested execute immediate calls and see if you get an error! You could create a dummy table and use the execute immediate to delete specific rows: http://www.dba-oracle.com/t_oracle_execute_immediate.htm Let us know the results of your test. I have also asked Bob Ganesan, a PL/SQL guru, to pop-in and advise you. Good luck!
  28. I have a stored procedure where is an Execute Immediate will call a "Begin ... End " block which contains another Execute Immediate which will be called only if a certain condition is met. The inner Execute Immediate command inside "Begin... End clock" is enclosed in single quotes. So it looks like BEGIN ...SOME STUFF HERE... FOR ..... LOOP EXECUTE IMMEDIATE 'BEGIN IF (condition) THEN EXECUTE IMMEDIATE 'DML STRING' END IF END;' END LOOP; END; That begs another question can an Execute Immediate statement be called from within the loop. I think the answer is yes.
  29. Hi Rich, Yes, ASM is part of the Oracle grid infrastructure: https://docs.oracle.com/cd/E11882_01/install.112/e47689/oraclerestart.htm#LADBI1212 Do you want to use ASM? I think that you can still create a vanilla database without using grid or ASM. But you are correct, in 12cR2 you must install the grid infrastructure: https://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle12c-aix-12201-3677038.html Or this page: https://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html Please let us know if this is the right download. Good luck!
  1. Load more activity