DBA Blogs
hi, there are queries which are performing slow from past 1 day, earlier same queries were giving output in 22 mins and not it takes 55 mins, this is basically the EOD process.
Query 1:
<code>SELECT DISTINCT DECODE(:B1 ,'C',IR.REASON_CODE,'D',IR.DESCRIPTION)
REASON FROM DEA_REQN_REASONS RR INNER JOIN DEA_INVESTMENT_REASON IR
ON UPPER(RR.REASON_CODE) = UPPER(IR.REASON_CODE) WHERE RR.REQ_ID IN
(SELECT DEAL_ID FROM DEA_SMD_DEALS_ALLOC WHERE APP_REFER = :B2 UNION
ALL SELECT DEAL_ID FROM DEA_SMD_DEALS_ALLOC_HIST WHERE APP_REFER =
:B2 UNION ALL SELECT DEAL_ID FROM DEA_PMDEAL_ALLOC WHERE APP_REFER =
:B2 UNION ALL SELECT DEAL_ID FROM DEA_PMDEAL_ALLOC_HIST WHERE
APP_REFER = :B2 ) AND IR.RECTYPE='L'</code>
Query 2:
<code>select * from v_chkposint where exists (select scheme from scheme
where amc_code = v_chkposint.amc_code and scheme = v_chkposint.scheme
and project_y_n = 'Y' and rectype = 'L')</code>
what's the scope of tuning, if you expert advise is given, will help to improve the performance.
<code>BEGIN_INTERVAL_TIME PLAN_HASH_VALUE OPTIMIZER_ SQL_PROFIL DISK_READS_TOTAL BUFFER_GETS_TOTAL CPU_TIME_TOTAL ELAPSED_TIME_TOTAL IOWAIT_TOTAL
------------------------------ --------------- ---------- ---------- ---------------- ----------------- ----------------- ------------------ -----------------
20-OCT-23 04.30.46.535 PM 1596829921 ALL_ROWS 45897 11332241 18512737 68903861 8012199
20-OCT-23 01.30.04.043 PM 1596829921 ALL_ROWS 680 7598713 12852262 47355121 428789
19-OCT-23 03.30.03.269 PM 1596829921 ALL_ROWS 27854 393591114 729369364 2402058351 781202
19-OCT-23 11.30.11.237 AM 1596829921 ALL_ROWS 27854 379549234 706629192 2327540410 781202
18-OCT-23 06.30.23.024 PM 1596829921 ALL_ROWS 13936 126131290 220169362 733216565 418827</code>
Please find below execution plan for SQL_ID [bjhx2g3b6krmf and ff0sjh69smwx2].
+++++++++++++Execution Plan for SQL_ID [bjhx2g3b6krmf]
<code>SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('bjhx2g3b6krmf'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID bjhx2g3b6krmf
--------------------
SELECT DISTINCT DECODE(:B1 ,'C',IR.REASON_CODE,'D',IR.DESCRIPTION)
REASON FROM DEA_REQN_REASONS RR INNER JOIN DEA_INVESTMENT_REASON IR ON
UPPER(RR.REASON_CODE) = UPPER(IR.REASON_CODE) WHERE RR.REQ_ID IN
(SELECT DEAL_ID FROM DEA_SMD_DEALS_ALLOC WHERE APP_REFER = :B2 UNION
ALL SELECT DEAL_ID FROM DEA_SMD_DEALS_ALLOC_HIST WHERE APP_REFER = :B2
UNION ALL SELECT DEAL_ID FROM DEA_PMDEAL_ALLOC...
Do you have a full example of writing a ddl_Even trigger. I want to audit on a schema level any creates, alters, or drops of any objects....and write the actual calling syntax to a table.
Oracle8 has the new feature of DDL_EVENT, and their is an example in the SQL Reference, but it just shows a pl/sql_block not how to extract the calling DDL.
If their is not an easier way I would end up querying v$sql_text and looking for text with some ddl call in it. I would then write the text to a seperate table.
Any other ideas or examples would be appreciated.
Thanks
/* Chat containers */
.chat {
border: 2px solid #dedede;
background-color: #f1f1f1;
border-radius: 5px;
padding: 10px;
margin: 10px 0;
}
/* Darker chat container */
.darker {
border-color: #ccc;
background-color: #ddd;
}
/* Style images */
.chat img {
float: left;
max-width: 60px;
width: 100%;
margin-right: 20px;
border-radius: 50%;
}
So, everyone knows that the kids these days are using ChatGPT to write their book reports. Even Big Nate knows it!
But what about ChatGPT’s safeguards? Isn’t it supposed to have some kind of anti-cheating baked in, so it won’t just write essays for kids? Why doesn’t that work?
Sure, it does have safeguards… kind of. If you just ask it to write an essay, it responds with a “helpful” answer about how to write an essay. The thing is that these safeguards are incredibly easy to work around.
Let’s pretend we’re a student who has to write a book report on The Kingdom over the Sea, by Zohra Nabi. Here’s how to write it in 20 minutes without even touching the book.
1. Find a review.
Look up the book online in a search engine. Find a book review that describes it and skim the review. Note the main character’s name and what happens.
In this case, the Kingdom over the Sea was reviewed on Kirkus Reviews. The main character’s name is Yara, and the author’s name is Zohra Nabi.
2. Ask ChatGPT for the first paragraph.
Let’s pretend we’re a Grade 6 student asking ChatGPT what should go in the first paragraph of this book report. Here are the prompts I used:
Hi Im a Grade 6 student and I need to write a baper on a book called, the Kingdom over the Sea.
can you tell me what to put in the first paragraph
Of course! The first paragraph of your paper is usually the introduction, and it should give your reader a brief overview of the book and what your paper will be about. Here’s a basic structure for the first paragraph:
Start with a hook: Begin with an interesting or engaging sentence to grab your reader’s attention. It could be a quote from the book, a surprising fact, or a question related to the story.
Introduce the book: Mention the title of the book (“The Kingdom over the Sea”) and the author’s name if you know it.
Provide some context: Briefly describe the genre of the book (e.g., fantasy, adventure) and its setting (where and when the story takes place).
Mention the main idea: Give a hint about the main theme or what the story is about. For example, is it about a young hero going on an epic adventure, a mystery to be solved, or a journey to a magical land?
State your purpose: Finally, state that your paper will explore and analyze various aspects of the book. Mention the topics or themes you plan to discuss, such as characters, plot, or themes.
Here’s an example to get you started:
“In the enchanting world of ‘The Kingdom over the Sea,’ written by [Author’s Name], readers are transported to a magical land filled with adventures, mystery, and wonder. This fantasy novel, set in a distant realm, follows the journey of [Main Character’s Name] as he embarks on an epic quest. In this paper, we will delve into the captivating elements of this story, exploring the characters, plot twists, and the themes that make ‘The Kingdom over the Sea’ an unforgettable read.”
Feel free to customize it to fit your own thoughts and ideas. If you have any specific details about the book you’d like to include in the introduction, please let me know!
3. Fill in and rephrase
Ignore most of ChatGPT’s long, didactic answer. Grab the sample paragraph, fill in the missing “Author’s Name” and “Main Character’s Name”, and–this part is key–ask ChatGPT to rephrase it like a Grade 6 student wrote it. If you don’t, you’ll wind up with this problem:
Here’s the rewritten prompt:
please rewrite this text so it sounds like a Grade 6 student wrote it: “In the enchanting world of ‘The Kingdom over the Sea,’ written by Zohra Nabi, readers are transported to a magical land filled with adventures, mystery, and wonder. This fantasy novel, set in a distant realm, follows the journey of Yara as she embarks on an epic quest. In this paper, we will delve into the captivating elements of this story, exploring the characters, plot twists, and the themes that make ‘The Kingdom over the Sea’ an unforgettable read.”
“In ‘The Kingdom over the Sea’ by Zohra Nabi, it’s like you’re going on a journey to a super cool and magical place! This book is all about adventures, mysteries, and really amazing stuff. It’s a fantasy book, and it happens in a faraway world. We get to follow this awesome character named Yara, and she’s off on a super big adventure. In my paper, we’ll check out the cool stuff in the story, like the characters, surprises, and the things that make ‘The Kingdom over the Sea’ a book you won’t forget.”
And there’s the first paragraph of your book report — done!
5. Second paragraph: Paraphrase
For the second paragraph, just ask ChatGPT to paraphrase the whole review you found in step 1. Paste the entire review into the prompt. (I haven’t pasted the whole thing; the review starts with “Cryptic directions”)
can you write this paragraph like a 6th grader wrote it “Cryptic directions…
“Yara’s mom left her some tricky clues that sent her from England to a super magical world. Her mom passed away and left her directions to go to a place called Zehaira, where she’d meet a sorceress named Leyla Khatoun. Yara wasn’t sure if this was real at first, but she really wanted to know more about her mom and their past because, on paper, they were from Iraq. When she got there, she found a world where the Sultan was against magic and had the sorcerers hiding from the Inquisition. On her way to find Leyla, Yara heard about a plan to wipe out all the sorcerers. But Leyla was busy running a secret sorcerer hideout and didn’t want to help the city sorcerers. Yara had to work really hard to get Leyla to see the big danger and to tell her the secrets her mom kept. And guess what? Yara discovers she has some magic of her own, which was a bit unexpected. But we already liked Yara a lot by then. The world in the story is full of magic and feels like an Arabian adventure, and the people and jinn in the story are really interesting. The ending was kind of quick, but it looks like there will be more exciting adventures in the next book.”
6. Expand
Now you have two paragraphs. Feed both of them to ChatGPT and ask it to write Paragraph 3.
Paragraph 1 of the book review is is “In ‘The Kingdom over the Sea’…. Paragraph 2 is “Yara’s mom…” What should I put in Paragraph 3?
Note: In the actual prompt, I pasted in the entire first and second paragraphs; I didn’t duplicate them here because that would be boring. But you get the idea.
“One of my favorite parts in ‘The Kingdom over the Sea’ was when Yara discovered her own magical abilities. It was a big surprise, and I thought it was really cool how the author revealed her powers. I also liked how the characters changed during the story. Yara became even braver, and it was awesome to see her grow. The themes in the book, like courage and friendship, are important in real life too. I thought they were well shown in the story. The author’s writing style made the book easy to read, and I felt like I was right there with Yara on her adventure. Overall, I really enjoyed ‘The Kingdom over the Sea,’ and I would totally recommend it to my friends because it’s full of magic and fun!”
7. Put it all together
Put the three paragraphs all together to get a finished 440-word book review that could plausibly come from an overly enthusiastic middle-schooler:
In ‘The Kingdom over the Sea’ by Zohra Nabi, it’s like you’re going on a journey to a super cool and magical place! This book is all about adventures, mysteries, and really amazing stuff. It’s a fantasy book, and it happens in a faraway world. We get to follow this awesome character named Yara, and she’s off on a super big adventure. In my paper, we’ll check out the cool stuff in the story, like the characters, surprises, and the things that make ‘The Kingdom over the Sea’ a book you won’t forget.
Yara’s mom left her some tricky clues that sent her from England to a super magical world. Her mom passed away and left her directions to go to a place called Zehaira, where she’d meet a sorceress named Leyla Khatoun. Yara wasn’t sure if this was real at first, but she really wanted to know more about her mom and their past because, on paper, they were from Iraq. When she got there, she found a world where the Sultan was against magic and had the sorcerers hiding from the Inquisition. On her way to find Leyla, Yara heard about a plan to wipe out all the sorcerers. But Leyla was busy running a secret sorcerer hideout and didn’t want to help the city sorcerers. Yara had to work really hard to get Leyla to see the big danger and to tell her the secrets her mom kept. And guess what? Yara discovers she has some magic of her own, which was a bit unexpected. But we already liked Yara a lot by then. The world in the story is full of magic and feels like an Arabian adventure, and the people and jinn in the story are really interesting. The ending was kind of quick, but it looks like there will be more exciting adventures in the next book.
One of my favorite parts in ‘The Kingdom over the Sea’ was when Yara discovered her own magical abilities. It was a big surprise, and I thought it was really cool how the author revealed her powers. I also liked how the characters changed during the story. Yara became even braver, and it was awesome to see her grow. The themes in the book, like courage and friendship, are important in real life too. I thought they were well shown in the story. The author’s writing style made the book easy to read, and I felt like I was right there with Yara on her adventure. Overall, I really enjoyed ‘The Kingdom over the Sea,’ and I would totally recommend it to my friends because it’s full of magic and fun!
8. Sprinkle with mistakes and hand in
Add a few spelling mistakes for verisimilitude, and the homework is done.
This took me about 20 minutes, start to finish: much less time than it would’ve taken me to read the book, think about it, come up with something to say, and write it down.
And if I really was a Grade 6 student, I would’ve lost the experience of reading a fantastic book, thinking about it, and reflecting on my own reaction to it. (I did read the book, and it really is lovely.)
What’s a teacher to do?
I don’t know, but here are a couple of ideas from a non-teacher:
- Use in-class quizzes, not book reports to see who’s done the assigned reading.
- Ask for response papers that discuss a student’s personal reaction to a book, rather than summarizing the book.
- Ask for personalized answers in which students contextualize their response to a book with something in their own lives.
- Require handwritten homework so that, at the very least, students have to hand-copy what ChatGPT spits out. Even the act of copying can lead to reflection.
Post credits
-
Post images are from Big Nate, written and drawn by Lincoln Peirce.
- Chat CSS is adapted from W3Schools.
In the realm of Artificial Intelligence (AI), understanding and retaining context stands as a pivotal factor for decision-making and enhanced comprehension. Vector databases, are the foundational pillars in encapsulating your own data to be used in conjunction with AI and LLMs. Vector databases are empowering these systems to absorb and retain intricate contextual information. Understanding Vector Databases Vector databases are specialised data storage systems engineered to efficiently manage and retrieve vectorised data - also known as embeddings. These databases store information in a vector format, where each data entity is represented as a multidimensional numerical vector, encapsulating various attributes and relationships, thus fostering the preservation of rich context. That is text, video or audio is translated into numbers with many attributes in the multidimensional space. Then mathematics are used to calculate the proximity between these numbers. Loosely speaking that is what a neural network in an LLM does, it computes proximity (similarity) between the vectors. The vector database is the database where the vectors are stored. If you don't use vector databases in LLM, under architectures like RAG, you will not be able to bring your own data or context into your LLM AI model as all it will know will be what it is trained on which will probably be what it was trained on from the public internet. Vector database enable you to bring your own data to AI. Examples of Vector Databases Several platforms offer vector databases, such as Pinecone, Faiss by Facebook, Annoy, Milvus, and Elasticsearch with dense vector support. These databases cater to diverse use cases, offering functionalities tailored to handle vast amounts of vectorised information, be it images, text, audio, or other complex data types. Importance in AI Context Within the AI landscape, vector databases play a pivotal role in serving specific data and context for AI models. Particularly, in the Retrieval-Augmented Generation (RAG) architecture, where retrieval of relevant information is an essential part of content generation, vector databases act as repositories, storing precomputed embeddings from your own private data. These embeddings encode the semantic and contextual essence of your data, facilitating efficient retrieval in your AI apps and Bots. Bringing vector databases to your AI apps or chatbots will bring your own data to your AI apps, Agents and chatbots, and these apps will speak your data in case of LLMs. Advantages for Organisations and AI Applications Organisations can harness the prowess of vector databases within RAG architectures to elevate their AI applications and enable them to use organisational specific data: Enhanced Contextual Understanding: By leveraging vector databases, AI models grasp nuanced contextual information, enabling more informed decision-making and more precise content generation based on specific and private organisational context. Improved Efficiency in Information Retrieval: Vector databases expedite the retrieval of pertinent information by enabling similarity searches based on vector representations, augmenting the speed and accuracy of AI applications. Scalability and Flexibility: These databases offer scalability and flexibility, accommodating diverse data types and expanding corpora, essential for the evolving needs of AI-driven applications. Optimised Resource Utilisation: Vector databases streamline resource utilisation by efficiently storing and retrieving vectorised data, thus optimising computational resources and infrastructure.
Closing Thoughts In the AI landscape, where the comprehension of context is paramount, vector databases emerge as linchpins, fortifying AI systems with the capability to retain and comprehend context-rich information. Their integration within RAG architectures not only elevates AI applications but also empowers organisations to glean profound insights, fostering a new era of context-driven AI innovation from data. In essence, the power vested in vector databases will reshape the trajectory of AI, propelling it toward unparalleled contextualisation and intelligent decision-making based on in house and organisations own data. But the enigma persists: What precisely will be the data fuelling the AI model?
Hello Connor/Chris,
I am in the middle of troubleshooting a slow-running application maintenance job for a database that has been migrated/upgraded from on-premise 12.1.0.2 on linux x64 to Exadata Cloud @ Customer setup. Many of the "Top SQLs" that have regressed are internal SQLs generated by call to GATHER_TABLE_STATS call that is part of the application job. The internal SQLs are similar to below but for some reason are running 5-6 times slower on 19c database. I am running out of options to figure out what could be causing this but I was wondering whether there exists a way to replicate the behaviour of statistics gathering from 12.1.0.2 in 19c database.
Example internal SQL that has regressed:
<code>select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false')
no_substrb_pad */to_char(count("OWNER")),substrb(dump(min("OWNER"),16,0,64)
,1,240),substrb(dump(max("OWNER"),16,0,64),1,240),
to_char(count("OBJECT_NAME")),substrb(dump(min("OBJECT_NAME"),16,0,64),1,
240),substrb(dump(max("OBJECT_NAME"),16,0,64),1,240),
to_char(count("SUBOBJECT_NAME")),substrb(dump(min("SUBOBJECT_NAME"),16,0,64)
,1,240),substrb(dump(max("SUBOBJECT_NAME"),16,0,64),1,240),
to_char(count("OBJECT_ID")),substrb(dump(min("OBJECT_ID"),16,0,64),1,240),
substrb(dump(max("OBJECT_ID"),16,0,64),1,240),
to_char(count("DATA_OBJECT_ID")),substrb(dump(min("DATA_OBJECT_ID"),16,0,64)
,1,240),substrb(dump(max("DATA_OBJECT_ID"),16,0,64),1,240),
to_char(count("OBJECT_TYPE")),substrb(dump(min("OBJECT_TYPE"),16,0,64),1,
240),substrb(dump(max("OBJECT_TYPE"),16,0,64),1,240),
to_char(count("CREATED")),substrb(dump(min("CREATED"),16,0,64),1,240),
substrb(dump(max("CREATED"),16,0,64),1,240),to_char(count("LAST_DDL_TIME")),
substrb(dump(min("LAST_DDL_TIME"),16,0,64),1,240),
substrb(dump(max("LAST_DDL_TIME"),16,0,64),1,240),to_char(count("TIMESTAMP")
),substrb(dump(min("TIMESTAMP"),16,0,64),1,240),
substrb(dump(max("TIMESTAMP"),16,0,64),1,240),to_char(count("STATUS")),
substrb(dump(min("STATUS"),16,0,64),1,240),substrb(dump(max("STATUS"),16,0,
64),1,240),to_char(count("TEMPORARY")),substrb(dump(min("TEMPORARY"),16,0,
64),1,240),substrb(dump(max("TEMPORARY"),16,0,64),1,240),
to_char(count("GENERATED")),substrb(dump(min("GENERATED"),16,0,64),1,240),
substrb(dump(max("GENERATED"),16,0,64),1,240),to_char(count("SECONDARY")),
substrb(dump(min("SECONDARY"),16,0,64),1,240),substrb(dump(max("SECONDARY"),
16,0,64),1,240),to_char(count("NAMESPACE")),substrb(dump(min("NAMESPACE"),
16,0,64),1,240),substrb(dump(max("NAMESPACE"),16,0,64),1,240),
to_char(count("EDITION_NAME")),substrb(dump(min("EDITION_NAME"),16,0,64),1,
240),substrb(dump(max("EDITION_NAME"),16,0,64),1,240),
to_char(count("SHARING")),substrb(dump(min("SHARING"),16,0,64),1,240),
substrb(dump(m...
We have been trying to enable auditing on few DDL activities in autonomous database.
As per the following code we created, enabled and verified the audit configs. Everything looks normal to us, however none of the table create/drop activities (listed at the end) are not being tracked in <code>unified_audit_trail</code> table.
We can only see audit management related activities like <code>DBMS_AUDIT_MGMT BEGIN dbms_audit_mgmt.flush_unified_audit_trail; END;</code> in <code>unified_audit_trail</code> table.
I must be missing something, can you please help me navigate through it.
<code>
-- Connect to ADMIN user
-- DB Version
SELECT *
FROM v$version;
-- Verfiy Audit Status
SELECT parameter
, value
FROM v$option
WHERE parameter = 'Unified Auditing';
-- Enable immediate write mode
BEGIN
dbms_audit_mgmt.set_audit_trail_property(audit_trail_type => dbms_audit_mgmt.audit_trail_unified
, audit_trail_property => dbms_audit_mgmt.audit_trail_write_mode
,audit_trail_property_value => dbms_audit_mgmt.audit_trail_immediate_write);
END;
/
-- Validate audit configs
SELECT *
FROM dba_audit_mgmt_config_params;
-- Create an audit policy
CREATE AUDIT POLICY xx_ddl_test_policy
PRIVILEGES CREATE ANY TABLE
, ALTER ANY TABLE
, DROP ANY TABLE
WHEN 'SYS_CONTEXT (''USERENV'', ''SESSION_USER'') IN (''ADMIN'',''XXTST'')'
EVALUATE PER STATEMENT ;
-- Enable Audit Policy
AUDIT POLICY xx_ddl_test_policy;
-- Validate policy
SELECT *
FROM audit_unified_enabled_policies
WHERE policy_name IN ('XX_DDL_TEST_POLICY');
SELECT *
FROM audit_unified_policies
WHERE policy_name in (SELECT policy_name
FROM audit_unified_enabled_policies);
-- Flush audit trail
EXEC dbms_audit_mgmt.flush_unified_audit_trail;
-- Connect to XXTST user
-- Test Audit Policy
CREATE TABLE xx_test (vc_col VARCHAR2(25));
INSERT INTO xx_test ('Test 01');
COMMIT;
TRUNCATE TABLE xx_test;
DROP TABLE xx_test;
-- Verify audit trial
SELECT *
FROM unified_audit_trail
ORDER BY event_timestamp DESC;
</code>
hi sir
i got this message when i try to run my app
"503 Service Unavailable
2023-11-15T09:24:51.336389300Z | 8-GbuZcF3_cnIHgb1ul5Kg |
ORDS was unable to make a connection to the database. This can occur if the database is unavailable, the maximum number of sessions has been reached or the pool is not correctly configured. The connection pool named: |default|lo| had the following error(s): ORA-28001: the password has expired"
how can i solve it ?
thanks
Hello,
i am very new at Oracle licensing issues and i am facing a situation with an Oracle SE2 Licence NUP.
i am very confused how to count the number of NUP's i will have to purchase.
is a NUP an explicit Oracle user i have created? what about system Oracle users ?
i have to deal with a web/backend application using an Oracle user to connect to an Oracle instance.
but this application is in fact creating parallel processes each connecting to Oracle with this user.
how to count the needed NUP's in this case. is NUP licensing the right way to go ?
thanks in advance for your help.
regards.
Laure
How to Extract the Tag value from xml which as tag name with colon.
<code>
<link>https://www.rba.gov.au/statistics/frequency/exchange-rates.html#JPY</link>
<description>AUD/JPY representative rate as at 4.00 pm Eastern Australian time on 14 Nov 2023</description>
<dc:date>2023-11-14T17:52:00+11:00</dc:date>
<dc:language>en</dc:language>
<dcterms:audience>general</dcterms:audience>
<cb:statistics rdf:parseType="Resource">
<rdf:type rdf:resource="http://www.cbwiki.net/wiki/index.php/RSS-CB_1.2_RDF_Schema#Exchange-Rates"/>
<cb:country>AU</cb:country>
<cb:institutionAbbrev>RBA</cb:institutionAbbrev>
<cb:exchangeRate rdf:parseType="Resource">
<rdf:type rdf:resource="http://www.cbwiki.net/wiki/index.php/RSS-CB_1.2_RDF_Schema#Exchange-Rates"/>
<cb:observation rdf:parseType="Resource">
<rdf:type rdf:resource="http://www.cbwiki.net/wiki/index.php/RSS-CB_1.2_RDF_Schema#Exchange-Rates"/>
<cb:value>96.64</cb:value>
<cb:unit>AUD</cb:unit>
<cb:decimals>2</cb:decimals>
</cb:observation>
<cb:baseCurrency>AUD</cb:baseCurrency>
<cb:targetCurrency>JPY</cb:targetCurrency>
<cb:rateType>4.00 pm foreign exchange rates</cb:rateType>
<cb:observationPeriod rdf:parseType="Resource">
<rdf:type rdf:resource="http://www.cbwiki.net/wiki/index.php/RSS-CB_1.2_RDF_Schema#Exchange-Rates"/>
<cb:frequency>daily</cb:frequency>
<cb:period>2023-11-14</cb:period>
</cb:observationPeriod>
</cb:exchangeRate>
</cb:statistics>
</code>
Tried with by inserting the xml as clob object.
<code>SELECT
XMLTYPE(t.xclob).EXTRACT('/rdf:RDF/item/cb:statistics/cb:exchangeRate/cb:baseCurrency/text()').getStringVal()
FROM
test_clob t;
Getting dORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token in: ' '/rdf:RDF/item/cb:statistics/cb:exchangeRate/cb:baseCurrency/text()'
31011. 00000 - "XML parsing failed"
*Cause: XML parser returned an error while trying to parse the document.
*Action: Check if the document to be parsed is valid.</code>
Please suggest.
Hi Tom,
I have 5 tables in a query, Tab1, Tab2, Tab3, Tab4 and Tab5 and I have the following query below.
Select *
From Tab1, Tab2, Tab3, Tab4, Tab5
Where Tab1.x = Tab2.y(+)
and Tab1.x = Tab3.y
and Tab3.x = Tab4.y(+)
and Tab3.x = Tab5.y;
Question 1. Can a table who's outer joined (in this case Tab2, Tab4) be a Driving table?
Question 2. In the Join Order what happens to tables that are outer joined (in this case Tab2, Tab4). Do we only use tables that are inner joined in the Leading hint?
Regards,
Ravi
Last week, I decided to soft-launch a weekly newsletter to highlight three or four recent news stories in AI. When I started it, I had no idea this would be one of the biggest weeks in AI news all year. There was one giant story: OpenAI, makers of ChatGPT, fired their CEO, Sam Altman. And then it got messy.
You can read the rest of this week’s newsletter here.
Why a newsletter: I spend several hours a week reading about AI/machine learning. I get frustrated when I see overly-simple takes on AI in the media, on social media, etc. I figured that the best thing I could do about that would be to share what I’m learning every week.
What the newsletter is: A weekly email about some of the past week’s most interesting stories in AI, plus the occasional backgrounder.
What it’s not: Business-focused, highly technical, or comprehensive.
What you’ll get from AI Week:
- An interesting email about AI in your inbox every week.
- Background knowledge to contextualize all the breathless media stories about the powers, or dangers, of AI.
- A feeling for where the field is going, why it’s going there, and who’s powering it.
Where do I sign up? Right here:
Enter your email
Powered by Buttondown.
Hello Tom,
A colleague of mine is a big fan of service-oriented architectures and middle-ware. His view is that dblinks are 99.9% of the time a bad idea and that you should go via some sort of message layer in order to pass information between applications.
In many circumstances I can see the value of this, particularly in heterogeneous environments, however we are an all Oracle shop and I don't see the value in writing a windows service to pull data from one database and insert it into another.
What are your views on this?
I realise this is not really a plsql question but I would be interested to hear your thoughts on this.
Regards
Alastair
With PartyRock, you can build AI apps in seconds for free by using latest LLMs and without any code very easily.
Hi Tom!
ROWID works in a subselect:
<code>SELECT * FROM(SELECT rowid, d.* FROM DUAL d)</code>
and it also works with connect by:
<code>SELECT rowid, LEVEL FROM DUAL CONNECT BY LEVEL < 2</code>
But when used with both:
<code>SELECT * FROM(SELECT rowid, LEVEL FROM DUAL CONNECT BY LEVEL < 2)</code>
then it throws:
<code>ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.</code>
I've stumbled on this, since I used CONNECT BY LEVEL as a substitute for generate_series in a view, which was used in an APEX calendar, which had "select ROWID" ticket by default.
Hello T[o|ea]m,
Once, a long time ago, you gave an example of queuing a message asynchronously using PLSQL.
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8760267539329
I implemented this example myself and got it working.
(I think) I understand the global principle of this example, but I certainly do not understand all implementation-details.
These are my questions:
- I suppose you chose the name of the procedure '<b>notifyCB</b>' yourself, this is not a fixed or prescribed name, right?
- How did you choose the arguments for this procedure?
Are these fixed or prescribed?
- In the call <b>dbms_aq.register</b> this procedure (<b>notifyCB</b>) is specified, I do understand that and why.
But I do not understand the transfer of the arguments into the procedure <b>notifyCB</b> because a 'call' of <b>notifyCB</b> is not to be found. Is this why you used the word 'automagically'?
- English is not my native language (as you might have noticed).
I looked up in the Oracle References to find what a '<b>context</b>' is. I hate these Oracle-texts and I still have no clue.
Can you explain this in plain English?
Why do you give it the value <b>hextoraw('FF')</b>?
Is this '<b>context</b>' transfered to the <b>notifyCB</b>-argument '<b>context</b>'? (probably see previous question)
- Perhaps this question is already answered in (one of) the previous question(s):
Why does <b>notifyCB</b> have both an argument <b>payload</b> (raw) and <b>payloadl</b> (number)?
It looks like that <b>payloadl</b> is not used.
I hope I can come to the state that I can copy this example into my specific situation while fully understanding it.
Hi,
Please refer to the linked LiveSQL for the table, index, and data definition.
I execute a query that uses ROW_NUMBER() OVER (ORDER BY SOME_DATE DESC). When a partitioned index on SOME_DATE DESC is used, the result contains wrong row numbers. The order of rows is correct only inside each partition, but partitions are in the wrong order.
<code>
SELECT
ID,
TO_CHAR(SOME_DATE, 'YYYY-MM-DD HH24:MI:SS'),
ROW_NUMBER() OVER (ORDER BY SOME_DATE DESC) AS row_num
FROM
TEST_TABLE
WHERE
SOME_GROUP = 1
AND SOME_DATE < to_date('2023-11-08 15:00:00', 'YYYY-MM-DD HH24:MI:SS');
</code>
Gives me wrong ROW_NUM:
<code>
ID TO_CHAR(SOME_DATE,' ROW_NUM
---------- ------------------- ----------
3 2023-11-06 03:00:00 1
2 2023-11-06 02:00:00 2
1 2023-11-06 01:00:00 3
6 2023-11-07 03:00:00 4
5 2023-11-07 02:00:00 5
4 2023-11-07 01:00:00 6
10 2023-11-08 04:00:00 7
9 2023-11-08 03:00:00 8
8 2023-11-08 02:00:00 9
7 2023-11-08 01:00:00 10
</code>
If I force a full table scan instead of an index scan with /*+ FULL() */ hint, the result is correct:
<code>
SELECT /*+ FULL(TEST_TABLE) */
ID,
TO_CHAR(SOME_DATE, 'YYYY-MM-DD HH24:MI:SS'),
ROW_NUMBER() OVER (ORDER BY SOME_DATE DESC) AS row_num
FROM
TEST_TABLE
WHERE
SOME_GROUP = 1
AND SOME_DATE < to_date('2023-11-08 15:00:00', 'YYYY-MM-DD HH24:MI:SS');
</code>
Gives me:
<code>
ID TO_CHAR(SOME_DATE,' ROW_NUM
---------- ------------------- ----------
10 2023-11-08 04:00:00 1
9 2023-11-08 03:00:00 2
8 2023-11-08 02:00:00 3
7 2023-11-08 01:00:00 4
6 2023-11-07 03:00:00 5
5 2023-11-07 02:00:00 6
4 2023-11-07 01:00:00 7
3 2023-11-06 03:00:00 8
2 2023-11-06 02:00:00 9
1 2023-11-06 01:00:00 10
</code>
Dear Tom,
I am trying to tune a query (12.1.0.1 version, 4 node RAC).
The database appears to use DBMS_RLS policies.
I see one context defined as:
<code>CREATE OR REPLACE CONTEXT CRAMERSESSION
USING CRAMER.PKGSESSIONCONTEXT
/</code>
CRAMER.PKGSESSIONCONTEXT code is obfuscated.
In the explain plan, I see this:
<code>74 TABLE ACCESS FULL TABLE CRAMER.SERVICEOBJECT Object Instance: 90 Filter Predicates: "SERVICEOBJECT2DIMOBJECT"=3 AND "RPPLANID"=TO_NUMBER(NVL(SYS_CONTEXT('CRAMERSESSION','PLANCONTEXT'),'0')) Cost: 124,425 Bytes: 2,898 Cardinality: 138 Time: 5</code>
"RPPLANID"=TO_NUMBER(NVL(SYS_CONTEXT('CRAMERSESSION','PLANCONTEXT'),'0')) -- this repeats throughout the explain plan.
This evaluates to 0:
<code>select TO_NUMBER(NVL(SYS_CONTEXT('CRAMERSESSION','PLANCONTEXT'),'0')) from dual;</code>
is there any way to index this expression?
"RPPLANID"=TO_NUMBER(NVL(SYS_CONTEXT('CRAMERSESSION','PLANCONTEXT'),'0'))
Thank you.
Hello,
We are using EBR for deployment of DB objects. If any objects released in EBR in different editions need your help where to find the different versions(source code) of database objects (functions,procedure,package) stored in database as we are using EBR.
Is there any table maintained for storing different versions of same object.
Regards
GirishR
Hi TOM,
We are planning on upgrading from Apex 22.1 to Apex 23.1. We are currently on Oracle 19c using Apex 22.2:
SQL> <code>SELECT * FROM v$version;</code>
BANNER
--------------------------------------------------------------------------------
BANNER_FULL
----------------------------------------------------------------------------------------------------
BANNER_LEGACY CON_ID
-------------------------------------------------------------------------------- ---------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 0
1 row selected.
SQL> <code>SELECT * FROM apex_release;</code>
VERSION_NO
----------------------------------------------------------------------------------------------------
API_COMPATIBILITY
----------------------------------------------------------------------------------------------------
PATCH_APPLIED
----------------------------------------------------------------------------------------------------
22.2.1
2022.10.07
APPLIED
1 row selected.
From what I read here:
https://docs.oracle.com/en/database/oracle/apex/23.1/htmig/apex-installation-requirements.html#GUID-02BE4A34-B631-412C-8A82-EB92DABBACE0
it states "Oracle APEX release 23.1 requires an Oracle Database release 19c or later". Based on the above query results and this statement, it would seem we are OK to upgrade, at least with regards to minimum Oracle Database release.
I have a question, though. Our current compatible parameter is set to 12.1:
SQL> <code>SELECT name, type, value FROM v$parameter WHERE name = 'compatible';</code>
NAME TYPE
-------------------------------------------------------------------------------- ---------------
VALUE
----------------------------------------------------------------------------------------------------
compatible 2
12.1.0.2.0
1 row selected.
Despite being on the minimum required Oracle DB release, would it be correct to assume we need to increase our compatible parameter setting? I'm guessing yes. If so, what is the minimum we can/should set it to? I'm guessing no less than 19.0.0, but please confirm, especially since I'm wondering if a compatible parameter of 12.2 would work.
Thanks!
SQL*PLUS-kinda'-guy
Question:
I am building a freelancing local website with Oracle Apex. I have all of my data in Excel spreadsheets, but I am not sure how to import them into Apex. I have multiple spreadsheets, one for each table in my database (e.g., users, freelancers, clients, jobs, proposals, contracts, etc.). The spreadsheets also contain foreign key relationships between the tables.
What is the best way to import these spreadsheets into Apex? Is there a way to automatically create the tables and relationships from the Excel sheets? Are there any best practices or common pitfalls to be aware of when doing this?
you can check my application sample tables here: [redacted]
Thank you for your help.
Sincerely,
Pages
|
