DBA Blogs

Executing ddl statements with triggers in batch mode

Tom Kyte - Tue, 2017-05-23 22:06
We generate in our application a ddl script, which we execute later on, wrapping it by jdbcTemplate (calling from java). Everything was great until we added some triggers into the ddl script. The execution fails with the error message: "...error...
Categories: DBA Blogs

Summarising Huge tables

Tom Kyte - Tue, 2017-05-23 22:06
Hi TOM Team, I am having a Huge table containing more the 100 Billion Records . The table is containing the transactions of more than 6 years and the table Structure is as follows : Table Name : FCT_TRANS Table Structure : ACID varchar(2...
Categories: DBA Blogs

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

Pythian Group - Tue, 2017-05-23 09:50

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

Oracle:

A Sneak Peek at Oracle’s Chatbot Cloud Service and 5 Key Factors Necessary for Bot ROI

Oracle JET Hybrid – NavDrawer Template Menu/Header Structure

Oracle Enterprise Linux 6.6 AMI Available on AWS

Datascape Podcast Episode 9 – What’s Up with Oracle These Days?

Sequential Asynchronous calls in Node.JS – using callbacks, async and ES6 Promises

SQL Server:

Fixing an SSRS Password Error while Changing Credentials

Azure DWH Part 8: Accessing Azure SQL Data Warehouse with C#

Personal Data, Privacy, and the GDPR

Performance Myths : Truncate Can’t Be Rolled Back

Troubleshooting CPU Performance on VMware

MySQL:

MySQL Shell: eye candy for a future release !

MySQL 8.0: It’s doxygen time for MTR

How to login in MariaDB with OS user without password

MySQL Enterprise backup : How to setup a slave for a standalone server with zero downtime?

Command Line Aficionados: Introducing s9s for ClusterControl

Categories: DBA Blogs

PL/SQL Collections Associate Array

Tom Kyte - Tue, 2017-05-23 03:46
<code>Hi, In the below example for Associative Array Indexed by String, set serveroutput on; DECLARE -- Associative array indexed by string: TYPE population IS TABLE OF NUMBER -- Associative array type INDEX BY VARCHAR2(64); -- indexed by str...
Categories: DBA Blogs

RMAN full back vs Incremental Backup

Tom Kyte - Tue, 2017-05-23 03:46
Hii AskTOM Team. I also refer following two website for my problem. https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmcncpt.htm#BRADV109 https://www.pythian.com/blog/rman-full-backup-level-0-incrmental/ But I have confusion abou...
Categories: DBA Blogs

Named NOT NULL Constraint

Tom Kyte - Tue, 2017-05-23 03:46
How can I name a "not null" constraint that sets the NULLABLE flag in DBA/USER_TAB_COLUMNS? If a column is nullable and I add a check constraint with a name that meets our company standards, the column still shows as NULLABLE. If I alter the tab...
Categories: DBA Blogs

System trigger doesn't save some information

Tom Kyte - Mon, 2017-05-22 09:26
Hello everyone. I have a problem with a simple trigger system. Here's my code <code>CREATE TABLE COLLEGATO( ID_SESSIONE VARCHAR(30), NOME_UTENTE VARCHAR(30), IDENTIFICAZIONE VARCHAR(30), INDIRIZZO_IP VARCHAR(30), HOST_MACC VARCHAR(40), ...
Categories: DBA Blogs

How to spool each record of a sql query into different files

Tom Kyte - Mon, 2017-05-22 09:26
I have a requirement where i need to spool reach record into a different file Eg. Select * from Test_Table Output :- ABC XYZ QWE ASD .... and so on So need to generate individual files which will have the output saved File1 : A...
Categories: DBA Blogs

Accessing HTTPS link from the plsql block

Tom Kyte - Mon, 2017-05-22 09:26
Hello There, I hope you're doing well. I'm trying to access a HTTPS link but I'm facing some errors DECLARE req UTL_HTTP.REQ; resp UTL_HTTP.RESP; name VARCHAR2(256); value VARCHAR2(1024); BEGIN UTL_HTTP.SET_PROXY('proxy...
Categories: DBA Blogs

Data migration from AIX 6.1 / 11.2.0.3 to CentOS 6.9 / 11.2.0.3

Tom Kyte - Mon, 2017-05-22 09:26
Hello, I am planning to migrate DB data from ORACLE 11gR2 on AIX 6.1 to ORACLE 11gR2 on CentOS 6.9. Many gurus told me using ASM and RMAN but my site is not using both of them. By the different endian format, I think I need to use transporta...
Categories: DBA Blogs

resoution for ORA-01476: divisor is equal to zero

Tom Kyte - Mon, 2017-05-22 09:26
Hi Sir, could you please provide a solution for the below error.ora 01476 divisor is equal to zero. Table structure:emp empno| curr_bal| prev_bal |prev_to_prev_bal 1 | 46 |47 |49 2 |18 |19...
Categories: DBA Blogs

12cR1 RAC Posts -- 11 : Managing Services in RAC (with PDB) (Video)

Hemant K Chitale - Sun, 2017-05-21 11:01
I have uploaded a video on managing services (add, start, check, remove) in a 12c RAC database with a Pluggable Database.
.
.
.


Categories: DBA Blogs

"SQL Error: ORA-28112: failed to execute policy function": Can't select with VPD.

Tom Kyte - Sat, 2017-05-20 21:06
I'm a newbie at Oracle. Im trying use VPD for my exam. But i could not select with other user. You can see my code above. Sorry about my english.
Categories: DBA Blogs

UNION ALL WITH CURSOR EXPRESSION

Tom Kyte - Sat, 2017-05-20 21:06
Hi Guys...I am new to PLSQL..the below code doesnt seem to work when used with union operator..i have a nested cursor "components" which differ in the first query and the second query.. i pass this as a ref cursor to java. code below (its an exc...
Categories: DBA Blogs

Issue with the update query in oracle

Tom Kyte - Sat, 2017-05-20 21:06
0 down vote favorite I have an update query which is taking 10 hours to update and still it is in the updating stage itself it has about 135171 records only. uPDATE hr.raw_address ora SET (ora.addr_id, ) = (SELECT ...
Categories: DBA Blogs

Extract element from XML Data using SQL

Tom Kyte - Sat, 2017-05-20 02:46
<code>Dear TOM, I like to extract simple data element from XML data using SQL. Could you please help, Option1: SELECT * FROM XMLTable( '/entry/' passing xmltype( '<entry xmlns="http://www.w3.org/2005/Atom"> <id>https://toitest1....
Categories: DBA Blogs

Data Base Date Formats

Tom Kyte - Sat, 2017-05-20 02:46
Hi, I would like to know the Database Character Set and its Default Date Format and language territory details. so would you tell me which table stores those details.
Categories: DBA Blogs

Automating removal of security after duplicate

Tom Kyte - Sat, 2017-05-20 02:46
When performing a duplicate database from prod to dev monthly. We have a script that captures the current user permissions on dev via the dbms package that generates DDL to another file to be used after the duplication. Thus preserving the permissio...
Categories: DBA Blogs

Toggle column value for alternate set of rows

Tom Kyte - Sat, 2017-05-20 02:46
Tom & team, I want to toggle the value in a column to 0 or 1 for alternate set of rows, can you please let me know the optimal way to accomplish this? select empno,dept.deptno, 0 counter from emp, dept where emp.deptno = dept.deptno orde...
Categories: DBA Blogs

Insert All Record Count for different tables

Tom Kyte - Sat, 2017-05-20 02:46
Hi Tom, I am using a multi-insert statement as below in a SQL script. There are around 50 insert all statements like this in my script for different tables. INSERT ALL WHEN FLAG='Y' THEN INTO table1() values() WHEN ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs