Tom Kyte

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

trigger body

Sun, 2016-11-06 10:26
Hi Tom, I want to search a trigger body for some particular text and would like to know the names of all such triggers... select * from user_triggers where trigger_body like '%CONSULTATION%' My Oracle Version is 10G Rel 1. Now my problem i...
Categories: DBA Blogs

Mechanism to migrate and two-way sync database

Fri, 2016-11-04 22:46
Hi Tom I have two schemas in different physical servers. The data structures and data are different because of poor data design. I want to migrate data from one server to another server and then two-way sync databases. In two-way sync, I pla...
Categories: DBA Blogs

Listener working without listener.ora

Fri, 2016-11-04 22:46
I have an oracleDB working en production for more than a year. I have my listener working but NO listener.ora file. <code>[oracle@base admin]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST...
Categories: DBA Blogs

Ora 22288 while trying to wirte an XML file to a clob column

Fri, 2016-11-04 22:46
Hi Tom, I am trying to put the contents of an xml into a clob column of a table in database using a sql query and am not being able to do so. I am getting <ORA-22288: file or LOB operation FILEOPEN failed ORA-06512: at "SYS.XMLTYPE", line 296 OR...
Categories: DBA Blogs

Unsafe behavior when using functions results in SELECTs and Views

Fri, 2016-11-04 22:46
Hello, I post you my issue after Oracle support says there is no 'great' workaround. It seems to be non documented and only oracle performance teams where informed of this 'expected' behavior (in their opinion). To summarize the problem : W...
Categories: DBA Blogs

bind variables

Fri, 2016-11-04 22:46
Hello, I created bind variables in a dataset and would like to reference them in a second dataset using "where 1=1". what do I need to put after each variable in the first for the reference to hold?
Categories: DBA Blogs

loop in plsql

Fri, 2016-11-04 22:46
Hi, Is loop (for,while) creates implicit or explicit cursor ? To me, it looks like a implicit cursor because it open the memory area implicitly. But the definition of implicit cursor is any SELECT and DML operation defined a implicit cursor. C...
Categories: DBA Blogs

Scope of Savepoint

Fri, 2016-11-04 22:46
1. How to access savepoint which is created in another procedure ? 2. Can we rollback only the records processed between two save points ?
Categories: DBA Blogs

Row Locking on Insert

Fri, 2016-11-04 22:46
Hello team, I want to discuss an Issue of Locking occur on Insert in my Table Issue is that I have a Table A with Primary Key on Column 1, foreign key on Nullable Column 2 with out any Index. Table B is having foreign key of Table A P.key w...
Categories: DBA Blogs

Sql scripts

Fri, 2016-11-04 04:26
Hi , can we call sql scripts containing procedures and packages without a SQL*plus. As of now we are running some scripts through sql*plus command line. So if we want to run these scripts from a system where they don't have sql plus installed is t...
Categories: DBA Blogs

SQL QUERY

Fri, 2016-11-04 04:26
hello, i have two tables DEPARTMENT_DETAILS(deptno,dname,loc) and EMPLOYEE_DETAILS(empno,ename,job,sal,mgr,comm,deptno) with having data 5 records in department_details and 14 records in employee table my query is that.... i wnat to display th...
Categories: DBA Blogs

SCAN IP

Fri, 2016-11-04 04:26
Can SCAN resolve to more than three IP address ?If so why we use only three ?What are the theory behind keeping these three IPs?
Categories: DBA Blogs

DBMS_COMPRESSION.get_compression_ratio ERROR on NON EXA platform

Fri, 2016-11-04 04:26
Hello Tom, I'm trying to use DBMS_COMPRESSION.get_compression_ratio on my AIX box to estimate HCC storage savings if I move to an Exadata platform. MOS notes & many public links says it is possible to use the advisor on NON EXA platforms like my ca...
Categories: DBA Blogs

Deletion from a atble having millions of records

Fri, 2016-11-04 04:26
Hi Tom, I have a table having 115 columns and 25 foreign key constraints having millions of records. I am running a deletion script from the table based on one field in that table. An index is created for the same field and also the table is non-p...
Categories: DBA Blogs

How to do migrate Oracle RAC standard to enterprise edition to a new server?

Fri, 2016-11-04 04:26
My current database server is Oracle RAC standard edition. I need to migrate my current database to Oracle RAC enterprise edition on a new server. How to do this? Could I use RMAN backup from standard edition and restore to enterprise edition on th...
Categories: DBA Blogs

How to syncronize Active-Active-Active databases

Fri, 2016-11-04 04:26
The customer need a system archietecture with 3 sites with all ACTIVE. My question here is how to do Real Time synchronization of Active-Active-Active topology. Active-Active we do based on archive logs. But with 3 Active it is very complex. Could ...
Categories: DBA Blogs

Using GUIDs as primary keys

Fri, 2016-11-04 04:26
Hello Tom, I have a question on using GUIDs as primary keys. Our application is being developed on 11gR2 with a 3-node RAC. The development team has introduced a new column to almost all of the tables which is technically a UUID generated by the app...
Categories: DBA Blogs

Does UTL_MAIL have an attachment limit of 32k

Fri, 2016-11-04 04:26
Tom, We have a requirement to send email with attachments using pl/sql. We will be querying data from the database, creating a file and attaching it to an email. I been reading about the UTL_MAIL send_attach_varchar2 procedure to send emails, ...
Categories: DBA Blogs

Invalid number error

Thu, 2016-11-03 10:06
I want to check whether there exist a negative duration or not in table \query:-select Emplid,effdt from batchhr.tr_time_mar_intrfce_tmp where SIGN(NVL( ttef_day,0) )= 2; error is showing invalid number where the data type of ttef_day is varch...
Categories: DBA Blogs

FAST refresh materialize view

Thu, 2016-11-03 10:06
Hi, First of all apologies for not putting test case in LiveSQL. I tried but got error ORA-06550: line 1, column 9: PLS-00201: identifier 'SYS.DBMS_SNAPSHOT_UTL' must be declared ORA-06550: line 1, column 9: PL/SQL: Statement ignored whil...
Categories: DBA Blogs

Pages