Readings : References, Tutorials, and Articles
Finding roles and privileges of a user or current session.
With more emphasis on securities, default privileges for a regular(non-DBA) user is very restrictive. As of Oracle database 10.2, CONNECT
and RESOURCE
which are typical predefined roles for a regular user don't include create view
and create synonym
privileges.
So often, you should have to check what roles and privileges are given for a user. You can do that using the following statements.
-- finding all roles for the current session SELECT * FROM SESSION_ROLES; -- finding all privileges for the current session SELECT * FROM SESSION_PRIVS;
Required roles to access dynamic performance (V$) views.
To access the dynamic performance views such as V$SESSION
, V$PARAMETER
and V$LOCK
as well as static dictionary views of DBA such as DBA_TABLES
, DBA_SEQUENCES
and DBA_SYNONYMS
, the user need SELECT_CATALOG_ROLE
DBA user can grant the role to the user using the following statement.
GRANT select_catalog_role TO :username;
Finding the values of parameters currently in effect.
Checking the context of current operation is really one of the most basic thing you can do in any circumstances. With Oracle, that is parameters. You can identify the current value of SGA max size, max sessions, optimizer mode or other settings throughout the parameter.
SELECT * FROM V$PARAMETER ORDER BY name;
V$PARAMETER
is one of public synonyms in default installation.
Identifying the character-set of the Oracle instance
Use the following statement.
select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
Changing the password of the user
Use the following statement.
ALTER USER :username IDENTIFIED BY :password;
Be cautious not to mark single quotation mark around values of :username
and :password
. The following is simple example.
ALTER USER hr IDENTIFIED BY hr88rh;
When you are connected to Oracle using SQL*Plus, you can use password
command to change your password.
The following is sample command line when changing password of hr
user
SQL>password
Changing password for hr
Old password :
New password :
Retype new password :
Password changed
Viewing information about users
About current user, access the following dictionary views.
View | Description |
---|---|
USER_USERS | Describes only the current user. |
ALL_USERS | Lists users visible to the current user, but does not describe them. |
USER_TS_QUOTAS | Describes tablespace quotas for current user. |
USER_RESOURCE_LIMITS | Displays the resource limits for the current user. |
USER_PASSWORD_LIMITS | Describes the password profile parameters that are assigned to the user. |
More privileged users can use the following views to access the information of all users.
View | Description |
---|---|
DBA_USERS | Describes all users of the database. |
DBA_TS_QUOTAS | Describes tablespace quotas for users. |
DBA_PROFILES | Displays all profiles and their limits. |
V$SESSION | Lists session information for each current session, includes user name. |
Use connect
command of SQL*Plus
without tnsnames.ora
Identifying execution plan of query
Using DBMS_XPLAN.DISPLAY_CURSOR
function
The execution plan of last executed query in plan table can be printed out using DBMS_XPLAN.DISPLAY_CURSOR
function.
DBMS_XPLAN
package was introduced at Oracle 9i, but DISPLAY_CURSOR
function was added with Oracle 10g.
DBMS_XPLAN.DISPLAY_CURSOR
function accesses V$SESSION
, so the user need to have SELECT_CATALOG_ROLE
role to correctly execute select * from table(DBMS_XPLAN.DISPLAY_CURSOR)
select employee_id, first_name || ' ' || last_name as name, department_id, job_id, manager_id from hr.employees where job_id = 'SA_REP' and manager_id in (146, 147); select * from table(DBMS_XPLAN.DISPLAY_CURSOR);
If you want to see just execution plan and not to actually execute the query. You can use EXPLAIN PLAN
statement.
explain plan for select employee_id, first_name || ' ' || last_name as name, department_id, job_id, manager_id from hr.employees where job_id = 'SA_REP' and manager_id in (146, 147); select * from table(DBMS_XPLAN.DISPLAY_CURSOR);
Using autotrace with SQL*Plus
When using SQL*Plus, you can turn on autotrace feature for the current session using SET AUTOTRACE ON
command.
After the autotrace is enabled, execution of query would display normal result data and the execution plan right after it.
To use this feature, you must create a PLAN_TABLE
table in your schema and then have the PLUSTRACE
role granted to you.
SQL>SET AUTOTRACE ON SQL>SELECT employee_id, first_name || ' ' || last_name as name, department_id, job_id, manager_id 2 FROM hr.employees 3 WHERE job_id = 'SA_REP' 4 AND manager_id IN (146, 147);
If you want to see just execution plan and not to actually execute the query, you can use SET AUTOTRACE TRACEONLY
command.
SQL>SET AUTOTRACE TRACEONLY SQL>SELECT employee_id, first_name || ' ' || last_name as name, department_id, job_id, manager_id 2 FROM hr.employees 3 WHERE job_id = 'SA_REP' 4 AND manager_id IN (146, 147);
0 comments:
Post a Comment