Questions

Trying to deal with timezone + DST aware datetime handling, but I couldn't get it safe and convenient at the same time.The almost perfect solution would be timestamp with local time zone , except the fact that it uses nls_timestamp_format (opposed to using nl...

Dear Sir,Hope doing well..Need some clarification on below points.1) What is fastest way to change DB_BLOCK_SIZE from 8k to 16k, my DB size is 2TB.2)Can we duplicate database as active database from standby database and snapshot database.Please comment on this...

Hi,Recently while preparing to run the Release Upgrade for 19.24 I was doing a cold back of my ORACLE_HOME and I noticed that I had a ton of old .log, .trc, and .trm files. I deleted old files in the trace directory, audit directory, and some of the log direc...

Hi,I am carrying out an update on dept table with update query as - "update dept set dname = initcap(dname) where deptno=20;"As, I have not committed, row lock will be placed on the row. I want to find out the rowid of dept table locked from V$LOCK. Currently,...

Dear Experts,Need your guidance/suggestions to resolve this issue:Part of oracle advance queueing implementation, we've to dequeue the message as soon as it has been enqueued into the queue. This should happen immediately without any manual intervention/schedu...

Hi,I would like to know IP Address of DB Server.So i executed below Query found on Google.SELECT A.HOST_NAME,A.INSTANCE_NAME,UTL_INADDR.GET_HOST_ADDRESS FROM V$INSTANCE A;I got one IP Address but its not matching with the IP address in TNS?So i would like to ...

I activated audit on some just for unsuccessful connection attempts ( my purpose was finding who/what locks some users ).This is something I had already done many times, always fine.However on a 19 version I noted something I could not find an explanation for:...

Hello Tom,I have a live table (Investor) with customers records and another table (Invdatew) has the same records but have one extra column for date,. After each end of day I use dbms_job to insert the the exact data from live table to the other one plus sysda...

Hello, As far as I understand it, Oracle processes regular expressions according to the POSIX standard, but also supports expressions that originate from Perl.Currently I had some missleading results when searching for a space. Theoretically, this should be fo...

Hello,Could you please clarify the meaning of the view V$RESERVED_WORDS and especially its contents?At first glance, the documentation has a clear explanation. The view contains reserved words, and columns RESERVED, RES_TYPE, RES_ATTR, and RES_SEMI describe, f...

We have seen in many situations in our environment where a SQL was running badly but the plan for the query has not changed. When we gather stats for the associated table.we see that same query performs significantly better. However there is no change in PHV o...

Hello, TomWe have been facing the slow rollback after killing job of huge transaction, then we discovered several ways to speed up the rollback process.So one possibility is altering hidden parameter "_cleanup_rollback_entries" from default value (100) to be 4...

Hi,<code>CREATE TYPE phone_typ AS OBJECT ( country_code VARCHAR2(2), area_code VARCHAR2(3), ph_number VARCHAR2(7));/CREATE TYPE phone_varray_typ AS VARRAY(5) OF phone_typ;/CREATE TABLE dept_phone_list ( dept_no NUMBER(5), phone_list ph...

We would like to encrypt data at rest in an oracle database column (in one specific database table only) using an encryption key held externally in a vault.Does Oracle provide standard interfaces to make API calls ?The encryption key should not persist in the ...

Hi,I have procedure via database link, and getting this errorORA-02064: distributed operation not supportedThis is because need to apply pragma autonomous_transaction. But is there any other way? some of the procedure we need to reuse, and we only need to per...

Hi,I have a query which i am trying to load the data using 2 options:1. Materialized View Refresh with Non-Atomic Refresh2. Insert into Select Materialized View Refresh: When i try to Complete refresh a Materialized View in 12c Database residing on Exadata, Th...

I want to get difference between two dates say(10-FEB-2000 - 02-JAN-1999)and the result has to be displayed in following format1 year, 1 month, 8 days ...

Good day,I want to backup my Snippets but cannot find the file to back it up as I cannot locate the XML file with my Snippets therein. I went to system23.1.1.345.2114 and in there I went to o.sqldeveloper.snippet and this folder is empty despite me setting "So...

Hi Gurus,I have a schema as described in the link https://livesql.oracle.com/ords/livesql/s/ct0zdah8slxq66h8fnpfhwdby.<code>drop table t;create table t( instance number , id_sup number , id number , srt number , v01 number , v02 number , v03 number ...

Hi Senior, My database is 12C PLSQL. Is it possible to alter table and add IDENTITY for primary key, if the data in the table is beyond <b>200Crore</b> or <b>2Billion</b> data. As I am concerned about database crashing or Cache overflow.Please guide ...