DBA Blogs

How true is the DBMS_UTILITY.GET_TIME function

Tom Kyte - Wed, 2017-06-28 19:26
I was trying to verify how true would be the values returned by the function DBMS_UTILITY.GET_TIME using the below code. <code>DECLARE curr_time1 DATE := SYSDATE; curr_time2 NUMBER := dbms_utility.get_time; BEGIN LOOP EXIT WHEN SYSD...
Categories: DBA Blogs

Oracle GoldenGate Cloud Service

Pakistan's First Oracle Blog - Wed, 2017-06-28 18:37
Even on Amazon AWS, for the migration of Oracle databases from on-prem to Cloud, my tool of choice is GoldenGate. The general steps I took for this migration was to create extract on source in on-prem, which sent data to replicat running in AWS Cloud in EC2 server, which in turn applied data to cloud database in RDS.




I was intrigued to see this new product from Oracle which is Oracle GoldenGate Cloud Service (GGCS).

So in this GGCS, we have extract, extract trail, and data pump running in the on-prem, which sends data to a Replication VM node in Oracle Cloud. This Replication VM node has a process called as Collector which collects incoming data from the on-prem. Collector then writes this data to a trail file from which data is consumed by a Replicat process and then applied to the cloud database.

This product looks good as it leverages existing robust technologies and should become default way to migrate or replicate data between oracle databases between on-prem and cloud.
Categories: DBA Blogs

Partner Webcast – The Rise of Chatbots: Oracle Intelligent Bot Service

"A chatbot is a computer program which attempts to maintain a conversation with a person". - Wikipedia Intelligent bots are computer programs that leverage artificial intelligence to...

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

Effects if WWV_FLOW_FILES table has the data cleared

Tom Kyte - Wed, 2017-06-28 01:06
Hello, This might be a simple question, but I wanted to be 100% sure that nothing will happen if the data is cleared from the WWV_FLOW_FILES table. Does this have any affect on anything major within Apex?
Categories: DBA Blogs

Autorestart a singleton database on Grid Infrastructure?

Tom Kyte - Wed, 2017-06-28 01:06
Good Afternoon, We have a grid infrastructure clustered environment that holds only singleton databases, so the instances are only active on one node. The instances are able to be started on other nodes, obviously. We have setup failover rules; ...
Categories: DBA Blogs

Delete a table from another schema after fetch cursor

Tom Kyte - Wed, 2017-06-28 01:06
Hi Guys, Iam fairly new to oracle and was asked a question in an interview about what happens when a table from another schema gets deleted by an user while the same table was used in a cursor by another user and the deletion happens before fet...
Categories: DBA Blogs

SQL Tuning Advisor- not really sure what it does

Tom Kyte - Wed, 2017-06-28 01:06
Good Morning, I am trying to understand what each of the three settings for the CBO really do. 1) There is the "normal", which allows the CBO to run as usual to create execution plans within a short time. 2) There is the Tuning Mode- Limit...
Categories: DBA Blogs

Find name of reference partitioned child tables

Tom Kyte - Wed, 2017-06-28 01:06
We want to delete the records from ?child tables (for e.g. `child1', `child2?, etc?)? before start deleting the records from parent table in a specific partition we are interested in for e.g. 'P_COMPLETED_20160519'. Child tables are partitioned using...
Categories: DBA Blogs

Create Insert Statements Dynamically

Tom Kyte - Wed, 2017-06-28 01:06
Tom,How do you create insert statments dynamically if I give a table name? in TOAD tool, they have this option for each table [Create insert statements] and I was wondering what kind of logic they might have used to create them. I was trying it my...
Categories: DBA Blogs

escape semicolon ?

Tom Kyte - Wed, 2017-06-28 01:06
hi Tom, I have a big script file that when executed gives an error: "ORA-01756: quoted string not properly terminated" I've reduced it to identifying as the semicolon being the problem: update table_x set x_sql_statement= 'DECLARE l_stat...
Categories: DBA Blogs

Log Buffer #515: A Carnival of the Vanities for DBAs

Pythian Group - Mon, 2017-06-26 09:15

This Log Buffer Edition covers Oracle, SQL Server and MySQL.

Oracle:

You may wish to clone an Oracle Home (for example you have all your databases on a single Oracle Home, but you want to separate Development from Test

Removing Outliers using stddev()

Installing Scala and Apache Spark on a Mac

Introduction to Oracle Big Data Cloud Service – Compute Edition (Part V) – Pig

More on Optimistic Locking with ORA_ROWSCN

SQL Server:

How to Decipher sysschedules

SQL Azure Reporting Automation

Database Code Analysis

Visual Studio 2017 and Swagger: Building and Documenting Web APIs

Techniques to Cleanse Bad Data in SQL Server

MySQL:

ClickHouse in a General Analytical Workload (Based on a Star Schema Benchmark)

Debian 9 and MySQL: Watch Out!

How to Install LAMP on Ubuntu 16.04

Linux perf and the CPU regression in MySQL 5.7

Secure Binlog Server: Encrypted binary Logs and SSL Communication

Categories: DBA Blogs

Cross platform migration using GG

Tom Kyte - Sat, 2017-06-24 23:46
Goldengate is primarily sold as an Replication software, I understand. Can you clarify whether GG can be used to migrate from one database platform to Oracle. Say, MSSQL for example? Or is it required to use SQL Dev to convert the metadata from...
Categories: DBA Blogs

Package Calling Error

Tom Kyte - Sat, 2017-06-24 05:45
Hi, I created a package Order_headers with two procedures price and quantity in Tan Schema .called them in other procedure like this tan.order_headers.price; but i got Error like PLS-00302: component Component 'order_headers' must be declared. but...
Categories: DBA Blogs

based on multiple packages and procedure

Tom Kyte - Sat, 2017-06-24 05:45
Can we access the procedure in a package outside the package using another package? i.e. i need to access a package procedure outside of another package (i need to call from 2nd package to access the first package procedure)
Categories: DBA Blogs

Using ZFS compression for all database files

Tom Kyte - Sat, 2017-06-24 05:45
Hi Tom, I am hoping for some expert advice on the above, We have a T5 server using SAN storage which is allocated by ZFS. We have Solaris 11 OS and Oracle Database 12c with single instances (no RAC) and we do not use ASM, our database is limi...
Categories: DBA Blogs

SQR with 077 umask creates file with 611 permissions

Bobby Durrett's DBA Blog - Fri, 2017-06-23 11:18

I ran across this strange situation. An SQR opened a new data file for output and created the file with 611 permissions. We needed group read access so this caused a problem. I knew that our login script for the PeopleSoft Unix user set umask to 022 but that without running the login scripts the mask is 077. So, my first thought was that we had started the process scheduler without running the login scripts and the mask was 077. But, why would the file that the SQR created be 611 permissions and not 600? The 077 mask should get rid of all the group and others bits. I built a simple test case to show that the SQR creates the file with 611 permissions with a 077 mask.

Here is the test SQR:

begin-report
  Let $Unix_Cmd = 'umask'
  Call System Using $Unix_Cmd #Status
  Let $Unix_Cmd = 'rm /tmp/bobby/bobby.txt'
  Call System Using $Unix_Cmd #Status
  let $testfile = '/tmp/bobby/bobby.txt'
  open $testfile as 1 for-writing  record=1500:fixed
  close 1
  Let $Unix_Cmd = 'ls -l /tmp/bobby/bobby.txt'
  Call System Using $Unix_Cmd #Status
end-report

Here is its output:

SQR for PeopleSoft V8.53.05
077
-rw---x--x   1 psoft      psoft            0 Jun 23 10:54 /tmp/bobby/bobby.txt

Notice the execute bits for group and others.  Why were they not masked out? Also, the default permissions to create a file is 644. So, creating a new file should not set the execute bits at all no matter what mask you are using.

I created a Korn shell script to do the same thing as the SQR:

umask
rm /tmp/bobby/bobby.txt
touch /tmp/bobby/bobby.txt
ls -l /tmp/bobby/bobby.txt

Here is its output:

077
-rw-------   1 psoft      psoft            0 Jun 23 10:58 /tmp/bobby/bobby.txt

Notice that there are no group and others bits which is what I expected with a 077 mask. I tried searching the Internet for SQR and 611 permissions but could not find anything.

As it turns out, we did start the process scheduler with umask 077 so I just modified the script that started it to set umask 022 and that resolved the problem. Here is the output from my test SQR with umask 022:

SQR for PeopleSoft V8.53.05
022
-rw-r--r--   1 psoft      psoft            0 Jun 23 11:01 /tmp/bobby/bobby.txt

This is what we wanted and of course the Korn shell script does the same thing as it should.

022
-rw-r--r--   1 psoft      psoft            0 Jun 23 11:02 /tmp/bobby/bobby.txt

Seems very odd to me. Anyway, I hope that this post helps someone.

This was on HP-UX 11.31 and PeopleTools 8.53.05

Bobby

Categories: DBA Blogs

Partner Webcast – Delivering Contextual Collaboration and Engagement with Oracle Content and ...

The key to digital business transformation are the productivity tools and services that employees rely on to deliver business value. However, the digital workplace of today requires a new set of...

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

where are sql tuning sets stored and how can you tell how much space a specific one or all of them are using?

Tom Kyte - Thu, 2017-06-22 16:46
It takes a certain amount of database access to be able to create a sql tuning set. It is more than a little fuzzy to me what kind of object they are in the database. My question though is a little more focused than that ( maybe ha ha ). How can...
Categories: DBA Blogs

Roles and Privileges

Tom Kyte - Thu, 2017-06-22 16:46
Hi, Good Day ! Today we have a structured approach to access control, based on use of schemas (to group together database objects), and assigning specific permissions to roles. 2 roles : - USER_ROLE = CONNECT + SELECT/INSERT/UPDATE/DELETE...
Categories: DBA Blogs

Unrolling loop speeds up program

Bobby Durrett's DBA Blog - Thu, 2017-06-22 15:55

This is a follow-up to my earlier post about the assembly language book that I am working through. I have struggled to speed up a program using something that the book recommends, unrolling a loop. I think I have finally found an example where unrolling a loop speeds up a program so I wanted to share it.

I am working on Chapter 17 Exercise 2 of the book which asks you to write a program to find the longest common substring from two strings. I choose an inefficient and simple way to find the common substring and tried to speed the program up without changing the algorithm.

Here is the C version on GitHub: url

The core of the program is three loops. The outer loop tries each character in string 1 as the start of the substring. The middle loop tries each character in string 2 as the start of the substring. The inner loop advances through both strings until it finds the end of the common substring.

The C version ran in 27.2 seconds.

I built an assembly version that uses registers for most of the variables and it ran in about 11.7 seconds. It has the same three loops. Assembly register version: url

I tried to improve on the 11.7 seconds by unrolling each of the three loops. Unrolling the outer and inner loops resulted in no improvement in runtime. I was about to give up but finally decided to try unrolling the middle loop and it caused the program to run in 10.2 seconds. Very cool. Assembly unrolled middle loop version: url

I had to figure out how to use %rep, %assign, and how to have a label that I based on a nasm variable such as .skipif %+ i.

Kind of fun. I realize that this is off topic for my “DBA Blog” but it is something I’m playing with so I thought I would throw it out there. It doesn’t hurt a DBA to know some low-level computer science, even if you are not using it directly in your job. Anyway, it was a bit of a struggle to come up with an example that was faster with the loop unrolling and I think that I have found one.

Bobby

Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs