Tom Kyte

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

Can I declare the record variables dynamically

Fri, 2016-08-12 23:46
Hi Tom , I am writing a procedure for field to field comparison of tables. Create or Replace procedure data_check AS type all_tabs is table of table of all_tables.table_name%type; v_tab_stg all_tabs := all_tabs(); v_tab_pstg all_tabs := all_t...
Categories: DBA Blogs

sql ,plsql

Fri, 2016-08-12 23:46
Is oracle support on update cascade and on delete cascade in child table? e.g I want to delete the particular value from parent table column which primary key column and this column have relationship with child table column so if try to delete ...
Categories: DBA Blogs

Purging Partition and subpartitions

Fri, 2016-08-12 23:46
Hi, We have a table with 120 partitions and we create around 3500 sub partitions in total every month. Not all subpartitions are having data in it eg (weekends, holidays). But over the period these subpartitions are increased to 70000. And now thi...
Categories: DBA Blogs

Presenting table data into a different format

Fri, 2016-08-12 23:46
I have a table with the following sample data: Branch Department ======== ========== Sydney Sales Sydney Research Sydney Finance London Sales New York Sales New York IT How do I present this data in the table as this format: B...
Categories: DBA Blogs

Database Design

Fri, 2016-08-12 23:46
Tom: 1. I am designing a database for order entry. I have a table PO (po_no, po_date, po_status) and another child table (po_no, stock_no,stock_desc,qty, unit). Because a user may not find a stock_no in a reference table (for all stock numbers)...
Categories: DBA Blogs

SQL*Net message from client for MView refresh

Fri, 2016-08-12 23:46
Hi tom, I am getting wait events with SQL*Net message from client continuously while I am doing mview refresh. Now here while refreshing the mview it is accessing the data through db link & source db & destination db are on same server. So can u ...
Categories: DBA Blogs

How to copy data in XMLTYPE type (or CLOB) over 32K in a local table into a LONG type column in a remote table

Fri, 2016-08-12 23:46
The purpose is to copy data contained in XMLTYPE type column residing in a local table into a LONG type column in a remote table. Our current PLSQL (package) code first selects XMLTYPE column into a XMLTYPE variable (lv_xml_doc), converts into CLOB a...
Categories: DBA Blogs

Oracle.

Fri, 2016-08-12 23:46
Sir this is my questions ************************ I have a sourcetableA with 10 records....and target tables are like(table1...table2....table5) id 1 2 3 4 5 . . 10... i wnat load 2 records into table1 and next 2 records into ta...
Categories: DBA Blogs

Freeing up space

Fri, 2016-08-12 23:46
Hi, I have a quick question that doesn't require a test script or anything, if I may? I have a table that is partitioned by date. Each year has it's own ASSM locally managed tablespace. The dates go back as far as 2006. There is a BLOB which ho...
Categories: DBA Blogs

Informational Error ORA-01013

Fri, 2016-08-12 05:26
Hi, This is my question : I need to log in a table in my database every action be made on a script, even like sudden interruptions such us ctrl+c. The problem is that i don't know how to catch ORA-01013 ( which is the code for abrupt interruptions...
Categories: DBA Blogs

tns and listener error

Fri, 2016-08-12 05:26
Hi tom, In our oracle database setup frequently getting error like - Fatal NI connect error 12537, connecting to: (LOCAL=NO) VERSION INFORMATION: TNS for Linux: Version 11.2.0.1.0 - Production Oracle Bequeath NT Protoco...
Categories: DBA Blogs

Recursive subquery factoring for a table function

Fri, 2016-08-12 05:26
Hello Sir, I have this table function call : select * FROM TABLE ( MOD_GENERATOR.PKG_GENERATOR_XREF.refcur_to_table_func ( MOD_GENERATOR.F_GENERATOR_XREF (trunc(sysdate)) )) Now I have to call this 24 times ever...
Categories: DBA Blogs

Restoration from incremental backup

Fri, 2016-08-12 05:26
Hi, I have Sunday full backup and then Monday Tuesday Wednesday incremental level 1 back-up and last 7 days archive now I want to restore all this to my Restoration server to check backup consistency So 1.which backup pieces I need to move on Re...
Categories: DBA Blogs

index-by table/associative array

Fri, 2016-08-12 05:26
Can you join a PL/SQL collection like an index-by table/associative array to a regular table? If not, what is a good technique for matching data in both sets? Thanx, Don
Categories: DBA Blogs

Moving XML data from BASICFILE CLOB to BINARY XML

Thu, 2016-08-11 11:06
Currently I have table which stores xml in XMLType column which internally stores XML in BasicFile Clob,I have to migrate the XML data to store XMLType column which stores XML in secure file Binary XML. I have tried using examples given in link ht...
Categories: DBA Blogs

select multiple columns having non default value

Thu, 2016-08-11 11:06
Hi, I have a table like below: create table test ( test_id number not null, employee_id number not null, type varchar2(10), col1 number default -1, col2 number default -1, col3 number default -1, col4 number default -1, primary key(test_...
Categories: DBA Blogs

Tuning SQL not in v$sql with sqltrpt.sql

Thu, 2016-08-11 11:06
Hi tom, I have not very much in performance tuning but due to learn i am taking the performance related query also from user end. For tuning i have generated awr report and get top 10 queries and take the execution plan of that and generate ...
Categories: DBA Blogs

Oracle Proxy User & database User Connections

Thu, 2016-08-11 11:06
Is there a way to create a database user that is only allowed to connect via a proxy user? A logon trigger to check how a user connected would work but I was wondering if there was a user or profile setting that could be set so a logon trigger woulf...
Categories: DBA Blogs

Tips on creating temporary table

Thu, 2016-08-11 11:06
Hi Tom, First of all, I would like to thank you for all the contribution you made on the oracle community. I started working on oracle DB 9 years ago, and I can confidently say that I got all my knowledge from asktom :) I have followed your blog f...
Categories: DBA Blogs

How to do a combination to sum to find all the options up to a certain value

Thu, 2016-08-11 11:06
Hello TomConChris I can have up to 7 (in reality 50) values, and I must sum all the combination and choose all the combination that get an amount In example All the combinations with the numbers to get a pizza of 5 kg. option 1 5Kg, Option 2 3K...
Categories: DBA Blogs

Pages