Tom Kyte

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

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

Shrink partition table

Tue, 2017-12-12 15:06
Hi, The shrink table doesn't defragments the partition tables. executed : alter table test_table_1 shrink space and checked with below query wasted space before and after but the values are identical. <code>select table_name,rou...
Categories: DBA Blogs

Imdp xml schema date format issue ORA-01858

Tue, 2017-12-12 15:06
Hi, I exported a schema from Oracle 11.2.0.1 and trying to import it in Oracle 12c. My Oracle schema contains xml schema and xmltype columns in a table. My xml fragment is <code><Tag0> <Tag1> <Tag2 Id="10202" date1="2017-11-15T13:36:34.00000...
Categories: DBA Blogs

Removal of Archive Files when using OS to backup offline DB

Tue, 2017-12-12 15:06
I have been thrown in at the deep end and given an Oracle DB to look after. I have no prior experience of Oracle so everything I am doing is new and a massive learning curve. The current DB data set is approx 400GB and we are working with the appl...
Categories: DBA Blogs

how to count the number of records in a file

Fri, 2017-12-08 16:06
How do i get to count the number of records in a flat file(.csv/.dat) including the header and trailer records.The code was return in a plsql using utl_file.Can you suggest me the best method to implement the logic in Plsql.And the trailer record sho...
Categories: DBA Blogs

advise given by segment advisor 12c

Fri, 2017-12-08 16:06
Hi Tom, My question is, Does <b>segment advisor in 12cR1</b>, advise the use of <b>online table redefination for tables</b> in both d<b>ictionary managed</b> and <b>locally managed tablespace</b>? In addition, What about the <b>use of segm...
Categories: DBA Blogs

Dynamic fields in External File

Fri, 2017-12-08 16:06
Sorry if its a weird requirement. I have an input feed coming in like the following(only two rows per file).. col1, col2, col3,Attribute_1,Attribute_2,Attribute_3,......,Attribute_n col1, col2, col3,Value_1,Value_2,Value_3,......,Value_n ...
Categories: DBA Blogs

Reconstruct sale from audit commands

Fri, 2017-12-08 16:06
We are running on 11.2.0.4, 12.1 and 12.2. SQL> show parameter audit_trail NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_trail ...
Categories: DBA Blogs

Pages