Tom Kyte

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

datapump export using DBMS_DATAPUMP

Mon, 2016-06-27 11:06
Good afternoon, it is a beginning programmer. I had a problem with DBMS_DATAPUMP.data_filter. Data_filter not work. create PROCEDURE exp_tables_w_qfilter ( p_schema_name IN VARCHAR2, -- 'LTP' p_table_name IN VARCHAR2, -- 'AUDITTABLE_TEST' p_t...
Categories: DBA Blogs

Transpose Rows into Columns

Mon, 2016-06-27 11:06
I have a table like: Client Process Date Status A B 21-june Y C D 22-june N A B 22-june N...
Categories: DBA Blogs

2 highest salary department wise ..alternate method

Mon, 2016-06-27 11:06
<b>select * from employees e1 where :parameter=(select count(distinct e2.salary) from employees e2 where e1.salary<=e2.salary);</b> --<i>hi can anyone explain me this query .i know this can be solve using rank,dense_rank but confused while ...
Categories: DBA Blogs

Oracle External Table ODCIEXTTABLEOPEN error while selecting count(*) of the table

Sat, 2016-06-25 04:06
Hi I am explaining from beginning, My Server is : UNIX BOX (HPSA) I have created the below folder in this UNIX server and folder permissions are mm13pb:/ $ ls -ltr drwxr-xr-x 7 root root 4096 Dec 15 2013 data mm13p...
Categories: DBA Blogs

Unused Index(s) for a specific Time?

Sat, 2016-06-25 04:06
<code>Hi Tom, I want to drop those indexes which are not used in the last 2 months so to reclaim space. For example There are two indexes on scott.emp on the empno and the other is on deptno. While the application is only using empno index but no...
Categories: DBA Blogs

SP2-0734 when try to execute line begins with "#" character

Fri, 2016-06-24 09:46
Hi, I need to execute script (package body) that contents a line with first character "#", for example: ... ... htp.p(' <script> <style> #tbTabs tr td table Port ... ... When execute line "#tbTabs tr td table Port", this raises the error:...
Categories: DBA Blogs

...

Fri, 2016-06-24 09:46
...
Categories: DBA Blogs

Confusing Genesis of Cursor

Fri, 2016-06-24 09:46
Hi Connor I am going through the book "Troubleshooting Oracle Performance" by Christian Antognini. The definition below are confusing. Open cursor :- A memory structure for the cursor is allocated in the server-side private memory of the ser...
Categories: DBA Blogs

Merge statement.

Fri, 2016-06-24 09:46
We have two tables A, B. in A table ID is matching columns in bother the tables. ID REF NO NAME 0434 98 93 ABC 0423 18 61 XYZ 4875 474 45 tyh In B t...
Categories: DBA Blogs

Role of undo in instance recovery

Fri, 2016-06-24 09:46
Hi Tom, I have a doubt regarding the role of undo segments in instance recovery, it is clear that the roll forward operation also generates the undo segments in the undo tablespace but I want to understand from where it gets the past image of the ...
Categories: DBA Blogs

Creating triggers on (tables in) another schema

Fri, 2016-06-24 09:46
In Oracle you can have triggers owned by schema x on tables of schema y. Unfortunately there seems to be no fine grained way to authorize this - user x needs CREATE ANY TRIGGER privilege. (contrary to the similar situation with indexes, user y can...
Categories: DBA Blogs

read consistency how to maintain which update record

Fri, 2016-06-24 09:46
hi can you explain when one update statement access a record and select statement want to access the same record , lets assume update takes 10 min(till not committed). what will happen to select statement. select statement can access previous image...
Categories: DBA Blogs

Top N order by query is slow

Fri, 2016-06-24 09:46
Hi Tom, I'm trying to retrieve the last 20 rows by time for users with more than 100K rows in a table with more than 1 million records. The query performance is fine (in ms) when the user has a small number of records. But takes more than 2 minute...
Categories: DBA Blogs

Unable to Retrieve sys_refcursor values from remote function

Thu, 2016-06-23 15:09
Hi, i have created a function in DB1 that returns a sys_refcursor as output which is giving the result as desired in DB1. But when other database DB2 is trying to execute the function using dblink, that cursor is not returning any values. It is no...
Categories: DBA Blogs

Want to skip record if it's length not matching with required length while loading data in oracle external table

Thu, 2016-06-23 15:09
Hi Tom, I want to load data from fixed length file to oracle external table. I have specified length for each column while creating external table so data for most records getting loaded correctly. But if record length dosent match then data gets...
Categories: DBA Blogs

how to use Connection String in VB.NET using Oracle Wallet ?

Thu, 2016-06-23 15:09
In vb.net we could use following connection string but i recently do practical on oracle wallet done successfully in SQL PLUS Tools but main question is i want to use this connection string (username and password and tnsping) using oracle wallet sto...
Categories: DBA Blogs

Tree and "Youngest Common Ancestor"

Thu, 2016-06-23 15:09
<code>Hello Tom, I could finally ask you a question... I have a table like this: create table tree(name varchar2(30), id number, pid number, primary key(id), foreign key(pid) references tree(id)); with sample data: insert into tree va...
Categories: DBA Blogs

How to split comma seperated column of clob datatype and insert distinct rows into another table?

Thu, 2016-06-23 15:09
Hi, I need to split the comma separated values of clob datatype column in one table and insert only the distinct rows in another table. The details of the table are given below. The toaddress column in Table A is of datatype CLOB. Table B has ...
Categories: DBA Blogs

Maintaining Partitioned Tables

Thu, 2016-06-23 15:09
<code>Hi Tom, I need to build a table that will hold read-only data for up to 2 months. The table will have a load (via a perl script run half hourly) of 3 million new rows a day. Queries will be using the date col in the table for data eliminati...
Categories: DBA Blogs

Handling ORA-12170: TNS:Connect timeout occurred & ORA-03114: not connected to ORACLE failures

Thu, 2016-06-23 15:09
Hi Tom, We had a scenario where the sqlplus connection failed with "ORA-12170: TNS:Connect timeout occurred" in one instance & "ORA-03114: not connected to ORACLE" in another instance while executing from a shell script, but in both the cases retu...
Categories: DBA Blogs

Pages