DBA Blogs

Unified Audit Log Change Tablespace Location

Tom Kyte - 7 hours 5 min ago
Hi Tom, Is that we can changed the unified audit log tablespace from SYSAUX to standalone tablespace(example: TBS_AUDIT) online without restart the database? If yes, is that will be any impact by doing so?
Categories: DBA Blogs

Installation Error 18c XE

Tom Kyte - 7 hours 5 min ago
I tried to install Oracle XE 18c,but it keeps rolling back. On trace log, it said no valid ip address return for the host. I'm using windows 10 pro. What should i do to fix this problem?
Categories: DBA Blogs

Oracle SQl Commit taking too long

Tom Kyte - 7 hours 5 min ago
<b></b>Team, let us consider our normal scott schema for this concern. Let us consider emp table. REM INSERTING into emp SET DEFINE OFF; Insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17-12-80 00:00:00','DD-MM-RR HH24:MI:SS'),800,null,20); We have two concerns here. <b><u>Concern 1;</u></b> In few scenarios ,Insert is taking too long in our prod DB . The insert statement in our Prod is different than the one provided above, though the syntax of insert stmt is same.(I meant to say that it's a single record insert stmt.) We would like to know what could be the possible reasons for the insert to take too long time. <b><u>Concern 2;</u></b> In few scenarios, the SQL commit is taking lot of time. We would like to know what could be the possible reasons for the commit to take too long time. Any of your suggestions are most welcome !! Note: Edited for 2 typos. Thanks & Regards, Vinesh
Categories: DBA Blogs

AUSOUG Connect 2021- “Automatic Indexing: An Update On Improvements and New Capabilities”

Richard Foote - 12 hours 52 min ago
  I’ve very pleased that my paper “Automatic Indexing: An Update On Improvements and New Capabilities” has been accepted for the upcoming AUSOUG Connect 2021 Virtual Conference. The conference runs between 9th – 12th November 2021 and features a host of great topics and speakers including Connor McDonald, Chris Saxon, Jim Czuprynski, Sandesh Rao, Karen […]
Categories: DBA Blogs

Visit the link when the event is about to start.

Tom Kyte - Thu, 2021-10-21 18:26
Please can I get the zoom link for today's conference?
Categories: DBA Blogs

index

Tom Kyte - Thu, 2021-10-21 00:26
Tom: I have a question regarding index. can you explain me in detail what the following means. If the index is a concatenation of multiple columns and one of the columns contains a NULL value, the row will be in the index column containing the NULL value and will be left empty. +++++++++++++++++++++++++++++++++++++++++++++ above is exactly what it says in a book. I can't even understand it's english."the row will be in the index column containing the NULL value and will be left empty" what does that mean?
Categories: DBA Blogs

Request for script to import data

Tom Kyte - Thu, 2021-10-21 00:26
Dear Ask TOM Team, Thank you for your appreciated efforts and providing the guidance to all concerned clients. My question: I have running and in production oracle DB, 11g ... and would like to import and update the data from other DB with the same structure and version, Please: 1- provide me the script to do such required import data to the running DB. 2- provide me the script to do such required export data to the running DB. Regards, Reda Eltayef
Categories: DBA Blogs

i want to export only the objects owned by given schema using EXPDP

Tom Kyte - Thu, 2021-10-21 00:26
Hi, i want to export only the objects owned by given schema using EXPDP, but unfortunately it is exporting all the objects which schema has access to. i am using below syntax. <code>expdp school/school@orcl schemas=(demo) directory=DATA_PUMP_DIR dumpfile=demo.dmp logfile=expdp.log</code> Please help. Thanks Fahd
Categories: DBA Blogs

Autonomous transactions and commit point optimization

Tom Kyte - Thu, 2021-10-21 00:26
Hi We have an application written with PL/SQL and thus with commit point optimization. However AWR reports show a lot of log file sync waits. How do autonomous transactions, which we are using for logging purposes operate with pl/sql code. Is the commit point optimization utilized? If some procedure is called which is defined as an autonomous transaction and no actual dml operations are done (which could happen in logging operations), is there still some commit handling done ? If so, what ? lh
Categories: DBA Blogs

How to calculate timestamp from an old ora_rowsn taking as a reference a current ora_rowscn timestamp_to_scn(sysdate) from DUAL

Tom Kyte - Thu, 2021-10-21 00:26
As you know, the association between an SCN and a timestamp when the SCN is generated is remembered by the database for a limited time period and an error is returned if the SCN specified for the argument to SCN_TO_TIMESTAMP is too old. Example: <code>select scn_to_timestamp(max(ora_rowscn)) from MY_TABLE</code> <i><b>ORA-08181: el numero especificado no es un numero de cambio del sistema valido ORA-06512: en "SYS.SCN_TO_TIMESTAMP", linea 1 08181. 00000 - "specified number is not a valid system change number" *Cause: supplied scn was beyond the bounds of a valid scn. *Action: use a valid scn.</b></i> Nevertheless, I can obtain the max(ora_rowscn) from MY_TABLE without any kind of error: <code> select max(ora_rowscn) from MY_TABLE MAX(ORA_ROWSCN) --------------- 99464620 </code> I would like to obtain the corresponding timestamp of this ORA_ROWSCN approximately (I don't care if it may vary some hours), <b>without using any AUDIT tool but using PL/SQL statements</b>. Does exist any relationship between the above MAX(ORA_ROWSCN) from MY_TABLE and the current ORA_ROWSCN from sysdate? <code> select timestamp_to_scn(sysdate), TO_CHAR(SYSDATE,'dd-mm-yyyy hh24:mi:ss') from dual TIMESTAMP_TO_SCN(SYSDATE) TO_CHAR(SYSDATE,'dd-mm-yyyy hh24:mi:ss') ------------------------- ---------------------------------------- 116631465 13-10-2021 12:12:02 </code> Could I calculate, using any kind of algorithm, the corresponding timestamp to <b>99464620</b> value? Thanks in advance.
Categories: DBA Blogs

Different sql id/sql text showing up in v$session

Tom Kyte - Wed, 2021-10-20 06:06
Hello! I have a particular ETL job that fires below 4 select queries on a view definition to our Oracle database - <code>select * from view where mod(id,4) = 1; select * from view where mod(id,4) = 2; select * from view where mod(id,4) = 3; select * from view where mod(id,4) = 0; </code> However when the queries start executing and I check v$session for the 4 sessions running these 4 queries, I see a common sql id showing up for all these 4 queries and the sql text for this sql id seems completely unfamiliar/unrelated to the queries that are actually being executed. So I am a bit lost as I have not come across such scenario earlier. What could this sql id be and why would it be showing up in v$session for all 4 different queries?
Categories: DBA Blogs

Failed to start The nginx HTTP and reverse proxy server on RedHat EC2 Linux

Pakistan's First Oracle Blog - Tue, 2021-10-19 21:55

 I needed a simple reverse proxy to redirect connections to a RDS database in a private subnet, so I quickly created a Redhat Linux EC2 instance, installed NGINX, and setup the nginx.conf file for session redirection. My nginx.conf looked like following:

user nginx;

worker_processes auto;

error_log /var/log/nginx/error.log;

pid /run/nginx.pid;

include /usr/share/nginx/modules/*.conf;

events {

    worker_connections 1024;

}

stream {

    upstream target_server {

        server targetdb:1521;

    }

    server {

        listen 1521;

        proxy_pass target_server; }

}


But starting ngnix process was giving following error:


[root@test nginx]# systemctl start nginx

Job for nginx.service failed because the control process exited with error code. See "systemctl status nginx.service" and "journalctl -xe" for details.

[root@test nginx]# systemctl status nginx.service
● nginx.service - The nginx HTTP and reverse proxy server
   Loaded: loaded (/usr/lib/systemd/system/nginx.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Wed 2021-10-20 13:40:57 AEDT; 5s ago
  Process: 14702 ExecStartPre=/usr/sbin/nginx -t (code=exited, status=1/FAILURE)
  Process: 14700 ExecStartPre=/usr/bin/rm -f /run/nginx.pid (code=exited, status=0/SUCCESS)

Oct 20 13:40:57 test systemd[1]: Starting The nginx HTTP and reverse proxy server...
Oct 20 13:40:57 test nginx[14702]: nginx: [emerg] unknown directive "stream" in /etc/nginx/nginx.conf:9
Oct 20 13:40:57 test nginx[14702]: nginx: configuration file /etc/nginx/nginx.conf test failed
Oct 20 13:40:57 test systemd[1]: nginx.service: control process exited, code=exited status=1
Oct 20 13:40:57 test systemd[1]: Failed to start The nginx HTTP and reverse proxy server.
Oct 20 13:40:57 test systemd[1]: Unit nginx.service entered failed state.
Oct 20 13:40:57 test systemd[1]: nginx.service failed.

Solution: Just install nginx-mod-stream

[root@test nginx]# ls -ltr /usr/lib/nginx/modules/ngx_stream_module.so
ls: cannot access /usr/lib/nginx/modules/ngx_stream_module.so: No such file or directory
[root@ip-10-219-40-147 nginx]# yum install nginx-mod-stream

Now if you start nginx service, it should work.
Categories: DBA Blogs

Out Parameter using the scheduler

Tom Kyte - Tue, 2021-10-19 11:46
Hi, There is no test case needed, it's more of a question. We have a stored proc which on success of the execution of the stored proc there is either a 1 or 0 returned. Can the 1 or 0 be captured by the oracle scheduler? Thanks Vic
Categories: DBA Blogs

My Posts on Standby Database[s] -- Data Guard

Hemant K Chitale - Sun, 2021-10-17 04:10
Although I have, in previous posts, covered Data Guard in 12c (and 12cR1 RAC), this is a list of recent Blog Posts on Data Guard. 





















Categories: DBA Blogs

My Posts on Database Flashback

Hemant K Chitale - Sun, 2021-10-17 03:43

 

My Blog Posts on Database Flashback

(The first 5 demonstrations are in 11gR2.  The last demonstration is in 19c)


(not in the series)



Categories: DBA Blogs

Basic DDL Replication with Oracle GoldenGate

DBASolved - Sat, 2021-10-16 19:40

With any type of replication configuration or replication tool, primary purpose is to move the data as transactions are committed […]

The post Basic DDL Replication with Oracle GoldenGate appeared first on DBASolved.

Categories: DBA Blogs

Help to understand this recursive query

Tom Kyte - Thu, 2021-10-14 22:06
Hi Tom, I am not able to fully understand below recursive query. Especially, the condition "WHERE POS > 0" and " WHERE POS = 0". my understanding is pos is result of REGEXP_INSTR (STR, '[A-Z][0-9]{5}',1, RN). 1. if the first POS > 0, how could the final condition has pos = 0. 2. what's difference between pos > o and regexp_instr(str, '[A-Z][0-9]{5}', 1, rn)>0. regexp_instr(str, '[A-Z][0-9]{5}', 1, rn)>0 doesn't give expected result. thanks in advance. <code>WITH T AS (SELECT '///2E/A12345/E53421$$@#/A123456*#/A123456**4E53421$$$$$@@!!!' STR FROM DUAL UNION ALL SELECT 'ABC' FROM DUAL UNION ALL SELECT 'ABC67890' FROM DUAL UNION ALL SELECT 'ABC67890XYZ345679' FROM DUAL UNION ALL SELECT 'ABC67890XYZ345679UVW384453' FROM DUAL UNION ALL SELECT 'ABC67890XYZ345679UVW384453' || 'ABC67890XYZ345679' FROM DUAL UNION ALL SELECT 'ABC67890' || 'ABC67890' || 'ABC67890' || 'ABC67890' || 'ABC67890' || 'ABC67890' || 'ABC67890' FROM DUAL), TMP (RN, POS, STR, STR0) AS (SELECT 1, 1, STR, STR FROM T UNION ALL SELECT RN + 1, REGEXP_INSTR (STR, '[A-Z][0-9]{5}', 1, RN), REGEXP_REPLACE (STR, SUBSTR (STR, REGEXP_INSTR (STR, '[A-Z][0-9]{5}', 1, RN), 6), '', REGEXP_INSTR (STR, '[A-Z][0-9]{5}', 1, RN) + 6), STR0 FROM TMP WHERE POS > 0) SELECT * FROM TMP WHERE POS = 0</code>
Categories: DBA Blogs

begin_time/end_time in DBA_HIST_SNAPSHOT vs DBA_HIST_SYSMETRIC_SUMMARY vs AWR report

Tom Kyte - Wed, 2021-10-13 09:26
Hello Connor & Chris :-), I have some confusion about the <b>begin_time/end_time</b> in <b>DBA_HIST_SNAPSHOT</b> vs <b>DBA_HIST_SYSMETRIC_SUMMARY</b> vs <b>AWR report</b>. <code> 13:52:22 SYS@emcdb> desc dba_hist_snapshot Name Null? Type ----------------------------------------- -------- ---------------------------- SNAP_ID NOT NULL NUMBER DBID NOT NULL NUMBER INSTANCE_NUMBER NOT NULL NUMBER STARTUP_TIME NOT NULL TIMESTAMP(3) <b>BEGIN_INTERVAL_TIME NOT NULL TIMESTAMP(3) END_INTERVAL_TIME NOT NULL TIMESTAMP(3)</b> FLUSH_ELAPSED INTERVAL DAY(5) TO SECOND(1) SNAP_LEVEL NUMBER ERROR_COUNT NUMBER SNAP_FLAG NUMBER SNAP_TIMEZONE INTERVAL DAY(0) TO SECOND(0) BEGIN_INTERVAL_TIME_TZ TIMESTAMP(3) WITH TIME ZONE END_INTERVAL_TIME_TZ TIMESTAMP(3) WITH TIME ZONE CON_ID NUMBER </code> <code> 13:54:21 SYS@emcdb> desc dba_hist_sysmetric_summary Name Null? Type ----------------------------------------- -------- ---------------------------- SNAP_ID NOT NULL NUMBER DBID NOT NULL NUMBER INSTANCE_NUMBER NOT NULL NUMBER <b>BEGIN_TIME NOT NULL DATE END_TIME NOT NULL DATE</b> INTSIZE NOT NULL NUMBER GROUP_ID NOT NULL NUMBER METRIC_ID NOT NULL NUMBER METRIC_NAME NOT NULL VARCHAR2(64) METRIC_UNIT NOT NULL VARCHAR2(64) NUM_INTERVAL NOT NULL NUMBER MINVAL NOT NULL NUMBER MAXVAL NOT NULL NUMBER AVERAGE NOT NULL NUMBER STANDARD_DEVIATION NOT NULL NUMBER SUM_SQUARES NUMBER CON_DBID NUMBER CON_ID NUMBER </code> I understand that there have begin/end snap time in an AWR report, is it like this, begin_snap_time (AWR) = begin_interval_time (DBA_HIST_SNAPSHOT) and end_snap_time (AWR) = begin_interval_time (DBA_HIST_SNAPSHOT)? or begin_snap_time (AWR) = begin_time (DBA_HIST_SYSMETRIC_SUMMARY) and end_snap_time (AWR) = end_time (DBA_HIST_SYSMETRIC_SUMMARY)? You know, since a snap_id has a begin/end_interval_time but an AWR report usually captures between two snap_id, so now I'm not sure where is the begin/end snap time in an AWR report? Best Regards Quanwen Zhao
Categories: DBA Blogs

Database link : Relation of sessions between databases

Tom Kyte - Mon, 2021-10-11 21:06
Hello, In the context of database link, is there a possibility to get the relation between both sessions of database "A" and database "B"? (database "A" query objects from database "B" using a dblink) In other words, from database "B" can we retrieve the SID of database "A" that create the session in database "B" (and vis versa, from database "A" the SID of database "B" that is created for the dblink)? I hope that my question is understandable. Thank you very much, Sebastien.
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs