Feed aggregator

Service Accounts suck - why data futures require end to end authentication.

Steve Jones - 6 hours 53 min ago
 Can we all agree that "service" accounts suck from a security perspective.  Those are the accounts that you set up so what system/service can talk to another one.  Often this will be a database connection so the application uses one account (and thus one connection pool) to access the database.  These service accounts are sometimes unique to a service or application, but often its a standard
Categories: Fusion Middleware

Need to calculate Age as part of select

Tom Kyte - 7 hours 21 min ago
Hi, We just went live on Oracle a couple of weeks ago. I have a legacy process that includes running a script that was coded for Sybase. I have most of it converted to Oracle, but I'm having trouble with the Age field (it's the last piece I need to get working). I thought about just including the Age piece... then thought to include the entire script for context if nothing else. Thanks in advance for the assist! -Denise Current legacy code <code>SELECT DISTINCT meme.MEME_MEDCD_NO, meme.MEME_BIRTH_DT, AGE = CASE WHEN ( month(convert(datetime, meme.MEME_BIRTH_DT, 103))*100)+ day(convert(datetime, meme.MEME_BIRTH_DT, 103)) - ((month(getdate())*100)+day(getdate())) <= 0 THEN DATEDIFF(YEAR,convert(datetime, meme.MEME_BIRTH_DT, 103),getdate())</b> ELSE DATEDIFF(YEAR,convert(datetime, meme.MEME_BIRTH_DT, 103),getdate())-1 END, sbsb.SBSB_ID, mepe.MEPE_EFF_DT, mepe.MEPE_TERM_DT, mepe.MEPE_ELIG_IND, mepe.CSPI_ID, sbad.SBAD_COUNTY AS 'Member_County', pdpd.LOBD_ID FROM dbo.CMC_MEME_MEMBER meme INNER JOIN dbo.CMC_MEPE_PRCS_ELIG mepe ON mepe.MEME_CK =meme.MEME_CK INNER JOIN dbo.CMC_SBSB_SUBSC sbsb ON sbsb.SBSB_CK = meme.SBSB_CK INNER JOIN CMC_PDPD_PRODUCT pdpd ON mepe.PDPD_ID = pdpd.PDPD_ID INNER JOIN CMC_SBAD_ADDR sbad ON sbsb.SBSB_CK = sbad.SBSB_CK AND sbsb.SBAD_TYPE_MAIL = sbad.SBAD_TYPE WHERE mepe.GRGR_CK IN (1,3,8) AND mepe.MEPE_ELIG_IND = 'Y' AND mepe.MEPE_EFF_DT <= '09/01/2020' AND -- Match file date mepe.MEPE_TERM_DT >= '09/01/2020' AND -- Match file date meme.MEME_MEDCD_NO IN ( )</code>
Categories: DBA Blogs

Database Wallet

Tom Kyte - 7 hours 21 min ago
Hi Team, We have SSL certificates imported on database server using ORAPKI after creating wallet. We are using utl_http for external system communication from database and using utl_http.set_wallet to access the certificates. Now, we are en-queuing the messages to database queue and writing logic in middle ware to read message from queue and send messages to external system. but the problem is certificates are database server and the communication to external system is from middleware. Can we read the SSL certificate from database server and pass it to middleware? is there a way to pass the certificate from DB to middleware. Can you please advise. Thank You.
Categories: DBA Blogs

Process in order to estimate how many DBAs are needed to support a project

Tom Kyte - 7 hours 21 min ago
GM, Do you guys know of a whitepaper or training that describes an approach to estimating how many Oracle DBA hours are needed to perform X, Y, Z? We are bidding on an effort, and I would prefer not to have to reinvent the wheel if something already exists. For instance, if a project has these requirements: DBA shall setup a three node 19c RAC cluster with ASM. DBA shall tune the system DBA shall be able to restore the database within a day with minimal data loss DBA shall It will need to be tuned. DBA shall setup a disaster recovery site using data guard DBA shall setup security to meet NIST-3029 I have started to break down all of the 50+ major tasks to satisfy the above requirements, and and threw in there rough daily estimates for each step. Database Security 10 days: o Database instance security hardening setup 3 o Database server security hardening implementation - 2 o Security scanner software setup and troubleshooting - 1 o Troubleshooting false positive security findings and waivers - 2 Oracle install and dB creation with RAC- 5 days o Clusterware setup- 3 days o RAC database creation- 1 o Licensing - .5 o Database Shutdown and Startup setup 1 Backup and Recovery Setup- 2 etc. Thanks, John
Categories: DBA Blogs

Choice State in AWS Step Functions

Pakistan's First Oracle Blog - 14 hours 39 min ago

Richly asynchronous server-less applications can be built by using AWS step functions. Choice State in AWS Step Functions is the newest feature which was long awaited.

In simply words, we define steps and their transitions and call it State Machine as a whole. In order to define this state machine, we use Amazon States Language (ASL). ASL is a JSON-based structured language that defines state machines and collections of states that can perform work (Task states), determines which state to transition to next (Choice state), and stops execution on error (Fail state). 

So if the requirement is to add a branching logic like if-then-else or case statement in our state transition, then Choice state comes handy. The choice state introduces various new operators into the ASL and the sky is now limit with the possibilities. Operators for choice state include comparison operators like Is Null, IsString etc, Existence operators like Ispresent, glob wildcards where you match some string and also variable string comparison.

Choice State enables developers to simplify existing definitions or add dynamic behavior within state machine definitions. This makes it easier to orchestrate multiple AWS services to accomplish tasks. Modelling complex workflows with extended logic is now possible with this new feature.

Now one hopes that AWS introduces doing it all graphically instead of dabbling into ASL.

Categories: DBA Blogs

Table vs Index Fragmentation

Tom Kyte - Wed, 2020-09-16 15:46
Hello, This is more of a fundamental question, sorry i dont have any test cases. Does table fragmentation also imply index fragmentation for the same table. ?
Categories: DBA Blogs

PARALLEL HINT and DML ERROR logging

Tom Kyte - Wed, 2020-09-16 15:46
HI, <code> CREATE TABLE TEMP_TEST ( ID NUMBER(10) ) ALTER TABLE TEMP_TEST ADD ( CONSTRAINT temp_test_pk UNIQUE (ID) ); </code> Scenario:1: <code> truncate table TEMP_TEST; ALTER SESSION ENABLE PARALLEL DML; INSERT INTO /*+ NOAPPEND PARALLEL(5) */ TEMP_TEST SELECT /*+ PARALLEL */DISTINCT BUCKET FROM source LOG ERRORS INTO ERR$_TEMP_TEST ('insert failed') REJECT LIMIT UNLIMITED; </code> Scenario:2: <code> truncate table TEMP_TEST; ALTER SESSION ENABLE PARALLEL DML; INSERT INTO /*+ NOAPPEND PARALLEL(5) */ TEMP_TEST SELECT DISTINCT BUCKET FROM source LOG ERRORS INTO ERR$_TEMP_TEST ('insert failed') REJECT LIMIT UNLIMITED; </code> Scenario:1 is failing with unique constraint error instead of error records inserting into error table, but scenario:2 error records are inserting into ERR$_TEMP_TEST? The only difference between these two is PARALLEL hint in select statement.
Categories: DBA Blogs

getting ora 01017, invalid username/password when configuring oracle mobile server to my repository db

Tom Kyte - Wed, 2020-09-16 15:46
my local db is a 19c i downloaded the latest version of the mobile server and while going through the installation, i came to this error, i have check my sqlnet.ora file, the tns configuration is good because i am able to connect with toad and sql developer. this is the sqlnet.ora #SQLNET.AUTHENTICATION_SERVICES= (NTS) SQLNET.AUTHENTICATION_SERVICES = (NONE) NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) #SQLNET.ALLOWED_LOGON_VERSION=12 SQLNET.ALLOWED_LOGON_VERSION=9 WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY=C:\Users\TEKYI\Documents\wallet\oracle)))
Categories: DBA Blogs

Anomaly detection

Tom Kyte - Wed, 2020-09-16 15:46
Hello, We have an application that monitors applications, detects anomalies, does correlation between metrics, performs Root Cause Analysis based on a few machine learning algorithms. We are planning onboard oracle monitoring for this application with a few metrics like below. Could you please suggest where we could get some baseline monitoring SQL's to plugin to our application, especially the SQLs that are used to generate ASH/AWR reports. We want to start small and expand over a period of time. Redo (Mb per second) Transactions per second Latency: Log file Sync, Log file parallel write, single block read all in Avg Ms IO MB/per sec Physical Reads MB/sec Physical writes MB/sec DB CPU % usage Network MB/sec Logons per sec Logical Reads Mb/sec File Sync(Avg/ms) RMAN IO mb/ms Waits Locks Top SQL?s Stale statistics on objects Top Objects by Size, growth, Avg growth per day, month Space growth (total vs used), Avg per day, month Thanks, Ravi B
Categories: DBA Blogs

cycle detected in recursive query where it seems to be no cycle

Tom Kyte - Wed, 2020-09-16 15:46
I have recursive query on Oracle 11g table with undirected graph data. Each row represents one edge. The recursive query traverses all edges starting from given input edge. The idea of query is: - input edge is at the 0th level - for n>0, edge is on n-th level if it incides with node of some edge on (n-1)-th level. Query: <code>with edges (id, a, b) as ( select 1, 'X', 'Y' from dual union select 2, 'X', 'Y' from dual ), r (l, id, parent_a, parent_b, child_a, child_b, edge_seen) as ( select 0, id, null, null, a, b, cast(collect(id) over () as sys.ku$_objnumset) from edges where id = 1 union all select r.l + 1, e.id, r.child_a, r.child_b, e.a, e.b , r.edge_seen multiset union distinct (cast(collect(e.id) over () as sys.ku$_objnumset)) from r join edges e on (r.child_a in (e.a, e.b) or r.child_b in (e.a, e.b)) and e.id not member of (select r.edge_seen from dual) ) select * from r; </code> The query worked well with other inputs until two parallel edges between same node pair occured. In this case, there is edge 1 on 0th level of recursion (initial row). I expected edge 2 would be added to result on 1st level of recursion since join condition holds. Instead I get "ORA-32044: cycle detected while executing recursive with query". I know this error is reported when the row newly joined to recursive query result would be same as some existing row. What I don't understand is why Oracle treats row with same node ids but different edge id as duplicate. Adding <code>cycle child_a, child_b set iscycle to 1 default 0</code> clause gives iscycle=1 for new row, adding <code>cycle id, child_a, child_b set iscycle to 1 default 0</code> gives iscycle=0, which is both correct. <b>Is it some known Oracle 11g bug and what's the best way to handle it?</b> I cannot fill LiveSQL link form since LiveSQL supports only Oracle 19 and the problem is reproducible only in Oracle 11g which I can't migrate from. The dbfiddle equivalent is https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=43af3cfae920e31f9a2748c1c31b54ad . Thanks.
Categories: DBA Blogs

ORA-22992, No LOB field selected

Tom Kyte - Wed, 2020-09-16 15:46
I have a SQL statement can run by itself and get the result back over db link. But if I want to put result set into a table either using ?create table?.as..? or ?insert into ?? before the same select statement, I will get ORA-22992 error. What caused this? The SQL statement like: <CODE> Select a.m, a.n, a.o, a.p, b.q, b.r, b.s, c.t, c.u,c.v From a@remote a left join b@remote b on b.m=a.m and b.n=a.n Left join c@remote c on c.m=a.m and c.m=a.n Where a.yr=2019 a.class=1 order by a.m </CODE> table ?a? and ?c? don?t have LOB fields, table ?b? has a field ?Ldesc? which is CLOB, But it is NOT in the select list. Local version : Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production Remote version: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Data Skew Part III (The Good Son)

Richard Foote - Wed, 2020-09-16 04:05
  I’m going to expand just a tad on my previous posts on data skew and run a simple query that returns a few rows based on a column predicate AND another query on the same column that returns many rows. The following table has a CODE column as with previous posts with the data […]
Categories: DBA Blogs

Planning And Recommendations Of Virtual Networks

Online Apps DBA - Wed, 2020-09-16 00:42

In this blog, you will see how to design a Virtual Network, we have discussed the planning and recommendation of virtual networks from Azure Solution Architect Design AZ-304 perspective. Check out the blog post at https://k21academy.com/az30413. Areas covered in this blog post: • What things you need to remember before taking subscription for single and […]

The post Planning And Recommendations Of Virtual Networks appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

The Island

Greg Pavlik - Tue, 2020-09-15 23:19

What is guilt? Who is guilty? Is redemption possible? What is sanity? Do persons have a telos, a destiny, both or neither? Ostrov (The Island) asks and answers all these questions and more.

A film that improbably remains one of the best of this century: "reads" like a 19th century Russian novel; the bleakly stunning visual setting is worth the time to watch alone.



[AZ-304] Microsoft Azure Architect Design (beta): Everything You Need To Know

Online Apps DBA - Tue, 2020-09-15 07:49

The AZ-304 exams replace the older AZ-301 exam, which will retire on September 30, 2020 Check out this blog post at k21academy.com/az30411 to know more about AZ-304 and other questions like, why is this certification important? What domains does it cover? What are the eligibility criteria? How to prepare for it? And whatnot. This blog […]

The post [AZ-304] Microsoft Azure Architect Design (beta): Everything You Need To Know appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Design Authentication and Authorization

Online Apps DBA - Tue, 2020-09-15 07:38

Want to get an idea about the authentication and authorization and other security aspects recommend in the cloud? Check out this blog post at K21Academy – k21academy.com/az30412, where you will get to know about the design authentication and authorization in brief from Azure Solution Architect Design AZ-304 perspective. Areas covered in this blog post: • […]

The post Design Authentication and Authorization appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Simple function returning Parallel slave info

XTended Oracle SQL - Tue, 2020-09-15 07:38

You can add also any information from v$rtsm_sql_plan_monitor if needed

create or replace function px_session_info return varchar2 parallel_enable as
   vSID int;
   res varchar2(30);
begin
   vSID:=userenv('sid');
   select 
           to_char(s.server_group,'fm000')
    ||'-'||to_char(s.server_set,'fm0000')
    ||'-'||to_char(s.server#,'fm0000')
    ||'('||s.sid||','||s.degree||'/'||s.req_degree||')'
    into res
   from v$px_session s 
   where s.sid=vSID;
   return res;
exception when no_data_found then
   return 'no_parallel';
end;
/

Simple example:

select--+ parallel
  px_session_info, count(*)
from sys.obj$
group by px_session_info
/
PX_SESSION_INFO           COUNT(*)
------------------------  --------
001-0002-0001(630,2/2)     38298
001-0002-0002(743,2/2)     34706
Categories: Development

静岡で優良な水道修理店を選ぶコツ

The Feature - Tue, 2020-09-15 05:56

静岡には水道修理店が複数存在していますが、日頃から依頼する用事はないのでいざ水まわりのトラブルが発生し、依頼するとなれば何を基準として選んだら良いかで悩むのではないでしょうから、料金や腕の良し悪しは修理店による違いが大きいので、しっかり下調べをした上で選ぶ必要があります。静岡で優良な水道修理店を選ぶためにも、複数から見積もりを取って相場を知ることや、相談して対応が丁寧で納得できるか調べることが大切です事前の説明がない修理店となれば、基本料金以外に作業料金や出張料金を請求されて、金銭面でトラブルになってしまうことも多いので気を付けましょう。それ以外にも、現地調査に来てもらった結果見積もり金額に納得できず、断ったら出張料金や見積もり料金、キャンセル料を請求されるケースもあります。事前に見積もりを出してもらったにもかかわらず、作業が終了してみると見積もりより高額な料金を請求されるケースも珍しくありません。

あらかじめ追加で発生する可能性がある料金の有無について確認しておいたり、丁寧でわかりやすい説明をしてくれる修理店から選ぶと安心できます。静岡には水道修理店が数多く存在していますが、優良な修理店もあればそうではないところもあるので、しっかりチェックして判断することは必須となってきます。専門的な知識や技術を備えたプロが対応していて、資格も取得しているのかなど、安心してその後水道を使うためにも確認しておくべきポイントは多々あります。

Categories: APPS Blogs

静岡で優良な水道修理店を選ぶコツ

Marian Crkon - Tue, 2020-09-15 05:56
静岡には水道修理店が複数存在していますが、日頃から依頼する用事はないのでいざ水まわりのトラブルが発生し、依頼するとなれば何を基準として選んだら良いかで悩むのではないでしょうから、料金や腕の良し悪しは修理店による違いが大きいので、しっかり下調べをした上で選ぶ必要があります。静岡で優良な水道修理店を選ぶためにも、複数から見積もりを取って相場を知ることや、相談して対応が丁寧で納得できるか調べることが大切です事前の説明がない修理店となれば、基本料金以外に作業料金や出張料金を請求されて、金銭面でトラブルになってしまうことも多いので気を付けましょう。それ以外にも、現地調査に来てもらった結果見積もり金額に納得できず、断ったら出張料金や見積もり料金、キャンセル料を請求されるケースもあります。事前に見積もりを出してもらったにもかかわらず、作業が終了してみると見積もりより高額な料金を請求されるケースも珍しくありません。 あらかじめ追加で発生する可能性がある料金の有無について確認しておいたり、丁寧でわかりやすい説明をしてくれる修理店から選ぶと安心できます。静岡には水道修理店が数多く存在していますが、優良な修理店もあればそうではないところもあるので、しっかりチェックして判断することは必須となってきます。専門的な知識や技術を備えたプロが対応していて、資格も取得しているのかなど、安心してその後水道を使うためにも確認しておくべきポイントは多々あります。

Pages

Subscribe to Oracle FAQ aggregator