How to unlock the user oracle (ORA-28000)

How to unlock the user
How to unlock the user in oracle, or how to solve ORA-28000



SQL> conn sqlplus AS sysdba
Enter password:
Connected.

SQL> ALTER USER username   2    ACCOUNT UNLOCK;

example:

SQL> conn sqlplus as sysdba
Enter password:
Connected.
SQL> alter user scott
  2  account unlock;

User altered.

SQL> conn  scott/tiger
ERROR:
ORA-28001: the password has expired


Changing password for scott
New password:
Retype new password:
Password changed
Connected.





Looking forward for your suggestions.. please feel free to write to me.. Your help would definitely help to improve our blog. Chetan Yadav

how to create tablesape in oracle

Create tablespace
SQL> create tablesapce tablesape_name
        datafile 'datafilepath'
        size 40m
        autoextend on
        next 32 m
        extend management local;


Create Temporary tablespce 

SQL> create temporary tablesapce tablesape_name
        datafile 'datafilepath'
        size 40m
        autoextend on
        next 32 m

        extend management local;

(temporary tablespce can have only temp files not datafiles )





Looking forward for your suggestions.. please feel free to write to me.. Your help would definitely help to improve our blog. Chetan Yadav

Few Oracle SQL Command

Increase the Size of Tablespace



If you want to increase the size of tablespace, its so simple. You can do this by enterprise manager console. Increase the size of datafile for particular tablespace.

OR

For Example

ALTER DATABASE
DATAFILE ‘(datafile path)/u01/oradata/ userdata03. dbf’
RESIZE 200M;

If you don’t have any free space on that partition of disk then you can add another datafile on the other partition  for particular tablespace.

For example

ALTER TABLESPACE app_data
ADD DATAFILE ‘/u01/oradata/ userdata03. dbf’
SIZE 200M;

-----------------------------------------------------------------------------------------

Tablespace used in Percentage 

# login oracle with sqlplus as  sysadm user
# conn as sysdba

select a.TABLESPACE_NAME,
a.BYTES bytes_used,
b.BYTES bytes_free,
b.largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
 and round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)  > 75
order by ((a.BYTES-b.BYTES)/a.BYTES) desc
/







any mistake is there or u wanna give any suggestion so please write to me ............

how to change sys password in Oracle 10g

 To Change the ""SYS"  Password

SQL> conn sys as sysdba
Enter password: (null) no need to give anything
Connected.
SQL> passw system
Changing password for system
New password: (new password)
Retype new password: (new password)
Password changed
SQL> conn system/(new password) 
Connected.
SQL> passw sys
Changing password for sys
New password: (new sys password )
Retype new password: (new sys password )
Password changed
SQL>




any mistake is there or u wanna give any suggestion so please write to me ............

MANAGE USER

HOW To create USER
SQL>create user chetan
  2  identified by c
  3  default tablsespace SYSTEM
  4  temporary tablespace TEMP
  5  quota 20 m on SYSTEM
  6  password expire;
-------------

change system tablespace 

SQL> create user chetan
  2  identified by c
  3  default tablespace EXAMPLE
  4  temporary tablespace TEMP
  5  quota 15m on EXAMPLE
  6  password expire;

User created.

now its created ...


TO see the session privilege

SQL>select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION

---------------------------------------------------


How to create Profile

______________________________
CREATION OF PROFILE
______________________________
create profile  profile_name limit
  sessions_per_user                 2   --
  cpu_per_session               10000   -- hunderth of seconds
  cpu_per_call                      1   -- hunderth of seconds
  connect_time              unlimited   -- minutes
  idle_time                        30   -- minutes
  logical_reads_per_session   default   -- db blocks
  logical_reads_per_call      default   -- db blocks
  -- composite_limit          default   --
  private_sga                     20M   --
  failed_login_attempts             3   --
  password_life_time               30   -- days
  password_reuse_time              12   --
  password_reuse_max        unlimited   --
  password_lock_time          default   -- days
  password_grace_time               2   -- days
  password_verify_function       null;



EXAMPLE Is blow


SQL >create profile ram limit
    sessions_per_user 2
    connect_time 30
    idle_time 30
    failed_login_attempts 4
    password_life_time 2
    password_reuse_time 5
    password_grace_time 1;
    
 Profile created.

-----------


how to see a profile

SQL> show user
USER is "SYS"

SQL> desc dba_profiles
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROFILE                                   NOT NULL VARCHAR2(30)
 RESOURCE_NAME                             NOT NULL VARCHAR2(32)
 RESOURCE_TYPE                                      VARCHAR2(8)
 LIMIT                                              VARCHAR2(40)

SQL> select profile from dba_profiles;

PROFILE
------------------------------
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT

PROFILE
------------------------------
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
MONITORING_PROFILE
MONITORING_PROFILE
MONITORING_PROFILE
MONITORING_PROFILE
MONITORING_PROFILE
MONITORING_PROFILE

PROFILE
------------------------------
MONITORING_PROFILE
MONITORING_PROFILE
MONITORING_PROFILE
MONITORING_PROFILE
MONITORING_PROFILE
MONITORING_PROFILE
MONITORING_PROFILE
MONITORING_PROFILE
MONITORING_PROFILE
MONITORING_PROFILE
RAM

PROFILE
------------------------------
RAM
RAM
RAM
RAM
RAM
RAM
RAM
RAM
RAM
RAM
RAM

PROFILE
------------------------------
RAM
RAM
RAM
RAM

48 rows selected.
---------------------------------------------------------------------------
SQL> select * from dba_profiles where profile='RAM';

PROFILE                        RESOURCE_NAME                    RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
RAM                            COMPOSITE_LIMIT                  KERNEL
DEFAULT

RAM                            SESSIONS_PER_USER                KERNEL
2

RAM                            CPU_PER_SESSION                  KERNEL
DEFAULT


PROFILE                        RESOURCE_NAME                    RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
RAM                            CPU_PER_CALL                     KERNEL
DEFAULT

RAM                            LOGICAL_READS_PER_SESSION        KERNEL
DEFAULT

RAM                            LOGICAL_READS_PER_CALL           KERNEL
DEFAULT


PROFILE                        RESOURCE_NAME                    RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
RAM                            IDLE_TIME                        KERNEL
30

RAM                            CONNECT_TIME                     KERNEL
30

RAM                            PRIVATE_SGA                      KERNEL
DEFAULT


PROFILE                        RESOURCE_NAME                    RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
RAM                            FAILED_LOGIN_ATTEMPTS            PASSWORD
4

RAM                            PASSWORD_LIFE_TIME               PASSWORD
2

RAM                            PASSWORD_REUSE_TIME              PASSWORD
5


PROFILE                        RESOURCE_NAME                    RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
RAM                            PASSWORD_REUSE_MAX               PASSWORD
DEFAULT

RAM                            PASSWORD_VERIFY_FUNCTION         PASSWORD
DEFAULT

RAM                            PASSWORD_LOCK_TIME               PASSWORD
DEFAULT


PROFILE                        RESOURCE_NAME                    RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
RAM                            PASSWORD_GRACE_TIME              PASSWORD
1


16 rows selected.
-----------------------------------------







any mistake is there or u wanna give any suggestion so please write to me ............