Tom Kyte

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

we are facing issue with the temp table returning data

Fri, 2017-12-15 16:46
Hi TOM Below is our issue Data in temp table should be deleted once procedure successfully executed but we are still getting old data from temp table. Query - ---------- Declare getdatabasetype SYS_REFCURSOR; l_databasetype varchar(200); BEG...
Categories: DBA Blogs

Required Remote Connect Privilege for Schema

Fri, 2017-12-15 16:46
I have installed Oracle 11g database and created sample schemas like hr, oe etc. Now I'm unable to access hr schema from different machine and it says insufficient privileges if I try to connect but I'm able to connect sys user from different mach...
Categories: DBA Blogs

LISTAGG 4000 CHAR restriction

Fri, 2017-12-15 16:46
I am using LISTAGG(card_name, ',') WITHIN GROUP(ORDER BY CardId) CARD_IDS to get the comma separate string. Since there is more data and concatenate data legth exceeds 4000 CHAR due to which LISGADD is failing. My need is to concate strings upto...
Categories: DBA Blogs

acknowledge over PGA limit at the top event in 12c

Fri, 2017-12-15 16:46
Hi, We are noticing this wait event (acknowledge over PGA limit) at the top after upgrade from 11g to 12c. We have already tried the workaround to set PGA_TARGET_AGGREGATE to 0 and this wait event still comes up at the top. Is this a problem to ha...
Categories: DBA Blogs

Pass Bind Variable Into Partition Keyword

Fri, 2017-12-15 16:46
Hi, I've created a simple example of what I'm trying to do (below). Basically, I'm wondering if I can tell Oracle to treat &var1 as a bind variable so that a profile can be attached to this SQL statement. Is that possible? If so, can you guide ...
Categories: DBA Blogs

How to create a trigger for a table and multiply the values inserted into the table by a function.

Fri, 2017-12-15 16:46
Dear Tom My requirement is.. I need to create a trigger for a empty table, where the data will be inserted into the columns. i need to multiply the inserting values by a function which is already created and the result should be stored i...
Categories: DBA Blogs

Difference between Lag and First_value

Fri, 2017-12-15 16:46
Hi Tom, I just want to know the difference between the below two queries. Lets say i have a table called member : SQL> select member_id,member_first_name from member; MEMBER_ID MEMBER_FIR ---------- ---------- 1 A 2 B 3 C ...
Categories: DBA Blogs

PLW-07206 compiler warning when assign parameter value to local date value

Fri, 2017-12-15 16:46
Please look at this small function: <code>create or replace function next_day_start ( p_date in date ) return date authid definer is l_date date not null:=p_date; begin return 1 + trunc(l_date); end;</code> The idea to assign th...
Categories: DBA Blogs

Set autotrace on

Thu, 2017-12-14 04:06
Hi Tom When I enter the statement set autotrace on i get the following error. SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled SP2-0611: Error enabling STATISTICS report How can solve it?
Categories: DBA Blogs

hash join

Thu, 2017-12-14 04:06
Hi Tom , I have your book and am still not able to understand the mechanism of hash join . it just says similar to this ( I do not have book at work so can not exactly reproduce it ) one table would be hashed into memory and then the other table...
Categories: DBA Blogs

SQL Loader - Load CSV file, double quoted record, dual double quoted field

Thu, 2017-12-14 04:06
Hello, Please tell me how to write the control file to load following data (without quotes) using sqlldr in three separate data fields. We get such a csv file from out client and they can't change the way it generates. This file opens fine in M...
Categories: DBA Blogs

ORA-29471: DBMS_SQL access denied

Thu, 2017-12-14 04:06
I have a problem in procedure: procedure .. begin v_c := dbms_sql.open_cursor; v_c2 := dbms_sql.open_cursor; dbms_sql.parse(v_c, v_sql, dbms_sql.native); dbms_sql.parse(v_c2, v_query, dbms_sql.native); v_stmt := dbms_sql.execute(...
Categories: DBA Blogs

Error in Opening KeyStore in Oracle 12c R2

Thu, 2017-12-14 04:06
I am trying to create TDE example . I was able to create a keystore ; but when I open the key store , I am getting ORA-28367. What am I missing here ? Thanks very much in advance. <code> SQL> select * from v$version ; BANNER ...
Categories: DBA Blogs

Oracle 12c - SQL query with inline PL/SQL function

Thu, 2017-12-14 04:06
I'm playing around with Oracle 12c and trying out the new features. One of the new features is that the WITH clause in a SQL query now allows for a PL/SQL function. I created the following sample query: <code> WITH FUNCTION get_number RE...
Categories: DBA Blogs

How do I determine where the ora_home directory is using SQL or PL/SQL?

Thu, 2017-12-14 04:06
I tried the following: <code> DECLARE theresult varchar2(1000); begin dbms_system.get_env('ORACLE_HOME', theresult); dbms_output.put_line('result = ' || theresult); end; </code> but got the following error <code>line 2: ORA-0655...
Categories: DBA Blogs

nls_date_format difference between v$parameter and database_properties

Thu, 2017-12-14 04:06
When I query v$parameter, nls_date_format is YYYY-MM-DD When I query database_properties, nls_date_format is DD-MON-RR Why is it different? Could this cause problems?
Categories: DBA Blogs

RMAN Parallelism question

Wed, 2017-12-13 09:26
Tom, I am reading Oracle? Database Backup and Recovery Advanced User's Guide, 10g Release 2 (10.2), Part Number B14191-02,http://download-east.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmconfg003.htm#sthref521 The document says: 1) "As a...
Categories: DBA Blogs

partitioned index

Wed, 2017-12-13 09:26
Hi Tom, please explain the differences between global partitioned index and local partitioned index. Thanks!
Categories: DBA Blogs

How to verify the parallel execution in a custom User-Defined Aggregate Function

Tue, 2017-12-12 15:06
I want to verify the parallel execution in my User-Defined Aggregate Function. So I put some DBMS_OUTPUT inside the code - but it seems not to work correctly... You can reproduce the behaviour by simple create the example user-defined aggregate fu...
Categories: DBA Blogs

How to concatenate string having length greater than 4000 characters

Tue, 2017-12-12 15:06
I am using xmlagg function to concatenate records with comma separated values. But i am getting error when columns count is than 300 I getting below error <code>Error starting at line : 8 in command - select rtrim (xmlagg (xmlelement (e...
Categories: DBA Blogs

Pages