SQL language for Oracle DBA

SQL language

SQL is a standard non-procedural language for relational database management systems (RDBMS)

Examples of SQL queries:

    SELECT ename FROM emp;
    SELECT last_name, department_id FROM employees;
    DELETE FROM department WHERE department_id IN (10, 30, 70);
    CREATE TABLE articles (article_id VARCHAR2 (15), designation VARCHAR2 (40));
    INSERT INTO articles VALUES (‘imp1’, laser printer ’);
    DROP TABLE articles;
    GRANT SELECT ON department TO scott;
    REVOKE DELETE ON department FROM scott;

The terms SELECT, FROM, DELETE, WHERE, IN, CREATE, TABLE, VARCHAR2, INSERT, INTO, VALUES, DROP, GRANT, TO, REVOKE, ON are words reserved for SQL

Oracle SQL statements are classified into categories:

  DML commands for Data Manipulation Language (SELECT, INSERT, UPDATE, DELETE)
    DDL commands for Data Definition Language (CREATE, ALTER, DROP, RENAME, TRUNCATE, GRANT, REVOKE…).
        DDL commands are automatically validated: they generate an implicit commit.
    Control Commands of:
        Transactions (COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION)
        Sessions (ALTER SESSION)
        and System (ALTER SYSTEM)

SQL Recursive commands:

When an DDL command is issued, Oracle automatically issues an LMD command to modify the corresponding information in the data dictionary. This last command is called a recursive SQL command.

An DDL "CREATE TABLE" command, for example, issued by a user to create a table in their own schema, automatically generates another recursive LMD command (INSERT) in the SYS schema: data dictionary tables. In fact, the information on the structure of the newly created table must be inserted and available in the data dictionary.


Stay connected with ORAGEEK for advance Oracle DBA Tips with Dbametrix.

What is Oracle Data Dictionary?

Oracle data dictionary

In a database, all of the objects that belong to a user form what is called a user schema.

The main types of objects are tables, indexes, views, synonyms, sequences, program units stored in the database (procedures, functions, packages, triggers).

Only tables and indexes correspond to segments of data. Other objects only have a definition stored in the data dictionary.

A user schema is created and managed by it, while the data dictionary is created and managed by Oracle.

The data dictionary is a repository which belongs to the SYS super user and which stores the tables and views necessary for the proper functioning of the Oracle database. The DBA_TABLES view and the corresponding synonym USER_TABLES is an example of a table and dictionary view.

For example, if a user USER1 wants to display the structure of a TABLE1 table that he owns, he issues the "DESC TABLE1" command. The result of this command is obtained from the data dictionary.

This is because the TABLE1 table stores data and is owned by user USER1, while the structure of TABLE1 is stored elsewhere, in dictionary tables that are owned by user SYS. This information about the data forming the structure of TABLE1 is called metadata.

Dictionary tables are not directly accessible but dictionary views allow you to query them. In a session of the user USER1, USER_TABLES (synonymous with the DBA_TABLES view) is used to list the structure of all the tables of USER1.

In summary, the data dictionary stores metadata on tables, columns of tables, constraints, indexes, etc.

It can also store:

    user objects such as views, synonyms, sequences, procedures, functions, packages, triggers, etc.
    the names of the users, their privileges and roles… etc.

You can get advance level oracle DBA Tutorials from this source and read some articles from here

Availability of Job in Oracle DBA

Availability of Job in Oracle DBA

Now a day there is no existent of oracle DBA jobs in the market. Still, there are two ways to enter into this area along with achieving success in succeeding steps. There are different gateways for getting a job in DBA. One could expose to the oracle by learning it so that one could get a large amount of cash. A huge number of people are intending to become a DBA, but unfortunately due to the slow market of this sector, they have to get failure. 

But among all of them the person who gets the job in DBA, really the person has strong luck with him or her. But still, the person is not in a safe zone to be in the sector. Due to a huge number of fluctuations, there is a major possibility to lose the job. There is no guaranty of the person to stay permanently employed. The most experienced persons also face the same problem. Plenty of people have to be harassed and returned to their homes after being unemployed. One has to give a continuous flow of good performance to stay employed as a DBA.

Due to these reasons, government workers are advised to find people to make them eligible to become oracle DBA. For this, the government workers are paid well from the taxes that the people have paid. In this way, people are getting excited to get a job in their dreamed sector with secure and safe employment. 

The students who are getting the certificates from a community college situated in their locality also could get the job by their talent. Hence they have the real opportunity to get the job without having any previous experience in this sector. But still, they have to work too hard to stay permanently as a DBA. Oracle DBA Blog always helps to improve your knowledge base to get job very easily.