Tom Kyte

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

What is the relationship of CPU, Memories against DB performances?

Fri, 2018-06-15 11:06
Hi Tom, Frequently I get asked quite a number of times when planning for a new server setup for the creation of databases. How much CPU cores should I get? How much Memories should I get. Normally I'll answer them, just get the highest cores & me...
Categories: DBA Blogs

DBMS_FILE_TRANSFER.PUT_FILE multiple "source_file_name"

Fri, 2018-06-15 11:06
Hi I am using Datapump to export dump file from a database and while exporting the dumpfile, I am splitting that dumpfile into multiple files. Now I want to transfer those files to another server using DBMS_FILE_TRANSFER.PUT_FILE. I know ...
Categories: DBA Blogs

UTL_FILE.FCOPY not working in FOR LOOP <file read error>

Fri, 2018-06-15 11:06
Hi There, I have a PIPELINED function which retrieves me filenames which I feed to UTL_FILE.FCOPY like below: <code>DECLARE PROCEDURE copy_var_templates (p_var_report_name st_string) IS lkv_template_dir CONSTANT st_string := 'T...
Categories: DBA Blogs

Side-effects when working with associative array in pl/sql

Fri, 2018-06-15 11:06
I've noticed strange side-effect when working with associative arrays in pl/sql. Basically, it appearts, that when element of the array is passed to procedure as "in out nocopy", then after procedure finishes, Oracle copies possibly updated element b...
Categories: DBA Blogs

Extracting attributes from JSON documents

Fri, 2018-06-15 11:06
Hi all, Have question in JSON array accessing along with normal columns like below, <code> (Reports : [( 'reportname': 'abc', 'Sort order':'abc', 'sortlabel':'name', 'columns' :[ ( 'component' : 'q_test1', ...
Categories: DBA Blogs

Get a JSON from a SQL query

Fri, 2018-06-15 11:06
Hello! Just a question. Is it possible to write a query that returns a JSON code? If yes, could you give me a brief example? Thanks!
Categories: DBA Blogs

Index-Organized Materialized View with different primary key than the master table?

Fri, 2018-06-15 11:06
Dear Oracle-Team, we need a daily snapshot from the company's personal data for our software. For that reason we want to use an index-organized materialized view (with daily 'refesh complete'). Unfortunately there are two user id's for every empl...
Categories: DBA Blogs

Sizing clusters

Wed, 2018-06-13 22:46
My question is generally how to determine the size to set the <u>size</u> value in a create cluster statement. And specifically for a parent table 400 bytes wide with 15 million rows, and a child 120 bytes wide with 40 million rows. There may be a...
Categories: DBA Blogs

SQL with inline view Errors in 11g with ORA-00979 Not a Group BY expression, but runs in 12c

Wed, 2018-06-13 22:46
Hi Following SQL (correctly) errors with "ORA-00979 Not a Group BY Expression" when run on 11.2.0.4.0. But when run on 12.1.0.2.0, SQL runs without error and returns incorrect information for total_tablespace_size - All rows return 0.5 whereas i...
Categories: DBA Blogs

Leaving fake hints in queries

Wed, 2018-06-13 22:46
Hello, I'm creating a script to automatically generate plan reports usings DBMS_XPLAN.DISPLAY_CURSOR, and to do so I want to put in a standard comment in the table and query it via dba_source and v$sql. e.g. <code>select /* xplan_my_test_pkg01 ...
Categories: DBA Blogs

Active clone recover needed arc files

Wed, 2018-06-13 04:30
Oracle 12.2 I am running the following command: *.db_file_name_convert='/db1/ifddb1/dbf/','/db1/ifdtest1/dbf/' *.log_file_name_convert='/redo/ifddb1/redologs/','/redo/ifdtest1/redologs/' sqlplus ' / as sysdba ' <<EOT shutdown abort startu...
Categories: DBA Blogs

Use DBMS_OUTPUT or HTP depending call origin

Wed, 2018-06-13 04:30
Hey, I have this procedure: <code>procedure output(i_msg in varchar2) is begin dbms_output.put_line(i_msg); -- if call is from apex i want to use htp.p end;</code> Is it possible to switch the "output chanel" to htp when the caller is...
Categories: DBA Blogs

Creating dll for executing external procedure('c' language)

Wed, 2018-06-13 04:30
Hi Tom, I am using Wint NT,Oracle 8i(server) and C language. My goal is calling 'c' routine thru stored procedure. For that I had made neccesary steps.I had modified Tnsnames and listener entry as follows. Tnsnames entry...
Categories: DBA Blogs

Avoid duplicates

Wed, 2018-06-13 04:30
Hi Tom, Thanks for your time. We have the scenario like this... DML on the table t should not populate the table with duplicate entries. So if we have table with data in it as : create table t ( x number); insert into t values (1);...
Categories: DBA Blogs

PDB AWR Host CPU

Tue, 2018-06-12 10:06
Does the "Host CPU" in the PDB AWR mean the CPU usage for the PDB, CDB or the host?
Categories: DBA Blogs

Insert multiple csv in a zip

Tue, 2018-06-12 10:06
Hello, My requirement is to 1. Read two query result and write into two different CSV files 2. Zip these two CSV files in a single zip file. There is a similar qn posted that tells who to create csv file and store the result (clob and blob) i...
Categories: DBA Blogs

Sql Execution Time v/s Elapsed time v/s CPU Time

Tue, 2018-06-12 10:06
Hello , I have been working on Database monitoring stuff where we are looking for long running queries in DB . From the inbuilt setup which i have received from DBA , its showing CPU_TIME and ELAPSED_TIME but none of them is matching with Oracle E...
Categories: DBA Blogs

what is read consistency

Mon, 2018-06-11 15:46
<i></i>Could you explain in your words what is Read consistence in Oracle 4.0 <i></i>
Categories: DBA Blogs

SESSION parameter shows different value

Mon, 2018-06-11 15:46
hi there As you guys suggested, last day i was trying to change process and session parameter values as follows, Everything gone perfectly. But after starting up the database, for the SESSION PARAMETER it shows me a value that i wasn't ex...
Categories: DBA Blogs

Listagg returning multiple values

Mon, 2018-06-11 15:46
hello, I am new to writing this kind of SQL and I am almost there with this statement but not quite. I'm trying to write a query using listagg and I am getting repeating values in the requirement column when I have 2 passengers. This is because...
Categories: DBA Blogs

Pages