Oracle Learning Notes (iv) _oracle

First, control user access
1, create modify user creating users
Create/alter user new_user identified by password;
Example: Create user user_1 indentified by pwd_1
Alter user user_1 identified by pwd_2
2, give the user the right to grant
Grant Privilege[,privilege] to user [, user|role,public ...]
Some system privileges:
Create Session/table/sequence/view/procedure

Alter/delete/execute/index/insert/references/select/update
Grant Object_priv [(columns)]
On object
to {user|role|public}
[WITH GRANT OPTION]
For example:
Grant user user_1 permission to query TT1 table
Grant Select on TT1 table to user_1
Grant user user_1 permission to modify table 1 (column 1, column 2)
Grant Update (column 1, column 2) on table 1 to user_1;
Grant the user user_1 permissions to the query and grant permissions to other users
Grant Select
On TT1 table
To user_1
With GRANT option
Grant permissions to all users
Grant Select
On table 1
to public;

Give User user_1 authorization
Grant create session to user_1;

creating roles and delegating to roles
1. Create roles
Create role Mangager;
For example: Create role test_role1;
2, to the role of authorization
Grant create table,create view to manager;
For example: Grant CREATE table to Test_role1;

3. Give the role to the user
Grant Manager to User_1,user_2 ...
Grant Test_user1 to user_1;

third, the cancellation of user rights
Revoke {privilege [, privilege...]| All
On object
from {user[,user...]| Role|public}
[Cascade constraints];

Revoke select on table 1 from user_1;

IV. Database Links
Create public database link hq.acme.com using ' Sales ';
SELECT * from emp@hq.acme.com;

Oracle Fetch and set, intersection, difference set
The data type of the column you are fetching must be compatible
1, take and set
Union: Data is sorted, duplicate records are compressed, union ALL does not
Select employee_id,job_id from Employess
Union
Select employee_id,job_id from Job_history;
Take all the sets and do not remove duplicate data
Select employee_id,job_id from Employess
UNION ALL
Select employee_id,job_id from Job_history;

2, take the intersection
Select employee_id,job_id from Employess
Intersect
Select employee_id,job_id from Job_history;

3, Difference set
Table employess Remove shared data
Select employee_id,job_id from Employess
Minus
Select employee_id,job_id from Job_history;

Six, date Time function
Ask for time difference
Select Tz_offset (' Us/eastern ') from dual;

Alter session set Time_zone= ' -8:0 ';
Select Sessiontimezone,current_date from dual;
Alter session set Time_zone= ' -8:0 ';
Select Sessiontimezone,current_timestamp from dual;

Alter session set Time_zone= ' -8:0 ';
Select Current_timestamp,localtimestamp from dual;

Select Dbtimezone,sessiontimezone from dual;
Select From_tz (timestamp ' 2000-03-23 08:00:00 ', ' 3:00 ') from dual;

Select To_timestamp (' 2000-02-01 11:00:00 ', ' yyyy-mm-dd HH:MI:SS ') from dual;

Select To_timestamp_tz (' 2000-02-01 11:00:00 ', ' yyyy-mm-dd HH:MI:SS tzh:tzm ') from dual;
To_ymininterval ()