oracle sql and dba tutorial logo

Tutorial for Oracle DBA


Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions

42 Backup & Recovery Questions

Unix For Oracle DBA 20 Questions


 

Oracle SQL Tutorial Contents

Introduction to Databases

CODD'S Rules

Datatypes and Create Table

Oracle SELECT Statement

Formatting in SQL*Plus

UNION, INTERSECT, MINUS Operators and Sorting Query Result

 

Oracle ALTER TABLE ADD COLUMN

Oracle ALTER TABLE MODIFY COLUMN

Oracle SQL Functions

Oracle NVL() Function with Examples

Oracle SUBSTR() function with Examples

Oracle TO_DATE() with complete examples

Oracle DECODE function

Oracle INSTR() function with examples

Oracle TO_CHAR() function

Oracle TO_TIMESTAMP

Number Functions (Math Functions)

Character Functions

Miscellaneous Functions

Aggregate Functions

Date and Time Functions

Oracle Join Queries

GROUP BY Queries, SUB Queries

CUBE, ROLLUP Functions

Oracle DML (INSERT, UPDATE, DELETE...)

Oracle DDL (CREATE, ALTER, DROP...)

COMMIT, ROLLBACK,SAVEPOINT

Data Control Language (GRANT, REVOKE)

 

 

Integrity Constraints (PRIMARY KEY, NOT NULL...)

DEFAULT Values

Dropping Constraints

Disabling and Enabling

Differing Constraints Check

View Info about Constraints

Working with Dates

Oracle Views

Oracle Sequences

Oracle Synonyms

Indexes and Clusters

Table Partitioning

Altering Partition Tables

Dropping Partitions

Merging Partitions

Splitting Partitions

Coalescing Partitions

Oracle Objects and Object Types

 

 

 

 

 

 

 

 

 

Using Synonyms in Oracle

SYNONYMS

A synonym is an alias for a table, view, snapshot, sequence, procedure, function, or package.

There are two types to SYNONYMS they are

            PUBLIC SYNONYM
           PRIVATE SYNONYM

If you a create a synonym as public then it can be accessed by any other user with qualifying the synonym name i.e. the user doesn’t have to mention the owner name while accessing the synonym. Nevertheless the other user should have proper privilege to access the synonym. Private synonyms needs to be qualified with owner names.

 

CREATING SYNONYMS

To create a synonym for SCOTT emp table give the following command.

create synonym employee for scott.emp;

A synonym can be referenced in a DML statement the same way that the underlying object of the synonym can be referenced. For example, if a synonym named EMPLOYEE refers to a table or view, then the following statement is valid:

select * from employee;

Suppose you have created a function known as TODAY which returns the current date and time. Now you have granted execute permission on it to every other user of the database. Now these users can execute this function but when the call they have to give the following command:

select scott.today from dual;

Now if you create a public synonym on it then other users don’t have to qualify the function name with owner’s name. To define a public synonym give the following command.

create public synonym today for scott.today;

Now the other users can simply type the following command to access the function.

select today from dual;


Dropping Synonyms

To drop a synonym use the DROP SYNONYM statement. For example, to drop EMPLOYEE synonym give the statement

drop synonym employee;

Listing information about synonyms

To see synonyms information give the following statement.

select * from user_synonyms;

 

 

 


HomeContact Us

Data Loader

Data Loader is a simple yet powerful tool to
export and import Data between many common database formats


Forms Data Loader

Tool to load data into Oracle E-Business Suite R12 / Oracle Apps using Macros and Forms Record and Playback

Interface Computers Academy © 2007-2017 All Rights Reserved