I’ve been using a query which utilizes both DBA_FREE_SPACE
and DBA_DATA_FILES
system views, but it often tends to be slow. Recently I’d come across another system view which is lightning fast – DBA_TABLESPACE_USAGE_METRICS
. Czytaj dalej
Archiwa kategorii: Oracle
MERGE Alias Bug – No Error Thrown When Using Wrong Alias
2015-05-01Alas
Back in the days, when I was just beginning working as an Oracle developer, I used to view Oracle Database Server as such a great piece of software to the extent where I wouldn’t even consider that a problem which I’ve just encountered wasn’t my fault, but Oracle’s bug!
Why am I bringing this up, you might ask? Well, a few weeks ago I’ve stumbled upon a weird bug in Oracle. I was using Oracle 11.2.0.1.0
. Take a look at the below example: Czytaj dalej
Searching in the source of a view
2015-02-08Let's take a view at it
Once upon a time… I needed to check in the source of which objects a particular character string was included.
LONG
(the pun!…) story short, non of the {USER|ALL|DBA}_SOURCE
views give you a chance to search in the source of the views.
“Well, why is that a problem?!” – I hear you saying – “Don’t you know the {USER|ALL|DBA}_VIEWS
views views (wait, one views too many)?”
O my, but of course! There’s a column named TEXT
in them with the source of the views! Let’s try that: Czytaj dalej
How to Get Referential Constraints Using DBMS_METADATA
2014-09-27The order matters
Getting Referential Constraints using DBMS_METADATA
The GET_DDL
function of the DBMS_METADATA
package, supplied by Oracle, is a nice tool to extract the DDLs of database objects. This quick guide will show you how to deal with the problem of exporting referential constraints – what problem, I hear you saying? Czytaj dalej
Answer to Quiz #2: NULL in Aggregate Functions
2014-09-04Mind the NULL!
1 Quiz #2 – NULL in Aggregate Functions
Hello again! It’s due time to provide the answers for my second quiz – the one about NULL
in aggregate functions.
In this particular quiz, it wasn’t the point to pick the correct choices, but rather figure out what will be the result of running each of the SELECTs. Czytaj dalej
Quiz #2: NULL in Aggregate Functions
2014-07-19Impact of NULL in Aggreaget Functions
Aww. So dusty in here. Where have I been for the past seven months?!
Either way, it’s that time. Yep, you know what I mean – it’s the QUIZ TIME!
I’ve got a new quiz for you today:
What will be the result of running each of the SELECTs? Czytaj dalej
Answer to Quiz #1: Package Dependency
2013-12-09Packages and Dependent Objects
In my last post, I’ve challenged you with a quiz about package dependency. The question was about when a dependent object will be invalidated by Oracle when the package on which it depends is recreated.
The choices which give the “INVALID
” status are:
Czytaj dalej
Quiz #1: Package Dependency
2013-12-06Packages and Dependent Objects
I’ve got the following quiz for you today:
After running which of the following code snippets will the ‘INVALID’ status be returned?
Assumptions:
- Version of Oracle: 11g R2.
- Before each snippet is run, both
dummy_pkg
andprint_message
objects do not exist.
I’ll provide the answer in my next post.
The choices:
Czytaj dalej
Text Index With a Sync Parameter
2013-12-03Subtitle is on holidays.
Oracle Text Index with SYNC (EVERY …) Parameter
Just a quick tip for today. Oracle allows us to create special kind of indexes on columns containing long texts. One of them is the CONTEXT
type. You don’t need any particular privileges to create an index of this type (other than being able to create indexes in you schema). At least, that’s what the documentation says.
There was a question about creating a CONTEXT
index on StackOverflow:
Returning BLOB From Embedded Java
2013-11-22- Waiter! There's a BLOB in my Java!
Returning a BLOB from a Java Method Embedded in a Database
Oracle supports embedding Java classes in its database. Different SQL types are mapped to corresponding Java classes to allow us to make the most of this feature. Author of the following question on StackOverflow:
create-java-sql-blob-instance-in-java-stored-procedure
had an issue with returning a BLOB
object from Java method back to PL/SQL context. In the beginning, I didn’t even think you could return a new BLOB
object from an embedded class. Fortunately, there was a BLOB
in my Java, too. Czytaj dalej