DBA Blogs

Create table using existing table and copy column defaults

Tom Kyte - Thu, 2018-01-11 09:46
Hi Tom, I am creating table "tmp_pks1" with SID as DEFAULT 1. <code>SQL> CREATE TABLE tmp_pks1(SID NUMBER DEFAULT 1, NAME VARCHAR2(50) NOT NULL); Table created. SQL> CREATE TABLE tmp_pks2 AS SELECT * FROM tmp_pks1; Table created. SQ...
Categories: DBA Blogs

how to read clob data as varchar which has length morethan 4000

Tom Kyte - Thu, 2018-01-11 09:46
I have an XML data in Oracle with ClOB as Datatype. I have to read that clob data as Varchar. the following is the sample data. <code>"<POSLog> <Transaction> <UnitID>1101</UnitID> <WorkstationID>1</WorkstationID> <TransSequenceN...
Categories: DBA Blogs

Reference Partitioning in 11g

Tom Kyte - Thu, 2018-01-11 09:46
The new 11g Reference Partitioning option requires a foreign key on the child table, which requires a unique key on the parent table. To create a unique key on a partitioned table, you either need to include the partitioning key to get a local index...
Categories: DBA Blogs

Redacting the Number Column using DBMS_REDACT

Tom Kyte - Thu, 2018-01-11 09:46
I have redacted the Number Column of a Database Table.The column contains Check Number which is ten digits. The redaction on this Check Number column is partially masked with 0 for first three digits. When I query the column from the Database, it ...
Categories: DBA Blogs

How to Extract PL/SQL Source Code Diffs ignoring comments, spaces, linefeeds and alike

Tom Kyte - Thu, 2018-01-11 09:46
Hi : I need to compare all the PL/SQL source code (ie, Procedures, Functions, packages, and Triggers) between two databases (let's say, Homo and Prod) to get the diffs. Point is, I want to get only functional diffs, ie, only meaningful source cod...
Categories: DBA Blogs

oracle commit writes to redo, but not to datafiles

Tom Kyte - Thu, 2018-01-11 09:46
Hi Tom, I want to verify once the commit the transaction, only writes to redo log, but does not write to data files. How the verify the situation??
Categories: DBA Blogs

Drop an Oracle user (Schema) using JDBC java

Tom Kyte - Thu, 2018-01-11 09:46
Hi Tom; I am developing a java application using oracle as database. I connect to the database once per program execution using : try { pds = PoolDataSourceFactory.getPoolDataSource(); pds.setConnectionFactoryClassName("oracle.jdbc.pool.O...
Categories: DBA Blogs

SPM accepted plan with different bind variable

Tom Kyte - Thu, 2018-01-11 09:46
<code>Hi Tom, I have POC on my lab about SQL Plan Management and found some thing that not make sense. My Oracle Database Version is "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production" with a single node, parameter ...
Categories: DBA Blogs

Get rows for first where condition

Tom Kyte - Wed, 2018-01-10 15:46
we face some issue as <code>select * from t where a is null or b is null or c is null</code> the question is if the query find the first where (a is null) then retrieve it only and not retrieve other where statment(b is null or c is null) can...
Categories: DBA Blogs

Very slow network interaction between Oracle DB and java application

Tom Kyte - Wed, 2018-01-10 15:46
I have a problem with my Oracle DB network speed. First of all, what's the essence of the problem. There are java application on my computer and Oracle DB on a remote server. Connection speed between them is about 2,5MB/s. I execute in my java app...
Categories: DBA Blogs

Update Statement with Duplicate Rows and Without

Tom Kyte - Wed, 2018-01-10 15:46
Hi, i created two table emp, emp2 from hr.employees. after that i executed below insert statements now in both tables employee_id is unique. <code>insert into emp select * from emp; --4 times insert into emp2 select * from emp2; --4 time...
Categories: DBA Blogs

Why we should segment advisor to get fragmented space for tablespaces where segment space management is AUTO

Tom Kyte - Wed, 2018-01-10 15:46
Hi AskTom Team, Normally, to get fragmentation we do (num_rows*avg_row_length) from dba_tables and compare that with bytes in dba_segments to see if there is any fragmentation. But for tablespaces with segment space management "AUTO" oracle re...
Categories: DBA Blogs

Split one string into multiple rows

Tom Kyte - Wed, 2018-01-10 15:46
Hi , I have a row that may contains upto 6000 char length string in a column. I want to check the length of string and then split the string into smaller string each of length 2000 and insert that into a second table. I want to do that in sql only. ...
Categories: DBA Blogs

Questions on DBMS_REDACT

Tom Kyte - Wed, 2018-01-10 15:46
Our applications works with ADF as UI and Oracle Database 12c in the Backend. As a regulation requirement,we have to mask few columns of the applications.The Entity Objects in the ADF are based on the Views. We have tried addressing these Col...
Categories: DBA Blogs

To Merge or Not to Merge (Instances) ??

Tom Kyte - Wed, 2018-01-10 15:46
Should we follow our Oracle DBA directive and merge our 5 instances into 1? That's the question!!?? A little background: Our Oracle EPM consultant installed our Oracle OBIEE and EPM apps (e.g. Hyperion Planning, HFM, DRM, FDMEE etc) using 5 ...
Categories: DBA Blogs

Identify free space gaps in a datafile

Tom Kyte - Wed, 2018-01-10 15:46
Oracle 11.2.0.4 12.1 and 12.2. You recently responded to a thread about reclaiming space.and suggested moving an object so it will not be at the end of a data file. https://asktom.oracle.com/pls/apex/f?p=100:11:33323285896587::NO::: Is the...
Categories: DBA Blogs

Using escape character --> ORA-01425

Tom Kyte - Wed, 2018-01-10 15:46
If I use the tilde to escape an underscore in SQL it works fine: select x from ( select 'A_Babc' x from dual union all select 'A$Bxyz' x from dual ) where x like 'A~_B%' escape '~' <b>X ------ A_Babc</b> However, when u...
Categories: DBA Blogs

"BEFORE EACH ROW" Trigger causes table locks without updating primary key column

Tom Kyte - Wed, 2018-01-10 15:46
Hi Tom, I've found a strange behaviour with our "BEFORE EACH ROW" triggers. In our database we have some tables that are refenrenced very often (50-80 references). All our tables have five common columns PK, CREATIONTIME, CREATIONUSER, UPDAT...
Categories: DBA Blogs

50K views on my YouTube Channel

Hemant K Chitale - Wed, 2018-01-10 01:06
My YouTube channel on Oracle has now exceeded 50thousand views.  A few more subscribers and the subscriber count will exceed 500.

Thank you all !

I have been busy for the past few months but, hopefully, in 2018 will keep adding to my YouTube and BlogSpot content.

.
.
.
 
Categories: DBA Blogs

Transfer table from sql server to oracle db

Tom Kyte - Tue, 2018-01-09 21:26
Hi Team, Thanks for your continues help. I want transfer one table(iccpayment) from sql server to oracle db. And also i want create schedule to this table because everyday user load data to sql server table same also happen in oracle. Pleas...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs