Feed aggregator

WebLogic, Docker, OCCS: Most Watched 2 Minute Tech Tips - June 5-11, 2017

OTN TechBlog - Mon, 2017-06-12 10:22

Sure, you can read through page after page of documentation. But why bother when you can get useful technical tips in 2-minute video chunks? Here's this week's list of the most-watched Tech Tip videos.

And if you have a tip of your own to share, let's make it happen. Contact me: bob.rhubart@oracle.com

1 Running WebLogic Applications on Docker using the OCCS | Craig Barr
May 12, 2017
Last week: #7
5th Top 10 appearance 2 New Features in Oracle Database 12.2 | Chris Saxon
February 22, 2017
Last week: #3
16th Top 10 appearance 3 Heap Tables and Index Organized Tables in Oracle | Chris Saxon
March 4, 2015
Last week: #6
49th Top 10 appearance 4 Why Learn Oracle Identity and Access Management | Atul Kumar
February 24, 2016
Last Top 10 appearance: May 29, 2017
22nd Top 10 appearance 5 When to Use Oracle SOA Cloud Service | Robert van Molken
December 1, 2016
Last week: #2
20th Top 10 appearance 6 Using the Declarative Features in Oracle APEX | Joel Kallman
March 5, 2015
Last Top 10 appearance: March 21, 2016
2nd Top 10 appearance 7 Programming Languages for the Raspberry Pi | Lonneke Dikmans
August 12, 2015
Last week: #10
28th Top 10 appearance 8 Oracle GoldenGate for Big Data | Michael Rainey
August 12, 2016
Last Top 10 appearance: May 22, 2017
12th Top 10 appearance 9 Oracle Function Result Cache | Steven Feuerstein
February 11, 2016
Last Top 10 appearance: September 12, 2016
5th Top 10 appearance 10 Oracle EBS integration with Identity and Access Management for SSO | Atul Kumar
Feb 14, 2017
Last Top 10 appearance: May 8, 2017
6th Top 10 appearance Last Week's Top 10

Integration, DevOps, Testing: Most-Watched 2 Minute Tech Tips - May 29-June 4, 2017

ETL using Oracle DBMS_HS_PASSTHROUGH and SQL Server

Amis Blog - Mon, 2017-06-12 10:04

While I prefer a “loosely coupled architecture” for replication between Oracle and SQL Server, sometimes a direct (database) link cannot be avoided. By using DBMS_HS_PASSTHROUGH for data extraction the 2 other ETL processes (transformation and load) can be configured and administered with more flexibility, providing an almost acceptable level of “loosely coupled processing“.
Consider this as a really simple ETL config:

    Extract: Select SQL Server data with native sql, using DBMS_PASSTHROUGH and a PIPELINED function.
    Transform: Define a view on top of the function and transform column_names and column datatypes correctly.
    Load: SQL> insert into oracle_table select * from oracle_view;

When you use DBMS_HS_PASSTHROUGH Oracle doesn’t interpret the data you receive from SQL Server. By default this is done by the dg4odbc process, and the performance benefit in bypassing this process is considerable. Also, you’re not restricted by the limitations of dg4odbc and can transform the data into anything you need.

Like dg4odbc DBMS_HS_PASSTHROUGH depends on Heterogeneous Services (a component built-in to Oracle) to provide the connectivity between Oracle and SQL Server. Installation of unixODBC and a freeTDS driver on Linux is required to setup the SQL Server datasource… installation and configuration steps can be found here and here. DBMS_HS_PASSTHROUGH is invoked through an Oracle database link. The package conceptually resides at SQL Server but, in reality, calls to this package are intercepted and mapped to one or more Heterogeneous Services calls. The freeTDS driver, in turn, maps these calls to the API of SQL Server. More about DBMS_HS_PASSTHROUGH here.

Next a short example of how to setup data extraction from SQL Server with DBMS_HS_PASSTHROUGH and data transformation within the definition of a view. In this example the SQL Server column names differ from the ones in Oracle in case, length and/or in name and/or in datatype, and are transformed by the view. NLS_DATE_FORMAT synchronization is an exception… it’s done in the extract package itself. Reason is that all dates in this particular SQL Server database use a specific format, and it doesn’t really obscure the code. But if you choose to refrain from all transformation code in the extract package, you could create types with VARCHAR2’s only, and put all your to_number, to_date and to_timestamp conversion code in the view definition.

Extract

-- create Oracle types for uninterpreted SQL Server data
CREATE OR REPLACE TYPE E01_REC 
AS OBJECT(
  C01    NUMBER(8),
  C02    VARCHAR2(25 CHAR),
  C03    VARCHAR2(3 CHAR),
  C04    NUMBER(8),
  C05    DATE,
  C06    DATE );
/

CREATE OR REPLACE TYPE E01_TAB AS TABLE OF E01_REC;
/

-- create the extract package
CREATE OR REPLACE PACKAGE E AUTHID DEFINER AS
--------------------------------------------------------- 
  FUNCTION E01 RETURN E01_TAB PIPELINED;
---------------------------------------------------------
END E;
/

-- create the extract package body
CREATE OR REPLACE PACKAGE BODY E AS
  v_cursor   BINARY_INTEGER;  
  v_out_e01  E01_REC:=E01_REC(NULL,NULL,NULL,NULL,NULL,NULL);
-------------------------------------------------------------------------
  v_stat_e01 VARCHAR2(100):= 'Select SiteID
                                   , SiteName
                                   , SiteMnemonic
                                   , PointRefNumber
                                   , OpeningDate
                                   , ClosingDate
                               From ObjSite';
                                 
-------------------------------------------------------------------------
FUNCTION E01
RETURN E01_TAB PIPELINED
  IS
BEGIN
  execute immediate 'alter session set NLS_DATE_FORMAT = ''YYYY-MM-DD HH24:MI:SS'' ';
  v_cursor := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@<DBLINK>;
  DBMS_HS_PASSTHROUGH.PARSE@<DBLINK>(v_cursor,v_stat_e01);
  WHILE DBMS_HS_PASSTHROUGH.FETCH_ROW@<DBLINK>(v_cursor) > 0
    LOOP
      DBMS_HS_PASSTHROUGH.GET_VALUE@<DBLINK>(v_cursor,1,v_out_e01.c01);
      DBMS_HS_PASSTHROUGH.GET_VALUE@<DBLINK>(v_cursor,2,v_out_e01.c02);
      DBMS_HS_PASSTHROUGH.GET_VALUE@<DBLINK>(v_cursor,3,v_out_e01.c03);
      DBMS_HS_PASSTHROUGH.GET_VALUE@<DBLINK>(v_cursor,4,v_out_e01.c04);
      DBMS_HS_PASSTHROUGH.GET_VALUE@<DBLINK>(v_cursor,5,v_out_e01.c05);
      DBMS_HS_PASSTHROUGH.GET_VALUE@<DBLINK>(v_cursor,6,v_out_e01.c06);
    PIPE ROW(v_out_e01);
    END LOOP;
  DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@<DBLINK>(v_cursor);
  RETURN;
EXCEPTION
  WHEN NO_DATA_NEEDED THEN
    DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@<DBLINK>(v_cursor);
  WHEN OTHERS THEN
    DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@<DBLINK>(v_cursor);
    DBMS_OUTPUT.PUT_LINE(SQLERRM||'--'||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
  RAISE;
END E01;
------------------------------------------------------------------------
END E;
/

Transform

CREATE OR REPLACE FORCE VIEW SITE_VW
AS 
SELECT TO_NUMBER(C01) SITEID,
       C02            STATIONNAME,
       C03            SITEMNEMONIC,
       TO_NUMBER(C04) STATIONID,
       C05            OPENINGDATE,
       C06            CLOSINGDATE
FROM TABLE(E.E01);

Load

INSERT INTO SITE SELECT * FROM SITE_VW;
COMMIT;

The post ETL using Oracle DBMS_HS_PASSTHROUGH and SQL Server appeared first on AMIS Oracle and Java Blog.

Unify: See Your Data From Every Perspective

Rittman Mead Consulting - Mon, 2017-06-12 09:09
 See Your Data From Every Perspective

 See Your Data From Every Perspective

Ad hoc access to accurate and secured data has always been the goal of business intelligence platforms. Yet, most fall short of balancing the needs of business users with the concerns of IT.

Rittman Mead has worked with hundreds of organizations representing all points on the spectrum between agility and governance. Today we're excited to announce our new product, Unify, which allows Tableau users to directly connect to OBIEE, providing the best of both worlds.

Governed Data Discovery

Business users get Tableau's intuitive data discovery features and the agility they need to easily blend their departmental data without waiting on IT to incorporate it into a warehouse. IT gets peace of mind, knowing their mission-critical data is protected by OBIEE's semantic layer and row-level security.

Unify Essentials

Unify runs as a desktop app, making it easy for departmental Tableau users to connect to a central OBIEE server. Unify also has a server option that runs alongside OBIEE, for organizations with a large Tableau user base or those using Tableau Server.

Desktop installation and configuration is simple. Once installed, users can query OBIEE from within Tableau with just a few clicks. Have a look at these short videos demonstrating setup and use of Unify.

Available Today

Download your free 7-day trial of Unify Desktop here.

No Tableau Desktop license? No problem. Unify is compatible with Tableau Public.

Categories: BI & Warehousing

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

Pythian Group - Mon, 2017-06-12 08:57

This Log Buffer Edition includes blog posts from Oracle, SQL Server and MySQL.

Oracle:

In 12.2 you can: Convert a simple table to partitioned with multi-column automatic list partitions, partially indexed, with read only segments, filtering out unwanted data, online in one operation.

12c How to UN Expire Password for CDB Users

rman auxiliary (for duplicate)

How long will Oracle APEX remain an included feature of the Oracle Database?

How to easily delete files in the Oracle Cloud using CloudBerry Explorer

SQL Server:

Understanding OPENJSON and FOR JSON in Azure and SQL 2016

How to setup Machine Learning Services in SQL Server 2017 CTP2

The OUTPUT Clause for the MERGE Statements

Simple script to backup all SQL Server databases

Working with Windows Containers and Docker: Into your Stride

MySQL:

MySQL on Docker: Running Galera Cluster on Kubernetes

Summary of recent performance tests for MySQL 5.6, 5.7 and 8

A Quick Look at Parallel Rsync and How it Can Save a System Hours

Docker, MySQL and Experience in Containers

HopsFS running on top of MySQL Cluster 7.5 wins IEEE Scale Challenge 2017

Categories: DBA Blogs

dbms_sqldiag

Jonathan Lewis - Mon, 2017-06-12 06:48

If you’re familiar with SQL Profiles and SQL Baselines you may also know about SQL Patches – a feature that allows you to construct hints that you can attach to SQL statements at run-time without changing the code. Oracle 12c Release 2 introduces a couple of important changes to this feature:

  • It’s now official – the feature had been moved from package dbms_sqldiag_internal to package dbms_sqldiag.
  • The limitation of 500 characters has been removed from the hint text – it’s now a CLOB column.

H/T to Nigel Bayliss for including this detail in his presentation to the UKOUG last week, and pointing out that it’s also available for Standard Edition.

There are a couple of other little changes as you can see below from the two extract from the 12.2 declarations of dbms_sqldiag and dbms_sqldiag_internal below:


dbms_sqldiag
------------
FUNCTION CREATE_SQL_PATCH RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_TEXT                       CLOB                    IN
 HINT_TEXT                      CLOB                    IN
 NAME                           VARCHAR2                IN     DEFAULT
 DESCRIPTION                    VARCHAR2                IN     DEFAULT
 CATEGORY                       VARCHAR2                IN     DEFAULT
 VALIDATE                       BOOLEAN                 IN     DEFAULT

FUNCTION CREATE_SQL_PATCH RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN
 HINT_TEXT                      CLOB                    IN
 NAME                           VARCHAR2                IN     DEFAULT
 DESCRIPTION                    VARCHAR2                IN     DEFAULT
 CATEGORY                       VARCHAR2                IN     DEFAULT
 VALIDATE                       BOOLEAN                 IN     DEFAULT

dbms_sqldiag_internal
---------------------
FUNCTION I_CREATE_PATCH RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN
 HINT_TEXT                      CLOB                    IN
 CREATOR                        VARCHAR2                IN
 NAME                           VARCHAR2                IN     DEFAULT
 DESCRIPTION                    VARCHAR2                IN     DEFAULT
 CATEGORY                       VARCHAR2                IN     DEFAULT
 VALIDATE                       BOOLEAN                 IN     DEFAULT

FUNCTION I_CREATE_PATCH RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_TEXT                       CLOB                    IN
 HINT_TEXT                      CLOB                    IN
 CREATOR                        VARCHAR2                IN
 NAME                           VARCHAR2                IN     DEFAULT
 DESCRIPTION                    VARCHAR2                IN     DEFAULT
 CATEGORY                       VARCHAR2                IN     DEFAULT
 VALIDATE                       BOOLEAN                 IN     DEFAULT

  • The function names changes from i_create_patch to create_patch when exposed in dbms_sqldiag.
  • There are two versions of the function – one that requires you to supply the exact SQL text, and a new version that allows you to supply an SQL ID.
  • The internal function also adds a creator to the existing parameter list – and it doesn’t have a default, so if you’ve got some code to use the internal version already it’s not going to work on 12.2 until you change it.

I was prompted to write this note by a tweet asking me if there’s any SQL available to see the contents of an SQL Profile in 11g and 12c. (I published some simple code several years ago for 10g, but Oracle changed the base tables in 11g). The answer is yes, probably on the Internet somewhere, but here’s some code I wrote a couple of years ago to scan the more recent versions of Oracle:

rem
rem     sql_profile_baseline_11g.sql
rem     J.P.Lewis
rem     July 2010
rem

set pagesize 60
set linesize 132
set trimspool on

column hint format a70 wrap word
column signature format 999,999,999,999,999,999,999

break on signature skip 1 on opt_type skip 1 on plan_id skip 1

spool sql_profile_baseline_11g

select
        prf.signature,
        decode(
                obj_type,
                1,'Profile',
                2,'Baseline',
                3,'Patch',
                'Other'
        )       opt_type,
        prf.plan_id,
        extractvalue(value(tab),'.')    hint
from
        (
        select
                /*+ no_eliminate_oby */
                *
        from
                sqlobj$data
        where
                comp_data is not null
        order by
                signature, obj_type, plan_id
        )       prf,
        table(
                xmlsequence(
                        extract(xmltype(prf.comp_data),'/outline_data/hint')
                )
        )       tab
;


This will report the hints associated with SQL Baselines, SQL Profiles, and SQL Patches – all three store the data in the same base table. As a minor variation I also have a query that will reported a named profile/baseline/patch, but this requires a join to the sqlobj$ table. As you can see from the substitution variable near the end of the text, the script will prompt you for an object name.


set pagesize 60
set linesize 180
set trimspool on

column  plan_name format a32
column  signature format 999,999,999,999,999,999,999
column  category  format a10
column  hint format a70 wrap word

break on plan_name skip 1 on signature skip 1 on opt_type skip 1 on category skip 1 on plan_id skip 1

spool sql_profile_baseline_11g

select
        prf.plan_name,
        prf.signature,
        decode(
                obj_type,
                1,'Profile',
                2,'Baseline',
                3,'Patch',
                  'Other'
        )       opt_type,
        prf.category,
        prf.plan_id,
        extractvalue(value(hnt),'.') hint
from
        (
        select
                /*+ no_eliminate_oby */
                so.name         plan_name,
                so.signature,
                so.category,
                so.obj_type,
                so.plan_id,
                sod.comp_data
                from
                        sqlobj$         so,
                        sqlobj$data     sod
                where
                        so.name = '&m_plan_name'
                and     sod.signature = so.signature
                and     sod.category = so.category
                and     sod.obj_type = so.obj_type
                and     sod.plan_id = so.plan_id
                order by
                        signature, obj_type, plan_id
        )       prf,
        table (
                select
                        xmlsequence(
                                extract(xmltype(prf.comp_data),'/outline_data/hint')
                        )
                from
                        dual
        )       hnt
;


Lagniappe:

One of the enhancements that appeared in 12c for SQL Baselines was that the plan the baseline was supposed to produce was stored in the database so that Oracle could check that the baseline would still reproduce the expected plan before applying it. These plans (also generated for Profiles and Patches) are stored in the table sqlobj$plan, and the dbms_xplan package has been enhanced with three new functions to report them:


FUNCTION DISPLAY_SQL_PATCH_PLAN RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 FORMAT                         VARCHAR2                IN     DEFAULT

FUNCTION DISPLAY_SQL_PLAN_BASELINE RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_HANDLE                     VARCHAR2                IN     DEFAULT
 PLAN_NAME                      VARCHAR2                IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT

FUNCTION DISPLAY_SQL_PROFILE_PLAN RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 FORMAT                         VARCHAR2                IN     DEFAULT

e.g.
SQL> select * from table(dbms_xplan.display_sql_profile_plan('SYS_SQLPROF_015c9bd3bceb0000'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL text: select        t1.id, t2.id from       t1, t2 where    t1.id between 10000 and
          20000 and     t2.n1 = t1.n1 and       t2.n1 = t2.v2
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL Profile Name: SYS_SQLPROF_015c9bd3bceb0000
Status:           ENABLED
Plan rows:        From dictionary
--------------------------------------------------------------------------------

Plan hash value: 3683239666

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          | 10501 |   287K|   248   (4)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR         |          |       |       |     0   (0)|          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10002 | 10501 |   287K|   248   (4)| 00:00:01 |  Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN BUFFERED   |          | 10501 |   287K|   248   (4)| 00:00:01 |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE          |          | 10002 |    97K|   123   (3)| 00:00:01 |  Q1,02 | PCWP |            |
|   5 |      PX SEND HASH       | :TQ10000 | 10002 |    97K|   123   (3)| 00:00:01 |  Q1,00 | P->P | HASH       |
|   6 |       PX BLOCK ITERATOR |          | 10002 |    97K|   123   (3)| 00:00:01 |  Q1,00 | PCWC |            |
|*  7 |        TABLE ACCESS FULL| T1       | 10002 |    97K|   123   (3)| 00:00:01 |  Q1,00 | PCWP |            |
|   8 |     PX RECEIVE          |          |   104K|  1845K|   124   (4)| 00:00:01 |  Q1,02 | PCWP |            |
|   9 |      PX SEND HASH       | :TQ10001 |   104K|  1845K|   124   (4)| 00:00:01 |  Q1,01 | P->P | HASH       |
|  10 |       PX BLOCK ITERATOR |          |   104K|  1845K|   124   (4)| 00:00:01 |  Q1,01 | PCWC |            |
|* 11 |        TABLE ACCESS FULL| T2       |   104K|  1845K|   124   (4)| 00:00:01 |  Q1,01 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T2"."N1"="T1"."N1")
   7 - filter("T1"."ID"<=20000 AND "T1"."ID">=10000)
  11 - filter("T2"."N1"=TO_NUMBER("T2"."V2"))

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2

Disclaimer – I’ve checked only the SQL_PROFILE function call on 12.2, after creating a profile to check that my old 11g report still worked in 12c.

 


KeePass 2.36

Tim Hall - Mon, 2017-06-12 03:12

KeePass 2.36 was released a few days ago. You can download it here.

You can read about how I use KeePass and KeePassX2 on my Mac, Windows and Android devices here.

Cheers

Tim…

KeePass 2.36 was first posted on June 12, 2017 at 9:12 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Where Did the EBS Certifications Page Go?

Steven Chan - Mon, 2017-06-12 02:00

Regular readers of this blog know that they could find my one-page summary of all important E-Business Suite technology stack component certifications under the "Certifications" link in the sidebar menu.

Since Oracle's migration of our blogging engine to Compendium in April, some people have been having trouble finding the new location.  It's here:

You can access it from the new blog's "Quick Links" button in the top banner:

Due to some restrictions about how the new blogging engine displays content, this page might look a bit different than before.  But regardless, it still lists technology stack certifications for EBS 12.2, 12.1, 12.0, and 11i, covering everything all three tiers: desktop and mobile clients, application tier servers, and database tier servers.

 

Categories: APPS Blogs

GoldenGate 12.2 TROUBLESHOOTING REPLICAT LAG

Michael Dinh - Sun, 2017-06-11 09:16

Time Since Chkpt and Lag at Chkpt from replicat keep increasing

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
REPLICAT    RUNNING     R_NEW12C    03:49:45      06:37:47    

This occurs for due to the following reasons:

Delivering a long running transaction
Waiting on a full table scan
Blocked by another sessions
Primary extract lag or pump lag keeps increasing

Is There a Way to Make Long-running Transactions Checkpoint? (Doc ID 969684.1)

The tradeoff with GROUPTRANSOPS is with efficiency. 
The tradeoff with MAXTRANSOPS is efficiency and transaction integrity. 

There are no long running transaction from the extract and is monitored using WARNLONGTRANS 15m, CHECKINTERVAL 3m.

grep "Long Running Transaction" dirrpt/E_OLD10G.rpt

My suspicion is FTS, but how to find out?

SQL> r
  1  select
  2  -- SQL_ID,PLAN_HASH_VALUE,
  3  OBJECT_OWNER,OBJECT_NAME, min(TIMESTAMP) min_ts, max(TIMESTAMP) max_ts, count(*) ct
  4  from DBA_HIST_SQL_PLAN
  5  where operation='TABLE ACCESS'
  6  and options='FULL'
  7  and NOT REGEXP_LIKE(object_owner,'SYS|SYSTEM|DBSNMP')
  8  and TIMESTAMP > TO_DATE('01-JUN-2017','DD-MON-YYYY')
  9  group by
 10  -- SQL_ID,PLAN_HASH_VALUE,
 11  OBJECT_OWNER,OBJECT_NAME
 12  order by count(*), OBJECT_OWNER,OBJECT_NAME
 13*
 
OBJECT_OWNER         OBJECT_NAME                    MIN_TS               MAX_TS                       CT
-------------------- ------------------------------ -------------------- -------------------- ----------
XXX                  THISISAREALLYLONGTABLENAME     01-JUN-2017 00:01:00 11-JUN-2017 02:55:36        114

SQL> select index_name from dba_indexes where table_name='THISISAREALLYLONGTABLENAME';
no rows selected
SQL> 

ASC 606/IFRS 15 & Oracle’s Revenue Management Cloud Service (RMCS)

OracleApps Epicenter - Sun, 2017-06-11 06:13
As we know , the entire accounting approach to revenue recognition is undergoing a wholesale re-write. Rather than relying on using a deferred revenue account, you will need to identify and account for performance obligations. WHAT CHANGES ARE BEING INTRODUCED? Revenue recognition is taking a more scenario and rules based approach to what can be […]
Categories: APPS Blogs

query for report generation in oracle 11g

Tom Kyte - Sun, 2017-06-11 06:06
how to generate a month wise,year wise report for banking?
Categories: DBA Blogs

How solve incident Error?

Tom Kyte - Sun, 2017-06-11 06:06
I search incident error suddenly and my database immediate down and show error message client connect could not hand-off. And I see alter log file and search on internet also but i can't able to find information for first argument mention in the or...
Categories: DBA Blogs

Unable to install Oracle 11g on Windows 10

Tom Kyte - Sun, 2017-06-11 06:06
Hello Oracle Experts, I am unable to install Oracle 11g on Windows 10 (64 Bit). Error Encountered : ORA 12631 UserName retrieval failed Database instance is created but the .DBF files are not created including the control files. Please note...
Categories: DBA Blogs

Why I can't get the user role when I logon the database?

Tom Kyte - Sun, 2017-06-11 06:06
Hello everyone I'm trying to solve a little problem that I have everytime I connect as another user on the database. First of all let me explain what I want to do. When the user connect on the database I want to know his role and just show on o...
Categories: DBA Blogs

Oracle 12C Database Running on 11G Grid/ASM

Tom Kyte - Sun, 2017-06-11 06:06
Hello all, I have a client who is currently running on an 11.2.0.3 DB and Grid/ASM. They want me to stand up a new database as 12.2.0.1 to export into while the current database is still running. I've gone over the documentation for upgrading but ...
Categories: DBA Blogs

Revenue Standard : effective dates for reporting using the new accounting standards

OracleApps Epicenter - Sun, 2017-06-11 04:09
Q :What are the effective dates for reporting using the new accounting standards A : The expected effective dates are For public entities, the effective date starts with reporting periods beginning on or after December 15,2017. Fornonpublic entities, the effective date will be deferred for one year to annual reporting periods beginning after December 15, […]
Categories: APPS Blogs

12c How to UN Expire Password for CDB Users

Michael Dinh - Sat, 2017-06-10 15:11

Use dbms_metadata.get_ddl to extract user and replace create with alter.

oracle@medintdbl01 ~ $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jun 10 15:31:13 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select username, expiry_date, account_status from dba_users where username like '%MONITOR%';

USERNAME
--------------------------------------------------------------------------------
EXPIRY_DA ACCOUNT_STATUS
--------- --------------------------------
C##MONITOR
10-JUN-17 EXPIRED

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> set long 1000000 longchunksize 32000 linesize 32000 pages 0 newpage none
SQL> set heading off tab off echo off define off sqlprefix off blockterminator off timing off verify off feedb off
SQL> set sqlblanklines on embedded on trimspool on  
SQL> select dbms_metadata.get_ddl('USER','C##MONITOR') from dual;

   CREATE USER "C##MONITOR" IDENTIFIED BY VALUES 'S:***'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE

SQL> ALTER USER "C##MONITOR" IDENTIFIED BY VALUES 'S:***'
SQL> select username, expiry_date, account_status from dba_users where username like '%MONITOR%';
C##MONITOR                                                                                       07-DEC-17 OPEN
SQL> 

Ubuntu: mouse jumps to trash icon / move to lower left corner / bottom left

Dietrich Schroff - Sat, 2017-06-10 14:02
There are many questions about jumping mouse cursors on ubuntu.
And there are often answers like
  • clean your touchpad
  • your touchpad is broken
  • send your laptop back
But there are so many people suffering this, so it is not reasonable that all these touchpads do not work well.

One thing to solve this is to install
gpointing-device-settingsbut this does not really help.
Next thing:
add-apt-repository ppa:atareao/atareao
apt-get update
apt-get install touchpad-indicator
/opt/extras.ubuntu.com/touchpad-indicator/bin/touchpad-indicator  But this is not really the solution. But you can disable your touchpad while typing, so that the window focus does not jump to the trash icon and your typing is interrupted.

After searching i found the following discussion on a mailing list:
https://lists.freedesktop.org/archives/xorg-devel/2014-June/042790.html

And the follwing did the job:
apt-get install xserver-xorg-core apt-get install xserver-xorg-input-libinput
apt-get remove --purge xserver-xorg-input-synaptics and then a logoff and login into the x window system...

GoldenGate Debugging

Michael Dinh - Fri, 2017-06-09 21:40

I was working on automating debug information to submit to Oracle Support and thought I share implementation for what was requested.

OGG_GROUP_NAME is from ggsci info all (case sensitive)
./debug_gg.sh: line 3: 1: —> USAGE: /debug_gg.sh OGG_GROUP_NAME
./debug_gg.sh E_LAX

#!/bin/sh -e
DN=`dirname $0`
BN=`basename $0`
OGG_GROUP_NAME=${1:?"---> USAGE: $DN/$BN "OGG_GROUP_NAME""}
set -x
ps -o pid,uname,cmd `pgrep -f "extract.*${OGG_GROUP_NAME}"`
pstack `pgrep -f "extract.*${OGG_GROUP_NAME}"`
pstack `pgrep -f "extract.*${OGG_GROUP_NAME}"`
pstack `pgrep -f "extract.*${OGG_GROUP_NAME}"`
set +x
$GG_HOME/ggsci << EOF
info ${OGG_GROUP_NAME} detail
send ${OGG_GROUP_NAME} status
sh sleep 2m
send ${OGG_GROUP_NAME} status
sh sleep 2m
send ${OGG_GROUP_NAME} status
send ${OGG_GROUP_NAME} report
exit
EOF
echo " "
ls -alrt $GG_HOME/dirrpt/${OGG_GROUP_NAME}*.rpt|tail -3
exit

Example output from pgrep and pstack

++ pgrep -f 'extract.*E_LAX'
+ ps -o pid,uname,cmd 40882
  PID USER     CMD
40882 ggsuser  /u01/gg/12.2.0/extract PARAMFILE /u01/gg/12.2.0/dirprm/e_lax.prm REPORTFILE /u01/gg/12.2.0/dirrpt/E_LAX.rpt PROCESSID E_LAX USESUBDIRS
++ pgrep -f 'extract.*E_LAX'
+ pstack 40882

Just realized does not scale since extract is hard coded.

Next step, learn how to read pstack output – YIKES!


Unable to run the job which is already created

Tom Kyte - Fri, 2017-06-09 17:06
Hi Tom, I need your help in fixing this. I have created the job which should call a shell script when triggered. But unfortunately I am getting error as test_job must be a job. Below are the scripts which I have used to create the job. Let ...
Categories: DBA Blogs

Query to get the count of records every two hours

Tom Kyte - Fri, 2017-06-09 17:06
I have a record creation time stamp in my table . I need to write a query to get the count of records every two hours between two specific dates. Starting from 1st January 2017 till today. I have the following table USER USER_ID CREATION_D...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator