Tom Kyte

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

changing default tablespace of LOB in a partitioned table

Tue, 2018-03-13 13:26
Hi, I want to move a Partitioned table having LOB to a different tablespace, I have moved all data,indexes and partitions, and set the default tablespace for table and indexes every thing is moved/changed except for the default tablespace of ...
Categories: DBA Blogs

Merge statement is throwing ORA-08006 error

Tue, 2018-03-13 13:26
We have a ETL job with merge statement. It was working fine earlier. There was a node eviction on last week and due to that the DB was bounced. After bouncing the DB the procedure started failing with ORA-08006 error. Same error is appearing when we ...
Categories: DBA Blogs

Rollback to save-point rollbacks everything

Tue, 2018-03-13 13:26
Hi, In the below example, "rollback to b;" rollbacks everything instead of clearing only emp3 entries. ---------- <code>create table emp1(empno number,ename char(4),sal number); create table emp2(empno number,ename char(4),sal number); create ...
Categories: DBA Blogs

character conversion for numbers

Mon, 2018-03-12 19:06
Dear Sir, Is there any way to convert number from english to other language like Bangla. e.g = 1 will be show when convert ? is possible.............
Categories: DBA Blogs

DB Cloning

Mon, 2018-03-12 00:46
Hi Team, Would you please share the steps for cloning 11.2.0.4 DB and making it as DR server2, we already have one DR server. now they are planning to make one more DR server. I want to understand what are the ways to clone DB with their in d...
Categories: DBA Blogs

Execute multiple ALTER statements inside EXECUTE IMMEDIATE

Mon, 2018-03-12 00:46
Hi Team My use case is to store table metadata - PK, Unique, FK constraints and Indexes DDLs in a temp table. Perform some operation on table, and at the end restore the stored metadata on the table. When executing the PL/SQL block, i receiv...
Categories: DBA Blogs

Database setup for DR

Mon, 2018-03-12 00:46
Hi Team, Our client are planning to create new DR server. Would you please give the step by step procedure to step new DR on IBM AIX server 6.1 64bit. Regards, Surendar Patel
Categories: DBA Blogs

Monitoring TRUNCAT TABLE - WHEN and WHAT SESSION ?

Mon, 2018-03-12 00:46
Hi We are facing some strange issues in our test environments where TRUNCATE table was happening and we could NOT find when it was truncated and what session is causing it ? Is there any way to monitor the truncate ? Such as Who ? When ? Sessi...
Categories: DBA Blogs

Shell function returning content of existing directory along with error code

Mon, 2018-03-12 00:46
Hi, We are using following code to find out ORA- errors. But not sure why it is returning directory contents on function error. db_scn() { OUTPUT=$(sqlplus -s /as sysdba << EOT whenever SQLERROR EXIT 11 select ccurrent_scn from v$database...
Categories: DBA Blogs

Best method to re-org Very Big Banking Tables

Sun, 2018-03-11 06:26
Hello All, We need expert advice how can we achieve the ONLINE table reorganization and data files shirking. this is a core banking database of one of the biggest bank. i can provide all required information from my side, if you are ready to ...
Categories: DBA Blogs

Invisible columns

Sun, 2018-03-11 06:26
hi tom, i hava a question about the feature oracle 12c (field invisible). Why if i to do create a new field with condition invisible and not null constrain this failure when insert values into the table, but dont is enable for set values to new field...
Categories: DBA Blogs

Sybase to Oracle migration

Sun, 2018-03-11 06:26
Is there any tools available to compare data (data validation post Sybase migration) between Sybase and Oracle 12c which high volume up to 1tB. SQL developer takes a long time even for a small set of data
Categories: DBA Blogs

How to count records without using count function

Sun, 2018-03-11 06:26
Hi, How we can find employee count department wise from emp table without using count function using a plsql block.
Categories: DBA Blogs

Memoptimized Rowstore tables

Sat, 2018-03-10 11:06
Are the Memoptimized Rowstore tables newly available in Oracle 18, a complete substitute for single-table hash clusters? Judging by the docs. about the direct memory access available with these types of table, I guess they're actually superior in ...
Categories: DBA Blogs

desc command not working in SQL Developer

Fri, 2018-03-09 16:46
Hi Tom, There are a few table on the remote server. I can't use the DESC command against these tables on SQL developer. But when I am using SQL*Plus it is working without any error. I can run this command against the local tables and those are o...
Categories: DBA Blogs

REST API in PLSQL Giving ORA-29273 & ORA-12541

Fri, 2018-03-09 16:46
Below is my environmental information Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit PL/SQL Release 12.1.0.2.0 - Below activity performed by DBA <code>grant execute on utl_http to schema_name grant execute on dbms_l...
Categories: DBA Blogs

Java version in Oracle DB

Fri, 2018-03-09 16:46
Hi, I have Oracle DB stored procedure (java) which: - pulls data from Miscosoft SQL Server 2000 and - inserts them into Oracle DB 11.2.0.2.0 Java version on Oracle DB is: 1.5.0_10 <code>SELECT dbms_java.get_ojvm_property(PROPSTRING=>'ja...
Categories: DBA Blogs

Procedure to read the input array and exclude in select

Fri, 2018-03-09 16:46
Hi, Have a question in accessing json array parameter in db proc. below is the example input json array. '{"List":["name1","name2"]}' or ["name1","name2"] how to access this type of input data and need to apply filter with this data. Means ...
Categories: DBA Blogs

I cannot automate the value for utl_http.begin_request()'s new https_host parameter

Fri, 2018-03-09 16:46
Hi "Tom", We're just beginning to test our application in earnest with Oracle 12.2 and have run into a significant problem with a coding change to UTL_HTTP for handling SSL sites. There is a new parameter called "https_host" for which the appli...
Categories: DBA Blogs

SQLLDR Replace Function not working

Fri, 2018-03-09 16:46
Help I am trying to use repace function on the Gender Column. The Column has only 2 possible values(-2,-3) and I am trying to replace those with M and F respectively. But I keep getting an error. Record 5: Rejected - Error on table L2K_TNCPS_T_PERSO...
Categories: DBA Blogs

Pages