Recently a friend asked me for this. I see it a lot on OraFaq as a question in the forums so here are the basics of working with delimited strings. I will show the various common methods for creating them and for unpacking them. Its not like I invented this stuff so I will also post some links for additional reading.
Star Schemas are proliferating with warehouses these days. Many practitioners I have met in this space are a bit new to the concept of star schemas and as such keep falling back to old habits. But this is only hurting them. So I'll try to give my simplistic view of how it works in the hopes of granting some clarity on the practice of Star Modeling and overcoming our previous training to resist its concepts.
You might face a situation where you need to interchange the values of two columns in an Oracle database table. This article will explore ways to achieve this.
During a quite evening of my last on-call bout I was alerted from our monitors that the UNDO tablespace was running out of free space. Thought of adding of a new data file and be done with it; When I checked the current allocation for this tablespace it was already at 40G - couldn't believe what I was seeing. The undo_retention was set to 7200 and max query length in v$undostat was not that high. One column that did caught my eye was the tuned_undoretention, its value was way very high.
Database tables are structured in columns and rows. However, some data lends itself to switching row data as column data for expository purposes. The pivot operation in SQL allows the developer to arrange row data as column fields. For example, if there are two customers who have both visited a store exactly four times, and you want to compare the amount of money spent by each customer on each visit, you can implement the pivot operation.
Checks to be performed at the machine level (note the example is Red Hat Linux)
run queue should be ideally not more than the number of CPU’s on the machine
At the maximum it should never be more than twice the number of CPU’s.
This is denoted by the column ‘r’ in the vmstat output shown below
vmstat – 5
procs memory swap io system cpu
r b swpd free buff cache si so bi bo in cs us sy id wa
4 1 488700 245704 178276 12513572 0 1 10 17 48 1365 40 12 43 5
AX (11i) SLA (R12)
Daily Journal Book - Line Descriptions Payables/Receivables/Inventory Journal Entries Report Daily Journal Book - Header Descriptions Payables/Receivables/Inventory Journal Entries Report Final Daily Journal Book - Header Descriptions Journal Entries Report Italian Journal Book Journal Entries Report
Sometimes Drilldown from GL will open OA pages and sometimes Oracle Forms.
Here is the reason:
- When the data is from 11i and not upgraded to R12 (means it is not in XLA tables and gl_je_headers.je_from_sla_flag is NULL), then it will open the subledger form directly.
- When the data is from R12 or from 11i and upgraded to R12 (means it is in XLA tables and gl_je_headers.je_from_sla_flag is 'Y' or 'U'), then it will open the SLA OA page first, so that we can check the XLA data and we can open the subledger form through View Transactions button.
If the following error occurs while submitting AX Posting Manager/Post Transactions, then set the profile options, AX Application Name and GL Set of Books Name.
Which will automatically set the other two profiles.
The following are the important profile options, which need to be set to submit AX Posting Manager/Post Transactions.
- AX Application Id
- AX Application Name
- GL Set of Books ID
- GL Set of Books Name
From AX User Guide,
The Global Accounting Engine does not support the following actions during a Customer or Supplier Merge because they violate reconciliation principles ensured by Global Accounting Engine reports:
- Deleting a customer after a merge.
- Creating accounting entries after a multiple merge.
- Merging unsuccessfully, such as a merge in Payables where only some of the invoices have been merged.
- Merging suppliers, when the new supplier site code already exists in another organization that has the same set of books ID.