Tom Kyte

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

export to CSV

Sun, 2016-07-17 07:06
Tom, I need to export data from a table into a .csv file. I need to have my column headres in between "" and data separated by ','. also depending on the column values a row may be printed upto 5 times with data differing in only one field. ...
Categories: DBA Blogs

Finding transacted tables,

Sat, 2016-07-16 12:46
Hello, Using Oracle data dictionary, how to find out the list of tables that have undergone Insert/Update/Delete by a particular user account in the last 7 days? Also, if possible I want to know the number of transactions happened and the size of...
Categories: DBA Blogs

What is best way to collect GLOBAL STATS of a table with 4 billion records

Sat, 2016-07-16 12:46
Hi Tom I need to take global stats collection for a table with 4 billion records. This is a partitioned table but I need to collect STATS globally. This table was last analyzed in Sept 2015. As of now following is Table statistics Actual Number...
Categories: DBA Blogs

DENSE_RANK function

Sat, 2016-07-16 12:46
Hi Tom, I have a problem with DENSE_RANK function. Let's see an example: <code>CREATE TABLE test_rank (val number); INSERT INTO test_rank VALUES(1); INSERT INTO test_rank VALUES(2); INSERT INTO test_rank VALUES(3); INSERT INTO test_rank VALUE...
Categories: DBA Blogs

DROP TABLE performance/delay

Sat, 2016-07-16 12:46
Hello Tom, We have a new application which has serious performance problems, and a reason for that might be the Oracle DB. Unfortunately, tests could not find the root cause yet. Now I saw a very strange behavior of the DROP TABLE performance: ...
Categories: DBA Blogs

count of distinct on multiple columns does not work

Sat, 2016-07-16 12:46
Hi, I am trying to count the number of distinct combinations in a table but the query gives error. For example, create table t(a varchar2(10), b varchar2(10), c varchar2(10)); insert into t values('a','b','c'); insert into t values('d','e'...
Categories: DBA Blogs

Creating and Executing a stored procedure that dynamically builds a table getting ORA-06550: PLS-00103

Sat, 2016-07-16 12:46
Receiving ORA-06550: PLS-00103 error when trying to execute a procedure that dynamically creates a table. I have created a pl/sql script to dynamically create a table: declare l_tablename varchar2(30) := 'TEST_3_'||to_char(sysdate, 'YYYYMMDD')...
Categories: DBA Blogs

Average of input two Dates

Fri, 2016-07-15 18:26
How Can i create a oracle sql Function which can take two input Dates and Display their Average.For Ex:- input :-28-Aug-2016,4-Sep-2016 output:- 1-Sep-2016
Categories: DBA Blogs

Constraints

Fri, 2016-07-15 18:26
<code>Tom, Are the following a full list of all possible user constraint_types P - primary key? C - check? R - foreign key? U - unique ? And what are their full meanings? Are the dab constraint_types the same? Thanks Brian </code>
Categories: DBA Blogs

Schema migration + unknown table utilization

Fri, 2016-07-15 00:06
Hi team, I have pretty much an unanswerable question, but I thought I'd see what advice you can give anyway. I am working on a project trying to separate many legacy applications using shared schemas to their own self contained. There are a co...
Categories: DBA Blogs

IN vs OR clause

Fri, 2016-07-15 00:06
I have following query. This table has millions of rows and table is partitioned by date. select * from test.testing d where D.code in ('123','124','136','136'); Like we have to pass 100 values. What is the best way to get the results back...
Categories: DBA Blogs

Recover Procedure

Fri, 2016-07-15 00:06
Hi ask tom team, we need to restore a procedure to a one month previous version.. unfortunately. Database is in Archine log mode and we take daily backups. Is it possible ?
Categories: DBA Blogs

RefCursor and Multiple responses in single call

Fri, 2016-07-15 00:06
Hello Tom, Thank you in advance for taking my question and for this wonderful platform. For search functionality for our Content Management system, I have created a package which returns a ref cursor. The package accepts search critetrias, dy...
Categories: DBA Blogs

Database Security

Fri, 2016-07-15 00:06
In Our organization , two employees who copy whole windows server system to image file. And our server has business and people's related information stored in oracle database. Both does not know the password but they know sys user. and they are a ...
Categories: DBA Blogs

Query does not use index

Fri, 2016-07-15 00:06
I would like to understand why my sql instruction doesn't use an index; I have a table that has 6 million rows. And I'm trying to extract some rows and Oracle optmizer are not using the index existent in column. TABLE ESTRUCTURE: <code> COLUM...
Categories: DBA Blogs

How to loop a select statement while inserting the values in a table

Fri, 2016-07-15 00:06
create or replace PROCEDURE PROC_PROJ_ID_AUTO_GENERATE(op_error_code OUT VARCHAR2, op_succ_msg OUT VARCHAR2) BEGIN FOR i IN (select a.program_id , a.PRODUCTIVITY_IDENTIFIER ,TARGET_START_DATE ,TARGET_COMP_DATE , PRGM_...
Categories: DBA Blogs

Truncate on cluster table

Thu, 2016-07-14 05:46
Hi Team, Could you please help me on how to truncate a table which part of a cluster. I want to truncate a table and getting below error. please help me ASAP. Below is the error: SQL> truncate table TEST1; truncate table TEST1 ...
Categories: DBA Blogs

Rebuild optimize failing for Text index in RDBMS 12c (12.1.0.2.0) CDB-PDB config

Thu, 2016-07-14 05:46
Hi Guys, DB: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production OS: OEL 6.5 I am testing use case for Oracle Text index on 12c CDB-PDB config (single PDB), and found that REBUILD optimize is failing with internal exce...
Categories: DBA Blogs

Spatial queries

Thu, 2016-07-14 05:46
Hi Tom I am calculating distance between two 2 points (using their gps co-ordinates) from the below query (Query 1). One column (name geo_location of sdo_geometry datatype where spatial index is created) in my table stores the geometric location o...
Categories: DBA Blogs

Session Memory Release

Thu, 2016-07-14 05:46
Hi Connor, Wanted to understand the session memory release process. 1] A database 2] Connections are established to the database, through connection pool 3] Connection pool is configured in Application Server [ Weblogic ] 4] The connection p...
Categories: DBA Blogs

Pages