Tom Kyte

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

Best way to change the datatype of a column in a Table with 20 million

Sun, 2017-01-15 16:46
Hi Team, Wish you a happy new Year. Welcome to Maria Colgan. I have a requirement to change the datatype of a column of number type to Varchar2(35) and then rename the table. The table has huge data around 20 millions. Since already data is the...
Categories: DBA Blogs

Creating Partitions

Sat, 2017-01-14 03:46
Hi Team, Please have a look at table structure shared via LiveSQL Link - https://livesql.oracle.com/apex/livesql/s/ee1slk0wo53esxy30e4l0ac2x table is used in application using stmt_end_date - 1. Since stmt_end_date is a part of primary key, i...
Categories: DBA Blogs

Find the number in list which is not present in Database column

Sat, 2017-01-14 03:46
I have one requirement where I need to find the first missing number in the List which is not there in Database(Oracle) column. Scenario is like this : Table 1: Link to table image is here :- <code>https://i.stack.imgur.com/iXm6N.png</code>...
Categories: DBA Blogs

Create linenumbers for sqlplus queries on the fly.

Sat, 2017-01-14 03:46
Is there a quick an easy way to create sequential line numbers on the fly (without Creating a Sequence or using PL*SQL) for a SQLplus query executed inside SQLplus? Example: Here is a live link to output: http://www.videofame.com/fragrance.asp ...
Categories: DBA Blogs

Oracle Apex applicationson company DB

Sat, 2017-01-14 03:46
Hi, I am trying to create oracle apex applications for my company,the thing i am worried about is that the applications will be complex and would require a lot of space on the DB.Can you let me know what is the default DB size that is available if...
Categories: DBA Blogs

Oracle Sort Order Issue

Fri, 2017-01-13 09:46
Hi Tom, According to Oracle document, the sort order depends on database option NLS_SORT, and there are only 2 kinds of sort: Binary Sort and Linguistic Sort. Is there any other options to change the sort order? Currently, the following sql wi...
Categories: DBA Blogs

Converting mixed Latin + Japanese text to Shift-JIS

Fri, 2017-01-13 09:46
Hi Tom, I have a string of mixed Latin and Japanese characters that must be converted to Shift-JIS when output to a particular report (not always). The DB character set is AL32UTF8. When I execute this statement: <code> convert('H. pylori?????...
Categories: DBA Blogs

Insert with select subquery hangs for a long time

Fri, 2017-01-13 09:46
<b>Oracle database version:</b> Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production "CORE 11.2.0.4.0 Production" TNS for 64-bit Windows: Version 11.2.0.4.0 - Production NLSRTL Versi...
Categories: DBA Blogs

Calculating last 5 business working days

Fri, 2017-01-13 09:46
Hi Tom, I want to get last 5 business days. If its friday today, I need from passed monday to this friday and if its monday today...last tuesday to this monday is required. Saturday and sunday need to skipped in calculating last 5 business days. ...
Categories: DBA Blogs

Frename function not working consistently in PL/SQL

Fri, 2017-01-13 09:46
Hi, I have a PL/SQL proc to rename files under directory. I am using the below function to rename the files :- UTL_FILE.FRENAME('ATTDIR', FILE_NAME2, 'ATTDIR', V_ID1, TRUE); <b>The files are existing on the directory</b> even though the P...
Categories: DBA Blogs

Imported Wrong data

Fri, 2017-01-13 09:46
Hi Team, Today by mistake I imported Wrong data from production to test server Db. I want to restore test tables to their original state .I don't have backup of test server data.I tried flash but it is not helpful here. What should I do to get ...
Categories: DBA Blogs

Is it possible to install 11G and 12C in Windows 7 64 bit.

Fri, 2017-01-13 09:46
Hi, Can you help me to resolve the issue. Scenario 1: I have 11G 64 bits running fine in Windows 64 bits but When I was trying to install 12C 64 bits in same system, Its throwing an error while creating database through DBCA. Error is: Error e...
Categories: DBA Blogs

Performance - Number vs. VARCHAR2 datatypes

Fri, 2017-01-13 09:46
Hello, I am designing a system that will store and query billions of IP addresses, both IPv4 and IPv6. I was wondering - (a) Will storing the IP as an integer (e.g Number datatype) instead of a string (e.g. VARCAHR2) give better performance in ...
Categories: DBA Blogs

Multiple DB triggers on a table for a given ledger or OU or Inventory Org

Fri, 2017-01-13 09:46
Hi, We have a requirement where the plan is to track any inserts/updates/deletes on some EBS tables. A company has operations across countries & continents. Can database triggers be created on the same table to track changes based on the Ledger (S...
Categories: DBA Blogs

Lost all controlfiles and not having database tablespace and datafiles information to create a new control file

Fri, 2017-01-13 09:46
Hello Tom/Maria/Team We ran a bad task in a cron in linux which remove all files modified 1 day ago, the database was up&running but idle=>without use bc it is test db, then the task removed control files and spfile bc it was down but I started it...
Categories: DBA Blogs

Oracle SQL Repeated words in the String

Thu, 2017-01-12 15:26
I need your suggestions/inputs on of the following task. I have the following table ID ID_NAME 1 TOM HANKS TOM JR 2 PETER PATROL PETER JOHN PETER 3 SAM LIVING 4 JOHNSON & JOHNSON INC 5 DUHGT LLC 6 THE POST OF THE OFFICE 7 ...
Categories: DBA Blogs

NO_DATA_FOUND in Functions

Thu, 2017-01-12 15:26
Tom, We've just migrated from Oracle 9.0.1 to 9.2 and, coincidence or not, I'm facing a problem I had never faced before. The NO_DATA_FOUND exception is not being raised from my PL/SQL functions anymore!!! If I put a exception block to handle t...
Categories: DBA Blogs

NEXT vs UNIFORM SIZE

Thu, 2017-01-12 15:26
Hi All, Please clarify below doubt, If I use NEXT 10M and UNIFIOM SIZE 1M, which size will my next extent will have 1M or 10M? if we use this command: create TABLESPACE ts_cvliste11_20180815 datafile '/ora1/app/oracle/oradata/L11/2018/ts_c...
Categories: DBA Blogs

MIT Prof Claims RDBMS Model is All Wrong

Thu, 2017-01-12 15:26
Hi Team, My manager recently sent out this interesting talk by the founder of Postgres and VoltDB, Michael Stonebraker. https://blog.jooq.org/2013/08/24/mit-prof-michael-stonebraker-the-traditional-rdbms-wisdom-is-all-wrong/ I am curious of ...
Categories: DBA Blogs

Historical CPU and memory usage by service

Thu, 2017-01-12 15:26
Hello Tom, Is there a database view like DBA_HIST_SYSSTAT grouping historical session statistics by user or service? I was asked by my boss to create a report showing the CPU and memory usage by department but I could not find a view with that kin...
Categories: DBA Blogs

Pages