DBA Blogs

Is it not possible to use a pluggable or container database as the AWR warehouse database? The documentation doesn't state anything about it but when i go to configure the awr warehouse, there is no option to select those types of databases.

Hello TOM, Is there a way to perform a accent-insensitive conversion ONLY? I'm using the construct: <code>SELECT utl_raw.cast_to_varchar2(NLSSORT('ABCoua', 'nls_sort=binary_ai')) FROM dual;</code> which - as byproduct - is performing also a case-insensitive replacement, that is not required. Something like this: <code>SELECT my_funct('ABCoua') FROM dual;</code> should output: <code>ABCoua</code> and not: <code>abcoua</code> In case you're wondering why i need this, it's because we're storing the data in UNICODE format in our database, but we need to export it using LATIN-based characters to an international institution. Thanks,

<code></code><code></code><code></code><code></code><code></code>Hi Gurus, I have a problem with my Pro*C application. MY OS -> RedHat release 5.5 (Tikanga) My database; Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production nls_paramters; NLS_LANGUAGE <b>AMERICAN</b> NLS_TERRITORY <b>AMERICA</b> NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_CHARACTERSET <b>WE8ISO8859P9</b> NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE Before I run my C app, I set NLS_LANG. For example ; export NLS_LANG=AMERICAN_AMERICA.UTF8 and I run appl on unix terminal -> <code>./app sqlstmt"select * from mus_test_char"</code> Connected to ORACLE as user: mus/mus123@orcl Unloading 'select * from must_test_char' Array size = 10 IDD,TEXT "1"|<b>"(null)"</b> if I export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9 and I run <code>./app sqlstmt"select * from mus_test_char" </code> Connected to ORACLE as user: mus/mus123@orcl Unloading 'select * from must_test_char' Array size = 10 IDD,TEXT "1"|<b>"?aaaa?aaaaa?aaaaa?aaaaa?aaaaa?aaaaa?aaaa?aaa"</b> 1 rows extracted If I unset NLS_LANG -> unset NLS_LANG and I run -> <code>./app sqlstmt"select * from mus_test_char"</code> Connected to ORACLE as user: mus/mus123@orcl Unloading 'select * from must_test_char' Array size = 10 IDD,TEXT "1"|<b>"Saaaa?aaaaaUaaaaaOaaaaaCaaaaa?aaaaa?aaaaoaaa"</b> 1 rows extracted My original data in database following; 1 Saaaa?aaaaaUaaaaaOaaaaaCaaaaa?aaaaa?aaaaoaaa Can you help me about this problem ? Edit: I tried my app another Database. It's NLS Settings , AMERICAN_AMERICA.AL32UTF8 And I set on my client NLS_LANG=AMERICAN_AMERICA.UTF8 Then I run my app on that machine , I get values correctly.. 1 Saaaa?aaaaaUaaaaaOaaaaaCaaaaa?aaaaa?aaaaoaaa Thanks.

 SQL*Plus now can provide you the SQL_ID of the last statement executed in your own session with SET FEEDBACK SQL_ID.

A quick demo :

SQL> set feedback on sql_id
SQL> select count(*) from my_target where factory='SYS';

  COUNT(*)
----------
     52217

1 row selected.

SQL_ID: g1mk14hdxc1ww
SQL> select * from table(dbms_xplan.display_cursor('g1mk14hdxc1ww'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g1mk14hdxc1ww, child number 0
-------------------------------------
select count(*) from my_target where factory='SYS'

Plan hash value: 1690349505

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     6 (100)|          |
|   1 |  SORT AGGREGATE   |               |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| MY_TARGET_NDX |  2683 | 13415 |     6   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("FACTORY"='SYS')


19 rows selected.

SQL_ID: 5dyyqqwuyu01v
SQL>

After I executed my query against the "my_target" table, SQL*Plus provided my the SQL_ID ("g1mk14hdxc1ww").  I could then immediately get the Execution Plan for the statement, without having to query for the SQL_ID in V$SQL or V$SQLAREA.

Hi, In apex i can able to extract data from EXCEL using XLSX PARSER, but in my case user uploading the file based on the file type (Ex: EXCEL/CSV/JSON/XML) i'm storing into as a blob column. i want to extract the data(from Blob column) based on the type and i want to storing into another table as a records. Is there any option to extract the data from blob column. Please provide any sample. Thanks, Praveen

In a RAC environment since we are using more than one nodes for a database, will there be more read/write congestion than in a standalone ASM database? If yes, is there any solution to minimise that congestion.

Hi everyone, I have one web page name sth like that: 'https://bus/api/check'. When I run this web, it returns data normally. But when I create web source module, it shows me as below An error occurred during URL invocation. ORA-29024: Certificate validation failure I search on GG about that problem. I try to solve my problem follow this web: https://apex.oracle.com/pls/apex/germancommunities/apexcommunity/tipp/6121/index-en.html It is very cleart. I created new wallet named https_wallet and added certificate successfully. Howerver, I check by select statement on DB, with code: <code>select APEX_WEB_SERVICE.make_rest_request(p_url => ''https://bus/api/check',p_http_method => 'GET',p_wallet_path => 'file:C:/temp/wallet/https_wallet',p_wallet_pwd => 'pass_word') from dual;</code> <b>It shows HTTPS request failed and security violation. I don't know why that reason. I tried those steps on Google web, I can get certificate successfully. Now my certificate is from Amazone. I check web via checkssl online, everything it is ok, but I don't understand when I check serial number of the first of certificate, it shows me serial number is 00. Can anyone help me to solve my those problem. I do not have much experience in create web source module. Thank in advance.</b> Ask about Web Source Module

Hi all, we have an application which runs reports from Windows7/10 client against an ORCALE 19c Standard Edition Database on Windows Server 2019. Before, the application queries where running against ORACLE 12c Standard Edition. We have noticed that the reports run significantly longer against ORACLE 19C. So we have then tried to find the cause. Therefore we have taken the pure select SQL of the report and have run it from SQL Developer 20.0.0 from an Windows 10 Client and then directly on the Windows server. And still the same. The select uery run from SQL Developer on the Server runs approximately 3.5 secs, and from the client it needs about 180 secs. Then we have run autotrace from within SQL Developer on both platforms and it gives us exactly the same execution plan. The connection in SQL Developer on both platforms is defined with sys as sysdba and a TNS connection via a network alias. We have currently abosulutely no clue why this is happening. Basically everything is the same, the only difference is that SQL Developer runs once on Windows 10 and once on Windows Server 2019. We are lookoing forwrd to any hints and tipps you might have. Thanks a lot for your expertise. Cheers Que

Hello, I have a stored procedure to send e-mails using UTL_SMTP. It's prepared to accept a list of e-mail addresses in the "To" and the "CC" parameter. And functionally, it works as expected: it sends the e-mail to the list provided. The issue is, when the recipients open the e-mail, they only see the first e-mail address on the list, and if they want to reply-all they won't be able to do it, because the rest of the e-mail addresses does not display. For example, if I use the following code: <code>BEGIN IVC_SEND_MAIL('no-reply@mydomain.com', 'alex@mydomain.com', 'one@mydomain.com;two@mydomain.com;three@mydomain.com',NULL, 'Subject', 'Body'); END;</code> It will send the e-mail to alex, one, two and three; but, the e-mail will show the header of the e-mail as: To: alex@mydomain.com Cc: one@mydomain.com and it won't show two@mydomain.com nor three@mydomain.com So, the question is: what am I missing? How can I make sure the whole distribution list is displayed? Thank you. This is the code for the procedure I'm using: <code>CREATE OR REPLACE PROCEDURE IVC_SEND_MAIL (v_from in varchar2, v_to in varchar2, v_cc in varchar2, v_bcc in varchar2, v_subj in varchar2,v_body in varchar2) IS v_crlf VARCHAR2(2) := CHR( 13 ) || CHR( 10 ); v_mesg VARCHAR2(10000); v_conn utl_smtp.connection; v_email_svr VARCHAR2(4) := 'mailserver'; v_port NUMBER := 25; v_cc_msg VARCHAR2(2000); BEGIN v_conn := utl_smtp.open_connection( v_email_svr, v_port ); utl_smtp.helo( v_conn, v_email_svr ); utl_smtp.mail( v_conn, v_from); FOR x IN (SELECT LEVEL AS id, REGEXP_SUBSTR(v_to, '[^;]+', 1, LEVEL) AS TO_EMAIL_NAME FROM DUAL CONNECT BY REGEXP_SUBSTR(v_to, '[^;]+', 1, LEVEL) IS NOT NULL) LOOP utl_smtp.Rcpt(v_conn,x.TO_EMAIL_NAME); END LOOP; IF v_cc IS NOT NULL THEN FOR x IN (SELECT LEVEL AS id, REGEXP_SUBSTR(v_cc, '[^;]+', 1, LEVEL) AS CC_EMAIL_NAME FROM DUAL CONNECT BY REGEXP_SUBSTR(v_cc, '[^;]+', 1, LEVEL) IS NOT NULL) LOOP utl_smtp.Rcpt(v_conn,x.CC_EMAIL_NAME); END LOOP; v_cc_msg := 'CC: ' || v_cc || v_crlf; ELSE v_cc_msg := ''; END IF; IF v_bcc IS NOT NULL THEN FOR x IN (SELECT LEVEL AS id, REGEXP_SUBSTR(v_bcc, '[^;]+', 1, LEVEL) AS BCC_EMAIL_NAME FROM DUAL CONNECT BY REGEXP_SUBSTR(v_bcc, '[^;]+', 1, LEVEL) IS NOT NULL) LOOP utl_smtp.Rcpt(v_conn,x.BCC_EMAIL_NAME); END LOOP; END IF; v_mesg := 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || v_crlf || 'From:' || v_from || v_crlf || 'Subject: ' || v_subj || v_crlf || 'To: ' || v_to || v_crlf || v_cc_msg || 'Mime-Version: 1.0;' || v_crlf || 'Content-Type: text/html; charset="ISO-8859-1";' || v_crlf || '' || v_crlf || ''|| v_crlf||v_body; utl_smtp.data( v_conn, v_mesg ); utl_smtp.quit( v_conn ); END; / </code>

Hi Tom, I would like to know if there is any way to code exception handlers in such a way as to capture the specific PLSQL or SQL statements that shall cause an exception inside the same PLSQL block for which the exception handler is written. For example, say below statement inside a PLSQL block, <code>v_num (a number datatype variable) := <text data>;</code> this shall cause the obvious exception and this particular statement shall be captured along with the parsed values (the actual value at the right hand side of this statement that was responsible for the exception), and logged from the exception handler. Similarly, another SQL statement in the same block, during the next run, shall cause some exception and the parsed version of that SQL statement shall be captured and logged from the exception handler. I know what I am asking is akin to the formal debugging mechanism, but is this something impossible to achieve ? Regards, Manohar Mishra.

Hello TOM, We've recently upgraded an 11.2.0.4 database to 19.3 on Windows 2012R2. As post-upgrade actions recommended, we changed the symbolic links on directories from UNC shares(\\computer_name\xxx) to a network drive with letter (e.g. Y:\) While trying to run expdp (with both 11.2 and 19.3 binaries) jobs on that network drive, we get the following errors <code>ORA-39000: bad dump file specification ORA-31641: unable to create dump file "Y:\...\...\..." ORA-27037: unable to obtain file status OSD-04011: GetFileInformationByHandle() failure, unable to obtain file info O/S-Error: (OS 5) Access is denied.</code> I googled this OSD-04011 error message that looks a Microsoft OS error, but they are simply sending me back to Oracle. Could it be linked to the OS user that installed Oracle database/ORACLE_HOME ? We are running the database service using "LocalSystem" and the permissions are set correctly (i would say) on the share, namely using server's domain account (SERVERNAME$). Any thoughts ?

Hi Tom, I've been using Oracle for Sometime now, I use a lot of MSSQL and Sybase, I'm trying to load some ascii file using SQL Loader(sqlldr), I want to know if there is any oracle function equvivalent to ISDATE() function in MSSQL or Sybase. If my Ascii file contains data which is not of date datatype, I want to insert a NULL instead of loading some wrong data or getting an error in sqlload. Thanks for your Help Srini

I have got an Oracle version 8.1.1 I'd like to know how you get the IP address from users when they have already logged on the Database. Would you, please, send me infomations about versions older than 8.i? Thanhs beforehand.

Hello I have a question on oracle redaction. Can oracle redaction be turned on based on a value in a column? please let me know Thanks subramanyam

Oracle is under the impression that 1/19 * 19 - 1 = -6 x 10^(-40). You can replace 19 with 19 multiplied by any positive power of 100 and the problem remains. (When you multiply 19 by an odd power of 10, the answer is zero. For example, 1/1900 * 1900 - 1 = -6x10^(-40), but 1/190 * 190 - 1 = 0.) I understand it has to do with the way numbers are stored internally, but is there a simple way around it? Note that this also happens in version 10.2.4

Sir, Greetings. I would just like to know if it is possible to truncate a sequence to reset back to its original starting number? Pls. help me. Thank you. Merry Christmass. - Vince Crismer C. Villena

Can you please give me an example for recursive function in plsql Which takes i_date as an input variable using this i_date it should perform operations like it should fetch data from table into cursor From this cursor I should insert data into a file .This file should be generated seperately for date that is passed in as input Let us take that my input date is 01012016 the function should recursively perform until it reaches 31122016 (for every last day of a month i.e total 12 times)

Hi Tom, 1) Is there or What is the relationship between number of CPUS the host has to the degree of Parallelism selected say while running DBMS_STATS. 2) Is there a relationship between DB sessions and Degree of Parallelism. If I have say 4 CPUS and select degree of 4 When I run DBMS_STATs will it show 4 DB sessions and consume 4 CPUS ? Or It will definitely show 4 DB sessions but no. of CPU depends upon availabilty from OS side. So even 2 CPUS will be used for 4 sessions ? 3) Is there always one to one relationship between a DB session ( from v$session) and OS process (seen with ps command ) 4) DBMS_SCHEDULER does it need to have job_queue_processes set like the old days with dbms_job Please answer with respect to 9208 and 11107 version.

Db Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production <code>desc Par_trials; Column Name Pk Null? Data Type PARTY_TRIAL_ID 1 N NUMBER (11) PARTY_ID N NUMBER (11) PARTY_ROLE_TYPE_ID N NUMBER (11) CLINICAL_TRIAL_ID N NUMBER (11) START_DT Y DATE END_DT Y DATE CORE_CREATE_DT N DATE CORE_LAST_UPDT Y DATE EDC_FLAG Y VARCHAR2 (1 Byte) CPAC_FLAG Y VARCHAR2 (1 Char) ELMS_FLAG Y VARCHAR2 (1 Char) CREATE MATERIALIZED VIEW LOG ON par_trials WITH ROWID, SEQUENCE (party_id,party_role_type_id,clinical_trial_id,start_dt,end_dt,core_create_dt,core_last_updt,edc_flag,cpac_flag,elms_flag), PRIMARY KEY INCLUDING NEW VALUES Insert into PAR_TRIALS (PARTY_TRIAL_ID, PARTY_ID, PARTY_ROLE_TYPE_ID, CLINICAL_TRIAL_ID, START_DT, CORE_CREATE_DT) Values (365352, 50858, 528, 1055, TO_DATE('10/30/2019 20:53:51', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/07/2019 12:03:59', 'MM/DD/YYYY HH24:MI:SS')); COMMIT; update par_trials set core_create_dt=sysdate where party_Trial_id=365352; select * from MLOG$_PAR_TRIALS</code> Question: 1) Would like to include a new column called "OPERATION$" in MLOG$_PAR_TRIALS table which should have below column values "UU" which indicates before update and should contain all its corresponding old values "UN" which indicates after update and should contain all its corresponding new values 2) In mlog creation script, have included "INCLUDING NEW VALUES" and i do have requirement to "exclude old values". Expectation here is just to retain only new values in mlog. Is that possible? If yes, kindly share example. 3) Can we do delete operation against MLOG$_PAR_TRIALS?If yes, kindly share example.

Dear Team We had recently upgraded our Application counter scheme ( Previously using oracle sequences) to our own created procedure to generate Next number/counter due to our requirement to generate seperate counters for our different Hospital Campuses. Every year we have to reset all sequences because our scheme is using year based counter values. Now the issue is locking problem on our Physician Encounter where we have to generate 7-8 different counters on the same time and our procedure sometimes get locked and does not return any counter value due to locking issue Our application user may loss data to No Counter return We have two options of Row update, wait 1 sec and Nowait, sample code is mentioned below. can you please guide us on method close to Oracle sequence so that we can update code and overcome this locking issue. Also Please guide do you recommend to use FOR UPDATE without wait or NOWAIT option in SQL ? <code> IF NVL(P_LOCK_WAIT, 'N') = 'Y' THEN SELECT ROWID INTO P_ROWID FROM COUNTERS.SYSTEM_COUNTERS_VALUES T WHERE T.COUNTER_ID = P_COUNTER_ID AND T.SERIAL_NO = P_SERIAL_NO FOR UPDATE WAIT 1; ELSE SELECT ROWID INTO P_ROWID FROM COUNTERS.SYSTEM_COUNTERS_VALUES T WHERE T.COUNTER_ID = P_COUNTER_ID AND T.SERIAL_NO = P_SERIAL_NO FOR UPDATE NOWAIT; END IF </code>