DBA Blogs

capture old and new values of the fields using Stored Procedure

Tom Kyte - Mon, 2017-09-18 12:26
Hi, We have a requirement to store old and new values of the fields and insert into another table. For example, If we have Table A and Table B and in both the tables columns are exactly same but we need to write a procedure like whenever an...
Categories: DBA Blogs

How to pass a list of values from one procedure to another

Tom Kyte - Mon, 2017-09-18 12:26
The first step is to fetch the data. Second is to pass the list of values fetched in step 1 to another procedure. This is the table <code>CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), ...
Categories: DBA Blogs

Refreshing Materialized view in a remote database

Tom Kyte - Mon, 2017-09-18 12:26
Hi there. I have created in the database A the following MV: CREATE MATERIALIZED VIEW MRW_USER.MB_TRACK_REPORT3_FINAL_SS (SOUNDREC_CODE,ISRC,CAT_NUM,TITLE,MIX_NAME, ARTIST,RELEASE_DATE,LABEL,COMPANY,CORPORATE_GROUP, DISTRIBUTOR,DEALER_PRICE,GEN...
Categories: DBA Blogs

Splitting and storing Json string from file to table

Tom Kyte - Mon, 2017-09-18 12:26
Hi Team..Please help me on below scenario. I have data in a file as below. It has two records Id and JsonString DELIMITED By "| 1001"|{"id":"1234","code":"test1","Version":v1,"time":"2017-08-31","rank":2,"device":"TEST1234TEST","response":"appr...
Categories: DBA Blogs

Flashlogs not getting deleted

Tom Kyte - Mon, 2017-09-18 12:26
Hi Tom, I see FRA size is Filling up and when checked Flashback Logs are occupying most of it as seen below, SQL> select * from v$recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES -------...
Categories: DBA Blogs

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

Pythian Group - Mon, 2017-09-18 10:22

This edition of Log Buffer covers Cloud, Oracle, SQL Server and MySQL and much more.

Cloud:

Introducing managed SSL for Google App Engine

Using Cloud Foundry CUPS to inject Spring Security credentials into a Spring Boot Application

ClusterControl in the Cloud – All Our Resources

Monitoring Amazon Aurora Audit Events with Amazon CloudWatch

Integrating Teradata with Amazon Redshift Using the AWS Schema Conversion Tool

Oracle:

Demo App for REST enabled SQL

The Insurance of Oracle APEX – Can Your Low Code Framework Do This?

Oracle GoldenGate is now avaliable on Docker! All the details you need to get up and running on an image is included in Oracle’s public github.

Assign Pseudo Random Password for Oracle DB Users

Simple regexp to check that string contains word1 and word2 and doesn’t contain word3

SQL Server:

Automate MSBI deployments Using PowerShell

NEW – Machine Learning with SQL Server 2017 and R Tutorial

Optional Parameters and Missing Index Requests

Moving Dynamic Data Masked Data to New Locations

Indexing for Windowing Functions: WHERE vs. OVER

MySQL:

Lock, Stock and MySQL Backups: Data Guaranteed Webinar Follow Up Questions

gh-ost 1.0.42 released: JSON support, optimizations

Massive Parallel Log Processing with ClickHouse

cscope: Searching Code Efficiently

Updating InnoDB Table Statistics Manually

Categories: DBA Blogs

Richard Foote Consulting Has Arrived (New Angels Of Promise)

Richard Foote - Sun, 2017-09-17 17:32
Today is the official launch of Richard Foote Consulting, my new independent company in which I’ll be providing specialist Oracle Database consulting and training services, focusing on database performance tuning, database problem resolutions and database health check assessments (at least until everyone moves across to the new self-tuning Oracle Cloud database). Based on all the […]
Categories: DBA Blogs

connect by rownum produce ORA-30009

Tom Kyte - Sat, 2017-09-16 05:26
SQL> select * from scott.t0909_1; ID S E ---------- ---------- ---------- 1 10 11 2 1 5 3 88 92 -----Scene 1 select * from scott.t0909_1 connect by rownum<=3; ID S E ---------- ---------- ---------- 1 10 11 1 10 11 1 10 ...
Categories: DBA Blogs

Vendor Code 17002 Unable to Connect to DB Windows 10

Tom Kyte - Sat, 2017-09-16 05:26
IO Error: The Network Adapter could not establish the connection. My laptop is client/server using Windows 10. I added Port 1521 as exception to Firewall, but this did not resolve the problem. I am knowledgeable in DB design/build using SQL Server, b...
Categories: DBA Blogs

TO_DATE with no month

Tom Kyte - Sat, 2017-09-16 05:26
If you use to_date without specifying a day, Oracle assumes the 1st of the month. However, if you don't specify a month, Oracle uses the current month. Is this by design, and if so do you know the rationale for it? eg on 15 September: <code> ...
Categories: DBA Blogs

Want to retrive numbers in words

Tom Kyte - Sat, 2017-09-16 05:26
I want output as: numbers string 101 One zero one 102 One zero two 851 eight five one 9856 nine eight five six 356 three five six 748 seven four eight 254 two five four ...
Categories: DBA Blogs

How to split rows into balanced sets based on a running total limited to 2000

Tom Kyte - Fri, 2017-09-15 11:06
Hi, my question fits in to your "Balanced sets in SQL" collection of questions. I have a table as follows (in reality 26 million rows): <code> CREATE TABLE T ( "PI" VARCHAR2(120), "S" NUMBER, "L" NUMBER ); Insert into T (PI,S,L...
Categories: DBA Blogs

About connect,resource and DBA

Tom Kyte - Fri, 2017-09-15 11:06
Hi Tom, I read your book and a article and read this quote where you have quoted that "connect,resource and DBA should not be used in a system for security reasons". Could you please elaborate on this.As in our project to perform dba role ...
Categories: DBA Blogs

Performance differ on Temporary tables

Tom Kyte - Fri, 2017-09-15 11:06
Please observe my queries explain plan and let me know what is the root cause taking more time on second execution. --GT Table creation <code>create global temporary table gt_table1 (column1 varchar2(4000), column2 varchar2(4000), ........... ...
Categories: DBA Blogs

Using Standby of CDB for Reporting purpose

Tom Kyte - Fri, 2017-09-15 11:06
Hi Tom I have active data guard setup for my CDB . It means i have physical standby CDB (with multiple pdbs) . I know replication and database_Role are at CDB level and not pdb level . I have a PDB from different application . Initially when we s...
Categories: DBA Blogs

Parse cpu to parse elapsed % very low

Tom Kyte - Fri, 2017-09-15 11:06
Greetings, As in last question, i am getting these stats from awr. I tried to dig it up more for wait class concurrecy wait class. <code>Event Waits Time(s) Avg wait(ms) % DB time Wait Class --------------...
Categories: DBA Blogs

Database Service Configuration Requirements

Tom Kyte - Fri, 2017-09-15 11:06
Hi TOM, Was going through the section 5.6.1.2 in http://docs.oracle.com/database/121/DGBKR/sofo.htm#DGBKR3425. It says "Services that are to be active while the database is in the physical standby role must also be created and started on the c...
Categories: DBA Blogs

Pro*C: How to set USERID precompiler option for proxy connection

Tom Kyte - Fri, 2017-09-15 11:06
Hi, Is there a way to set the Pro*C USERID precompiler option for a proxy connection? For example, I have an OS authenticated user GEORGE who as been granted "CONNECT THROUGH" to user GEORGE_P and can perform a proxy connect to GEORGE_P in SQL*...
Categories: DBA Blogs

Cloning a PDB into a CDB having a active data guard setup

Tom Kyte - Fri, 2017-09-15 11:06
Hi Tom I have been trying to clone a pdb using database link into a cdb having dataguard setup using STANDBYS=ALL clause. But my replication stops . I tried changing the file_name_convert parameter at CDB level and still the new cloned pdb could not...
Categories: DBA Blogs

Ggsci interface

Tom Kyte - Thu, 2017-09-14 16:46
Hi Tom, I want to ask. Is there any ways to create ggsci interface? Like using netbeans or something. Thanks
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs