Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 4 days 2 hours ago

Loading email content into oracle table

Fri, 2019-05-17 21:46
Hi Tom, I have an interesting requirement, I want to load complete emails ( example outlook) in oracle tables. => When a mail content ( preview) is greater than 4000 than store in attachement table with name "long content" and store complete...
Categories: DBA Blogs

Ora-12560: TNS: protocol adapter error

Fri, 2019-05-17 21:46
I use a single instance 12.2C 64-bit Oracle database on a window server 2012R2. suddenly this error <i>ORA-12560: TNS:protocol adapter error</i> began to show when I try to enter the sqlplus. whatever I have searched the internet a lot for a soluti...
Categories: DBA Blogs

Manipulate the autogenerated names for types inside packages

Fri, 2019-05-17 21:46
Hey, if you create a type inside a package, this type is created in the database with a name like 'SYS_...'. Is there any possibility to affect/influence the auto generated name? Or do i can rename it? And how? Why I asked that? I work a lot w...
Categories: DBA Blogs

exporting packages,function etc. from one user to another.

Fri, 2019-05-17 21:46
Hi, For example X user have many packages,functions,procedures etc. And I want to delete some of them after copying to another user (Y). I mean I want to classify packages,functions etc... I can copy-paste by using Procedure Builder. But this way...
Categories: DBA Blogs

SYSDATE behavior in SQL and PL/SQL

Fri, 2019-05-17 21:46
Hello, My quess: there are two different SYSDATE functions ? one defined in STANDARD package and another one somewhere ?inside? Oracle. Example: SQL> select * from dual; D - X SQL> select sysdate from user_objects where rownum=1;...
Categories: DBA Blogs

PLS-00436: implementation restriction error when using forall in update

Fri, 2019-05-10 19:26
I have a object as follows: create or replace type claims_only as object (fordnr varchar2(15),glaeubigernr number,fordergnr number); and I have a a collection as: create or replace type claims_only_collection as table of claims_only; I have a...
Categories: DBA Blogs

Update statement with outer join

Sat, 2019-05-04 08:06
Hi, From the sql script, I would like to understand the difference in the behavior between executions of statement 8 and statement 11. While statement 8 updates the flag_1 to null, statement 11 updates it to 2. What difference it makes when I u...
Categories: DBA Blogs

Pass parameter to where clause in bulk collect statement

Sat, 2019-05-04 08:06
Hi I have a basic procedure which bulk collects the results of a select statement into a table array. I then print out one line to show that it has worked.. Code (SQL): <code>CREATE OR REPLACE PROCEDURE use_var IS TYPE r_tab IS TABLE OF msf010%...
Categories: DBA Blogs

View SYS permissions

Thu, 2019-05-02 21:46
A vendor would like me to run a script against a client's database. The script requires 'SYS' user access and makes the following grants: <code>grant select on sys.gv_\$mystat to public;</code> <code>grant select on sys.gv_\$vsession to public;</...
Categories: DBA Blogs

Ways to Name the Output Columns of a Pipelined Table Function

Thu, 2019-05-02 21:46
How many ways are there to name the output columns of a pipelined table function? My understanding is the only way is via a RECORD type like this: <code>CREATE PACKAGE blah_blah_blah IS TYPE abc_rec IS RECORD (ID NUMBER, ...
Categories: DBA Blogs

Sequence behavior skipping values

Thu, 2019-05-02 21:46
Hello, Ask Tom Team. I have a table in a 2-node RAC with an identity column. The sequence is generated by default, cache 3000 and noorder option. I see something weird. The first insert was id 1, which means that it came from instance 1, then...
Categories: DBA Blogs

Unable to complete finish_redef_table on reference partitioned table

Thu, 2019-05-02 21:46
Hi , Could you please have a look at below reference partition scenario Rowcount of order_items is 25 millions: I need to change the partition technique of child table i.r. order_items to DAILY-RANGE INTERVAL partition. Table dont have primary k...
Categories: DBA Blogs

Search on concatenated index values

Thu, 2019-05-02 21:46
I am looking into an issue with concatenated index (also known as multi-column, composite or combined index). So, by the current design, I have 2 columns KEY1 and KEY2, which produce my PRIMARY KEY PK1. <code> CREATE TABLE "myTable" ( "...
Categories: DBA Blogs

Error while cloning remote database using dblink: ORA-65345: cannot refresh pluggable database

Thu, 2019-05-02 21:46
<code>create pluggable database test3 from test32@ttxtest_link1 parallel 8 refresh mode none create_file_dest ='/u02/app/oracle/oradata/test3/' * ERROR at line 1: ORA-65345: cannot refresh pluggable database ORA-17627: ORA-03135: connection lost ...
Categories: DBA Blogs

DML Operation in PL/SQL Function

Thu, 2019-05-02 21:46
Sir, 1) Can I perform DML (Insert, Update, Delete) also into a PL/SQL function.....? 2) Can I call a Trigger Explicitly.
Categories: DBA Blogs

Migrating Oracle database

Thu, 2019-05-02 21:46
Hello, Thanks for taking up this question. There is an enterprise java application hosted on a huge (40 TB) Oracle database. Can this Oracle database be migrated to : 1. SQL Server or any other RDBMS? 2. Any No SQL database? I have been as...
Categories: DBA Blogs

When-Tree-Node-Selected question.

Thu, 2019-05-02 21:46
I've created a tree and it is populating correctly during the when-new-form-instance trigger, but my question is that when I click the mouse on a tree node, the when-tree-node-selection trigger fires properly and the node is highlighted. But wh...
Categories: DBA Blogs

Rebuilding local non prefix index raises ORA 02149: Specified partition does not exist

Tue, 2019-04-30 16:06
I truncated the partition on a table with local partition index. After inserting the records again in the same partition If I try to rebuild the index on that partition it gives ORA 02149: Specified partition does not exist. However i can see the res...
Categories: DBA Blogs

Combine json objects into one document

Tue, 2019-04-30 16:06
I need to combine some existing json objects into a new json object. Using the syntax: <code>json3 := json_object('json1' value json1, 'json2' value json2);</code> the existing json objects get wrapped in an extra set of double quotes, and t...
Categories: DBA Blogs

Query Rewrite to improve performance.

Tue, 2019-04-30 16:06
Hi, I have this query which takes about 45 minutes to populate the data into AFS_TABLE. The AFS_TABLE is truncated before this insert runs and it does not have any indexes or constraints. The Oracle version is 11.2.0.4. The VW_PAYMENT table is a very...
Categories: DBA Blogs

Pages