DBA Blogs

Oracle Sort Order Issue

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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.

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

OPN Oracle Cloud Subscription, Pricing and Metering

Exclusive Live Webcast ...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Oracle SQL Repeated words in the String

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

DYNAMIC INSERT SCRIPT WITH TIMESTAMP ISSUE

Tom Kyte - Thu, 2017-01-12 15:26
Hi, I am new to oracle, i have used your create dynamic insert script for generating the insert script. But that query is taking care of only three datatypes like NUMBER, DATE and VARCHAR2(). It is not taking care about the TIMESTAMP data type si...
Categories: DBA Blogs

Use of Wrap utility for 12K LOC packages

Tom Kyte - Thu, 2017-01-12 15:26
I've create a SP to encrypt the source code of existing packages, my problem is that legacy packages contain more than 12K LoC (lines of code), with small packages I have no issue, but with the heavy ones I got : <i>06502. 00000 - "PL/SQL: nu...
Categories: DBA Blogs

Oracle 11g Exadata SQL performance Tuning books?

Tom Kyte - Thu, 2017-01-12 15:26
Hi Tom, Can you please suggest some books for Oracle 11g Exadata SQL performance Tuning books? Regards, Mayank Jain
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs