Tom Kyte

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

Changing Select_Catalog_Role Privileges

Thu, 2017-09-21 13:46
Hello, I need to customize the Select_Catalog_Role taking out some Grants but don't have the necesary previledges. Can you please tell me what previledge I need? Thanks Patrick
Categories: DBA Blogs

How to track long running sql query

Thu, 2017-09-21 13:46
Using V$SESSION_LONGOPS View, How can DBA self check long running query and also help to find out most resources consume?
Categories: DBA Blogs

Joining multiple tables inside a UPDATE statement

Thu, 2017-09-21 13:46
Hello Tom, Need your feedback on joining multiple tables inside UPDATE statement Scenario: i want to update a column value of table 'a' based on the reference/key value in table 'd'. Below is my query. It's working good for small number of record...
Categories: DBA Blogs

Golden Gate to AQ

Wed, 2017-09-20 19:26
I am trying to find the solution on sending the Data from Golden Gate Streams to Oracle Advanced Queue. Please advise what are the steps i need to follow.
Categories: DBA Blogs

Error while compiling java source

Wed, 2017-09-20 19:26
Hi, My question is While compiling java source I am getting warning as : JAVA SOURCE compiled Warning: execution completed with warning Failed to resolve object details Code: <code>create or replace and compile java source named "DirTestLi...
Categories: DBA Blogs

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

Pages