Turn off autoextend

Sometimes we need to turn off autoextend and resize the file to 10G. It can be done in very simple steps. Let take we have datafile like this c:\oracle\oradata\example01.dbf

alter database datafile 'c:\oracle\oradata\example01.dbf' resize 10G;
alter database datafile 'c:\oracle\oradata\example01.dbf' autoextend off;

That is all :)

Oracle 11g sets by default password expiration - how to change that

To check if we have users with password status expired we have to run following query

SQL> select username, account_status, expiry_date, profile from dba_users;

USERNAME ACCOUNT_STATUS EXPIRY_DA
------------------------------ -------------------------------- ---------
PROFILE
------------------------------
OE EXPIRED 07-FEB-12
DEFAULT

As we can see the password for user OE has been expired. In this case first we have to change the password using

SQL> alter user oe identified by newpassword;

User altered.

Now if we check default profile we will see that default PASSWORD_LIFE_TIME is 180 days.

SQL> SELECT profile, resource_name, limit FROM dba_profiles WHERE profile='DEFAULT';

PROFILE RESOURCE_NAME
------------------------------ --------------------------------
LIMIT
----------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS
10

DEFAULT PASSWORD_LIFE_TIME
180

Now i am going to change PASSWORD_LIFE_TIME to UNLIMITED

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.

SQL> SELECT profile, resource_name, limit FROM dba_profiles WHERE profile='DEFAULT';

PROFILE RESOURCE_NAME
------------------------------ --------------------------------
LIMIT
----------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS
10

DEFAULT PASSWORD_LIFE_TIME
UNLIMITED

Create external table to read alert log

connect sys /as sysdba

create directory BDUMP as 'C:\app\Administrator\diag\rdbms\orcl\orcl\trace';

create table
alertlog ( msg varchar2(100) )
organization external (
type oracle_loader
default directory BDUMP
access parameters (
records delimited by newline
)
location('alert_orcl.log')
)
reject limit unlimited;


select msg from alertlog;

MSG
-----------------------------

Wed Feb 01 14:51:51 2012
DBW0 started with pid=9
Wed Feb 01 14:51:51 2012
LGWR started with pid=10
Wed Feb 01 14:51:52 2012
CKPT started with pid=11
Wed Feb 01 14:51:52 2012
SMON started with pid=12
Wed Feb 01 14:51:52 2012
RECO started with pid=13
Wed Feb 01 14:51:52 2012