These two roles are defined automatically for Oracle databases.The privileges associated with these role changed from 9i,10g R1 and 10g R2.Before you assign this role to a new user, you can see what all privileges a user granted with this role can get, using the below queries.


SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> select grantee, privilege, admin_option from dba_sys_privs where grantee='CONNECT';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        CREATE SESSION                           NO

SQL> select grantee, privilege, admin_option from dba_sys_privs where grantee='RESOURCE';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE TABLE                             NO



Published with Blogger-droid v1.6.8

Frequency of archive log generation.

Find the frequency in which archive logs are generated for a database.

This will give the number of archive logs generated for each day.

SQL> select count(*),trunc(first_time) from v$log_history group by trunc(first_time) order by trunc(first_time);

  COUNT(*) TRUNC(FIR
---------- ---------
         2 05-APR-11
        44 06-APR-11
        42 07-APR-11
        42 08-APR-11
        43 09-APR-11
        43 10-APR-11
        42 11-APR-11
        45 12-APR-11
        43 13-APR-11
        43 14-APR-11
        43 15-APR-11

  COUNT(*) TRUNC(FIR
---------- ---------
        43 16-APR-11
        49 17-APR-11
        46 18-APR-11
        14 19-APR-11

15 rows selected.

If you want to know at what time log switches happened for each day, you can use the below query.

SQL> SELECT TO_CHAR(first_time,'YYYY-MON-DD') DAY,
  2    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'00',1,0)),'99') "00",
  3    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'01',1,0)),'99') "01",
  4    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'02',1,0)),'99') "02",
  5    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'03',1,0)),'99') "03",
  6    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'04',1,0)),'99') "04",
  7    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'05',1,0)),'99') "05",
  8    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'06',1,0)),'99') "06",
  9    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'07',1,0)),'99') "07",
10    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'08',1,0)),'99') "08",
11    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'09',1,0)),'99') "09",
12    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'10',1,0)),'99') "10",
13    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'11',1,0)),'99') "11",
14    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'12',1,0)),'99') "12",
15    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'13',1,0)),'99') "13",
16    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'14',1,0)),'99') "14",
17    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'15',1,0)),'99') "15",
18    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'16',1,0)),'99') "16",
19    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'17',1,0)),'99') "17",
20    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'18',1,0)),'99') "18",
21    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'19',1,0)),'99') "19",
22    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'20',1,0)),'99') "20",
23    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'22',1,0)),'99') "22",
24    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'23',1,0)),'99') "23"
25  FROM v$log_history
26  GROUP BY TO_CHAR(first_time,'YYYY-MON-DD')
27  ORDER BY TO_CHAR(first_time,'YYYY-MON-DD') ;

DAY         00  01  02  03  04  05  06  07  08  09  10  11  12  13  14  15  16  17  18  19  20  22  23
----------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
2011-APR-05   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   2
2011-APR-06   5   0   3   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   2  16
2011-APR-07   4   0   3   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   2  15
2011-APR-08   4   0   3   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   2  15
2011-APR-09   5   0   3   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   2  15
2011-APR-10   5   0   3   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   2  15
2011-APR-11   5   0   3   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   2  14
2011-APR-12   7   0   3   0   2   0   2   0   2   0   2   0   2   1   2   0   2   0   2   0   2   2  14
2011-APR-13   6   0   3   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   2  14
2011-APR-14   6   0   3   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   2  14
2011-APR-15   6   0   3   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   2  14

DAY         00  01  02  03  04  05  06  07  08  09  10  11  12  13  14  15  16  17  18  19  20  22  23
----------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
2011-APR-16   6   0   3   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   2  14
2011-APR-17   7   0   3   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   2  19
2011-APR-18   2   2   3   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   2  19
2011-APR-19   2   0   3   0   2   0   2   0   2   0   2   0   1   0   0   0   0   0   0   0   0   0   0

The retention period or the history of log switches / arvchive log generation in v$log_history is controlled by MAXLOGHISTORY. This is specified during database creation or control file creation.

You can query this as shown below.

SQL> select type,records_used  from v$controlfile_record_section where type like 'LOG%'
  2  ;

TYPE                         RECORDS_USED
---------------------------- ------------
LOG HISTORY                           584

SQL> select count(*) from v$log_history;

  COUNT(*)
----------
       584
      

Put files in remote machine by FTP in background

If you have a requirement to put files from one machine to a remote machine where the client in which you are connecting might disconnect after some time out. In this case you would want the file transfer to take place in background ( in server ).Here is a small shell script which can put files in background. This is for solaris.

#!/bin/sh
HOST='remote machine ip'
USER='remote machine user name'
PASSWD='remote machine user password'

ftp -n $HOST <<END_SCRIPT
quote USER $USER
quote PASS $PASSWD
prompt off
cd /data1/oracle/rman -- source directory where your files are present
mput *
quit
END_SCRIPT
exit 0

You can check whether the process is running as shown below.

-bash-3.00$ ps -ef | grep scriptname