Home > Database, Oracle > Some Useful Administrative Queries on Oracle…

Some Useful Administrative Queries on Oracle…

-----------------------------------------------------------------------
Purpose:    Display database uptime in days and hours to SYS or SYSTEM
-----------------------------------------------------------------------

select SYSDATE-logon_time "Days", (SYSDATE-logon_time)*24 "Hours"
from   sys.v_$session
where  sid=1 /* this is PMON */
/
-----------------------------------------------------------------------
-----------------------------------------------------------------------

Purpose:    Script to create a new user (with privs) like an existing
database user. User data will not be copied.
-----------------------------------------------------------------------

set pages 0 feed off veri off lines 500

accept oldname prompt "Enter user to model new user to: "
accept newname prompt "Enter new user name: "
-- accept psw     prompt "Enter new user's password: "

-- Create user...
select 'create user &&newname identified by values '''||password||''''||
-- select 'create user &&newname identified by &psw'||
       ' default tablespace '||default_tablespace||
       ' temporary tablespace '||temporary_tablespace||' profile '||
       profile||';'
from   sys.dba_users 
where  username = upper('&&oldname');

-- Grant Roles...
select 'grant '||granted_role||' to &&newname'||
       decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from   sys.dba_role_privs
where  grantee = upper('&&oldname');  

-- Grant System Privs...
select 'grant '||privilege||' to &&newname'||
       decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from   sys.dba_sys_privs
where  grantee = upper('&&oldname');  

-- Grant Table Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||' to &&newname;'
from   sys.dba_tab_privs
where  grantee = upper('&&oldname');  

-- Grant Column Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||
       '('||column_name||') to &&newname;'
from   sys.dba_col_privs
where  grantee = upper('&&oldname'); 
-----------------------------------------------------------------------

-----------------------------------------------------------------------
Purpose:    Log all database errors to a table Oracle8i or above/ DBA orCREATE ANY TRIGGER privs/ and GRANT SELECT ON SYS.V_$SESSION required 
-----------------------------------------------------------------------
create table log_errors_tab (
	error     varchar2(30),
	timestamp date,
	username  varchar2(30),
        osuser    varchar2(30),
        machine   varchar2(64),
	process   varchar2(8),
	program   varchar2(48));

create or replace trigger log_errors_trig 
	after servererror on database
declare
	var_user     varchar2(30);
	var_osuser   varchar2(30);
	var_machine  varchar2(64);
	var_process  varchar2(8);
	var_program  varchar2(48);
begin
	select username, osuser, machine, process, program
	into   var_user, var_osuser, var_machine, var_process, var_program
	from   sys.v_$session
	where  audsid = userenv('sessionid');

	insert into log_errors_tab
	  values(dbms_standard.server_error(1),sysdate,var_user,
	         var_osuser,var_machine,var_process,var_program);
end;
/
-----------------------------------------------------------------------
-----------------------------------------------------------------------
Purpose :   List analyzed tables with un-analyzed indexes. Sometimes indexes are re-build for performance and maintenance reasons but the assosiated table/index is not re-ANALYZED. This can cause server performance problems. 
-----------------------------------------------------------------------
-- select distinct 'analyze table '||i.table_name||
-- ' estimate statistics sample 25 percent;'
select 'Index '||i.index_name||' not analyzed but table '||
       i.table_name||' is.'
  from user_tables t, user_indexes i
 where t.table_name    =      i.table_name
   and t.num_rows      is not null
   and i.distinct_keys is     null
/
-----------------------------------------------------------------------

-----------------------------------------------------------------------
Purpose :   List all indexed columns for a given table  
-------------------------------------------------------------------------
prompt Indexes for table: &&1
select c.index_name, i.uniqueness, c.column_name
from   user_indexes i, user_ind_columns c
where  i.index_name = c.index_name
  and  i.table_name = upper('&&1')
order  by c.index_name, c.column_position
/
-----------------------------------------------------------------------

-----------------------------------------------------------------------
Purpose:    Shows active (in progress) transactions
-----------------------------------------------------------------------

select username, terminal, osuser,
       t.start_time, r.name, t.used_ublk "ROLLB BLKS",
       decode(t.space, 'YES', 'SPACE TX',
          decode(t.recursive, 'YES', 'RECURSIVE TX',
             decode(t.noundo, 'YES', 'NO UNDO TX', t.status)
       )) status
from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
where t.xidusn = r.usn
  and t.ses_addr = s.saddr
/
-----------------------------------------------------------------------
Advertisements
Categories: Database, Oracle
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: