Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 8 hours 15 min ago

DBMS_AQ.DEQUEUE

Thu, 2018-09-06 17:06
Hi Tom, Can you please explain me difference between dequeue options dbms_aq.remove and dbms_aq.remove_nodata using in dbms_aq.dequeue. 2. Does dbms_aq.dequeue generate more redolog log that normal delete statement. Is it advisable to hav...
Categories: DBA Blogs

connecting database in network

Thu, 2018-09-06 17:06
Hi, I have installed oracle 11g and created database name XYZ in machine A Two systems are connected in same network which is reachable by ping ip address port is also open How B machine will access the DB XYZ on A machine ?? Thanks ...
Categories: DBA Blogs

Heavy swapping in database

Thu, 2018-09-06 17:06
Hi Oracle professionals, Please advice us and give right direction. We have database on 12c on OS Red Hat Enterprise Linux Server release 6.10 with memory settings: SGA: 4928M PGA: 1250M OS memory: 8GB CPU q-ty: 2 Huge Pages disabled. I...
Categories: DBA Blogs

v$bh@oracle vs innodb-buffer-page@mysql

Thu, 2018-09-06 17:06
From https://docs.oracle.com/cd/E17952_01/mysql-5.6-en/innodb-information-schema-buffer-pool-tables.html warning that Querying the INNODB_BUFFER_PAGE or INNODB_BUFFER_PAGE_LRU table can can affect performance. From the source code i can find th...
Categories: DBA Blogs

execute command scp from inside the database

Wed, 2018-09-05 22:46
good day Tom - I am writing to you because I am having problems with a development where I need to access a linux server to extract files dynamically and place them on my windows server using the scp command supported in a java library. but it is ...
Categories: DBA Blogs

How oracle identifies the "Dirty Blocks"

Wed, 2018-09-05 22:46
Hello Oracle Masters, I have a very basic question to understand the internal behavior of oracle. I read couple of blogs and unfortunately end up in confusion; So thought of asking you .. Assume, i m updaing a block (a record) and not issued...
Categories: DBA Blogs

Regexp_Replace Help- Column Level Data Masking

Wed, 2018-09-05 04:26
Hi Tom, I have this requirement but regexp_replace is behaving odd here. It gives me all NULL Requirement for function- Suppose a number 9845-089160 ? function logic should convert one digit at a time and not entire number in one go. ? First ...
Categories: DBA Blogs

Finding partitions of two tables within the same date range

Wed, 2018-09-05 04:26
Hi Tom, How can I find partitions (PARTITION BY RANGE (DATE) INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) of two tables within same date range (HIGH_VALUE) to merge those partitions? I have many tables and I am trying to create a script for it. Thanks...
Categories: DBA Blogs

Veem backup solution viability

Wed, 2018-09-05 04:26
Hi, My Windows System Administrator colleague wants to replace classical RMAN backup with a proprietary tool (VEEM). That tool apparently has some ability to take the snapshot backup of the database (see more https://www.veeam.com/blog/how-to-back...
Categories: DBA Blogs

How to check Oracle server operating system from SQL or PL/SQL?

Wed, 2018-09-05 04:26
During the installation of some application software, we build the necessary schema objects in Oracle if they don't exist. Additionally, we point out any potential problems to the user doing the install. Some of these problems vary with the Oracle se...
Categories: DBA Blogs

How to set escape for special characters inside contains block of select statement.

Wed, 2018-09-05 04:26
Scenario: I have a table called test1 with 2 columns say Id(NUMBER(10,0)), SearchData(CLOB). <code>Id SearchData 1 COLL-ETTE AKACOMMON 2 @#$!%*@#$!%*@#$!%*</code> Statement1: select * from test1 where contains (SEARCHDATA ,'\-ETTE AKACOMMO...
Categories: DBA Blogs

function that does not allow a non user to login to the application

Tue, 2018-09-04 10:06
Hello, There is a requirement for us where non users should be restricted from logging to the application in a trigger in Oracle forms which use PL SQL. This needs to be done using a new database function. I only have a developer access and not a...
Categories: DBA Blogs

ORA-22165: given index error encountered when adding a materialized view in a materialized view group

Tue, 2018-09-04 10:06
Hi, We dropped and re-created a materialized view group and would like to add it in a certain materialized view group but an ORA-22165 is encountered when trying to add the materialized view in an existing group. Can I ask how this is encountered?...
Categories: DBA Blogs

How to constrain an employees salary against his manager in the same table

Tue, 2018-09-04 10:06
I just want to create triggers for checking salary that employee salary cannot exceed manager's. there are one table: EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SALARY,DEPTNO) FK:MGR references EMPNO Thanks.
Categories: DBA Blogs

Deploying Oracle RAC 18c for Linux x86_64 on OL 7.5

Tue, 2018-09-04 10:06
Hello, teams These two days I've deployed Oracle RAC 18c on OL 7.5 (my Linux server) via VirtualBox 5.2.18. During this time I encountered some problems as follows, (1) after installing RPM "oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm",...
Categories: DBA Blogs

sql performance

Tue, 2018-09-04 10:06
Hi Tom,I have met a SQL that with the latest statistics and the execution plan is in the wrong estimate-rows ,that's to say,it's a large difference between the estimate and the actual,the sql statement and execution is below: <code>select t1.card_no...
Categories: DBA Blogs

SQLLDR with multiple when conditions

Tue, 2018-09-04 10:06
Hello, we have a requirement to load multiple data based on different conditions. The details are as below, Create: <code> create table table_a (empno number, empname varchar2(50),salary number, status varchar2(30)) / Control file: opt...
Categories: DBA Blogs

SYSTEM tablespace growing too huge

Mon, 2018-09-03 15:46
My System tablespace has grown to approx 15GB. Is that normal?? The top 3 space taking segments are as follows- I_HH_OBJ#_COL# 1.375 I_HH_OBJ#_INTCOL# 1.375 I_COL1 ...
Categories: DBA Blogs

ITL waits and deadlocks

Sun, 2018-09-02 21:26
Hi Tom, We are facing one issue where we are running jobs in parallel basically same procedure called with different parameters.Proc merges into a target table , so when we are trying to run in parallel. We are seeing enq : Tx ITL transaction wait. ...
Categories: DBA Blogs

lob storage options for partition table

Sun, 2018-09-02 21:26
Q1/IF I HAD TWO PARTITION (p1, p2). EACH PARTITION HAVE 3 tablespaces P1?Tsdata1,, p2? tsdata2 global fields data type P1?Tsidx1,, p2?tsidx2 for primry key index And P1?Tsblob1,, p2?tsblob2 for photo field How can I create table( tab_test) wi...
Categories: DBA Blogs

Pages