Feed aggregator

Export Data in Excel multiple sheets

Tom Kyte - Tue, 2020-12-01 23:46
Hi, Good afternoon! I can able to export the data using <b>apex_data_export.export</b> package but i need to export into multiple sheets. we have one user input screen, based on the user input we need to generate the stock report with multiple sheets, but now i can able to generate the only one sheet using apex_data_export. <b>Pls suggest me if possible to generate multiple sheets using above mentioned the package.</b> Thank you. regards, Praveen Paulraj
Categories: DBA Blogs

How frequent should i backup Archive logs

Tom Kyte - Tue, 2020-12-01 23:46
i take complete database backup once daily with following command <code> backup as compressed backupset database plus archivelog; delete noprompt obsolete; </code> now i am planning to backup archive log every 5 mints with following command <code> backup archivelog all; </code> my archivelog deletion policy is 1 'times to DISK', so there will not be duplicate backups of same log files. i am doing this because i could not do complete recovery of one of my database due to missing archive log. my question is that, is this a good approch ? or there is any better way to be able to do complete recovery ? my RMAN settings are as folows <code> configure controlfile autobackup on; configure backup optimization on; configure retention policy to redundancy 5; configure archivelog deletion policy to backed up 1 times to DISK; configure retention policy to recovery window of 7 days; </code>
Categories: DBA Blogs

Shorter time for resolving deadlock

Tom Kyte - Tue, 2020-12-01 23:46
Hi, we have 2 processes: the FIRST is our in our domain, the SECOND is outer process and we can't control it. We are too late for FIRST process to maintain and change the logic. So the problem is when these 2 processes colide together - in that case we get deadlock and wait until deadlock is resolved or when one process give up. But while we are waiting for resolving a deadlock, we wait too much and all system (that one where is FIRST process) stucks. Can we somehow correct or change some parameter in order to "shorten" deadlock resolving? We want that deadlock in resolved after 180 seconds. What can we do ? Kind regards, Darko
Categories: DBA Blogs

Oracle Visual Builder Studio - Development Process Experience

Andrejus Baranovski - Tue, 2020-12-01 09:25
I describe how you can handle the development process in Visual Builder Studio. It is really straightforward and very well defined.

 

Service Connector Hub in Oracle Cloud (OCI)

Online Apps DBA - Tue, 2020-12-01 07:20

Service Connector Hub provides a single access point to monitor all data movement providing a centralized place where administrators can manage and monitor data movements across all their services within and from OCI to third-party tools. Do you want to know about the Service connecter hub? How will it benefit you? Read the blog post […]

The post Service Connector Hub in Oracle Cloud (OCI) appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

AWS CodeCommit Overview & Its Benefits

Online Apps DBA - Tue, 2020-12-01 07:13

AWS CodeCommit is a fully-managed source control service that hosts your secure Git-based repositories. It makes it easy for groups to collaborate on code in a secure and highly scalable ecosystem. CodeCommit eliminates the need to operate your source control system or worry about scaling its infrastructure. For more information, check out this blog post […]

The post AWS CodeCommit Overview & Its Benefits appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

SELECT Statement With Cursor In The Condition

Tom Kyte - Tue, 2020-12-01 05:26
Hello I have a use case, which has the following steps: 1. get a list of account keys using a function <i>get_keys</i>; 2. select results from a table where account keys <b>NOT</b> in the function results i.e. <code>SELECT * FROM accounts WHERE account_key NOT IN ( get_keys ); </code> I am getting the following error: <i>ORA-00932: inconsistent datatypes: expected - got CURSOR</i> Could you please advise if there is a way to use a function in such a way instead of a nested SELECT like <i>... NOT IN ( SELECT ... )</i>
Categories: DBA Blogs

SGA allocation among SQL statements

Tom Kyte - Tue, 2020-12-01 05:26
Hello Team, does SGA allocate individual buffer pool to each sql statement or it is for set of sql statements? if one session overloades the pool, how does it effect other sessions querying same table or different tables?
Categories: DBA Blogs

Stored procedure to hash a text column in a table with one of the hashing methods

Tom Kyte - Tue, 2020-12-01 05:26
Hello Experts, I have written the function that hashed a string but I donot know how to use this function to take in a column from another table to hash that column and then insert the hashed value into another hastable. any help will be appreciated, here is my hashing function <code>CREATE OR REPLACE Function Hash_msg_clob (msg IN clob) RETURN varchar2 AS hashed_value CLOB; --added l_timestamp_begin varchar2(100); l_timestamp_end varchar2(100); l_interval varchar2(100); BEGIN IF msg IS NULL THEN dbms_output.put_line('No clob was passed'); ELSE l_timestamp_begin := dbms_utility.get_time; DBMS_OUTPUT.PUT_LINE('The started time is: '|| l_timestamp_begin); SELECT STANDARD_HASH(dbms_lob.substr(msg, 4000, 1))INTO hashed_value FROM dual; --default algorithm SHA-1 l_timestamp_end := dbms_utility.get_time; DBMS_OUTPUT.PUT_LINE('The finished time is: '|| l_timestamp_end); DBMS_OUTPUT.PUT_LINE(hashed_value); l_interval := l_timestamp_end - l_timestamp_begin; DBMS_OUTPUT.PUT_LINE('The interval time is: '|| l_interval/100); RETURN hashed_value; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); RAISE; RETURN msg; END;</code>
Categories: DBA Blogs

PDB user connecting as sysdba

Tom Kyte - Tue, 2020-12-01 05:26
I have a query regarding username and it's roles. 1) When I try to connect pdb user user1@mypdb1 using sysdba and I fire user_tables then I do not get all my tables. but using the same connection i use dba_tables and i filter user1 it shows only my tables. 2) same user user1@mypdb1 connect using normal and i fire user_tables work fine like show only those tables which i created. dba_tables not work means dba_* view not accessible. So what is wrong with me? Can you help me? Please check below my output. *************************************************************************************************** Environment --------------------------------------------------------------------------------------------------- Using plsql developer Tool Oracle Database :- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit *************************************************************************************************** <code>select * from dba_users where username='USER1' order by 1; Output > Column Name Data USERNAME :- USER1 USER_ID :- 107 PASSWORD :- ACCOUNT_STATUS :- OPEN LOCK_DATE :- EXPIRY_DATE :- 16-03-2021 15:02 DEFAULT_TABLESPACE :- USERS TEMPORARY_TABLESPACE :- TEMP LOCAL_TEMP_TABLESPACE :- TEMP CREATED :- 28-11-2018 12:59 PROFILE :- DEFAULT INITIAL_RSRC_CONSUMER_GROUP :- DEFAULT_CONSUMER_GROUP EXTERNAL_NAME :- PASSWORD_VERSIONS :- 10G 11G 12C EDITIONS_ENABLED :- N AUTHENTICATION_TYPE :- PASSWORD PROXY_ONLY_CONNECT :- N COMMON :- NO LAST_LOGIN :- 28-NOV-20 12.18.39.000000000 PM +05:30 ORACLE_MAINTAINED :- N INHERITED :- NO DEFAULT_COLLATION :- USING_NLS_COMP IMPLICIT :- NO ALL_SHARD :- NO *********************************** Connection string user1@mypdb1 and connect as sysdba *********************************** sql> select * from user_tables; I got 1484 rows from table but i do not get my right table here. *********************************** Connection string user1@mypdb1 and connect as sysdba *********************************** sql> select * from dba_tables where owner='USER1'; I got my tables only. that is right. *********************************** Connection string user1@mypdb1 and connect as normal *********************************** sql> select * from user_tables;</code> I got my tables only. that is right.
Categories: DBA Blogs

Tuning Dynamically Generated SQL from PeopleSoft COBOL and Application Engine

David Kurtz - Tue, 2020-12-01 04:58

When working on a performance issue, you may reach the point where you want to introduce one or more hints into a SQL statement.  It is often tempting to simply add the hints to the source code.  Doing so may appear to be simpler.  That is often the case with Application Engine programs, however, it is much more difficult in PeopleSoft COBOL programs.  

A strong argument against such code change is that having made it, you have also to functionally test the change and push it through the entire release management process to get it into production.  Then, should you ever want to change or remove the hints, you have to go through the entire process again.

Oracle provides several mechanisms to define a database object containing a hint or set of hints and to apply them to matching SQL statements.  These mechanisms work best with SQL that uses bind variables rather than literals.  If an otherwise identical SQL statement has different literal values then it has a different SQL_ID and is treated by the database as a new statement.  SQL Plan Baselines, SQL Patches and SQL Profiles match to the specific text of a SQL statement.  Different literal values will prevent matching and defeat these mechanisms.  These techniques must still be tested and migrated in a controlled manner, but they have no functional impact and so only testing of performance is needed.

SQL Profiles can also perform forced matching, where statements that are similar except for literal values are matched.  However, note that they also require licencing of Tuning Pack.

Some parts of PeopleTools and the PeopleSoft applications are better than others at producing sharable SQL with bind variables.  Most of the SQL generated by the component processor uses bind variables.  In Application Engine, if the ReUse Statement property is not set, which it is not by default, the %BIND fields are substituted with their literal values in the SQL statement.  However, if the property is set then %BIND fields become bind variables.  Over the years much more PeopleSoft code has been delivered with this attribute enabled.  Doing so has significant performance benefits (see Performance Benefits of ReUse Statement Flag in Application Engine).  

Where, under normal circumstances, I might use a baseline or patch to inject a hint or profile of hints into a particular SQL statement (i.e. where I am dealing with a single SQL_ID), if the statement has literal values that change, then each statement has a different SQL_ID.  I have experimented with setting CURSOR_SHARING to FORCE at session-level for a specific scheduled process, but I have always had very poor experiences with that approach.  It invariably causes more problems than it solves.  Instead, I use force matched SQL Profiles.

The PeopleTools documentation sets out situations where ReUse Statement cannot be set.  This includes dynamically generated code where %BIND(…,NOQUOUTES) is used to embed a piece of SQL held in a variable.  This is a very common technique in PeopleSoft; often dynamically generated code is driven by the application configuration.  

We also see a similar design in PeopleSoft's COBOL programs.  Static statements are loaded from the stored SQL statements table (PS_SQLSTMT_TBL) and do use bind variables, but dynamic statements are assembled at runtime from static fragments in the COBOL code and any variable values are embedded as literals rather than using bind variables.

Forced matching will allow a SQL profile to match a statement that is the same except for different literal values.   However, dynamic SQL statements can change in ways that are beyond that, including:

  • Different instances of working storage tables can be used by different instances of the same process.
  • Different columns can be used in select and group by clauses.
  • Different criteria can be introduced. 
  • A different number of terms in an IN() condition.

Occasionally, and I really mean very occasionally when I have exhausted other alternatives, I have dynamically created groups of SQL Profiles (still with forced matching) to cover every permutation of the variations of the dynamic SQL statement.

Example
Here is a dynamic statement from such a COBOL process, FSPCCURR.  This General Ledger Revaluation process adjusts the base currency value of the account balances by creating adjusting entries for the accounts being revalued. It creates corresponding entries for any gain or loss that results from the revaluation.  It was captured by AWR, and I have extracted it with DBMS_XPLAN.DISPLAY_AWR.
The ledger table (PS_LEDGER) is joined to a working storage table of tree selectors (PS_TREE_SEL10_R001) and working storage table (PS_CURR_WRK_RT001) and the result is put into another working storage table (PS_CURR_WRK_TBL001).
INSERT INTO PS_CURR_WRK_TBL001 (ACCOUNT,ACCOUNTING_PERIOD,AFFILIATE,AFFI
LIATE_INTRA1,AFFILIATE_INTRA2,ALTACCT,BOOK_CODE,BUDGET_REF,CHARTFIELD1,C
HARTFIELD2,CHARTFIELD3,CLASS_FLD,CURRENCY_CD,DATE_CODE,DEPTID,FISCAL_YEA
R,FUND_CODE,GL_ADJUST_TYPE,LEDGER,OPERATING_UNIT,POSTED_BASE_AMT,POSTED_
TOTAL_AMT,POSTED_TOTAL_CR,POSTED_TOTAL_DR,POSTED_TRAN_AMT,POSTED_TRAN_CR
,POSTED_TRAN_DR,PROCESS_INSTANCE,PRODUCT,PROGRAM_CODE,PROJECT_ID,STATIST
ICS_CODE,RATE_DIV,RATE_MULT,GAINLOSS_ACCT,RESTATED_AMT,REVAL_ADJUSTMENT,
TARGET_CURRENCY) SELECT A.ACCOUNT,003,AFFILIATE,' ',' ','
',A.BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIELD2,CHARTFIELD3,'
',A.CURRENCY_CD,DATE_CODE,DEPTID,2020,FUND_CODE,GL_ADJUST_TYPE,'X_UKCORE
',OPERATING_UNIT,SUM(A.POSTED_BASE_AMT),SUM(POSTED_TOTAL_AMT),0,0,0,0,0,
0002858795,PRODUCT,PROGRAM_CODE,PROJECT_ID,' ',R.RATE_DIV,R.RATE_MULT,'
',ROUND(SUM(((((A.POSTED_TOTAL_AMT) / (RATE_DIV))) * (RATE_MULT))),02
),ROUND(SUM(((((A.POSTED_TOTAL_AMT) / (RATE_DIV))) * (RATE_MULT))),02 )
- SUM(A.POSTED_BASE_AMT),'USD' FROM PS_LEDGER A , PS_TREE_SEL10_R001 B
, PS_CURR_WRK_RT001 R WHERE A.LEDGER='X_UKCORE' AND A.FISCAL_YEAR =
2020 AND A.ACCOUNTING_PERIOD IN ( 0, 1, 2, 3) AND B
.PROCESS_INSTANCE=0002858795 AND B .CHARTFIELD='ACCOUNT' AND
A.ACCOUNT>=B .RANGE_FROM_10 AND A.ACCOUNT<=B .RANGE_TO_10 AND
A.BUSINESS_UNIT='96013' AND A.CURRENCY_CD <> 'GBP' AND FROM_CUR =
A.CURRENCY_CD AND TO_CUR = 'GBP' AND R.PROCESS_INSTANCE = 0002858795
GROUP BY A.ACCOUNT,AFFILIATE,A.BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIE
LD2,CHARTFIELD3,A.CURRENCY_CD,DATE_CODE,DEPTID,FUND_CODE,GL_ADJUST_TYPE,
OPERATING_UNIT,PRODUCT,PROGRAM_CODE,PROJECT_ID,RATE_DIV,RATE_MULT
However there are several copies of each of these working storage tables, and different concurrent instances of this process may be allocated different copies. 
There is also an in clause that lists the accounting periods to be processed. 
So the statement can vary. Here is another version of what is essentially the same statement with different literal values and different tables and for a different accounting period (this time period 5). The parts in bold a the ones that vary from statement to statement that are not literal values
INSERT INTO PS_CURR_WRK_TBL001 (ACCOUNT,ACCOUNTING_PERIOD,AFFILIATE,AFFI
LIATE_INTRA1,AFFILIATE_INTRA2,ALTACCT,BOOK_CODE,BUDGET_REF,CHARTFIELD1,C
HARTFIELD2,CHARTFIELD3,CLASS_FLD,CURRENCY_CD,DATE_CODE,DEPTID,FISCAL_YEA
R,FUND_CODE,GL_ADJUST_TYPE,LEDGER,OPERATING_UNIT,POSTED_BASE_AMT,POSTED_
TOTAL_AMT,POSTED_TOTAL_CR,POSTED_TOTAL_DR,POSTED_TRAN_AMT,POSTED_TRAN_CR
,POSTED_TRAN_DR,PROCESS_INSTANCE,PRODUCT,PROGRAM_CODE,PROJECT_ID,STATIST
ICS_CODE,RATE_DIV,RATE_MULT,GAINLOSS_ACCT,RESTATED_AMT,REVAL_ADJUSTMENT,
TARGET_CURRENCY) SELECT A.ACCOUNT,005,AFFILIATE,' ',' ','
',A.BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIELD2,CHARTFIELD3,'
',A.CURRENCY_CD,DATE_CODE,DEPTID,2020,FUND_CODE,GL_ADJUST_TYPE,'XXXX',OP
ERATING_UNIT,SUM(A.POSTED_BASE_AMT),SUM(POSTED_TOTAL_AMT),0,0,0,0,0,0002
991789,PRODUCT,PROGRAM_CODE,PROJECT_ID,' ',R.RATE_DIV,R.RATE_MULT,'
',ROUND(SUM(((((A.POSTED_TOTAL_AMT) / (RATE_DIV))) * (RATE_MULT))),02
),ROUND(SUM(((((A.POSTED_TOTAL_AMT) / (RATE_DIV))) * (RATE_MULT))),02 )
- SUM(A.POSTED_BASE_AMT),'AUD' FROM PS_LEDGER A , PS_TREE_SEL10_R B ,
PS_CURR_WRK_RT002 R WHERE A.LEDGER='XXXX' AND A.FISCAL_YEAR = 2020 AND
A.ACCOUNTING_PERIOD IN ( 0, 1, 2, 3, 4, 5) AND B
.PROCESS_INSTANCE=0002991789 AND B .CHARTFIELD='ACCOUNT' AND
A.ACCOUNT>=B .RANGE_FROM_10 AND A.ACCOUNT<=B .RANGE_TO_10 AND
A.BUSINESS_UNIT='13051' AND A.CURRENCY_CD <> 'AUD' AND FROM_CUR =
A.CURRENCY_CD AND TO_CUR = 'AUD' AND R.PROCESS_INSTANCE = 0002991789
GROUP BY A.ACCOUNT,AFFILIATE,A.BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIE
LD2,CHARTFIELD3,A.CURRENCY_CD,DATE_CODE,DEPTID,FUND_CODE,GL_ADJUST_TYPE,
OPERATING_UNIT,PRODUCT,PROGRAM_CODE,PROJECT_ID,RATE_DIV,RATE_MULT
If I want to use SQL Profiles to introduce hints to control the execution plan, then I will need a different profile for every possible permutation.
I start by using Carlos Sierra's coe_xfr_sql_profile.sql script.  This is a part of Oracle's SQLTEXPLAIN (SQLT) tool. It generates a SQL script that generates a SQL profile to reproduce a given execution plan for a given SQL statement that was captured by AWR.
From there is not a big jump to add a SQL statement to generate all the permutations of the variations in the SQL (other than for bind variables) and create a profile inside a loop.  The exact details will vary depending on the behaviour of the program.  However, in this particular example I need:
  • Different SQL profiles will be needed for each accounting period because there will be a different list of accounting periods in the IN() condition.  Subquery factors n and n1 produce a list of accounting periods.
WITH n AS (SELECT rownum n FROM dual CONNECT BY level<=12
), n1 AS (
SELECT n1.n period, LISTAGG(n2.n,', ') WITHIN GROUP (ORDER BY n2.n) periods
FROM n n1, n n2
WHERE n2.n <= n1.n
AND n1.n <= 12
GROUP BY n1.n
)
SELECT * FROM n1
/

PERIOD PERIODS
---------- ----------------------------------------
1 1
2 1, 2
3 1, 2, 3
4 1, 2, 3, 4
5 1, 2, 3, 4, 5
6 1, 2, 3, 4, 5, 6
7 1, 2, 3, 4, 5, 6, 7
8 1, 2, 3, 4, 5, 6, 7, 8
9 1, 2, 3, 4, 5, 6, 7, 8, 9
10 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
11 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11
12 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
  • Lists of the various tables used for working storage can be queried from the PeopleSoft data dictionary, PSRECDEFN.  I can see that there are 5 versions of the current work table that the process can choose from.  Note that these are ordinary tables, so there are 5 different records in PSRECDEFN. 
SELECT TO_NUMBER(substr(recname,13)) seq
, decode(sqltablename,' ','PS_'||recname,sqltablename) table_name
FROM psrecdefn
WHERE recname like 'CURR_WRK_TBL%'
/

SEQ TABLE_NAME
---------- ------------------
PS_CURR_WKK_TBL
1 PS_CURR_WKK_TBL001
2 PS_CURR_WKK_TBL002
3 PS_CURR_WKK_TBL003
4 PS_CURR_WKK_TBL004

  • However, if I was working on a temporary record used in an Application Engine program, I would need to look up the number of instances of that record.  
    • The number of non-shared temporary record tables is the sum of all the instances defined on each application engine program to which the record is allocated, plus the number of global instances, up to a maximum of 99.  Instance 0 is the shared instance.  The number can be altered in development and the additional tables built by Application Designer.  This can require additional SQL Profiles be built. 
WITH n AS (
S
ELECT rownum-1 n FROM dual CONNECT BY LEVEL<=100
), c AS (
SELECT c.recname
, n.n instance
, DECODE(r.sqltablename, ' ', 'PS_'||r.recname,r.sqltablename)||DECODE(n.n,0,'',n.n) table_name
FROM n
, pstemptblcntvw c
, psrecdefn r
, psoptions o
WHERE r.recname = c.recname
AND n.n <= c.temptblinstances+o.temptblinstances
)
SELECT instance, table_name
FROM c
WHERE recname = 'WRK_XREF_CAL'
/

INSTANCE TABLE_NAME
---------- ----------------------------------------------------------
0 PS_WRK_XREF_CAL
1 PS_WRK_XREF_CAL1
2 PS_WRK_XREF_CAL2
3 PS_WRK_XREF_CAL3
4 PS_WRK_XREF_CAL4
5 PS_WRK_XREF_CAL5
6 PS_WRK_XREF_CAL6
  • In this particular example, I know that every permutation of all three tables could occur in all accounting period, so I simply Cartesian join all the subquery factors.  
    • In other cases, only some permutations may occur.  This must be handled in the code that is written.  Literal values do not need to be considered because the profile will be created with force matching.
    • In Application Engine, although you often see the same instance of different temporary records used in the same process, there is nothing to prevent different instances of different records being used, and so all permutations must be considered.
  • I will also concatenate the ID for each table, and also the accounting period to produce an ID string that I can use in the name of the SQL profile.
'TS'||ts.seq||'_RT'||rt.seq||'_TBL'||wk.seq||'_'||n1.period
The profile generated by coe_xfr_sql_profile.sql will contain the complete profile of hints for the SQL statement captured by AWR.  That is officially the only way to guarantee a particular execution plan.  However, as in this example, I could specify the just that I want to introduce in the statement, effectively treating a SQL Profile as if it was a SQL Patch.  See also:
Here is the modified script.  Note the sections in bold.
  • The SQL statement in the FOR clause returns all the permutations of the variations in the SQL statement in an implicit cursor.  
  • Table names are concatenated into the SQL text from the columns in the implicit cursor.
  • Single quotation marks are doubled so that the string contains the single quotation mark.
  • It is important not to add or remove any spaces when introducing these changes.
  • Profiles are dropped and created inside the loop.  Force Matching is enabled.
REM coe_xfr_sql_profile_FSPCCURR_tree_rate_curr_wrk.sql
SPO coe_xfr_sql_profile_FSPCCURR_tree_rate_curr_wrk.log;

WHENEVER SQLERROR CONTINUE
REM WHENEVER SQLERROR EXIT SQL.SQLCODE;
VAR signature NUMBER;

DECLARE
sql_txt CLOB;
h SYS.SQLPROF_ATTR;
e_no_sql_profile EXCEPTION;
PRAGMA EXCEPTION_INIT(e_no_sql_profile, -13833);
BEGIN
FOR i IN(
WITH n AS (SELECT rownum n FROM dual CONNECT BY level<=12
), n1 AS (
SELECT n1.n period, LISTAGG(n2.n,', ') WITHIN GROUP (ORDER BY n2.n) periods
FROM n n1, n n2
WHERE n2.n <= n1.n
GROUP BY n1.n
), ts AS (
SELECT TO_NUMBER(substr(recname,13)) seq
, decode(sqltablename,' ','PS_'||recname,sqltablename) table_name
FROM psrecdefn
WHERE recname like 'TREE_SEL10_R%'
), rt AS (
SELECT TO_NUMBER(substr(recname,12)) seq
, decode(sqltablename,' ','PS_'||recname,sqltablename) table_name
FROM psrecdefn
WHERE recname like 'CURR_WRK_RT%'
), wk AS (
SELECT TO_NUMBER(substr(recname,13)) seq
, decode(sqltablename,' ','PS_'||recname,sqltablename) table_name
FROM psrecdefn
WHERE recname like 'CURR_WRK_TBL%'
)
SELECT 'TS'||ts.seq||'_RT'||rt.seq||'_TBL'||wk.seq||'_'||n1.period id
, ts.table_name ts_table_name
, rt.table_name rt_table_name
, wk.table_name wk_table_name
, n1.period, n1.periods
FROM n1, ts, rt, wk
) LOOP
sql_txt := 'INSERT INTO '||i.wk_table_name||' (ACCOUNT,ACCOUNTING_PERIOD,AFFILIATE,AFFILIATE_INTRA1,AFFILIATE_INTRA2,ALTACCT,
BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIELD2,CHARTFIELD3,CLASS_FLD,CURRENCY_CD,DATE_CODE,DEPTID,FISCAL_YEAR,
FUND_CODE,GL_ADJUST_TYPE,LEDGER,OPERATING_UNIT,POSTED_BASE_AMT,POSTED_TOTAL_AMT,POSTED_TOTAL_CR,POSTED_TOTAL_DR,
POSTED_TRAN_AMT,POSTED_TRAN_CR,POSTED_TRAN_DR,PROCESS_INSTANCE,PRODUCT,PROGRAM_CODE,PROJECT_ID,STATISTICS_CODE,
RATE_DIV,RATE_MULT,GAINLOSS_ACCT,RESTATED_AMT,REVAL_ADJUSTMENT,TARGET_CURRENCY)
SELECT A.ACCOUNT,011,AFFILIATE,
'' '','' '','' '',A.BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIELD2,CHARTFIELD3,'' '',A.CURRENCY_CD,DATE_CODE,DEPTID,2016,
FUND_CODE,GL_ADJUST_TYPE,''X_UKCORE'',OPERATING_UNIT,SUM(A.POSTED_BASE_AMT),SUM(POSTED_TOTAL_AMT),0,0,0,0,0,
0001234567,PRODUCT,PROGRAM_CODE,PROJECT_ID,'' '',R.RATE_DIV,R.RATE_MULT,'' '',
ROUND(SUM(((((A.POSTED_TOTAL_AMT) / (RATE_DIV))) * (RATE_MULT))),02
),ROUND(SUM(((((A.POSTED_TOTAL_AMT) / (RATE_DIV))) * (RATE_MULT))),02 )
- SUM(A.POSTED_BASE_AMT),''GBP'' FROM PS_LEDGER A , '||i.ts_table_name||' B
, '||i.rt_table_name||' R WHERE A.LEDGER=''X_UKCORE'' AND A.FISCAL_YEAR =
2016 AND A.ACCOUNTING_PERIOD IN ( 0, '||i.periods||')
AND B .PROCESS_INSTANCE=0001234567 AND B .CHARTFIELD=''ACCOUNT'' AND
A.ACCOUNT>=B .RANGE_FROM_10 AND A.ACCOUNT<=B .RANGE_TO_10 AND
A.BUSINESS_UNIT=''12345'' AND A.CURRENCY_CD <> ''GBP''
AND FROM_CUR = A.CURRENCY_CD
AND TO_CUR = ''GBP''
AND R.PROCESS_INSTANCE = 0001234567
GROUP BY A.ACCOUNT,AFFILIATE,A.BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIELD2,CHARTFIELD3,A.CURRENCY_CD,DATE_CODE,
DEPTID,FUND_CODE,GL_ADJUST_TYPE,OPERATING_UNIT,PRODUCT,PROGRAM_CODE,PROJECT_ID,RATE_DIV,RATE_MULT';
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[ALL_ROWS]',
q'[LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1" "R"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "A"@"SEL$1")]',
q'[SWAP_JOIN_INPUTS(@"SEL$1" "R"@"SEL$1")]',
q'[END_OUTLINE_DATA]');
:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
dbms_output.put_line(i.id||':'||:signature);

BEGIN
DBMS_SQLTUNE.drop_SQL_PROFILE (name => 'FSPCCURR_'||i.id);
EXCEPTION
WHEN e_no_sql_profile THEN NULL;
END;

IF 1=1 THEN
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text => sql_txt,
profile => h,
name => 'FSPCCURR_'||i.id,
description => 'coe FSPCCURR '||i.id||' @ '||:signature||'',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL).
FALSE:EXACT (similar to CURSOR_SHARING) */ );

END IF;

END LOOP;
END;
/

column name format a30
select name, status, sql_text
from dba_sql_profiles
where name like '%FSPCCURR%'
order by 1
/
SPO OFF;

When I implemented this particular example, it created 900 SQL profiles.  Each one has a different force matching signature.  Having this many SQL Profiles is not a problem for the database because they are looked up by the signature. 

TS1_RT4_TBL4_12:1278395827722096644
TS2_RT4_TBL4_12:4126874017730826984

PL/SQL procedure successfully completed.

However, managing that many profiles could become a cause for concern by the DBA.  Therefore, I think it is important that they have a sensible naming convention so that it is obvious to what they relate.
NAME                           STATUS   SQL_TEXT                                                                        
------------------------------ -------- --------------------------------------------------------------------------------

FSPCCURR_TS1_RT4_TBL4_12 ENABLED INSERT INTO PS_CURR_WRK_TBL004 (ACCOUNT,ACCOUNTING_PERIOD,AFFILIATE,AFFILIATE_IN

FSPCCURR_TS2_RT4_TBL4_12 ENABLED INSERT INTO PS_CURR_WRK_TBL004 (ACCOUNT,ACCOUNTING_PERIOD,AFFILIATE,AFFILIATE_IN


900 rows selected.
Further reading

Cloud Infrastructure Monitoring Overview And Step By Step

Online Apps DBA - Tue, 2020-12-01 00:53

Azure monitoring services are helpful to monitor all resources very easily that are available in your subscription, with the help of azure alerts you can get alerts for your particular resource via email. In this blog – k21academy.com/az30318, you will see an overview of Azure cloud infrastructure monitoring. Here we have covered various monitoring services […]

The post Cloud Infrastructure Monitoring Overview And Step By Step appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

[AZ-104] Microsoft Azure Administrator Certification: PDF Documentation & Tutorials

Online Apps DBA - Tue, 2020-12-01 00:42

Azure Certifications is the Most Demanding Certifications in Cloud computing in 2020. Azure Administrator is responsible for managing Deployment, Monitoring, Backup, Cost Analysis, security functions in the organization with Azure services. Check out this blog post at k21academy.com/az10314, which covers the following information: • What is the AZ-104 Administrator Exam? • Tips and Tricks for […]

The post [AZ-104] Microsoft Azure Administrator Certification: PDF Documentation & Tutorials appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Anti-Malware Software Installation on LINUX servers with Oracle DB

Tom Kyte - Mon, 2020-11-30 11:06
Hello! Our Billing system works with Oracle DB of version 11.2.0.4 (next year we are planning to upgrade all DBs to 19C with RAC). Oracle DBs run on LINUX servers. We got a requirement from our customer to install Anti-Malware Software on all LINUX servers (DB and Application). Please advise how it will affect the Oracle software and DBs. What will be the Oracle recommendations for such approach. Thank you in advance!
Categories: DBA Blogs

Weblogic Logs - alerting mechanisms

Tom Kyte - Mon, 2020-11-30 11:06
hello I hope you can guide me. I need a tool to notify me of significant log messages from weblogic to some other tool (for example whatsapp) My goal is "not to be sitting in front of the console watching the logs"
Categories: DBA Blogs

How can I find out from within a stored function whether it was called from a query?

Tom Kyte - Mon, 2020-11-30 11:06
Hi In a stored function I would like to detect whether it was called from an sql query. If not then my function will perform DML. Otherwise it will save function parameters into a buffer - a package variable. The DML execution will be deferred until after the query. Please could you let me know what would be a good, fast, reliable way of determining whether my function was called from a query? Please note that I would not like to run an autonomous transaction from within my query. This is because the decision whether to go ahead with the deferred DML cannot be made until after the query is completed. I would very much like finding the answer to the original puzzle rather than finding a workaround. Kind Regards Alex
Categories: DBA Blogs

How to find child or parent from data with only ID and Level

Tom Kyte - Mon, 2020-11-30 11:06
Hi Tom, I have this data <code>select 1 LineNumber, 1 mylevel, 10 id from dual union all select 2 , 2 , 11 from dual union all select 3 , 3 , 13 from dual union all select 4 , 3 , 14 from dual union all select 5 , 4 , 15 from dual union all select 6 , 2 , 20 from dual union all select 7 , 2 , 30 from dual union all select 8 , 3 , 31 from dual union all select 9 , 4 , 33 from dual union all select 10 , 3 , 32 from dual union all select 11 , 3 , 34 from dual union all select 12 , 4 , 35 from dual union all select 13 , 5 , 36 from dual</code> I'm look for a way to get - the child ids recursively of the id = 30. - the parent id of the id = 30
Categories: DBA Blogs

How to dynamically transpose data into with changing column headers with or without pivot/pivot xml?

Tom Kyte - Mon, 2020-11-30 11:06
This question is most common with no clear solution. I have 2 scenarios for dynamic pivoting and the solution requested could be using pivot xml(tried, but extracting is a task)/dynamic sql execution/arays if possible we could use.. Scenario 1: In the first case,I i need to pivot the data where data source is same table. However the pivot needs to be dynamic as the columns header would keep changing as per column app_id. So if app_id=1. The column header would be A,B,C,D, If app_id=2, column would be CDEF and so on. Also each set of value has an id. So for id, 120 and app_id=1 , column A,B,C,D, would display the values and so on. The current sample data has only 2 app_ids, but there could be many more, so app_id and labels would kepe changing thus i need to write a dynamic query. Table is DATA_1 <code>ID label value app_id --- ----- ----- ------ 120 A Alpha 1 120 B Beta 1 120 C Class 1 120 D Delta 1 120 C Alpha 2 120 D Beta 2 120 E Class 2 120 F Delta 2</code> And expected output would be something like this. P.S. Data is dummy and app_ids, and ids would keep getting inserted and removed with changing column headers,so a dynamic solution is needed. <code>SELECT * FROM data WHERE ID = 120 AND app_id = 1; app_id A B C D ID ------ ------ ----- ----- ----- ----- 1 Alpha Beta Class Delta 120 SELECT * FROM data WHERE ID = 120 AND app_id = 2; app_id C D E F ID ------ ------ ----- ----- ----- ----- 2 Alpha Beta Class Delta 120</code> Scenario 2: Here were had the data in one table. Now we have separate table containing the labels and current table with values. SO in table from scenario one we will focus on id, app_id and value columns only. Select app_id,id,value from data_1; The labels will come from another table DATA_Labels in column Header_Name: <code>APP_ID SEQ HEADER_NAME 1 1 A 1 2 B 1 3 C 1 4 D 1 5 E 1 6 F 1 7 G 1 8 H 1 9 I 1 10 J 2 1 P 2 2 Q 2 3 R 2 4 S 2 5 T 2 6 U 2 7 V 2 8 W 2 9 X 2 10 Y</code> So, for labels we would use header_names. ANd Data_1 and Data_Labels could be joined on basis of app_id. In case the values exceed column headers, we can assume that the headers would reach say maximum 20. And in case the header/name is not available, it could put some default header name like COL11,COL12 if the value stretches upto there. I did lot of research but most solutions are too complex and confusing. Any leads would be appreciated.
Categories: DBA Blogs

export DB dumps using DBMS_DATAPUMP api to the Object storage on Oracle Cloud Infrastructure

Tom Kyte - Mon, 2020-11-30 11:06
Hi, I have a requirement where I need to export DB dumps using DBMS_DATAPUMP api to the Object storage on Oracle Cloud Infrastructure. I found below statement on official document to export dumps at object storage using expdp utility. <code>expdp admin/password@ADWC1_high \ filesize=5GB \ dumpfile=default_credential:https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/bucketname/o/exp%U.dmp \ parallel=16 \ encryption_pwd_prompt=yes \ logfile=export.log \ directory=data_pump_dir</code> But I didn?t get any reference for DBMS_DATAPUMP api, Could you please help me to know how to perform same task using DBMS_DATAPUMP api.
Categories: DBA Blogs

How can I get min_date and max_date, grouping by some field

Tom Kyte - Mon, 2020-11-30 11:06
Hi. I'm trying to build a query to get dates intervals. Let me explain it in a simple way. We have a table with this fields: <code>Soldier_ID | Soldier_Name | Location | Ranking | Start_Date | End_Date 1001 Jones Texas Lieutenant 2000/03/20 2002/08/15 1001 Jones Afghanistan Lieutenant 2002/08/16 2003/03/18 1001 Jones Michigan Lieutenant 2003/03/19 2004/06/01 1001 Jones NY Lieutenant 2004/06/02 2004/10/01 1001 Jones NY Captain 2004/10/02 2005/04/20 1001 Jones Berlin Captain 2005/04/21 2007/02/20 1001 Jones Michigan Major 2007/02/21 2008/10/22 1001 Jones Ohio Major 2008/10/23 2010/01/26 1001 Jones Ohio Captain 2010/01/27 2013/11/26 1001 Jones Texas Captain 2013/11/26 2014/05/11 1001 Jones Texas Major 2014/05/12 2016/04/22 1001 Jones Texas General 2016/04/23 2020/10/10 1001 Jones Washington General 2020/10/11 2020/11/30</code> I need to get the time intervals that soldier spent on each ranking, so the end result I need to get should be something like this: <code>Soldier_ID | Soldier_Name | Ranking | Start_Date | End_Date 1001 Jones Lieutenant 2000/03/20 2004/10/01 1001 Jones Captain 2004/10/02 2007/02/20 1001 Jones Major 2007/02/21 2010/01/26 1001 Jones Captain 2010/01/27 2014/05/11 1001 Jones Major 2014/05/12 2016/04/22 1001 Jones General 2016/04/23 2020/11/30</code> As you can see the soldier is promoted/demoted along the time. Any suggestion on how to do this?
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator