Oracle DBA Daily Experiences

Using “oradebug dump events 1″ one can find/list events set in a session.

For example:
In a session events 10046 and 1410 events are set
SQL> ALTER SESSION SET EVENTS='10046 trace name context forever, level 12';

Session altered.

SQL> ALTER SESSION SET EVENTS='1410 trace name context forever, level 12';

Session altered.

In another session login as an account as sysdba
SQL> oradebug setorapid 12
Unix process pid: 10932, image: oracle@localhost.localdomain (TNS V1-V3)

SQL> oradebug dump events 1
Statement processed.

SQL> oradebug tracefile_name
/u01/oradata/admin/TEST/udump/test_ora_10932.trc

After running the above oradebug will dump the events in the trace file in the above case /u01/oradata/admin/TEST/udump/test_ora_10932.trc shows event 1410 and 10046 events set.
…
Dump event group for level SESSION
TC Addr Evt#(b10) Action TR Addr Arm Life
974FED50 1410 1 974fede0 0 0
TR Name TR level TR address TR arm TR life TR type
CONTEXT 12 0 -1 2 0
974FEBF8 10046 1 974fec88 0 0
TR Name TR level TR address TR arm TR life TR type
CONTEXT 12 0 -1 2 0
*** 2010-07-15 23:40:05.671
..

To list source code one can call dbo.sp_helptext '<store procedure>' to list code.

Example:
exec dbo.sp_helptext 'sp_databases'

Text
—————————–
create procedure sys.sp_databases
as
set nocount on

select
….

Another way of viewing code is to query syscomments but it doesn’t format the output.
Example:
select so.name, text
FROM syscomments sc, sysobjects so
where so.id = sc.id
and name = 'sp_databases'
order by number, colid

Using the SQL below one can list SQL Server tables

Use testDB
SELECT table_name FROM INFORMATION_SCHEMA.TABLES;

table_name
———–
ExceptionLog
Employee
…

Using the SQL one can create a list of tables to pass as a parameter to export (exp).

set heading off
set pages 0

– create the list for a specific user
SELECT DECODE( rownum, 1, ‘tables=(‘, ‘,’ ), owner || ‘.’ || table_name FROM dba_tables WHERE owner IN ( ‘SCOTT’ )
UNION ALL
SELECT ‘)’, null FROM dual;

Output:
tables=( SCOTT.USERS
, SCOTT.DEPT
, SCOTT.EMP
, SCOTT.BONUS
, SCOTT.SALGRADE
, SCOTT.TEST_OBJECTS
, SCOTT.STATS_BACKUP
, SCOTT.DUMMY1
, SCOTT.BIGEMP
, SCOTT.MYDUAL
)

– in the example below it will create list of tables that begin with F but using the example you can create
SELECT DECODE( rownum, 1, ‘tables=(‘, ‘,’ ), owner || ‘.’ || table_name FROM dba_tables WHERE table_name like ‘F%’
UNION ALL
select ‘)’, null from dual
/

Using the code below one can find the tables/indexes that have stale statistics in a database, when options=>’GATHER AUTO’ is used oracle gathers statistics analyzes the tables/indexes that have stale statistics. Table monitoring is enabled by default in 10g to find table which is used to find table statistics, when STATISTICS_LEVEL is set to “BASIC” table monitoring is disabled. In 10g when the value of statistics_level is “typical” or “all” table monitoring is turned enabled. In 9i one can enable/disable table monitoring by calling DBMS_STATS.ALTER_SCHEMA_TABLE_MONITORING

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(objlist=>ObjList, options=>’LIST STALE’);
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || ‘.’ || ObjList(i).ObjName || ‘ ‘ || ObjList(i).ObjType || ‘ ‘ || ObjList(i).partname);
END LOOP;
END;
/
– shows tables that have stale statistics
SYS.COL_USAGE$ TABLE
SYS.DEPENDENCY$ TABLE
SYS.HISTGRM$ TABLE
SYS.HIST_HEAD$ TABLE
…

Note: To find schema level stats that are stale one can call DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>’SCOTT’, objlist=>ObjList, options=>’LIST STALE’);

The view has the monitoring information about tables user_tab_modifications, all_tab_modifications and dba_tab_modifications.

When a database is started in restricted mode only users with restricted access can login to the database. The reason to start database in restrict is to restrict normal users to connect to the database. To switch the database back to normal mode, you would need to restart the database.

One can start the database in restricted mode by using the following option when starting up.
SQL> STARTUP RESTRICT;

– shows database is in restricted mode
12:49:30 @> select logins from v$instance;

LOGINS
———-
RESTRICTED

If you need to grant a user access to database when it’s in restricted mode, you can run the following SQL.

SQL> GRANT RESTRICTED SESSION TO scott;

Grant succeeded.

To revoke access restricted mode access
SQL> REVOKE RESTRICTED SESSION FROM scott;

How to find the users that have restricted session access:
– find users who have been granted through the role (displays 1 level)
SELECT b.grantee, a.grantee || ‘ (Role)’ AS granted
FROM dba_sys_privs a, dba_role_privs b
WHERE a.privilege = ‘RESTRICTED SESSION’
AND a.grantee = b.granted_role
UNION
SELECT b.username, ‘User (Direct)’ — find users who have given access not through role
FROM dba_sys_privs a, dba_users b
WHERE a.privilege = ‘RESTRICTED SESSION’
AND a.grantee = b.username;

GRANTEE GRANTED
———— ————————————-
SCOTT User (Direct)
SYS DBA (Role)
SYSTEM DBA (Role)

To change from restricted mode to unrestricted/active mode, which can switch without restarting the database.

SQL> alter system disable restricted session;

System altered.

SQL> select logins from v$instance;

LOGINS
———-
ALLOWED

Also database can be placed in restricted mode by running the following DDL without restarting the database.

SQL> alter system enable restricted session;

System altered.

SQL> select logins from v$instance;

LOGINS
———-
RESTRICTED

Note: There seems to be an issue when disabling restricted mode in 10.2.0.1, you can try to shutdown from the same session where restricted session was disable was executed from.

SQL> shutdown immediate;
ORA-01097: cannot shutdown while in a transaction – commit or rollback first

You can work around this by executing shutdown from a new session or executing commit/rollback from the session where restricted session was disabled.