FAILED_LOGIN_ATTEMPT

10gR2将default profile中的FAILED_LOGIN_ATTEMPTS改为了10,也就是如果使用错误密码登录10次，该帐号就会被lock住

SQL> select * from dba_profiles;

PROFILE RESOURCE_NAME RESOURCE LIMIT
—————————— ——————————– ——– —————————————-
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITEDDEFAULT SESSIONS_PER_USER KERNEL UNLIMITEDDEFAULT CPU_PER_SESSION KERNEL UNLIMITEDDEFAULT CPU_PER_CALL KERNEL UNLIMITEDDEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITEDDEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITEDDEFAULT IDLE_TIME KERNEL UNLIMITEDDEFAULT CONNECT_TIME KERNEL UNLIMITEDDEFAULT PRIVATE_SGA KERNEL UNLIMITEDDEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITEDDEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITEDDEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITEDDEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITEDDEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED
16 rows selected.

在以前的版本中，都是UNLIMITED,可以通过下面命令将其改回去

SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

Profile altered.

SQL> select * from dba_profiles;

PROFILE RESOURCE_NAME RESOURCE LIMIT
—————————— ——————————– ——– —————————————-
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITEDDEFAULT SESSIONS_PER_USER KERNEL UNLIMITEDDEFAULT CPU_PER_SESSION KERNEL UNLIMITEDDEFAULT CPU_PER_CALL KERNEL UNLIMITEDDEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITEDDEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITEDDEFAULT IDLE_TIME KERNEL UNLIMITEDDEFAULT CONNECT_TIME KERNEL UNLIMITEDDEFAULT PRIVATE_SGA KERNEL UNLIMITEDDEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITEDDEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITEDDEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITEDDEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITEDDEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITEDDEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED
16 rows selected.

Find if you need to add new partitions

Find if you need to add new partitions
                                                 Last update (2010-07-09 21:11:28)
                                                                                                                   Date added (2008-02-29 08:44:19)

Summary 
If you have partitions in your database it is very common in the future to need a method knowing in which partition you are currently inserting data and if this is the last available partition. If yes, then you must add very soon new partitions to the table.

In this example partitions have been setup for tables in AX, AR, GL schemas of an E-Business Suite ERP. The algorith is very simple. It checks if rows exist in the last partition of the table, if yes then it creates the ddl sql to add the new partition.

Connect as sys from sqlplus and set

sql>set serveroutput on

DECLARE
CURSOR c1 IS
SELECT 'select count(*) from '|| a.table_owner || '.' || a.table_name ||' partition('||a.partition_name||')' s1,a.*
FROM dba_tab_partitions a,dba_part_tables b
WHERE a.table_owner IN ('AX','AR','GL')
AND a.table_name NOT LIKE 'HZ%' 
AND a.table_name NOT LIKE 'AX_DOC%'
AND a.partition_position = b.partition_count
AND b.owner = a.table_owner
AND b.table_name = a.table_name;

CURSOR c2(v_owner VARCHAR2,v_tname VARCHAR2) IS
SELECT high_value,partition_name
   FROM (SELECT * FROM dba_tab_partitions 
             WHERE table_owner = v_owner  
             AND   table_name  = v_tname 
         ORDER BY partition_position DESC) WHERE ROWNUM <3;


CURRENT_MAX_VALUE_NUMBER NUMBER;
CURRENT_MAX_VALUE_NUMBER2 NUMBER;
v_statement   VARCHAR2(200);
v_rec        dba_tab_partitions%ROWTYPE;
v_oldtname   dba_tab_partitions.table_name%TYPE := NULL;
v_maxhvalue  dba_tab_partitions.high_value%TYPE;
v_minhvalue  dba_tab_partitions.high_value%TYPE;
v_newhvalue  dba_tab_partitions.high_value%TYPE;
v_newpname   dba_tab_partitions.partition_name%TYPE;
BEGIN
FOR i IN c1 LOOP
 v_statement           := i.s1;
    EXECUTE IMMEDIATE v_statement INTO CURRENT_MAX_VALUE_NUMBER;
    IF CURRENT_MAX_VALUE_NUMBER > 0 THEN 
    FOR j IN c2(i.table_owner,i.table_name) LOOP
        IF i.table_name = v_oldtname THEN
        v_minhvalue := j.high_value;
     ELSE
        v_maxhvalue := j.high_value;   
     END IF;
     v_oldtname := i.table_name;
    END LOOP;
    v_newhvalue := TO_NUMBER(v_maxhvalue) + (TO_NUMBER(v_maxhvalue)-TO_NUMBER(v_minhvalue));
       v_newpname  := SUBSTR(i.partition_name,1,LENGTH(i.partition_name)-3)||
       LTRIM(TO_CHAR(SUBSTR(i.partition_name,LENGTH(i.partition_name)-2)+1,'099'));
       DBMS_OUTPUT.PUT_LINE('The table '|| i.table_owner || '.'|| i.table_name||' needs partition');
    DBMS_OUTPUT.NEW_LINE();
       DBMS_OUTPUT.PUT_LINE('Alter table '||i.table_name||' add partition '||v_newpname || 
       ' VALUES LESS THAN ('||v_newhvalue||')');
    DBMS_OUTPUT.PUT_LINE(' LOGGING NOCOMPRESS TABLESPACE '||i.tablespace_name);
    DBMS_OUTPUT.PUT_LINE(' PCTFREE '||i.pct_free);
    DBMS_OUTPUT.PUT_LINE(' INITRANS '||i.ini_trans||' MAXTRANS '||i.max_trans);
    DBMS_OUTPUT.PUT_LINE(' STORAGE    (INITIAL '||i.initial_extent);
    DBMS_OUTPUT.PUT_LINE(' NEXT '||i.next_extent);
    DBMS_OUTPUT.PUT_LINE(' MINEXTENTS '||i.min_extent);
    DBMS_OUTPUT.PUT_LINE(' MAXEXTENTS '||i.max_extent);
    DBMS_OUTPUT.PUT_LINE(' PCTINCREASE '||i.pct_increase);
    DBMS_OUTPUT.PUT_LINE(' BUFFER_POOL      DEFAULT)');
    DBMS_OUTPUT.NEW_LINE();
    END IF;
END LOOP;
END;

Tip:It works for partitions based on NUMBER columns, not DATE

Create a super user like DBA but with no administration power

  Oracle Scripts » Categories » Oracle Real Cases »

Create a super user like DBA but with no administration power
                                                 Last update (2008-05-14 10:44:40)
                                                                                                                   Date added (2008-05-14 01:49:15)

Summary 
Some time you have the requirement to create a user to the database that has access to all v$session, v$lock, dba_free_space and other dictionary views to monitor performance issues, sql tuning, and other DBA operations.

But you don't want to have real power to change things like a DBA can do. 

The quick and easy word around is to grant the select_catalog_role role to the new user.

create user dba_monitor identified by dba_monitor;
grant connect, select_catalog_role to dba_monitor;

Now with the new user you can use tools like TOAD or Grid Control to monitor the database as a DBA but with no real power to change things