Tom Kyte

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

SQLERRM:ORA-06531: Reference to uninitialized collection

Mon, 2018-06-18 12:46
<code>Hi I am facing this error. my script is like below. please suggest: / create table address_test( Addr_id number, addr_cus_id number, street_name varchar2(100), town varchar2(100), county varchar2(100), sub_county_state_province varchar2...
Categories: DBA Blogs

Sqlldr is throwing OCI.dll exception with Oracle 12.2 Instant Client

Fri, 2018-06-15 11:06
Hi, I have downloaded Oracle 12.2 Instant Client (both SQLPlus and Tools) and on my Window 7 64 Bit system from http://www.oracle.com/technetwork/topics/winx64soft-089540.html. I have unzipped the all files to C:\oracle122, along with by tnsname...
Categories: DBA Blogs

Converting rows to columns

Fri, 2018-06-15 11:06
Hi Tom, Hope you are good. I have a requirement where I need to display rows as columns. Suppose there are 2 rows with 4 columns each then the result should display 2*4 i.e, 8 columns. Is it possible just using SQL? Thanks
Categories: DBA Blogs

Oracle Goldengate

Fri, 2018-06-15 11:06
What is the advantage of Goldengate over Stream? Oracle Goldengate has high license cost compared to Streams. So, why an organization should use Goldengate for their data replication need and not Streams? Does Goldengate has advantage, which is wo...
Categories: DBA Blogs

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

Pages