DBA Blogs

Why is the optimizer not choosing INDEX FULL/RANGE SCAN (MIN/MAX) with KEEP function

Tom Kyte - Thu, 2017-05-25 10:46
Hello, I have a function MAX(b) KEEP(DENSE_RANK LAST ORDER BY a) and want to get execution plan like this: <code> ---------------------------------------------------- | Id | Operation | Name | Rows | -----------------------...
Categories: DBA Blogs

Query to find missing sequence between number and date

Tom Kyte - Thu, 2017-05-25 10:46
Hi Tom, suppose i have table like this, Acc_number Opening_date (MM/DD/YYYY) 123 01-01-2015 125 01-02-2016 126 01-04-2016 128 01-01-2014 (break the rule) 129 01-07-2016 Here account n...
Categories: DBA Blogs

Find all routes possible from the given source and destination values in table

Tom Kyte - Thu, 2017-05-25 10:46
I have a table containing columns(source , destination).Suppose it has values as given below: select * from t; Source Destination ---------- ----------------------- 1 Pune Mumbai 2 Mumbai Pune 3 Nashik Goa 4 Goa Nas...
Categories: DBA Blogs

Is it possible detected that a nested table is updating in a trigger?

Tom Kyte - Thu, 2017-05-25 10:46
hi everyone, I've a trouble because I'm trying detect in a trigger if a nested table has been changed or not. I'm using the function UPDATING([column_of_name]) but doesn't work with this column type. Only I want detect that the column has been cha...
Categories: DBA Blogs

tkprof formatted trace file

Tom Kyte - Thu, 2017-05-25 10:46
Hey How can I look into the tkprof formatted trace file after executing the following commands for my function :- 1) exec dbms_monitor.session_trace_enable(waits=>true) 2) run the function 3) exec dbms_monitor.session_trace_disable T...
Categories: DBA Blogs

Strange Behavior in 11.2.0.4 Instant Client

Tom Kyte - Thu, 2017-05-25 10:46
My developer has a trigger that I can run, without issue, in 12.1.0.2 sqlplus on my DB server. He, however, cannot get it to run from the application server - it is using the 11.2.0.4 instant client. The code in question is: <code> CREATE OR REP...
Categories: DBA Blogs

Invalid Date in PeopleCode in App Engine

Tom Kyte - Thu, 2017-05-25 10:46
I am receiving the following error: Start Main - Non-Title IV (0,0) Invalid date. (180,112) UWX_FA_NOTIV.MAIN.GBL.default.1900-01-01.GetStuds.OnExecute PCPC:2263 Statement:4 Process 3136112 ABENDED at Step UWX_FA_NOTIV.MAIN.GetStuds (Peopl...
Categories: DBA Blogs

collection error

Tom Kyte - Thu, 2017-05-25 10:46
<u>why below error is comming for this code?</u> declare type tt is table of test_tab%rowtype index by binary_integer; vt tt; begin for i in (select id from test_tab1) loop select name bulk collect into vt from test_tab where f...
Categories: DBA Blogs

Using Oracle Compute API Part 1 of 3 - Shell Script for getting API Authentication Cookie

You can programmatically provision and manage Oracle Compute Cloud Service instances and the associated storage and networking resources by using a REST (REpresentational State Transfer) application...

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

PL/SQL types scope and memory usage

Tom Kyte - Wed, 2017-05-24 16:26
Consider a generic set of type definitions for use within a package. <code> TYPE TYPE_TABN1 IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE TYPE_TABV1 IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; TYPE TYPE_REC1 IS RECORD (COL1 VARC...
Categories: DBA Blogs

Add ON DELETE CASCADE to foreign key used in partition by reference

Tom Kyte - Wed, 2017-05-24 16:26
Is it possible to add ON DELETE CASCADE to a foreign key that is used as a partitioning key when used with PARTITION BY REFERENCE? My solution would be to drop the constraint and recreate it, but it doesn't work, as I cannot drop a constraint that...
Categories: DBA Blogs

Oracle External Table - Field terminated by clause

Tom Kyte - Wed, 2017-05-24 16:26
I have a flat file with the following record format: 2017-05-01 17:12:28yAuth_Referraly71631y6803448.70 Notice the field sep is a small y with 2 dots above it. This is windows extended ascii for FF, or 255. I'm unable to designate that characte...
Categories: DBA Blogs

DBMS Scheduler executable external job getting failed

Tom Kyte - Wed, 2017-05-24 16:26
i have created dbms schedular executable external job to run shell script ,it is getting failed. please find below begin dbms_scheduler.create_job ( job_name => 'change_ip_address', job_type => 'EXECUTABLE', ...
Categories: DBA Blogs

Replace backslash and double quotes inside a string using sqlldr

Tom Kyte - Wed, 2017-05-24 16:26
Hi Tom. I need your help. I'm trying to loader data using sqlldr to an Oracle 11g database. My data has a column that sometimes comes like below: ,"\"Taiwan Mobile\"", My ctl has: FIELDS TERMINATED BY "," optionally enclosed by '"' ...
Categories: DBA Blogs

X86-64 Assembly Book

Bobby Durrett's DBA Blog - Wed, 2017-05-24 15:32

I have written earlier blog posts about my diversion from studying Oracle to studying computer science. Here are some relevant posts: url1,url2,url3,url4. After finishing the math for computer science online class and book that I was working on I stared working through a book about assembly language programming. I bought the book in a frenzy of enthusiasm about studying computer science for fun. But then I had to decide if I was going to work through the assembly book now and delay moving on to the algorithms class using Python that I had intended to do next. I intended to use the math that I studied to prepare me for the algorithms class. But, assembly is a nice low-level hardware oriented thing to study and a contrast from the math, computer science theory, and higher level Python scripting language. So, I decided to delay my study of algorithms and work on assembly.  I’m working on the last exercise of the 16th chapter out of 19 in the book and thought I would start this blog post to document my experience so that others might benefit from it. The book is Ray Seyfarth’s  “Introduction to 64 Bit Assembly Language Programming for Linux and OS X“. I have saved my work on the exercises on GitHub: repository.

I want to let people know what type of environment and tools that I used so they can compare notes with my experience if they decide to work through the book. I started out on an Oracle Enterprise Linux 7 virtual machine running under VirtualBox on my laptop. Oracle’s Linux is a version of Red Hat Linux. I believe that I had to compile YASM and the author’s ebe tool. It has been a while but I think I had to search around a bit to get the right packages so that they would compile and I had some parts of ebe that never worked correctly. Starting with Chapter 9 Exercise 2 I switched from YASM, the assembler recommended by the author of the book to NASM, a more commonly used assembler. I switched because I hit a bug in YASM. So, chapter 9 exercise 1 and earlier were all YASM. Also, after the early lessons I moved from the GUI ebe debugger to the command line gdb debugger. I wanted to get more familiar with gdb anyway since I use Linux for my job and might need gdb to help resolve problems. After I got a new laptop I switched to using Centos 7 on VirtualBox. I was able to install nasm and gdb using yum from standard repositories and did not have to do any manual compilation of development tools in my new environment. So, if you choose to work through the book you could go the nasm and gdb route that I ended up with if you have challenges installing and using ebe and yasm. There are some minor differences between nasm and yasm but they are pretty easy to figure out using the nasm manual.

There are connections between x86-64 assembly programming on Linux and my job working with Oracle databases. At work, 64 bit Linux virtual machines are our standard Oracle database platform. They are also the building blocks of the cloud. You see a lot of 64 bit Linux on Amazon Web Services, for example. So, I’m really kind of doing assembly language for fun since it is so impractical as a programming language, but at the same time I’m doing it on the platform that I use at work. Maybe when I’m looking at a dump in an Oracle trace file on Linux it will help me to know all the registers. If I’m working with some open source database like MySQL it can’t hurt to know how to debug in gdb and compile with gcc.

The assembly language book also connected with my passion for performance tuning. The author had some interesting things to say about performance. He kind of discouraged people from thinking that they could easily improve upon the performance of the GCC C compiler with all of its optimizations. It was interesting to think about the benefits of SIMD and how you might write programs to work better with pipelining and the CPU’s cache. It was kind of like Oracle performance tuning except you were looking at just CPU and lower level factors. But you still have tests to prove out your assumptions and you have to try to build tests to show that what you think is so will really hold up. Chapter 16 Exercise 1 is a good example of SIMD improving performance. I started with a simple C version that ran in 3.538 seconds. An AVX version of the subroutine did 8 floating point operations at a time and ran the same function in 2.1057 seconds. Here are some of the AVX instructions just for fun:

    vmovups ymm0,[x_buffer]      ; load 8 x[i] values
    vmovups ymm1,[rsi+r10*4]     ; load 8 x[j] values
    vsubps ymm0,ymm1             ; do 8 x[i]-x[j] ops
    vmulps ymm0,ymm0             ; square difference

Generally, x86-64 assembly ended up feeling a lot like C. The book has you use a variety of calls from the C library so in the later chapters the assembly programs had calls to a lot of the functions that you use in C such as printf, scanf, strlen, strcmp, and malloc. Like C it was common to get segmentation faults without a lot to go on about what caused it. Still the back trace (bt) command in gdb leads you right to the instruction that got the error so it some ways it was easier to diagnose segmentation faults in assembly than I remember it being in C. It brought back memories of taking C in college and puzzling over segmentation faults and bad pointers. It also made me think of the time in a previous job when I progressed from C to C++ to Java. I came out of school having done a fair amount of C programming. Then I read up on C++ and object-oriented programming. But C++ still had the segmentation faults. Then I found Java and thought it was great because it gave you more meaningful error messages than segmentation fault. Now, I have embraced Python recently because of the edX classes that I took and because of the ways I have used it at work. Working with assembly has kind of taking me back down the chain of ease of use from Python to C to assembly. I can’t see using C or assembly for every day use but most of the software that we use is written in C so it seems reasonable to have some familiarity with C and the lower level assembly that lies beneath it.

Anyway, I have three more chapters to go but thought I would put out this update now while I am thinking about it. I may tweak this post later or put out a follow-up, but I hope that it is useful to someone. If you feel inclined to study 64 bit x86 assembly on Linux I think that you will find the Ray Seyfarth book a good resource for you. If you want to talk to me about my experience feel free to leave at comment on this post or send an email.

Bobby

Categories: DBA Blogs

How To Setup Spark, Scala, Sbt and Generate Jar Files

VitalSoftTech - Wed, 2017-05-24 15:18
Prerequisites • Install Scala: http://www.scala-lang.org/download/ • Install SBT: http://www.scala-sbt.org/download.html • Install Eclipse (/Scala IDE): http://scala-ide.org/download/sdk.html • Install Spark • Create main folder for application and inside that folder, create build.sbt file Configure Create the following directory and files. mkdir -p src/{main,test}/{java,resources,scala} mkdir lib project target touch build.sbt touch project/plugins.sbt • Paste following code inside build.sbt […]
Categories: DBA Blogs

Guidance on UTL_SMTP

Tom Kyte - Tue, 2017-05-23 22:06
Hi Team, I just read the post on "Sending HTML using UTL_SMTP". I am a beginner in pl/sql. I need your assistance on how to run the "select * from v$session" thru the below code, which in turn send the output as mail in HTML format. <code> c...
Categories: DBA Blogs

Bind variable does not exist error for an sql after migration from 11.2.0.2 to 11.2.0.4

Tom Kyte - Tue, 2017-05-23 22:06
Hi Guys, I ran into some errors after migration to 11.2.0.4 version. In the older version 11.2.0.2 , i got 'ORA-00604:error occured at recursive SQL level 1 CURSOR expression not allowed' error whenever the application tried to execute many inline ...
Categories: DBA Blogs

more specific error message with bulk insert save exception

Tom Kyte - Tue, 2017-05-23 22:06
When I do a Bulk insert with save exceptions, I get a generic error message like ORA-01400: cannot insert NULL into () Is there a way to capture the specific exception (like include column name which we get on a regular insert)
Categories: DBA Blogs

MATERIALISED VIEW USING WITH CLAUSE

Tom Kyte - Tue, 2017-05-23 22:06
Greetings!!! I am trying to create a materialized view based on a FACT and 3 Dimension tables, all IOT. As per my requirement to handle complex scenarios, I need to create multiple with clauses in the materialized view. My question is: 1, As I ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs