DBA Blogs

sketchnote of Gael Colas on ‘Devops’

Matt Penny - Fri, 2017-07-14 10:56


Categories: DBA Blogs

update rows from multiple tables (correlated update)

Learn DB Concepts with me... - Fri, 2017-07-14 10:01

Cross table update (also known as correlated update, or multiple table update) in Oracle uses non-standard SQL syntax format (non ANSI standard) to update rows in another table. The differences in syntax are quite dramatic compared to other database systems like MS SQL Server or MySQL.
In this article, we are going to look at four scenarios for Oracle cross table update.

Suppose we have two tables Categories and Categories_Test. See screenshots below.

lets take two tables TABA & TABB:

Records in TABA:















Records in TABB:













1. Update data in a column LNAME in table A to be upadted with values from common column LNAME in table B.

The update query below shows that the PICTURE column LNAME is updated by looking up the same ID value in ID column in table TABA and TABB.

 update TABA A
set (a.LNAME) = (select B.LNAME FROM TABB B where A.ID=B.ID);















2. Update data in two columns in table A based on a common column in table B.

If you need to update multiple columns simultaneously, use comma to separate each column after the SET keyword.

update TABA A
set (a.LNAME, a.SAL) = (select B.LNAME, B.SAL FROM TABB B where A.ID=B.ID);



Categories: DBA Blogs

SQL Developer version confusion 4.2 vs 17.2

Tom Kyte - Thu, 2017-07-13 07:46
I am somewhat confused about sql developer versions - the latest I have installed from OTN is 4.2 and then I come across - ThatJeffSmith - SQL Developer v17.2 is now Available - Please clarify. Thanks.
Categories: DBA Blogs

SQL Time Remaining

Tom Kyte - Thu, 2017-07-13 07:46
Hi Tom, I use v$session_longops for estimating the time remaining on a long running sql. I have noticed recently a sql that was running for more than 2 hours was not showing up in v$session_longops and the only wait event i see was DB File Sequent...
Categories: DBA Blogs

OSGi Support in Oracle Weblogic Server

Starting with 12.1.2 , Weblogic supports OSGi. I wanted to blog about this since I got this question several time in various events. OSGi is a modularity system in Java that is maintained by OSGi...

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

sqloader load multiple file into 1 table

Tom Kyte - Wed, 2017-07-12 13:26
Hi Tom, I have multiple csv-files in a directory. This directory will be updated with new csv-files. I need to load all csv files with sqloader in 1 table. So all the files have the same columns only different data. This is how my control file...
Categories: DBA Blogs

Create Type by using %type columns

Tom Kyte - Wed, 2017-07-12 13:26
Hi TOM, I want to write an extract utility, which will get data from selected columns of multiple tables so planning to use pipeline function which will return a ORACLE TYPE. To create type, I would like give reference of column type from source...
Categories: DBA Blogs

Difference between stale object result from *_tab_statistics and gather_schema_stat with "LIST STALE"

Tom Kyte - Wed, 2017-07-12 13:26
I am trying to find all stale objects. As I understand there are two ways and both should return same result. Before starting I first did a flush monitoring <code> begin dbms_stats.flush_database_monitoring_info; end; / </...
Categories: DBA Blogs

Pivot with total

Tom Kyte - Wed, 2017-07-12 13:26
<code>create table ticket1 (ticketid number, tcktname varchar2(10), status varchar2(10) ); INSERT INTO ticket1 VALUES (101,'bug','open'); INSERT INTO ticket1 VALUES (102,'bug','close'); INSERT ...
Categories: DBA Blogs

Summarizing data over time - by time interval

Tom Kyte - Wed, 2017-07-12 13:26
Hello I have an application that gathers and stores data over time. Because of the applications reliance on the network and other functions the data is gathered at irregular intervals. example table TimeStamp Object Value --------- ...
Categories: DBA Blogs

SQL over PL/SQL

Tom Kyte - Wed, 2017-07-12 13:26
Hi Team, Could you please have a look at below scenario: I have 3 tables: select * from tab_login_details; select * from tab_request; select * from tab_access; Basically i need output as below: FK_TB_LOGIN_MASTER FK_TB_COMPANY_DETAILS FL...
Categories: DBA Blogs

1000 Column Limit populating a collection (ORA-00939)

Tom Kyte - Wed, 2017-07-12 13:26
Hi, I have a need to work with a collection of composite data type with more than 1000 columns in it. Here is the sample code below for collection with composite data type of 2 columns. <code> CREATE OR REPLACE TYPE obj_typ1 AS OBJECT (col...
Categories: DBA Blogs

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

Pythian Group - Wed, 2017-07-12 11:02

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

Oracle:

12.2 New Feature: the FLEX ASM disk group part 2

Oracle ASM in Azure corruption – follow up

Set-based processing

ADF 12c BC Proxy User DB Connection and Save Point Error

Enabling A Modern Analytics Platform

SQL Server:

Batch SSIS pkg execution from Business Intelligence Development Studio

Find Database Connection Leaks in Your Application

Troubleshooting CPU Performance on VMware

SQLskills Wait Types Library now shows SentryOne data

PowerShell Tool Time: The Tool Framework

MySQL:

Installing Zabbix into Azure using a MySQL PaaS

Streaming Global Cyber Attack Analytics with Tableau and Python

Thread Statistics and High Memory Usage

On slave_parallel_workers and the logical clock

RDS / Aurora OS monitoring with Monyog v8.1.0

Categories: DBA Blogs

Is there a possibility to use db_link dynamically without using cursor and execute immediate?

Tom Kyte - Tue, 2017-07-11 19:06
Hi, I would like to know if I am able to implement db_link dynamically without using cursor or execute immediate? I have 2 tables stored in different location which are accessible via db_link. These 2 tables are identical in structure and the data c...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs