Tom Kyte

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

Invoke Remote stored procedure that has an Array of varchar's as IN parameter

Wed, 2017-09-20 19:26
I have the following scenario: 1 ? Two databases, SOURCE e TARGET, connected by a dblink. 2 ? On TARGET database, i have a procedure that invokes another procedure on database SOURCE 3 ? The procedure on database SOURCE has an IN parameter that ...
Categories: DBA Blogs

What are the datatype restrictions, while moving tables via the ALTER TABLE <table_name> MOVE command

Wed, 2017-09-20 19:26
Hello, We are running, 12.1.0.2 db. I have been tasked to move all the tables,spread across various schemas, from un-encrypted tablespace to encrypted tablespace. I am planning on using the ALTER TABLE <table_name> move command, to move most...
Categories: DBA Blogs

Query Performance Large SQL

Wed, 2017-09-20 19:26
Hi Tom, Thanks for taking time to read my question. I have a query that joins about 36 tables. Driving table has about 43 million records. 5 other tables have about 9 million records. Rest of the tables are small. Its a combination of inner and l...
Categories: DBA Blogs

Golden Gate, Streams

Wed, 2017-09-20 01:06
Hi Chris/Connor, Can you please help to clarify below query: We have a Archival requirement, where we need to : 1. Copy certain tables data (older than 7 years) from Primary DB to secondary DB 2. Delete those copied data from Primary DB. S...
Categories: DBA Blogs

need to extract numbers from a varchar upto a non-numeric character

Wed, 2017-09-20 01:06
Hi, I have a column called house number which is a varchar2, and it has all kinds of combinations of data entered (incorrect format) as shown below: House_Number ------------- 416-A 416-A 1573A 131# A23 133 A-21 133 A22 13320A 133A-21 1...
Categories: DBA Blogs

transfert files,delete&loadData

Wed, 2017-09-20 01:06
Hello, I would like to do these 3 steps automatically one time per day with oracle: 1) transfert .csv files. 2) Delete data from table. 3) Load table again with sqlldr from .csv files transferred. So if you could please let me know what would ...
Categories: DBA Blogs

Database is very slow....

Wed, 2017-09-20 01:06
Hi, I have a database in Oracle 10.2.0.4. My database is being used by JDE application and Block size set to 16KB. We are using FATA disk to store the datafiles of the database. We are maintaining separate tablespaces for data and indexes. We are us...
Categories: DBA Blogs

XMLQuery ORA-19114: XPST0003 - error during parsing the XQuery expression:

Wed, 2017-09-20 01:06
I'm doing examples from workbook. I created table and insert couple of records. Below is my code: Create table: <code>CREATE TABLE test_Pracownicy (IDPracownika NUMBER(3), Dane XMLTYPE); Insert record to the table: INSERT INTO test_Pracow...
Categories: DBA Blogs

Generating large json in 12.2 using json_object and json_arrayagg

Wed, 2017-09-20 01:06
Is it possible to get a result from the following query? <code> select JSON_OBJECT( KEY 'objects' VALUE (SELECT JSON_ARRAYAGG( JSON_OBJECT( KEY 'object_type' VA...
Categories: DBA Blogs

Database redaction with PL/SQL invoker rights

Tue, 2017-09-19 06:46
Dear Oracle Masters, I am trying to create a proof of concept application architecture based upon the thick-database paradigm incorporating invoker rights, code based access control and redaction in Oracle 12cR2. Background =========== I h...
Categories: DBA Blogs

Client Result Cache not supported by sqlplus ?

Tue, 2017-09-19 06:46
<code> sokrates > select distinct sokrates > client_connection, client_oci_library, client_version, client_driver sokrates > from v$session_connect_info sokrates > where sid = (select sid from v$mystat where rownum=1) sokrates > / CLIENT_CONN...
Categories: DBA Blogs

SQL*Loader

Tue, 2017-09-19 06:46
I have an MS ACCESS application that will be developed with an Oracle table (back-end) Weekly, a user will get Spreadsheets that are imported (via VBA scripting) into Access. In the temporary Access tables, other data is added. The plan is then ...
Categories: DBA Blogs

DB Cloning

Tue, 2017-09-19 06:46
Team: We have two oracle databases Source database - DB1 running on HP Unix platform, version is 11.2.0.2 having 20+ TB of data. Target database - DB2 running on Linux platform, version is 11.2.0.4 having no data for now. Our goal is to clo...
Categories: DBA Blogs

Evolving SQL Plan Baselines

Tue, 2017-09-19 06:46
We have recently upgraded one of our main databases from 10.2.0.4 to 11.1.0.7, and we used SQL Plan Baselines to try to minimize the impact of many plan changes. We are running a job to evaluate and evolve new plans captured into the baseline (calls ...
Categories: DBA Blogs

AUTO_START CRS Attribute

Tue, 2017-09-19 06:46
Hi TOM, We have ASM implemented in lot of databases and below are the values of auto_start attribute in our crs config: NAME=ora.asm AUTO_START=never NAME=ora.DG_GRID_CL.dg --> This is the disk group which we use for keeping OCR & Voting di...
Categories: DBA Blogs

Evolute from a Developer to DBA

Mon, 2017-09-18 12:26
Hi Tom, I found this website by accident one year ago and get addicted to it in just a couple of hours. Thank you for all the interesting and useful instructions/suggestions. I am a database developer who uses PL/SQL to code everyday. I always ho...
Categories: DBA Blogs

Delete a record at the at end when others stage

Mon, 2017-09-18 12:26
I have a procedure which writes a record each in two tables. Want to delete the record inserted into the first table if the second table insert fails. However, the delete does not happen. The problem is that the before insert trigger in the second t...
Categories: DBA Blogs

capture old and new values of the fields using Stored Procedure

Mon, 2017-09-18 12:26
Hi, We have a requirement to store old and new values of the fields and insert into another table. For example, If we have Table A and Table B and in both the tables columns are exactly same but we need to write a procedure like whenever an...
Categories: DBA Blogs

How to pass a list of values from one procedure to another

Mon, 2017-09-18 12:26
The first step is to fetch the data. Second is to pass the list of values fetched in step 1 to another procedure. This is the table <code>CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), ...
Categories: DBA Blogs

Refreshing Materialized view in a remote database

Mon, 2017-09-18 12:26
Hi there. I have created in the database A the following MV: CREATE MATERIALIZED VIEW MRW_USER.MB_TRACK_REPORT3_FINAL_SS (SOUNDREC_CODE,ISRC,CAT_NUM,TITLE,MIX_NAME, ARTIST,RELEASE_DATE,LABEL,COMPANY,CORPORATE_GROUP, DISTRIBUTOR,DEALER_PRICE,GEN...
Categories: DBA Blogs

Pages