Tom Kyte

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

Massive Update

Tue, 2016-10-18 09:06
Dear Team, Kindly help me for below problems 1) Needs to update all the records of 1 billion record table. Only one column values to be updated to reverse the order (abcde -> edcba) 2) Needs to update records based on a column condition (column ...
Categories: DBA Blogs

Extract Logical Operators (AND/OR) from String

Tue, 2016-10-18 09:06
I want to help to extract Logical Operators (AND/OR) from string having n numbers of operators For Example: ((1=1 AND 1=1) OR 1=1) AND (1=1 AND 1=1)
Categories: DBA Blogs

MODEL CLAUSE

Tue, 2016-10-18 09:06
Hi? this my query I have a dictionary table create table D_CONFIG_TST ( row_name VARCHAR2(1500), -- text field with name of row row_number VARCHAR2(50), -- text field with row number for order row_array VARCHAR2(15...
Categories: DBA Blogs

Change Data Capture to get modified records of a table

Tue, 2016-10-18 09:06
Hello Tom, Hope you are doing good! Request your suggestions for the following scenario. We have a requirement wherein we have to migrate modified/delta data on a table(Modified by DML statements) from Oracle to MongoDb. To do this i have su...
Categories: DBA Blogs

DBMS_OUTPUT to Query output

Tue, 2016-10-18 09:06
Hello Tom, First thanks for helping me out. Few days ago you explained how to insert dbms_output into temporary tables or nested table. This was the example: This example works perfectly if I have write permission in the database, Is there a wa...
Categories: DBA Blogs

The output file alignment must be adjusted.

Tue, 2016-10-18 09:06
Hi Team, I executed shell script file. The shell script file has the below sql information in it. SET SERVEROUTPUT ON; SET LINESIZE 4600; #SET TRIMSPOOL ON; SET WRAP OFF; SET HEADING OFF; #SET TRIMOUT ON; SET TIMING ON; SET FEEDBA...
Categories: DBA Blogs

"Error occurred : ORA-01555: snapshot too old: rollback segment number 313 with name "_SYSSMU313_2192191193$" too small"

Tue, 2016-10-18 09:06
Hi Tom, I'm posting a email conversation between a DBA and a Technical lead in our organization. Is the answer you gave 15 years back on "ORA-01555" is still valid for the new oracle releases. <b>DBA --</b> <i>"This is due to long running qu...
Categories: DBA Blogs

truncate vs delete with constraints on tables

Mon, 2016-10-17 14:46
Tom, I haven't used constraints as much as this new project that I am on. Usually I truncate tables to clear them out as it frees up the space etc. Yet when I tried to truncate the tables in RF order, I received constraint errors and it took a ...
Categories: DBA Blogs

Question / Answer table structure design

Mon, 2016-10-17 14:46
Hi, We are building a facility that allows our customers to configure questions which there customers in turn can then answer. These questions can be configured in various ways which determines what is / is not a valid answer and importantly (to t...
Categories: DBA Blogs

Find all queries in application that use string literal

Mon, 2016-10-17 14:46
Hi , We have an application in Java that uses Oracle as the database. The current performance is not great, and the DBA have identified a few reasons, one of which is SQLs having string literals instead of bind variables. If we want to change t...
Categories: DBA Blogs

how to track modification of records on a table

Mon, 2016-10-17 14:46
Hi Tom, My existing functionality is having Triggers on about 15 tables for insert/update/delete. The modified rows are inserted into a target table. However, I have been asked to use a different functionality (good performance) to track th...
Categories: DBA Blogs

Insert performance issue

Mon, 2016-10-17 14:46
Hi Tom, We have an application to load data into database, It uses insert statements to insert data into tables. Each table contains nearly 220 colums. insert into table1(col1,col2,col3,col6,col7,col8................col220)values(1,2,3,6,7...
Categories: DBA Blogs

Data insertion strategy in normalized tables

Mon, 2016-10-17 14:46
Hi Team, We have many tables (master tables) having primary and foreign key relationships. These tables (normalized) contain static data (master data). Inserting data manually in these tables is a tedious task because if we insert data out of ord...
Categories: DBA Blogs

export each XML Message to separate .xml files from Databases

Mon, 2016-10-17 14:46
Hi , We store specific information as XML field in Oracle database I have specific Query which has results in xml's already as each filed stores as xml file. I wanted to export each field as XML file select REQEmployeeXML from User.Employe...
Categories: DBA Blogs

How to acheive the output in the aligned format ?

Mon, 2016-10-17 14:46
The following .sql file is called by the .sh shell script. connect username/password@sidname SET SERVEROUTPUT ON; SET LINESIZE 4600; #SET TRIMSPOOL ON; SET WRAP OFF; SET HEADING OFF; #SET TRIMOUT ON; SET TIMING ON; SET FEEDBACK ON; SET SPOO...
Categories: DBA Blogs

Export to CSV using UTL_FILE

Sun, 2016-10-16 20:26
Hi, I have gone through the code on link https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:88212348059 I am getting ora-29283 invalid file operation, I need to know how to resolve this issue I have checked the following...
Categories: DBA Blogs

Can you recover a column marked as UNUSED?

Sun, 2016-10-16 20:26
We have a situation where somebody marked one too many columns as UNUSED by mistake. We do not intend to drop this additional column. I was searching around for a solution to this but I have not found one yet... The question is simple: is there a way...
Categories: DBA Blogs

Attribute Clustering/Zone Maps with Hash partitioning

Sun, 2016-10-16 20:26
How do Attribute Clustering/Zone work with hash partitioned tables?
Categories: DBA Blogs

Enterprise manager

Sun, 2016-10-16 20:26
Tom, I know I could use </code> http://localhost:5500/em" <code>to get the enterprise manager. But if I have several instances in the same server, it only lets me connect one database this way. How could I connect to every instance using tns-strin...
Categories: DBA Blogs

Audit execution of package body related to TABLE

Sat, 2016-10-15 07:46
HI TOM, question about auditing We turned on DML commands for table X1. How we check if package_body X1_PKGB (contains views, triggers, packages, synonyms) related to table is executed? I am in interested in details - table(X1), package body, ...
Categories: DBA Blogs

Pages