Posts Tagged ‘Learning Oracle’

Oracle Jobs

If you are want to schedule a job in the Oracle Database to execute a certain task regularly, basically you can use one of these methods:

1. Oracle Scheduler (DBMS_SCHEDULER):

DBMS_SCHEDULER is a powerful feature that you can use to schedule jobs on the database, which can be very useful for certain DBA tasks like indexes rebuilding, backups checking or data consistency.

To view the information about currently running jobs, you can execute this query:


With this query you can check all the scheduled jobs into the database:


It is a very strong feature and very recommended currently. If you want to find more information about the scheduler, check the Oracle documentation in this link:

2. Oracle Jobs (DBMS_JOB):

DBMS_JOB is a deprecated feature but you can still use it even in Oracle 12c, even if it started in Oracle 7!

The DBMS JOB feature is a lot less intuitive to use, although some DBAs state that it is more flexible. In any case, either if you want to take advantage of its flexibility or if you need to understand how it works because your database already uses this feature, it is another option.
You can find the Oracle documentation here:

And a very good and practical post explaining how to use DBMS_JOB:
3. Crontab (Unix/Linux) / Windows Task Scheduler

The third option is to externalize the task execution to the Operating System, so that the tasks are executed through a .sh in Unix, or a .bat in Windows, executing later a .sql to the database.

This method is easier to manage if you are already using crontab or Task Scheduler for other tasks not related with the database, but the disadvantage is that it is node dependent so it can be a problem in the case of Oracle RAC.
Do you know any other method to schedule jobs in Oracle? What is your prefered one?

Removing an Oracle Instance in silent mode

February 14, 2011 3 comments

This post will be simple but really efective. If you want to delete an instance in silent mode, without using any GUI, you just have to write the following as oracle user:


dbca -silent -deleteDatabase -sourceDB YOUR_DATABASE


I hope you will find this command as useful as it is for me ūüôā

Categories: linux, oracle Tags: ,

What is the data dictionary?

The scope of this article is to clarify what exactly is the data dictionary. Probably all of you have heard of it, and you know it’s a bunch of metadata that Oracle uses to work, but if you want to know more about this Data Dictionary, then I hope this article teaches you something new.

The data dictionary is a repository of metadata (Information about information), about all the information inside the database. This repository is owned by SYS, and is stored principally in the SYSTEM tablespace, though some components are stored in the SYSAUX tablespace (in Oracle 10+).

The data dictionary is composed of tables and views. Some of these tables are inside of the Oracle kernel, so you would never work directly with them unless you are working for Oracle support or performing a disaster recovery scenario. But instead you can access to the views in order to know the “information about the information”.

For example, a possible usage of this data dictionary would be to know all the tables owned by a single user, or the list or relationships between all the tables of the database.

The main view of the data dictionary is the view DICT (or DICTIONARY):

Nombre                                    ?Nulo?   Tipo
—————————————– ——– —————————-
TABLE_NAME                                         VARCHAR2(30)
COMMENTS                                           VARCHAR2(4000)

Through the DICT view, you can access to all the data dictionary views that could provide you the information that you need.

For example, if you are looking for information related to db_links, but you don’t know where to look for, then query the DICT view:



Now, you have just to query one of these views to find the data you are looking for. GV$ views are very useful when you are working with RAC, and V$ views are instance related.

Remember that the data dictionary provides critical information of the database, and it should be restricted to users. However, if a user really needs to query the data dictionary, you can use the following sentence:


What is a Schema in Oracle?

It may sound like an easy question, but the answer isn’t always clear to everyone, so I intend to clarify it in this post.

Most users think that, when they are developing an application, what they need is a database. Wrong. What they are looking for is a schema (or maybe a set of schemas), but almost no database needs a schema for itself.

A schema is a collection of database objets, owned by a single database user. The name of the schema is the same as the name of the user. The objets in this schemas can be in different tablespaces, because there is no direct relationship between a tablespace and a schema.

On the other hand, we can have a user that doesn’t owns a schema, but has grants to work with it. That’s a clear example of the differences between a schema and an user: a schema is always owned by an user, but an user doesn’t have to own a schema.

Or better said: a user that doesn’t owns a schema, what really owns is an empty schema. Because he can eventually have some object owned by him, so the schema is still there.

However, remember that a schema is a set of database objects, so… ¬Ņa null collection of objects is still considered a collection of objects? That depends on the point of view, but anyway, an empty schema doesn’t have any interest for us.

I hope that this post has made a clearer perspective of what a schema is, and to know the differences between a schema and a database.