The following is the cause of this error:
The values of a scalar datatype such as CHAR or REAL have a predefined order, which allows them to be compared.
But an object type, such as a some_type, which can have multiple attributes of various data types, has no predefined axis of comparison.
To be able to compare and order variables of an object type, you must specify a basis for comparing them.
Two special kinds of member methods can be defined for doing this: map methods and order methods.
The action you can take to resolve this issue: Define a MAP or ORDER method for the object type.
For more information, you can see https://docs.oracle.com/cd/B19306_01/appdev.102/b14260/adobjbas.htm#sthref211
Monday, 4 December 2017
Wednesday, 29 November 2017
ORA-02030 error when try to grant permissions to a v$ tables
Face with an error ORA-02030 when you try to give permission on a v$ view
SQL> grant select on v$session to hr;
grant select on v$session to hr
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
Why it couldn’t give select privileges to a v$ view. If you tried to give permission to other v$views you will face same the error.
The problem is caused because of trying to give select privileges on a synonym. Oracle v$ views are named V_$VIEWNAME and they have synonyms in format V$VIEWNAME and you can’t give privilage on a synonym.
If you want to give permission to a V$ view you must grant permissions like below:
SQL> grant select on v_$session to hr;
Grant succeeded.
SQL> grant select on v$session to hr;
grant select on v$session to hr
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
Why it couldn’t give select privileges to a v$ view. If you tried to give permission to other v$views you will face same the error.
The problem is caused because of trying to give select privileges on a synonym. Oracle v$ views are named V_$VIEWNAME and they have synonyms in format V$VIEWNAME and you can’t give privilage on a synonym.
If you want to give permission to a V$ view you must grant permissions like below:
SQL> grant select on v_$session to hr;
Grant succeeded.
Wednesday, 15 November 2017
RAISE_APPLICATION_ERROR
The RAISE_APPLICATION_ERROR is actually a procedure defined by Oracle that allows the developer to raise an exception and associate an error number and message with the procedure. This allows the application to raise application errors rather than just Oracle errors. Error numbers are defined between -20,000 and -20,999. All other numbers belong to Oracle for its own errors. The message can be anything that will fit in a varchar2(2000).
RAISE_APPLICATION_ERROR can be used in either (or both) the executable section and the exception section of a PL/SQL program. The error number and message is displayed to the user.
Syntax:
RAISE_APPLICATION_ERROR ({error_number}, {error_message});
{error_number} - The Error number must be between -20000 and -20999
{error_message} - The Error message is the message you want to display when the error occurs
Using in Execution section
BEGIN
...
RAISE_APPLICATION_ERROR (-20001,'This is a user error message');
...
END;
Using in Exception section:
BEGIN
...
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20003, 'This is another user error message');
END;
Examples:
Input:
DECLARE
l_number NUMBER;
BEGIN
SELECT 2 INTO l_number FROM DUAL;
dbms_output.put_line('l_number = '||l_number);
END;
Output:
l_number = 2
Input:
DECLARE
l_number NUMBER;
BEGIN
SELECT 2 INTO l_number FROM DUAL;
RAISE_APPLICATION_ERROR (-20001,'This is a user error message');
dbms_output.put_line('l_number = '||l_number);
END;
Output:
There is no output due to our ERROR
line 19: ORA-20001: This is a user error message
ORA-06512: at line 5
RAISE_APPLICATION_ERROR can be used in either (or both) the executable section and the exception section of a PL/SQL program. The error number and message is displayed to the user.
Syntax:
RAISE_APPLICATION_ERROR ({error_number}, {error_message});
{error_number} - The Error number must be between -20000 and -20999
{error_message} - The Error message is the message you want to display when the error occurs
Using in Execution section
BEGIN
...
RAISE_APPLICATION_ERROR (-20001,'This is a user error message');
...
END;
Using in Exception section:
BEGIN
...
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20003, 'This is another user error message');
END;
Examples:
Input:
DECLARE
l_number NUMBER;
BEGIN
SELECT 2 INTO l_number FROM DUAL;
dbms_output.put_line('l_number = '||l_number);
END;
Output:
l_number = 2
Input:
DECLARE
l_number NUMBER;
BEGIN
SELECT 2 INTO l_number FROM DUAL;
RAISE_APPLICATION_ERROR (-20001,'This is a user error message');
dbms_output.put_line('l_number = '||l_number);
END;
Output:
There is no output due to our ERROR
line 19: ORA-20001: This is a user error message
ORA-06512: at line 5
Wednesday, 30 August 2017
Oracle File extensions
SQL*Plus files
.sql - SQL script
.lst - spool file
PL/SQL files
.pls - PL/SQL source
.plb - PL/SQL binary
.pks - Package source or package specification
.pkb - Package binary or package body
.pck - Combined package specification plus body
Oracle database files
.dbf - database file
.log - Online Redo Log
.rdo - Online Redo Log
.arc - Archive log
SQL*Loader files
.ctl - Control file
.dat - Data file
.bad - Bad file
.dsc - Discard file
SQL*Net files
.ora - tnsnames.ora, sqlnet.ora, etc.
Forms files
.fmb - Forms binary
.fmt - Forms text
.fmx - Forms executable
Reports files
.rdf - contains a single report definition in binary format.
.rdf files are used to both run and edit reports.
.rep - contains a single report definition in binary format.
.rep files are used solely to run reports; you cannot edit a .rep file
.rex - contains a single report definition in text format. .REX files are portable.
Tuesday, 29 August 2017
Switching to a Different Schema
The following statement sets the schema of the current session to the schema name specified in the statement.
ALTER SESSION SET CURRENT_SCHEMA = <schema name>;
In subsequent SQL statements, Oracle Database uses this schema name as the schema qualifier when the qualifier is omitted. In addition, the database uses the temporary tablespace of the specified schema for sorts, joins, and storage of temporary database objects. The session retains its original privileges and does not acquire any extra privileges by the preceding ALTER SESSION statement.
You can use this command above to avoid the use of public synonyms. By setting the CURRENT_SCHEMA attribute to the schema owner name it is not necessary to create public synonyms for production table names. Without the current_schema syntax, you would have to assign a public synonym for every table in your production database.
If you want to check the current schema of the session you can use the following statement:
SELECT sys_context( 'userenv', 'current_schema' ) from dual;
Let's look in the following example:
CONNECT user1
ALTER SESSION SET CURRENT_SCHEMA = schema_2;
SELECT * FROM empoyers;
Because user1 is not schema-qualified, the table name is resolved under schema schema_2. But if user1 does not have select privilege on table schema_2.empoyers, then user1 cannot execute the SELECT statement even switching the current session schema.
Monday, 28 August 2017
Useful options that you should know about SQL Developer
1. Search Preferences
You can easily navigate to any of SQL Developer preferences simply start typing the name of it in the search box. DO NOT HIT ENTER, this will close the preferences dialog with hit “OK” button.
4. Hiding Database Object Types from your Connection Trees
You probably don’t work with EVERYTHING in Oracle – so set your trees to show just what you need. Less scrolling, less searching. More happy
5. Connection Script Startup
You can setup your connection startup script (LOGIN.SQL) under Database. The contents of the script will be executed every time when the database connection is established.
You can easily navigate to any of SQL Developer preferences simply start typing the name of it in the search box. DO NOT HIT ENTER, this will close the preferences dialog with hit “OK” button.
2. Database – Worksheet – Show Query Results in new tabs
Wouldn’t it be nice to keep around query results from different iterations of your query as you work through it? Enabling this feature will keep your query results open as you execute new queries. You can turn on/off with the feature with hit the red pin under ‘Query Result’. Mouse over the ‘Query Result’ labels to see the SQL statement used to populate that grid. Of course, the more result sets you leave open, the more memory SQL Developer will need. So be sure to close them when you’re finished.
3. Open Object on Single Click
Good for n00bs, probably annoying to experienced users.
You probably don’t work with EVERYTHING in Oracle – so set your trees to show just what you need. Less scrolling, less searching. More happy
You can setup your connection startup script (LOGIN.SQL) under Database. The contents of the script will be executed every time when the database connection is established.
Monday, 14 August 2017
ROWID vs. UROWID in Oracle DB
Each row in the database has an address. The following describe the two forms of row address in an Oracle Database - rowid and urowid.
ROWID Data Type
The rows in heap-organized tables that are native to Oracle Database have row addresses called rowids. You can examine a rowid row address by querying the pseudocolumn ROWID. Values of this pseudocolumn are strings representing the address of each row. These strings have the data type ROWID. You can also create tables and clusters that contain actual columns having the ROWID data type. Oracle Database does not guarantee that the values of such columns are valid rowids. Refer to Chapter 2, "Pseudocolumns" for more information on the ROWID pseudocolumn.
Rowids contain the following information:
ROWID Data Type
The rows in heap-organized tables that are native to Oracle Database have row addresses called rowids. You can examine a rowid row address by querying the pseudocolumn ROWID. Values of this pseudocolumn are strings representing the address of each row. These strings have the data type ROWID. You can also create tables and clusters that contain actual columns having the ROWID data type. Oracle Database does not guarantee that the values of such columns are valid rowids. Refer to Chapter 2, "Pseudocolumns" for more information on the ROWID pseudocolumn.
Rowids contain the following information:
Tuesday, 16 May 2017
PGA usage by sessions
Summary
Each user session contains its own PGA taken from the operating system free memory pool. As sessions have the need to use PGA memory, the Oracle database will allocate the session memory from the PGA until the total allocation of memory is equal to the pga_aggregate_target value set within the database. As sessions require additional memory from the PGA, Oracle will pull the memory from other sessions that no longer need the memory.
SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ')
|| TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE - LOGON_TIME, 1) * 86400), 'SSSSS'),'HH24:MI:SS') LOGON,
SID,
v$session.SERIAL#,
v$process.SPID,
ROUND(v$process.pga_used_mem / (1024 * 1024), 2) PGA_MB_USED,
v$session.USERNAME,
STATUS,
OSUSER,
MACHINE,
v$session.PROGRAM,
MODULE
FROM v$session, v$process
WHERE v$session.paddr = v$process.addr
--AND status = 'ACTIVE'
--AND module = 'ECR'
--AND v$session.sid = 452
--AND v$session.username = 'MR54'
--AND v$process.spid = 5698
ORDER BY pga_used_mem DESC;
To find the total PGA memory used by processes
SELECT ROUND(SUM(pga_used_mem) / (1024 * 1024), 2) PGA_USED_MB
FROM v$process;
To find PGA usage for a specific session
SELECT SID, b.NAME, ROUND(a.VALUE / (1024 * 1024), 2) MB
FROM v$sesstat a, v$statname b
WHERE (NAME LIKE '%session uga memory%' OR
NAME LIKE '%session pga memory%')
AND a.statistic# = b.statistic#
AND SID = 596;
To calculate the amount of memory that you gone need for PGA, estimate the number of maximum connected sessions and run:
SELECT &MAX_CONNECTED_SESSIONS * (2048576 + P1.VALUE + P2.VALUE) /
(1024 * 1024) YOU_NEED_PGA_MB
FROM V$PARAMETER P1, V$PARAMETER P2
WHERE P1.NAME = 'sort_area_size'
AND P2.NAME = 'hash_area_size';
V$PGASTAT provides PGA memory usage statistics as well as statistics about the automatic PGA memory manager when it is enabled (that is, when PGA_AGGREGATE_TARGET is set). Cumulative values in V$PGASTAT are accumulated since instance startup.
To query the contents of the current PGA settings within Oracle 11g, issue a request against the v$pgastat dynamic performance view as shown:
SELECT * FROM V$PGASTAT;
NAME VALUE UNIT
----------------------------------------------------------------
aggregate PGA target parameter 163577856 bytes
aggregate PGA auto target 80363520 bytes
global memory bound 32714752 bytes
total PGA inusen 74283008 bytes
total PGA allocated 156244992 bytes
maximum PGA allocated 240105472 bytes
total freeable PGA memory 9240576 bytes
process count 30
max processes count 41
PGA memory freed back to OS 273874944 bytes
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 7929856 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 270336 bytes
over allocation count 0
bytes processed 581040128 bytes
extra bytes read/written 0 bytes
cache hit percentage 100 percent
recompute count (total) 717
19 rows selected.
To change PGA memory parameter
ALTER SYSTEM SET pga_aggregate_target = 3500M SCOPE=BOTH;
Each user session contains its own PGA taken from the operating system free memory pool. As sessions have the need to use PGA memory, the Oracle database will allocate the session memory from the PGA until the total allocation of memory is equal to the pga_aggregate_target value set within the database. As sessions require additional memory from the PGA, Oracle will pull the memory from other sessions that no longer need the memory.
SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ')
|| TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE - LOGON_TIME, 1) * 86400), 'SSSSS'),'HH24:MI:SS') LOGON,
SID,
v$session.SERIAL#,
v$process.SPID,
ROUND(v$process.pga_used_mem / (1024 * 1024), 2) PGA_MB_USED,
v$session.USERNAME,
STATUS,
OSUSER,
MACHINE,
v$session.PROGRAM,
MODULE
FROM v$session, v$process
WHERE v$session.paddr = v$process.addr
--AND status = 'ACTIVE'
--AND module = 'ECR'
--AND v$session.sid = 452
--AND v$session.username = 'MR54'
--AND v$process.spid = 5698
ORDER BY pga_used_mem DESC;
To find the total PGA memory used by processes
SELECT ROUND(SUM(pga_used_mem) / (1024 * 1024), 2) PGA_USED_MB
FROM v$process;
SELECT SID, b.NAME, ROUND(a.VALUE / (1024 * 1024), 2) MB
FROM v$sesstat a, v$statname b
WHERE (NAME LIKE '%session uga memory%' OR
NAME LIKE '%session pga memory%')
AND a.statistic# = b.statistic#
AND SID = 596;
To calculate the amount of memory that you gone need for PGA, estimate the number of maximum connected sessions and run:
SELECT &MAX_CONNECTED_SESSIONS * (2048576 + P1.VALUE + P2.VALUE) /
(1024 * 1024) YOU_NEED_PGA_MB
FROM V$PARAMETER P1, V$PARAMETER P2
WHERE P1.NAME = 'sort_area_size'
AND P2.NAME = 'hash_area_size';
V$PGASTAT provides PGA memory usage statistics as well as statistics about the automatic PGA memory manager when it is enabled (that is, when PGA_AGGREGATE_TARGET is set). Cumulative values in V$PGASTAT are accumulated since instance startup.
To query the contents of the current PGA settings within Oracle 11g, issue a request against the v$pgastat dynamic performance view as shown:
SELECT * FROM V$PGASTAT;
NAME VALUE UNIT
----------------------------------------------------------------
aggregate PGA target parameter 163577856 bytes
aggregate PGA auto target 80363520 bytes
global memory bound 32714752 bytes
total PGA inusen 74283008 bytes
total PGA allocated 156244992 bytes
maximum PGA allocated 240105472 bytes
total freeable PGA memory 9240576 bytes
process count 30
max processes count 41
PGA memory freed back to OS 273874944 bytes
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 7929856 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 270336 bytes
over allocation count 0
bytes processed 581040128 bytes
extra bytes read/written 0 bytes
cache hit percentage 100 percent
recompute count (total) 717
19 rows selected.
To change PGA memory parameter
ALTER SYSTEM SET pga_aggregate_target = 3500M SCOPE=BOTH;
Wednesday, 18 January 2017
How to Grant/Revoke privileges
Data control language (DCL)
A data control language (DCL) is a syntax similar to a computer programming language used to control access to data stored in a database (Authorization). In particular, it is a component of Structured Query Language (SQL).Examples of DCL commands include:
GRANT to allow specified users to perform specified tasks.
REVOKE to cancel previously granted or denied permissions.
The operations for which privileges may be granted to or revoked from a user or role apply to both the Data definition language (DDL) and the Data manipulation language (DML), and may include CONNECT, SELECT, INSERT, UPDATE, DELETE, EXECUTE, and USAGE.
In the Oracle database, executing a DCL command issues an implicit commit. Hence you cannot roll back the command.
Privileges
When multiple users can access database objects, authorization can be controlled to these objects with privileges. Every object has an owner. Privileges control if a user can modify an object owned by another user. Privileges are granted or revoked either by the instance administrator, a user with the ADMIN privilege or, for privileges to a certain object, by the owner of the object.There are two type of Privileges: System Privileges and Object privileges
System privileges
A system privilege is the right to perform a particular action or to perform an action on any object of a particular type. Objects include tables, views, materialized views, synonyms, indexes, sequences, cache groups, replication schemes and PL/SQL functions, procedures and packages. Only the instance administrator or a user with ADMIN privilege can grant or revoke system privileges.
System Privileges are normally granted by a DBA to users.
System privileges are
Privilege
|
Description
|
ADMIN
|
Enables a user to perform administrative tasks including checkpointing, backups, migration, and user creation and deletion.
|
ALTER ANY CACHE GROUP
|
Enables a user to alter any cache group in the database.
|
ALTER ANY INDEX
|
Enables a user to alter any index in the database.
|
Note: There is no ALTER INDEX statement.
|
|
ALTER ANY MATERIALIZED VIEW
|
Enables a user to alter any materialized view in the database.
|
Note: There is no ALTER MATERIALIZED VIEW statement.
|
|
ALTER ANY PROCEDURE
|
Enables a user to alter any PL/SQL procedure, function or package in the database.
|
ALTER ANY SEQUENCE
|
Enables a user to alter any sequence in the database.
|
Note: There is no ALTER SEQUENCE statement.
|
|
ALTER ANY TABLE
|
Enables a user to alter any table in the database.
|
ALTER ANY VIEW
|
Enables a user to alter any view in the database.
|
Note: There is no ALTER VIEW statement.
|
|
CACHE_MANAGER
|
Enables a user to perform operations related to cache groups.
|
CREATE ANY CACHE GROUP
|
Enables a user to create a cache group owned by any user in the database.
|
CREATE ANY INDEX
|
Enables a user to create an index on any table or materialized view in the database.
|
CREATE ANY MATERIALIZED VIEW
|
Enables a user to create a materialized view owned by any user in the database.
|
CREATE ANY PROCEDURE
|
Enables a user to create a PL/SQL procedure, function or package owned by any user in the database.
|
CREATE ANY SEQUENCE
|
Enables a user to create a sequence owned by any user in the database.
|
CREATE ANY SYNONYM
|
Enables a user to create a private synonym owned by any user in the database.
|
CREATE ANY TABLE
|
Enables a user to create a table owned by any user in the database.
|
CREATE ANY VIEW
|
Enables a user to create a view owned by any user in the database.
|
CREATE CACHE GROUP
|
Enables a user to create a cache group owned by that user.
|
CREATE MATERIALIZED VIEW
|
Enables a user to create a materialized view owned by that user.
|
CREATE PROCEDURE
|
Enables a user to create a PL/SQL procedure, function or package owned by that user.
|
CREATE PUBLIC SYNONYM
|
Enables a user to create a public synonym.
|
CREATE SEQUENCE
|
Enables a user to create a sequence owned by that user.
|
CREATE SESSION
|
Enables a user to create a connection to the database.
|
CREATE SYNONYM
|
Enables a user to create a private synonym.
|
CREATE TABLE
|
Enables a user to create a table owned by that user.
|
CREATE VIEW
|
Enables a user to create a view owned by that user.
|
DELETE ANY TABLE
|
Enables a user to delete from any table in the database.
|
DROP ANY CACHE GROUP
|
Enables a user to drop any cache group in the database.
|
DROP ANY INDEX
|
Enables a user to drop any index in the database.
|
DROP ANY MATERIALIZED VIEW
|
Enables a user to drop any materialized view in the database.
|
DROP ANY PROCEDURE
|
Enables a user to drop any PL/SQL procedure, function or package in the database.
|
DROP ANY SEQUENCE
|
Enables a user to drop any sequence in the database.
|
DROP ANY SYNONYM
|
Enables a user to drop a synonym owned by any user in the database.
|
DROP ANY TABLE
|
Enables a user to drop any table in the database.
|
DROP ANY VIEW
|
Enables a user to drop any view in the database.
|
DROP PUBLIC SYNONYM
|
Enables a user to drop a public synonym.
|
EXECUTE ANY PROCEDURE
|
Enables a user to execute any PL/SQL procedure, function or package in the database.
|
FLUSH ANY CACHE GROUP
|
Enables a user to flush any cache group in the database.
|
INSERT ANY TABLE
|
Enables a user to insert into any table in the database. It also enables the user to insert into any table using the synonym, public or private, to that table.
|
LOAD ANY CACHE GROUP
|
Enables a user to load any cache group in the database.
|
REFRESH ANY CACHE GROUP
|
Enables a user to flush any cache group in the database.
|
SELECT ANY SEQUENCE
|
Enables a user to select from any sequence or synonym on a sequence in the database.
|
SELECT ANY TABLE
|
Enables a user to select from any table, view, materialized view, or synonym in the database.
|
UNLOAD ANY CACHE GROUP
|
Enables a user to unload any cache group in the database.
|
UPDATE ANY TABLE
|
Enables a user to update any table or synonym in the database.
|
XLA
|
Enables a user to connect to a database as an XLA reader.
|
Object Privileges
Object privileges mean privileges on tables, views, materialized views, indexes, synonyms, sequences, cache groups, replication schemes and PL/SQL functions, procedures and packages.
An object's owner has all object privileges for that object, and those privileges cannot be revoked. The object's owner can grant object privileges for that object to other database users. A user with ADMIN privilege can grant and revoke object privileges from users who do not own the objects on which the privileges are granted.
Object Privileges are
Privilege
|
Object type
|
Description
|
DELETE
|
Table
|
Enables a user to delete from a table.
|
EXECUTE
|
PL/SQL package, procedure or function
|
Enables a user to execute a PL/SQL package, procedure or function directly.
|
FLUSH
|
Cache group
|
Enables a user to flush a cache group.
|
INDEX
|
Table or materialized view
|
Enables a user to create an index on a table or materialized view.
|
INSERT
|
Table or synonym
|
Enables a user to insert into a table or into the table through a synonym.
|
LOAD
|
Cache group
|
Enables a user to load a cache group.
|
REFERENCES
|
Table or materialized view
|
Enables a user to create a foreign key dependency on a table or materialized view.
The REFERENCES privilege on a parent table implicitly grants SELECT privilege on the parent table. |
REFRESH
|
Cache group
|
Enables a user to refresh a cache group.
|
SELECT
|
Table, sequence, view, materialized view, or synonym
|
Enables a user to select from a table, sequence, view, materialized view, or synonym.
The SELECT privilege enables a user to perform all operations on a sequence. A user can be granted the SELECT privilege on a synonym or a view without being explicitly granted the SELECT privilege on the originating table. |
UNLOAD
|
Cache group
|
Enables a user to unload a cache group.
|
UPDATE
|
Table
|
Enables a user to update a table.
|
Dictionary views which show the granted privileges and roles
DBA_SYS_PRIVS - contains information about System privilegesDBA_TAB_PRIVS - contains information about Object Privileges
DBA_ROLE_PRIVS - contains information about assigned rows;
* You can use user_sys_priv, user_tab_privs and user_role_privs to list the privileges information to the current user.
Examples
To grant the SELECT privilege on table tab1 to the authorization IDs maria and harry, use the following syntax:
GRANT SELECT ON TABLE tab1 TO maria,harry
To grant the UPDATE and TRIGGER privileges on table tab1 to the authorization IDs anita and zhi, use the following syntax:
GRANT UPDATE, TRIGGER ON TABLE tab1 TO anita,zhi
To grant the SELECT privilege on table tab1 to all users, use the following syntax:
GRANT SELECT ON TABLE tab1 to PUBLIC
To grant the EXECUTE privilege on procedure proc_1 to the authorization ID george, use the following syntax:
GRANT EXECUTE ON PROCEDURE proc_1 TO george
To grant the role purchases_reader_role to the authorization IDs george and maria, use the following syntax:
GRANT purchases_reader_role TO george,maria
To grant the SELECT privilege on table tab_1 to the role purchases_reader_role, use the following syntax:
GRANT SELECT ON TABLE tab_1 TO purchases_reader_role
To grant the USAGE privilege on the sequence generator order_id to the role sales_role, use the following syntax:
GRANT USAGE ON SEQUENCE order_id TO sales_role;
To grant the USAGE privilege on the user-defined type price to the role finance_role, use the following syntax:
GRANT USAGE ON TYPE price TO finance_role;
The following statement revokes the DROP ANY TABLE system privilege from the users george and oe:
REVOKE DROP ANY TABLE FROM hr, oe;
The following statement revokes the role dw_manager from the user sh:
REVOKE dw_manager FROM sh;
The following statement revokes the CREATE TABLESPACE system privilege from the dw_manager role:
REVOKE CREATE TABLESPACE FROM dw_manager;
To revoke the DELETE privilege on orders from hr, issue the following statement:
REVOKE DELETE ON orders FROM hr;
Subscribe to:
Posts (Atom)