Tom Kyte

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

ORA-12560 ERROR - do I have all the services I need ?

Wed, 2018-11-28 02:06
Hello, I am using a Macbook and I downloaded the Parallels virtual machine to get Windows 10 in order to download SQL Plus, I have done all of the minimum requirements and installed SQL Plus, but when it comes to entering my username and password...
Categories: DBA Blogs

Database Link across versions

Wed, 2018-11-28 02:06
Can a database link created on an Oracle 12 database access tables hosted on an Oracle 11 database? We have an existing application Oracle Argus that we are upgrading to Argus 8.1.2.1 which will run on Oracle 12 Exadata server. We have an existing...
Categories: DBA Blogs

Insert datetime values from .NET apps

Wed, 2018-11-28 02:06
Hello, Team. A .NET app is being developed by our team. We are using Oracle database 18.3.0. In order to insert date and time in a date column developers use the following code: <b>TO_DATE(TO_CHAR(:DATE_COLUMN,'DD/MM/RRRR' HH:MI:SS AM), 'DD/...
Categories: DBA Blogs

Unix Strings command and data not encrypted

Wed, 2018-11-28 02:06
Hello TOM, I have a problem with the Unix command named STRINGS. If I do not use encryption on my datas, I can read my string datas via Unix, even if these datas were truncated under Oracle. And dtranger, I have their history too... Fir...
Categories: DBA Blogs

Table storage parameters

Wed, 2018-11-28 02:06
I've always created tables using the defaults for storage parameters. <code> create table blah ( a number; b varchar2(100) ); </code> however I recently worked with an organization where the following storage parameters were always spe...
Categories: DBA Blogs

Default Value From A Sequence: Sequence Updates On Provided Value Entries With INSERT Through LOOP But Not on Individual INSERTs

Wed, 2018-11-28 02:06
I have two samples of code below that use the same table and sequence definitions. The first one inserts using individual insert statements, with the first three inserts giving an override value to the sequenced column and the final insert relyi...
Categories: DBA Blogs

Compare all table content from one schema to another

Tue, 2018-11-27 07:46
Hi, I need to compare full schema table contents with another schema. Both will have same tables, with exact the same DDL, just some records will be different in few columns. Is there any way to execute a script or any way to do it in SQL Deve...
Categories: DBA Blogs

parsing recusirvely "#" from strings without PL/SQL procedure

Tue, 2018-11-27 07:46
Dear Tom, First of all thanks for the site, the tips... always useful... I faced recently an sql issue and i designed a beginning of answears but i think it should be optimized. I have a table containing posts from a social network platforms. Thos...
Categories: DBA Blogs

Sending mail using utl_mail

Tue, 2018-11-27 07:46
Dear Team, I tried to install UTL_MAIL using the below command sqlplus sys/<pwd> SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb it runs successfully and created the package and synonym but when i tri...
Categories: DBA Blogs

LOB compression

Tue, 2018-11-27 07:46
Hello, I have a table with BLOBs and they are actually character logs generated by fax transmission software. They are about 4k-16k big and pretty redundant, they compress with zlib to just about 1k-2k. And I want to store them compressed in DB. N...
Categories: DBA Blogs

return multiple address to mail_pkg

Mon, 2018-11-26 13:26
Hi Tom. I'm trying to get your package mail_pkg (found it on this site) to accept multiple values for the p_to (recipient list). begin mail_pkg.send ( p_sender_email => 'me@acme.com', p_from => 'Oracle Database Account <me@acme.co...
Categories: DBA Blogs

Merge vs Update

Mon, 2018-11-26 13:26
MERGE INTO pkt_prty_fncl_st ppst USING tmp_pkt_prty_fstate_clnb_stgg tmp on (tmp.fncl_ast_id = ppst.fncl_ast_id AND tmp.prty_id = ppst.prty_id AND tmp.pkt_pcsg_st_cd = ppst.pkt_pcsg_st_cd AN...
Categories: DBA Blogs

Select first value if exists, otherwise select another value

Mon, 2018-11-26 13:26
Hello I have a table like this <code>ID NTYPE 1 0 2 0 3 1 4 2</code> I need a select to get all IDs according of a list of NTYPE (1 to N), but if any of the NTYPE list does not exist then get where NTYPE = 0.. ...
Categories: DBA Blogs

removing control characters from text

Sun, 2018-11-18 22:06
Is there a routine in Oracle that can test for and remove unwanted characters in a text string, ie control characters?
Categories: DBA Blogs

Migration from 11g to 12c change execution plan(Adaptative plan)

Fri, 2018-11-16 15:06
Hi, we are working on a PeopleSoft Migration and Database too. We're migrating Oracle 11.2.0.3 to 12.2.0.1, so we have an issiue with a PeopleSoft Query. The query on actual database enviroment(11.2.0.3), have a excecution plan with minimal cost ...
Categories: DBA Blogs

pushing predicate into union-all view

Fri, 2018-11-16 15:06
Hi, LiveSQL link: https://livesql.oracle.com/apex/livesql/s/hjml6z0yg45qznob5sebg53vk I have the big table with an index on ID: <code> create table tst1 as select level id, mod(level, 10) code from dual connect by level < 1000000; create...
Categories: DBA Blogs

Nvarchar to Varchar2 conversion (UTF8 to AL32UTF8)

Thu, 2018-11-15 20:46
We are planning to convert all NVarchar fields to Varchar2 fields as we're going to change our character set and since Oracle recommends AL32UTF8 character set encoding. My question is it 100% sure that all characters from Nvarchar (UTF8) can be conv...
Categories: DBA Blogs

sql plan management - difference in defining parameters at system and session level

Thu, 2018-11-15 20:46
Hi Tom, I am very new to performance tuning. there's something that I am unclear about sql plan management. which one is faster - 1. setting OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE to TRUE at session level (inside function body) and OPTIMIZE...
Categories: DBA Blogs

Moving Oracle DB from one server to another

Thu, 2018-11-15 20:46
Hi, I am having an Oracle 11g database in an AIX linux server. I am planning to move this to a different server with same OS. I will be using same version of Oracle database in target DB as well. I have multiple schema in source database and in t...
Categories: DBA Blogs

Log DML, DDL and DCL user activity

Thu, 2018-11-15 02:26
Hello, Ask TOM Team. I want to know if there's a straightforward (not using triggers or things like that lol) way to log DML, DDL and DCL user activity on specific objects (12c). I do not know if <b>Database Vault</b> can help me with that. Any Do...
Categories: DBA Blogs

Pages