Tom Kyte

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

to_char number format

Thu, 2016-11-24 00:06
hi, I am printing a number using below to_char format : declare var number(6,2) begin var := 65.2 dbms_output.put_line(to_char(var,'9999.99')); end; the above code prints ' 65.20' (3 spaces and the number). one extra space is printed...
Categories: DBA Blogs

oracle 12c capacity planning

Thu, 2016-11-24 00:06
Hello Tom & Team, Can you please shed some light on capacity planning h/w + Oracle database configuration for Oracle 12c - Multitenant Option being used. We are planning to consolidate/upgrade 10 databases using Multitenant architecture. My thou...
Categories: DBA Blogs

Rename constraint

Wed, 2016-11-23 05:46
I am trying to rename Oracle constraint name, the below code does not works. Please can you suggest a workaround or a correction. DROP TABLE B; DROP TABLE a; CREATE TABLE a (a clob); CREATE TABLE B (a number, CONSTRAINT PK_1 PRIMARY KEY(a...
Categories: DBA Blogs

CTAS with column datatype

Wed, 2016-11-23 05:46
Hello AskTom team, Typically we code CTAS as CREATE TABLE <TABLE_NAME> AS SELECT <COL_LIST> FROM <TABLE_NAME> WHERE .. I have a requirement where i have to create a TMP table with a particular structure which is later used for exchange part...
Categories: DBA Blogs

drop pluggable database does not remove all files

Wed, 2016-11-23 05:46
Hello, I use multitenant architecture(CDB) in my daily deployments. So on my oracle server i have two "model" pluggable db with name PDB10, PDB11. These db are using in cloning process. using following statement i make duplicate of these db e.g ...
Categories: DBA Blogs

Oracle Apps Database

Wed, 2016-11-23 05:46
Hi, I would like to know about Difference between Oracle Apps(r12) Database and Oracle Database(11g)?
Categories: DBA Blogs

Sql file and batch file

Wed, 2016-11-23 05:46
Hi, I have a sql file which will take schema name prefix as the parameter.This file in turn will call various sql files and all these files use this variable to do the job. I execute this file like this: sql> @execute_scripts.sql &&1 Enter ...
Categories: DBA Blogs

12C - what does the C stand for

Wed, 2016-11-23 05:46
Hi, In Oracle 12C DataBase,is C means Cloud?
Categories: DBA Blogs

Logic to get the first row with one column value of the last duplicate row among all the duplicates

Wed, 2016-11-23 05:46
I have a table A with following data. ID Acc_no start_date verify_date completed_date ===========================================================================...
Categories: DBA Blogs

Full Export to testing with different S.O schema

Wed, 2016-11-23 05:46
Hi, I want to know if it is possible to import on a testing server, with different file locations. I.e. for example. -- A prod server with: /o1/oradata : datafiles /ssd1/oradata : datafiles -- A testing server with: /o1/oradata : da...
Categories: DBA Blogs

How to skip header and footer line a file while accessing records from external table?

Tue, 2016-11-22 11:26
Hi Tom, You guys are doing a great job. How to skip header and footer like (first and last line) in a .dat file while loading records from external table concept. I can able to remove those line in Unix but they are having information about re...
Categories: DBA Blogs

Multiple Instances for One Database

Tue, 2016-11-22 11:26
Hi, Can we create a Multiple Instances on Single Database,If we Can Create Will we Face Any issues with that multiple instance architecture? Thank you.
Categories: DBA Blogs

is the search_columns not part of building the plan_hash_value?

Tue, 2016-11-22 11:26
Hello there, I have a table with a composite primary key over 3 columns (a,b,c). Now there are 2 typical queries running: <code>select * from my_table t where t.a = ? and t.b = ?</code> <code>select * from my_table t where t.a = ? and t.b betw...
Categories: DBA Blogs

Complex sql

Tue, 2016-11-22 11:26
Hi Tom, I am a newbie to oracle and i have a requirement. I even got struck at creating the table as i want to store only time in one of the column 'login' input 1 userid login 1 9:00 2 9:15 3 9:20 1 9:25 4 10:30 ...
Categories: DBA Blogs

FULL TABLE SCAN

Tue, 2016-11-22 11:26
Hi, I have table test_tab having billins of data. One of the column used in below query is TEST_KEY with datatype VARCHAR2(76). I have a requiremnt to fecth data from particular start and end position of string whch i sthere WHERE clause below...
Categories: DBA Blogs

Store performance history of SQL

Tue, 2016-11-22 11:26
Hello Tom, Please let me know if we can store the history of a sql that we wish to, for example I would like to store the statistics like hash plan, execution, time elapsed etc for a given sqlid, this may not be a top 10 or top 100 sql.
Categories: DBA Blogs

Upgrade from Oracle 11g to 12c

Tue, 2016-11-22 11:26
Hi, We have a stable application on 11.2.0 database. Business is planning to upgrade database from 11g to 12c. Kindly advise, if anything particular we need to take care during this upgrade. Is it advisable to lock the existing explain plans befor...
Categories: DBA Blogs

Slowness on a new database

Tue, 2016-11-22 11:26
I have a primary database and a standby database. On daily basis, during night, we stop the replication between primary and standby and using a storage technology I create a clone of that standby database which I open as an standalone database in rea...
Categories: DBA Blogs

Difference between SQL and PL/SQL

Mon, 2016-11-21 17:06
Hi, What is the Exact Difference Between SQL and Pl/SQL?How This Both are Integrated to Achieve the data from database? Thank you.
Categories: DBA Blogs

INSERT with APPEND hint

Mon, 2016-11-21 17:06
Hi, I need to Copy tables data From One DB to Another DB. There are some tables having row count around 99,000,000,000. One approach I can recollect is - insert /*+ APPEND */ into local_table select * from table@database_link; But APPE...
Categories: DBA Blogs

Pages