Ulfat`s DataBase Blog

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                       ...

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

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;