In a post a couple days ago, I promised to provide a best practice approach to reading external files with the
UTL_FILE package. My first assumption is that you’re reading unstructured data because structured data is best read by external tables because external tables can read data much faster with the
My second assumption is that you’re you don’t know how to use or choose not to use the
DBMS_LOB package; specifically, (more...)
Sometimes I’m surprised. Today, the surprise came when somebody pointed to an error in another author’s book. The person who asked the question had to send me a screen shot before I believed it.
The author’s code encounters the following error because the code was designed to loop through a multiple line file, and the code called the
UTL_FILE.FOPEN procedure with three instead of four parameters:
In my current project I need to query an MS SqlServer database.
Unfortunately the dates are stored as a BigInt instead of a proper date datatype.
So I had to find out how to do compare the dates with the systemdate, and how to get the system date. To log this for possible later use, as an exception, a blog about SqlServer.
To get the system date, you can do:
(SELECT dt=GETDATE()) a
It's maybe (more...)
Description The Average Active Sessions (AAS) metric is a very good indicator of the database activity. This metric represents the number of sessions, either working or waiting for a resource at a specific point in time. Idle sessions are not included in the calculation of this metric. To calculate AAS, we need another metric called [...]
The post Average Active Sessions (AAS) appeared first on Oracle DBA Scripts and Articles (Montreal).
Being a huge fan of Logger, the PL/SQL logging utility, I really wanted this be to included in the project that I'm currently working on. So I downloaded it (link at the bottom of this blog) and included it in our deployment scripts. Done.... at least I thought so, but of course this wasn't the case.
The regular install script for Logger looks something like the following (parts removed and table names are changed):
Description This query will show you repartition percentage between I/O, WAITS and CPU from the v$active_session_history view Be careful, this view is part of the diagnostic pack, you should not query this view if you don’t have license for it. Instance activity repartition [crayon-533ee1b43b0b3323059005/] This query returns percentage of I/O, CPU and waits consumed by [...]
The post Oracle instance activity repartition appeared first on Oracle DBA Scripts and Articles.
There is a ‘rule’, I think it was created by Tom Kyte, stating: If you can do it in SQL, do it in SQL. I came across some code the other day that makes perfect sense to do then you are running an Oracle 10g (or earlier) instance. I rewrote the code to use only the EMP and DEPT tables to protect the suspects and maybe innocent.
The function defined is something like this:
It’s possible to get an error after granting privileges to an external file system. One of those errors is tedious to resolve until you understand the rules governing Java NIO file permissions.
You grant privileges to external file systems as the
sys user with the
grant_permission procedure of the
dbms_java package, like
5 ,'read' (more...)
Description This query return the top 10 queries by resource consumption (CPU+IO+WAIT) in the last hour from v$active_session_history. Be careful, this view is part of the diagnostic pack, you should not query this view if you don’t have license for it. top 10 queries from v$active_session_history [crayon-533b91631cebb871999454/] This query return top queries by resources consumed, [...]
The post Top 10 queries from v$active_session_history appeared first on Oracle DBA Scripts and Articles.
Years ago I wrote an article (in Dutch) on the XML functions in Oracle SQL. It can be found here
It describes how to create an xml document as an XMLType with an Oracle SQL Query.
The query that is described is based on a pretty simple table, with no relationships. I'm creating a new course based on a datamodel we created years ago, that contains data. I wanted to abstract some of that data (more...)
Here is an example of surprising behaviour from a remote DB from an OTN forum thread
Setup a link to a remote DB (I’ve used an actual remote DB and not tested a loopback)
create table t1
create or replace view v1
select count(*) c1 from t1@l1;
Then alternate variations on this sequence of events:
1. On local DB execute SELECT:
SELECT * FROM v1;
2. On remote (more...)
For a project we are currently working on, we needed to generate, and send a Word 2010 document to the client. The document was generated by a great PL/SQL document generation tool called Doxxy, and was send to the client using the wpg_docload package. This is a standard Oracle pl/sql package that can be used to download files, BLOBs and BFILEs.
Before the download, we set the Content-type in the http header as follows :
A while ago I tried to install Oracle 11g R2 Express Edition on a 64-bit Ubuntu machine. This proved to be not as easy as you would expect. There are many blogs and articles about this subject and I tried a number of them. Unfortunately neither of the instructions seemed to work completely on my machine. With the combined information from the authors, I finally got it to work and I’ll gladly share my recipe (more...)
Description This query returns the archivelog sequence containing a scn number. This could be usefull in case you want to manually recover a standby database until a scn or just want to know which arvhivelog you should copy to recover the standby. Archivelog containing your SCN [crayon-533b9163d0b27884196414/]
The post which archivelog sequence contains your SCN number ? appeared first on Oracle DBA Scripts and Articles.
Somebody thought it was nice to show how to drop object type dependents in Oracle Database 11g, but they thought I should show how you discover dependent object types first. More or less, they were concerned how they discover type dependents when they raise the following error:
DROP TYPE item_object
ERROR at line 1:
ORA-02303: cannot DROP OR REPLACE a TYPE WITH TYPE OR TABLE dependents
They had a (more...)
During my time off I noted a bunch of information to dig into later, and I thought I'd make some notes as I go - see if you missed any interesting news.
I'll do a separate post for noteable blog posts.
Oracle REST Data Services
As Dimitri announced here
, Oracle has another product "formerly known as" - this time it's the APEX Listener.
Now known by what I think is a name that may also (more...)
Description This query generate the data needed to build the load graph from v$active session_history, the output represent what sessions did in your database for the last hour, be careful, the v$active_session_history is part of the diagnostic pack, if you don’t have a license for this pack you should not run this query. Query to [...]
The post Graph from v$active_session_history appeared first on Oracle DBA Scripts and Articles.
firstname.lastname@example.org > select banner from v$version;
Oracle Database 12c Enterprise Edition Release 18.104.22.168.0 - 64bit Production
PL/SQL Release 22.214.171.124.0 - Production
CORE 126.96.36.199.0 Production
TNS for Linux: Version 188.8.131.52.0 - Production
NLSRTL Version 184.108.40.206.0 - Production
email@example.com > select value from nls_database_parameters where parameter='NLS_CHARACTERSET';
firstname.lastname@example.org > ! (more...)
Chris Saxon posted a nice quiz regarding a pitfall when creating tables with VARCHAR2-columns: when you are not explicit in specifying the length-semantics ( CHAR or BYTE ), a session parameter, which may vary, is used.
This is a short follow-up of his story, which shows more pitfalls when creating views and selecting from those.
We end up in a structure which can – by definition – contain only 1 byte, but actually shows containing (more...)