Tom Kyte

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

Defference

Fri, 2016-10-21 10:26
1) What is difference between conventional path load and direct path load? 2) when to use conventional path load? 3) when to use direct path load?
Categories: DBA Blogs

dbms_parallel_execute and 2 packages of mine !

Fri, 2016-10-21 10:26
Hi I've 2 PL/SQL packages. - First is dedicated to : * create a parallel task, * then to create chunks (by rowid), * and finally to execute (previous) created task by executing a second PL/SQL Package....
Categories: DBA Blogs

How to check if oracle directory points to the right location?

Fri, 2016-10-21 10:26
Hello! Is there a way to check if my directories look at the right folders on my network? ALL_DIRECTORIES view says the directory path is something like /d01/data/xfer/BLA/BLABLA/IN. The actual network location is something like \\xyz14311.bla.c...
Categories: DBA Blogs

Update rows using MERGE on rows that do not have a unique identifier

Thu, 2016-10-20 16:06
I have an external table that reads from a csv file. I then need to merge any updates or new rows to a table. The problem is the table does not have a unique identifier. I have account numbers and dates, but the date may get updated on an account ...
Categories: DBA Blogs

Handle individual UKs on bulk inserts

Thu, 2016-10-20 16:06
Hello, I need to execute bulk insert into a table where two columns have unique constraints. One column has native values (cannot be changed) and another one contains abstract pseudo-random value (I generate it myself but I cannot change the algorit...
Categories: DBA Blogs

Convert my rows in columns

Thu, 2016-10-20 16:06
Hi friends! I tried to use pivot, unpivot and other ways to return the expected, but i'm not successfully. <code>create table t1 (ID NUMBER(5), tp char(1), nm varchar2(5), st number(2), en number(2)); insert into t1 values (1,'A', 'a', 0, ...
Categories: DBA Blogs

How to use case statement inside where clause ?

Thu, 2016-10-20 16:06
I'm getting error-- PL/SQL: ORA-00905: missing keyword when i compile the following script create or replace procedure testing (ass_line in char, curs out sys_refcursor ) is begin open curs for select asl.Production_Group,asl.last_sequen...
Categories: DBA Blogs

Mixing 9s and 0s in format number

Thu, 2016-10-20 16:06
Hello, I'm a bit confused by SQL Refenrence Lets consider Elements 0 and 9 of "Table 2-14 Number Format Elements" of the current "SQL Reference" Lets also consider the emxamples of "Table 2-15 Results of Number Conversions" I have SO much to a...
Categories: DBA Blogs

Column level access restrictions on a table

Thu, 2016-10-20 16:06
Let's say I have a table T with columns A, B, C and D. Data in each column by itself is not considered sensitive, but a combination of columns A,B,C in the same resultset is considered sensitive. Is it possible to allow queries that select A,B,D or A...
Categories: DBA Blogs

Related to Job scheduler

Thu, 2016-10-20 16:06
Hi Tom, actually i created a job (it will run for every 5 mins)which will execute the procedure,In that procedure we are reading file list from directory and constructing a string which contains all the file names using java class. Here my prob...
Categories: DBA Blogs

Invalid identifier while merge using dynamic sql - static merge statement suceeds

Thu, 2016-10-20 16:06
Hi, I am facing a weird problem in which a merge statement using dynamic sql is failing with the below error while the static merge statement is suceeding. <b>Error:</b> ORA-00904: : invalid identifier ORA-06512: at line 60<u></u> Below is...
Categories: DBA Blogs

View with a CTE?

Wed, 2016-10-19 21:46
Can I create a view that uses a CTE?
Categories: DBA Blogs

Format for TRUNC(TIMESTAMP,<seconds>)

Wed, 2016-10-19 21:46
Is there a format specifier for TRUNCATE(timestamp,?) to seconds? The following all work: TRUNC(SYSTIMESTAMP,'MI') TRUNC(SYSTIMESTAMP,'HH') TRUNC(SYSTIMESTAMP,'MM') TRUNC(SYSTIMESTAMP,'YY') But TRUNC(SYSTIMESTAMP,'SS') and any variation I'v...
Categories: DBA Blogs

How to tackle 'ORA-14024: number of partitions of LOCAL index must equal that of the underlying table' error?

Wed, 2016-10-19 21:46
Hi, I have created a plsql program which does the following. 1) Create Backup tables using script from dbms_metadata.get_ddl utility. 2) Insert records from Main tables into backup tables. 3) Rename indexes on Main table 3) Create Indexes (W...
Categories: DBA Blogs

how to get regular updates on database space left and database used space in my table

Wed, 2016-10-19 21:46
how to get regular updates on database space left and database used space in my table . i have created a table with 3 columns , dbspacetotal & dbspaceused,dbspaceremaining. how to get data into these columns when i insert update or delete in my...
Categories: DBA Blogs

running application with an app_user instead of schema owner user

Wed, 2016-10-19 21:46
Hi I have a java application that requires access to oracle database with an app_user instead of schema owner user and this is for security purposes. - The schema owner user is the user that owns oracle objects that need to be accessed from j...
Categories: DBA Blogs

Database

Wed, 2016-10-19 21:46
i learn all sql and pl/sql concept very well. but still not working on any project. so my question is please give me project or idea to develop project in oracle(SQL and PL/SQL) which helps to improve my knowledge very well... please give me the prj...
Categories: DBA Blogs

Need help on dbms_scheduler

Wed, 2016-10-19 21:46
Hi Tom, I have a scheduler which is linked to my package. The package was running for long and hence I cancelled the task. Now when I try to run the package back, the scheduler is not running. I checked in "USER_SCHEDULER_JOB_LOG" AND THE...
Categories: DBA Blogs

SQL group by query shenanigans

Wed, 2016-10-19 03:26
Hi Chris or Connor, Saw you guys at OOW so I thought I toss a basic SQL query to you. I?ll use the HR.EMPLOYEES table to represent my problem so forgive me if it's a bit contrived. I?ll like to construct a query to sum the salaries grouped by ...
Categories: DBA Blogs

Grant Access on Table

Wed, 2016-10-19 03:26
I've given grant to one user on a table but user unable to access the table though I'm getting output as grant succeeded. The scenario is 1. There is one database A on remote location. 2. A is trying to access some table on database B(place on othe...
Categories: DBA Blogs

Pages