Oracle View, Synonym and Sequence

 Views:

1. A view is a database object that is of a stored query. A view can be accessed as a virtual table.it doesn’t store data in a physically.
2. It is a logical table that represents data of one or more underlying tables through a SELECT statement.

Advantages:

Users can interact with views without needing to know the details of the base tables.
Views can restrict access to specific columns or rows, enhancing security.
It is to reduce the complexity of a query which is made by joining various tables.

Needed:

grant create view to user_name;
grant select on table_name to user_name;

Simple View:

A simple view is based on a single table and contains only a select statement. It does not include GROUP BY, HAVING, START WITH, or CONNECT BY clauses.
CREATE VIEW employee_view AS SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10;

Complex View:

A complex view can be based on multiple tables or another view. It can include GROUP BY, HAVING, START WITH, or CONNECT BY clauses.
CREATE VIEW sales_summary_view AS SELECT product_name, SUM(quantity * price) AS total_sales FROM sales JOIN products ON sales.product_id = products.product_id GROUP BY product_name;

force View:

view is created without base table.
create force view view_name as select id,name from table_name;

How to list the oracle views:

select view_name,text from user_views;(user_views is system table)
select * from user_views;
select * from all_views;
select * from dba_views;

Synonym:

A synonym is an alias for a table, view, sequence, procedure, or other schema object.
It provides an alternative name that you can use to reference the original object.

Needed privileges:

grant create synonym to user_name;
grant create public synonym to user_name; //create public synonym
grant drop public synonym to user_name;
grant create any synonym to user_name; // create synonym for other user object
grant drop any synonym to user_name;

find synonym privileges:

select * from session_privs where privilege like '%SYNONYM';

Create the SYNONYM:

create synonym synonym_name  for table_name;

give privilege for user:

grant select, insert on synonym_name to user_name;

How to list the synonyms:

select * from dba_synonyms;
select * from all_synonyms;
select * from user_synonyms;

Sequence:

sequence is a database object used to generate unique numeric values in a sequential order. Sequences are often used to provide unique identifiers for primary key columns in database tables.

Needed privilege:

grant create sequence to user_name;

find privilege:

select * from session_privs where privilege like '%SEQUENCE';

Syntax:

CREATE SEQUENCE schema_name.sequence_name
[INCREMENT BY interval]
[START WITH first_number]
[MAXVALUE max_value | NOMAXVALUE]
[MINVALUE min_value | NOMINVALUE]
[CYCLE | NOCYCLE]

Example:

CREATE SEQUENCE seq_name
INCREMENT BY 1
START WITH 1
MINVALUE 1
MAXVALUE 500
CYCLE
CACHE 5

How to use:

insert into table_name(seq_name.nextval,’xxxxxx’);
insert into table_name(seq_name.currval,’xxxxxx’);

Alter the SEQUENCE:

ALTER SEQUENCE seq_name
INCREMENT BY 2
MINVALUE 1
MAXVALUE 1000000
NOCYCLE;

List of sequences:

select * from dba_sequences;
select * from all_sequences;
select * from user_sequences;