Tom Kyte

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

How to schedule a script in sql developer

Sat, 2016-07-02 01:06
Hi; I have tried many ways to schdule an a script using sql developer;But for these scripts those are failed.But those can nicely run using built in oracle sql-plus.i can use the script by using @ sign and with the file name (eg : @filename) Here...
Categories: DBA Blogs

SQL Server 2016 TCO

Fri, 2016-07-01 07:06
Team, i was going through this link. https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/01/sql-server-2016-is-generally-available-today/ Comparing the cost of SQL Server 2016 with Oracle 12c, Oracle cost 12x more. We know Ora...
Categories: DBA Blogs

Block user account to PRIMARY production server and move them to the REPORTING STANDBY Db.

Fri, 2016-07-01 07:06
We are planning to all our Business users to use our read only Active data guard standby database and restrict them using the Primary production database using logon trigger. But as part of security, users have to change their passwords every 60 ...
Categories: DBA Blogs

How to validate the XML data exists in the XML table

Fri, 2016-07-01 07:06
Hi Tom, Can you please help me on how to validate the XML data available in the XML table. I have one XML table as: CREATE TABLE TEST_XML OF XMLTYPE XMLTYPE STORE AS SECUREFILE BINARY XML; And the data as: INSERT INTO TEST_XM...
Categories: DBA Blogs

Would there be a better way of improving the performance?

Fri, 2016-07-01 07:06
I have a code as shown. Objective of this piece of code is to give a report on counts for student enrollments, student absences and percentage present in the district starting from the first day of school up until the last day (i.e. a total of 18...
Categories: DBA Blogs

Number filtering from Oracle with Decimal

Fri, 2016-07-01 07:06
Hi Team, We had a situation where the customer is pulling a report from a table. This table has a number format as number (5,2) . They have a value 9 and also its updating another value as 9.00 while selecting it from the table if we use to_char(n,9...
Categories: DBA Blogs

ORA-27125: unable to create shared memory segment

Fri, 2016-07-01 07:06
Have an error when trying to start DB - ORA-27125: unable to create shared memory segment SVR4 Error: 22: Invalid argument
Categories: DBA Blogs

PLSQL profiling and auditing

Fri, 2016-07-01 07:06
1)In 100 lines of plsql program I have raised performance issue how to find which line occur the performance issues. 2)Is there any way in which i can find out when a stored procedure was last executed in oracle. NOT only a procedure am looki...
Categories: DBA Blogs

tuning array join

Fri, 2016-07-01 07:06
hi, i write relating the 71th statment of the following link. https://livesql.oracle.com/apex/livesql/s/dif16pb1eyri6hmzxxr1wg1zb i've to speed up this cursor: <code> cursor v3(c1 redd_tbTotali, c2 redd_tbPatrim) is select a1.prod...
Categories: DBA Blogs

SQL query running longer

Fri, 2016-07-01 07:06
I have three tables T1, T2 & T3. T1 has about 29 million rows T2 has 25 million rows T3 has about 9 million rows The below query takes more than an hour to run. I have tried indexes on the join columns, temp tables, hints but nothing helps. is...
Categories: DBA Blogs

Partition By and Group By

Fri, 2016-07-01 07:06
Hi Tom, I know the purpose of Partition by is to divide the data into groups and Group By is to retrieve result of some aggregate functions. What is the main difference between partition by and group by?
Categories: DBA Blogs

Date Function Filter

Thu, 2016-06-30 12:46
I have table having date with two different formats in the same column. When I'm querying like <b>extract(year from to_timestamp(colmnname, 'DD/MM/YYYY hh24:mi'))</b> its giving me as<b> date format picture ends before converting entire input string<...
Categories: DBA Blogs

Query/Disk aggregated figures not matching execution plan in SQL trace for a single SQL statement.

Thu, 2016-06-30 12:46
Hi Tom, My question is, not sure I'm hitting some sort of bug though I can't find anything on mos, why the query-current-disk aggregated figures do not match the execution plan cr value multiplied by the number of iterations or fetch calls: SEL...
Categories: DBA Blogs

Regarding the Oracle SQL Tuning Report

Thu, 2016-06-30 12:46
Team, I recently had one issue with one of my production database where we had an application upgrade completed. Once the upgrade completed , one of the select query stared performing very slow. I have tried debugging using the sqltrpt.sql , but thi...
Categories: DBA Blogs

Security

Thu, 2016-06-30 12:46
how to shadow user's tables' structure for other users. I have two users (schemas) - USER1 and USER2. USER1 has roles CONNECT and RESOURCE, but if i run 'DESC USER2.TABLE_NAME' i can show user2's table's structure. How can I forbid to show table'...
Categories: DBA Blogs

Avoid triggers

Thu, 2016-06-30 12:46
Hi Tom I have table A which goes through various DML and owned by different team, we want to capture a similar data with all the DML that's happening. we want to avoid triggers as this is owned by different team , is there a solution to capture wit...
Categories: DBA Blogs

querying data from backup file

Thu, 2016-06-30 12:46
Hi Tom / Connor, As a regular DBA Activity, everyday Production DBs are scheduled for backup and the backup files will be stored in archive location for a retention period depending on business requirement. Now my query is, I just want to pic...
Categories: DBA Blogs

how to store output of dynamic sql into a cursor.

Thu, 2016-06-30 12:46
Hi tom, I want to know how to declare a cursor which contains the data obtained by dynamically executing a query,where the query was taken from another cursor. CURSOR c1 IS SELECT QRY from QUERY_MASTER WHERE Q_ID IN (1,2); where QRY(an SQ...
Categories: DBA Blogs

How to export xml data ( clob column ) to a csv file

Thu, 2016-06-30 12:46
Hi, Let say, I have a table employee ( id number, name varchar2, notes clob ). I use dbms_util to write the data to a file as csv. The id (number) and name( Varchar2) columns are writing to csv file as expected. But when it comes to notes (clo...
Categories: DBA Blogs

Title on Report.

Thu, 2016-06-30 12:46
Hello All- May be another set of eyes!! How do I get the title to show on the spool output report file? Below is my report lay out. Thanks! <code> set heading on SPOOL U:\...\tt.txt ttitle left " Test report " skip 1 COLUMN...
Categories: DBA Blogs

Pages