Purge trace, alert, incident files using adrci

Today suddenly I saw more disk usage. After investigation I found what used more space.

[oracle@fc-db-tst1 FPREPROD]$ du -sh *
9.4G alert
4.0K cdump
4.0K hm
4.0K incident
4.0K incpkg
4.0K ir
4.0K lck
3.5M metadata
4.0K metadata_dgif
4.0K metadata_pv
4.0K stage
4.0K sweep
7.2G  trace



[oracle@fc-db-tst1 alert]$ adrci

ADRCI: Release 11.2.0.3.0 - Production on Fri Mar 28 08:17:10 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle"
adrci> show homes
ADR Homes:
diag/rdbms/fpreprod/FPREPROD
adrci> show control

ADR Home = /u01/app/oracle/diag/rdbms/fpreprod/FPREPROD:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME                            
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1753316741           720                  8760                 2014-01-24 00:26:21.230527 +04:00        2014-03-24 15:02:28.535497 +04:00                                                 1                    2                    80                   1                    2014-01-24 00:26:21.230527 +04:00      
1 rows fetched


LONGP_POLICY and SHORTP_POLICY set`s via hour.
720 = (720/24 = 30 days)
8760 = (8760/24 = 365 days)

Let`s set new values.

adrci> set control (SHORTP_POLICY = 240)
adrci> set control (LONGP_POLICY = 1095)
adrci> show control

ADR Home = /u01/app/oracle/diag/rdbms/fpreprod/FPREPROD:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME                            
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1753316741           240                  1095                 2014-03-28 08:19:14.448120 +04:00        2014-03-24 15:02:28.535497 +04:00                                                 1                    2                    80                   1                    2014-01-24 00:26:21.230527 +04:00      
1 rows fetched


--Purging alert
adrci> purge -age 10080 -type ALERT

--Purging trace
adrci> purge -age 10080 -type TRACE

--Purging incident
adrci> purge -age 10080 -type incident

--Purging all
adrci> purge -age 10080



adrci> show alert

ADR Home = /u01/app/oracle/diag/rdbms/fpreprod/FPREPROD:
*************************************************************************
Output the results to file: /tmp/alert_27908_1397_FPREPROD_1.ado

2014-02-07 06:24:17.356000 +04:00
Thread 1 advanced to log sequence 347 (LGWR switch)
  Current log# 2 seq# 347 mem# 0: /u01/app/oracle/oradata/FPREPROD/redo02.log
2014-02-07 06:30:00.027000 +04:00
adrci>


--Display last 50 rows
adrci> show alert -TAIL 50

--Display last 10 rows and output appended data as the file grows
adrci> show alert -TAIL -F

For more detail please refer to :
http://docs.oracle.com/cd/B28359_01/server.111/b28319/adrci.htm#BGBHHBGB

Change db user`s default tablespace

Today I found on my friend`s database some application user`s default tablespace is SYSTEM which is not recommended by Oracle.

Select users list which default_tablespace is SYSTEM. In my case, application users prefix are APP.

 SELECT username, default_tablespace FROM dba_users WHERE username like 'APP%';  
 USERNAME   DEFAULT_TABLESPACE    
 APPLWEB    SYSTEM  
 APPLDESK   SYSTEM  
 APPLTEST    SYSTEM  

Now, use below little bit PLSQL block.

 BEGIN  
  FOR i IN (  
            SELECT username FROM dba_users   
            WHERE default_tablespace in ('SYSTEM') and username like 'APP%'  
          ) LOOP  
   EXECUTE IMMEDIATE 'ALTER USER ' || i.username || ' DEFAULT TABLESPACE USERS';  
  END LOOP;  
 END;  
 /  

Check

 SELECT username, default_tablespace FROM dba_users WHERE username like 'APP%';   
 USERNAME   DEFAULT_TABLESPACE    
 APPLWEB    USERS  
 APPLDESK   USERS  
 APPLTEST    USERS

ORA-03206: maximum file size of (5242880) blocks in AUTOEXTEND clause is out of range

Today when I want to change max size of datafile I got strange error.

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/MYDB/users01.dbf' 
AUTOEXTEND ON NEXT 1280K MAXSIZE 32GB  
ORA-03206: maximum file size of (5242880) blocks in AUTOEXTEND clause is out of range

Here is reason: The maximum file size for an autoextendable file has exceeded the maximum number of blocks allowed.

After research internet I found that instead of using 32GB we can use 32767M. (same values but in MB).
Or you may use 31GB too.

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/MYDB/users01.dbf' 
AUTOEXTEND ON NEXT 1280K MAXSIZE 32767M;