DATABASE

CONFIGURE AND INSTALLATION OF ORACLE 12C IN OEL 7.0

Configure and Installation of Oracle 12c in OEL 7.0

===================================================

Installation of OEL 7.0

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

Network :- Choose Nat

#dhclient -v (for binding the ip)

#vi /etc/hosts (modify and add the last line as the machine ip)

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

192.168.0.215 ol7.localdomain ol7

#ping 192.168.0.215

#ping ol7

#ping ol7.localdomain

#ping google.com

#yum install oracle-rdbms-server-12cR1-preinstall -y

#yum update -y

#vi /etc/sysctl.conf

fs.file-max = 6815744

kernel.sem = 250 32000 100 128

kernel.shmmni = 4096

kernel.shmall = 1073741824

kernel.shmmax = 4398046511104

kernel.panic_on_oops = 1

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

net.ipv4.conf.all.rp_filter = 2

net.ipv4.conf.default.rp_filter = 2

fs.aio-max-nr = 1048576

net.ipv4.ip_local_port_range = 9000 65500

:wq

#/sbin/sysctl -p

#/sbin/sysctl -a

Create the new groups and users.

#groupadd -g 54321 oinstall

#groupadd -g 54322 dba

#groupadd -g 54323 oper

#groupadd -g 54324 backupdba

#groupadd -g 54325 dgdba

#groupadd -g 54326 kmdba

#groupadd -g 54327 asmdba

#groupadd -g 54328 asmoper

#groupadd -g 54329 asmadmin

#useradd -u 54321 -g oinstall -G dba,oper oracle

#vi /etc/selinux/config

(change to permissive)

SELINUX=permissive

:wq

#setenforce permissive

#system stop firewalld

#system disable firewalld

#mkdir -p /u01/app/oracle/product/12.1.0.2/db_1

#chown -R oracle:oinstall /u01

#chmod -R 775 /u01

#xhost +

Configure the bash profile

#vi .bash_profile

(go to the last and add those lines)

export TMP=/tmp

export TMPDIR=$TMP

export ORACLE_HOSTNAME=ol7.localdomain

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/production/12.1.0.2/db_1

export PATH=/usr/sbin:$PATH

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

:wq

#xhost +

#su - oracle

$DISPLAY=192.168.0.215:0.0;export DISPLAY

$export DISPLAY=:0.0

$xclock

$cd /install/database

$./runInstaller

Congrats...................

Table space Management

ORACLE DBA

DBA SCRIPTS

POSTGRES SCRIPTS

R-STUDIO

Search.....

HOME / DATABASE / TABLESPACE MANAGEMENT IN ORACLE

Tablespace Management In Oracle

7647 views 1 min , 56 sec read 3

What is a tablespace and datafile:

Tablespace is the primary logic structure of the oracle database. It consists of one or more physical datafiles. Datafiles physical stores database data in storage.(DISKS) . So when we create a datafile of 30G in database, it will eat 30G of space from defined storage. All the table or index segment is created in tablespace only.

Create a NEW tablespace:

Create tablespace DATA datafile '/u01/dbaclass/oradata/data01.dbf' size 5G autoextend on next 500M;

Here, 500M of extent will be added to the datafile automatically, when the space is required.

For creating a tablespace on ASM disk group:

– With OMF file system:

Create tablespace DATA datafile '+DATAG' size 5G autoextend on next 500M;

--- Else we can give the complete path also:

Create tablespace DATA datafile '+DATAG/oradata/datafile/data01.dbf' size 5G autoextend on next 500M;

Adding a datafile:

alter tablespace DATA add datafile '/u01/dbaclass/oradata/data02.dbf' size 2G;

Resize a datafile: ( To increase or decrease the datafile)

alter database datafile '/u01/dbaclass/oradata/data02.dbf' resize 3G;

Change default tablespace:

Every database will have one default tablespace. If any user is created without mentioning any tablespace_name, then that schema objects will be created under default tablespace. – Get the current default tablespace:

SELECT PROPERTY_VALUE

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

ALTER DATABASE DEFAULT TABLESPACE DATATBS;

Rename a tablespace:

set pagesize 200

set lines 200

col file_name for a57

SQL❯ select file_id,file_name,tablespace_name from dba_data_files where file_id=37;

FILE_ID FILE_NAME TABLESPACE_NAME

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

37 /home/oracle/app/oracle/oradata/cdb1/testin1.dbf TESTING

--- Rename the tablespace_name from TESTING to PRODUCING;

SQL❯ alter tablespace TESTING rename to PRODUCING;

Tablespace altered.

SQL❯ select file_id,file_name,tablespace_name from dba_data_files where file_id=37;

FILE_ID FILE_NAME TABLESPACE_NAME

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

37 /home/oracle/app/oracle/oradata/cdb1/testin1.dbf PRODUCING

NOTE: SYSTEM and SYSAUX tablespace cannot be renamed.

Drop a tablespace:

-- Drop a tablespace without removing the physical database files.

SQL❯ drop tablespace TESTING;

Tablespace dropped.

SQL❯ select file_name from dba_data_files where tablespace_name='TESTING';

no rows selected

<i>-- Drop tablespace including the physical datafiles.</i>

SQL❯ drop tablespace TESTING including contents and datafiles;

Tablespace dropped.

Make tablespace Read only:

alter tablespace DATA2 READ ONLY;

alter tablespace DATA2 read write;

Take tablespace offline:

DBAs usually make the tablespace offline, while restoring or recovering any underlying corrupt datafile.

-- To make a tablespace offline

ALTER TABLESPACE DATATS OFFLINE;

-- To make it online

ALTER TABLESPACE DATATS ONLINE;

Create a temporary tablespace

CREATE TEMPORARY TABLESPACE TEMP1 '/u01/dbaclass/tempfile/temp01.dbf' size 1G autoextend on next 200M;

Adding a tempfile:

alter tablespace TEMP1 add tempfile '/u01/dbaclass/tempfile/temp02.dbf' size 1G autoextend on next 200M;

Resize tempfile:

alter database tempfile '/u01/dbaclass/tempfile/temp02.dbf' resize 2G;

Drop tempfile:

It will drop and remove the tempfile from physical location.

ALTER DATABASE TEMPFILE '/u01/dbaclass/tempfile/temp02.dbf' DROP INCLUDING DATAFILES;

Change default temporary tablespace:

When we create a new user, we assign a default temporary tablespace. If nothing is assigned, then it will use the default temporary tablespace set a database level.

Get the current default temporary tablespace for the database

SQL❯ SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE

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

TEMP

-- Set New default temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

-- Check the new default temp tablespace

SQL❯ SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE

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

TEMP2

DATAFILETABLESPACETEMPUNDO

DATABASE

CONFIGURATION OF LISTENER AND TNS SERVICE

CONFIGURE LISTENER AND TNS SERVICE

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

LISTENER SHOULD BE CREATED IN HRMS DATABASE AND TNS SHOULD BE CREATED IN CATDB

Target Database Side(hrms)

Catalog Database Side(catdb)

$export ORACLE_SID=hrms

$sqlplus / as sysdba

sql>startup

sql>archive log list

(should be enabled)

create listener.ora file manually

[oracle@mac1 admin]$ cat listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = mac1.oracle.com)(PORT = 7777))

#(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

SID_LIST_LISTENER =

(SID_LIST=

(SID_DESC=

(SID_NAME=hrms)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

)

ADR_BASE_LISTENER = /u01/app/oracle

:wq

$lsnrctl start LISTENER

create password file

$cd $ORACLE_HOME/dbs

$orapwd file=orapwhrms password=sys force=y

Catalog Database Side

=====================

$export ORACLE_SID=catdb

$sqlplus / as sysdba

sql>startup mount;

sql>alter database archivelog;

sql>alter database open;

Create tnsnames.ora file manually

+++++++++++++++++++++++++++++++++++++++

[oracle@lnx02 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

TO_HRMS =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = lnx01.oracle.com/or ip address of target database)(PORT = 7777))

)

(CONNECT_DATA =

(SERVICE_NAME = hrms)

)

$tnsping to_hrms

USER MANAGEMENT-04

Let us we can discuss about roles in user management first we need know

what is roles ? how it ll be different from profiles?

Roles:

- Role is a set of privileges that can be granted to users or to other roles

- We can add privileges to roles and then grant the role to a user.

Difference between roles and privileges

- Profiles are set of authorization objects , but roles are set of profiles

-Without giving profile we can't create a role,but profile can exist/function in user master record without being role. which means that, when you assign a Role, without a profile generated for that Role, to a user, the user will not be able to perform any activity. but if however you assign a Profile to a user, he will be able to perform the activities/functions, attributed to that Profile.

-role can't be effectively impact on session (connected) user views of roles

1) USER_ROLE_PRIVS

2)ALL_ROLE_PRIVS

3)ROLE_ROLE_PRIVS

4)DBA_ROLE_PRIVS

Creating role

S-1: logged on sysdba user and created a new user called 'Mike'

SQL> create user Mike identified by mike default tablespace users quota 5m on users;

S-2: Now i 'm open the new session try to log on Mike users

oracle@oracle11g~]$export ORACLE_SID=orcl

oracle@oracle11g~]$sqlplus Mike/mike

ORA-01045 user mike lacks CREATE SESSION privilege logon denied

here we have to grant the user creation session system privilege or in order to grant role

S-3 let us go to sysdba login and grant connect role to mike

SQL>grant connect to mike;

S-4; logon mike user

SQL>sqlplus Mike/mike

connected instance

SQL>select * from Session_roles;

ROLE

--------

CONNECT

let us mike user try to create table

SQL> create table r (sal number);

sql error insufficient privileges

which means mike user doesn't have create table system privilege s-5 so let us creating role query without password

SQL>create role manager;

grant system privileges to manager

SQL>grant create table, create view to manager;

assign the role manager to mike

SQL>grant manager to Mike;

we can create role with password also

SQL> create role accounts identified by acco123;

grant object privilge

SQL> grant select on scott.emp to accounts;

S-6 after assign Manager role let us try create table from mike side

SQL> create table r (sal number);

sql error ORA-01031 insufficient privileges

the same error has throughout because role will get impact the user not immediately while user session is active so disconnect the session again we can logged in as Mike user

SQL> create table r (sal number);

created

check our roles

SQL>select * from Session_roles;

ROLE

--------

CONNECT

MANAGER

Assign one role into another

S-1 logon sys user grant the role accounts into manager

SQL>grant accounts to manager;

in above we assigned role accounts into manager

S-2 let us check mike user disconnect mike user session and again logon

SQL>select * from scott.emp;

it's working fine so check roles which has been assigned to mike

SQL>select * from Session_roles;

ROLE

--------

CONNECT

MANAGER

ACCOUNTS

Drop roles

S-1; logon on sys user and drop the roles

SQL> drop role manager;

S-2 check the mike session after drop manager role

SQL>select * from Session_roles;

ROLE

--------

CONNECT

ACCOUNTS

Manager role has been gone which means once the role is dropped its impact simultaneously to assigned users

USER MANAGEMENT-03

Let us we discuss about user management another type managing privilege

what is privilege / User privilege?

- Privilege is nothing but to give users access rights for that particular objects in oracle

- Privileges are granted or revoked either by the instance administrator user

- A user privilege is a right to execute a particular type of SQL statement, or a right to access another user's object

Types of Privileges

privilege types

*System privilege or System level privilege *Object Privilege or object level privilege

Object Privilege ( with grant option )

-The listed of objects are assigned to roles or users

Privilege Description

SELECT Privilege to perform SELECT statements on the table.

INSERT Privilege to perform INSERT statements on the table.

UPDATE Privilege to perform UPDATE statements on the table.

DELETE Privilege to perform DELETE statements on the table.

REFERENCES Privilege to create a constraint that refers to the table.

ALTER Privilege to perform ALTER TABLE statements to change the table definition.

INDEX Privilege to create an index on the table with the create index statement.

ALL All privileges on table.

Views of object privs

1)USER_TAB_PRIVS

2)ALL_TAB_PRIVS

3)ROLE_TAB_PRIVS

4)DBA_TAB_PRIVS

With grant option and cascading revoke

S-1 connect our database as sys db user

[oracle@localhost ~]$ sqlplus / as sysdba

S-2 create sample schemas

SQL> create user u01 identified by u01;

User created.

Grant rights to them

SQL> grant create session, create table to u01;

Grant succeeded.

create schema 'u02'

SQL> create user u01 identified by u02;

User created.

grant object rights to u02

SQL> grant create session to u02;

Grant succeeded.

create schema 'u03'

SQL> create user u01 identified by u03;

User created.

grant rights to u03

SQL> grant create session to u03;

Grant succeeded.

S-3 connect u01

SQL>conn u01/u01

connected.

create table t0001 from u01

SQL> create table t0001 (col1 number, col2 varchar2(100));

Table created

to give with grant option to u02 user

SQL>grant select on t0001 to u02 with grant option;

Grant succeeded.

Check user_tab_privs

SQL> column grantee format a7

SQL> column owner format a5

SQL> column table_name format a10

SQL> column grantor format a7

SQL> column privilege format a9

SQL> column grantable format a9

SQL> select * from user_tab_privs

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIE

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

U02 U01 T0001 U01 SELECT YES NO

S-4 connect u02

SQL>conn u02/u02

connected.

grant privilege select u01.t0001 to u02

SQL> grant select on u01.t0001 to u03;

Grant succeeded.

S-5 connect user u03

SQL>conn u03/u03

connected.

check the privilege given table

SQL>select * from u01.t0001;

no rows selected.

s-6 connect the user session u01 and check the user _tab_privs

SQL> select * from user_tab_privs;

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIE

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

U03 U01 T0001 U02 SELECT NO NO

U02 U01 T0001 U01 SELECT YES NO

s-7 revoke 'With grant option ' from user u01

SQL> revoke select toool from u02;

Revoke succeeded.

USER MANAGEMENT-2

what is profile ?

- Profile is a set of limits on database resources

- Once we assign the user with in the profile then that user cannot exceeds the limits

- Before creating profile we must enable resource_limit parameter

Resource Limit

- Resource limits are enforced in database profiles

- Profiles only take effect when resource limits are "turned on" for the database as a whole

Check resource_limit

=============================

SQL> show parameter resource_limit

NAME TYPE VALUE

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

resource_limit boolean FALSE

enable these parameter

============================

SQL>Alter system set resource_limit=TRUE;

System altered.

check the parameter again

SQL> show parameter resource_limit

NAME TYPE VALUE

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

resource_limit boolean TRUE

Resource Parameters

*SESSION_PER_USER - specify the no of concurrent session allow to limit the user.

*CPU_PER_SESSION - specify the CPU time limit for a session, expressed in hundredth of seconds.

*CPU_PER_CALL - Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds.

*CONNECT_TIME - Specify the total elapsed time limit for a session, expressed in minutes.

*IDLE_TIME - Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.

*LOGICAL_READS_PER_SESSION - Specify the permitted number of data blocks read in a session, including blocks read from memory and disk

*LOGICAL_READS_PER_CALL - Specify the permitted number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).

*PRIVATE_SGA - Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA), expressed in bytes.

Creating Profile Resource Parameters

===================================

SQL> Create profile my_profile LIMIT

SESSIONS_PER_USER 2

IDLE_TIME 5

CONNECT_TIME 10;

in above i'm created one profile along with limits then i created a user for profile

SQL> create user tom5 identified by tom5;

Assign the user to profile

SQL>alter user tom5 profile my_profile;

User altered.

right now i ll login as SAM user and over limitation as we assigned means already opened two sessions and i tired connect third session it ll throw an error

sqlplus tom5

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Nov 26 15:57:23 2007

Enter password:

ERROR:

ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

Lets go to 2nd step IDLE_TIME.Here we go again

SQL>select * from tab;

select * from tab

ERROR at line 1:

ORA-02396: exceeded maximum idle time, please connect again

here my session idle time is more than 5 mins that why oracle server kill mine session.

To view profile limitations

=============================

SQL>select * from dba_profiles where profile='MY_PROFILE';

PROFILE RESOURCE_NAME RESOURCE LIMIT

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

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

MY_TEST COMPOSITE_LIMIT KERNEL DEFAUL

MY_TEST SESSIONS_PER_USER KERNEL 2

MY_TEST IDLE_TIME KERNEL 5

MY_TEST CONNECT_TIME KERNEL 10

PASSWORD MANAGEMENT

*FAILED_LOGIN_ATTEMPTS - Maximum times the user is allowed in fail login before locking the user account * 10

*PASSWORD_LIFE_TIME :Number of days the password is valid before expiry * 108 days

*PASSWORD_REUSE_TIME :Number of day after the user can use the already used password * UNLIMITED

*PASSWORD_REUSE_MAX :Number of times the user can use the already used password

* UNLIMITED *PASSWORD_LOCK_TIME :Number of days the user account remains locked after failed login * 1 day

*PASSWORD_GRACE_TIME :Number of grace days for user to change password * 7 days

*PASSWORD_VERIFY_FUNCTION :PL/SQL that can be used for password verification * NO DEFAULT SETTING

*SEC_CASE_SENSITIVE_LOGON :To control the case sensitivity in passwords * TRUE

Check the profile

=============================================

only DBA role person to view this

SQL> describe 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 * from dba_profiles ;

RESOURCE_NAME RESOURCE_TYPE LIMIT

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

COMPOSITE_LIMIT KERNEL UNLIMITED

CONNECT_TIME KERNEL UNLIMITED

CPU_PER_CALL KERNEL UNLIMITED

CPU_PER_SESSION KERNEL UNLIMITED

IDLE_TIME KERNEL UNLIMITED

LOGICAL_READS_PER_CALL KERNEL UNLIMITED

LOGICAL_READS_PER_SESSION KERNEL UNLIMITED

PRIVATE_SGA KERNEL UNLIMITED

SESSIONS_PER_USER KERNEL UNLIMITED

FAILED_LOGIN_ATTEMPTS PASSWORD 10

PASSWORD_GRACE_TIME PASSWORD 7

PASSWORD_LIFE_TIME PASSWORD UNLIMITED

PASSWORD_LOCK_TIME PASSWORD 1

PASSWORD_REUSE_MAX PASSWORD UNLIMITED

PASSWORD_REUSE_TIME PASSWORD UNLIMITED

PASSWORD_VERIFY_FUNCTION PASSWORD NULL

Alter Profile

-We can alter the profile once we created

===========================================

SQL> alter profile MY_TEST LIMIT SESSIONS_PER_USER 1;

system altered.

View the profile

=================

SQL>select * from dba_profiles where profile='MY_TEST';

PROFILE RESOURCE_NAME RESOURCE LIMIT

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

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

MY_TEST COMPOSITE_LIMIT KERNEL DEFAUL

MY_TEST SESSIONS_PER_USER KERNEL 1

MY_TEST IDLE_TIME KERNEL 5

MY_TEST CONNECT_TIME KERNEL 10

Drop Profile

- Drop profile using "Drop Profile" command

- We can drop "Default Profile" - If the user has been assigned to profile we can't drop the profile so we use the CASCADE it drop the profile and it ll change user into default profile

SQL> DROP PROFILE MY_TEST;

ERROR at line 1:

ORA-02382: profile ACCOUNTANT has users assigned, cannot drop without CASCADE

SQL> DROP PROFILE MY_TEST CASCADE;

USER MANAGEMENT-1

1)Managing Users

2)Managing Privileges

3)Managing Roles

4)Managing Profiles

1 )Create User - We can create new user by using "Create User" statement -Before executing this statement we must have "Create user" system privilege -It's a powerful privilege ,a DBA or security administrator is normally have this privilege

sql>create user tom identified by tom;

i)Creating user with default tablespace:

create user tom1 identified by tom1 default tablespace users;

ii)Creating user with default and temp tablespaces:

create user tom2 identified by tom2 default tablespace users temporary tablespace temp;

ii)Allocating Space:

create user tom3 identified by tom3 default tablespace users quota 1m on users;

here i'm created a user sam and allocated space for "SAM" on zen tablespace (Note: if we not mentioned quota size on tablespace it automatically allow up to 125mb but its not showing dba_ts_quotas so we must allocate space and

one more thing bytes column is '0' that user ve unlimited space on that particular tablepsace)

2) Alter User -To change user password and account limitations -Before executing this statement we must have "Alter user" system privilege

alter user tom4 identified by tom4;-->reset password

i)Alter space with limit:

alter user tom3 quota 2m on users;

here i 'm change the user quota of tablepsce

ii)Alter space with unlimited

alter user tom3 quota unlimited on users;

To View Quota Allocation

i)DBA Level

select * from dba_ts_quotas;

ii)User Level

select * from user_ts_quotas;

To view default tablespace

SQL> select property_name,property_value from database_properties;

SQL> col property_name for a25

SQL> col property_value for a28

SQL> /

PROPERTY_NAME PROPERTY_VALUE

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

DICT.BASE 2

DEFAULT_TEMP_TABLESPACE TEMP

DEFAULT_PERMANENT_TABLESP USERS

ACE

DEFAULT_EDITION ORA$BASE

Flashback Timestamp TimeZ GMT

one

TDE_MASTER_KEY_ID

DST_UPGRADE_STATE NONE

DST_PRIMARY_TT_VERSION 11

DST_SECONDARY_TT_VERSION 0

DEFAULT_TBS_TYPE SMALLFILE

NLS_LANGUAGE AMERICAN

NLS_TERRITORY AMERICA

NLS_CURRENCY $

NLS_ISO_CURRENCY AMERICA

NLS_NUMERIC_CHARACTERS .,

NLS_CHARACTERSET WE8MSWIN1252

NLS_CALENDAR GREGORIAN

NLS_DATE_FORMAT DD-MON-RR

NLS_DATE_LANGUAGE AMERICAN

NLS_SORT BINARY

NLS_TIME_FORMAT HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY $

NLS_COMP BINARY

NLS_LENGTH_SEMANTICS BYTE

NLS_NCHAR_CONV_EXCP FALSE

NLS_NCHAR_CHARACTERSET AL16UTF16

NLS_RDBMS_VERSION 11.2.0.1.0

GLOBAL_DB_NAME TEST

EXPORT_VIEWS_VERSION 8

WORKLOAD_CAPTURE_MODE

WORKLOAD_REPLAY_MODE

NO_USERID_VERIFIER_SALT 820EA1118701F6539A50393BB68B

7AD0

DBTIMEZONE 00:00

36 rows selected.

-By using this command we can find which tablespace is set on default

To change default tablespace

====================================

alter database default tablespace users1;

-The above command replace the default tablespace

To view username and passwords

==============================

select username,password from dba_users;

To view the user password

select spare4 from users$ where username='SAM';

To view user password version

==============================

SQL> select username,password,PASSWORD_VERSIONS from dba_users where username='USA';

USERNAME PASSWORD PASSWORD

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

USA 10G 11G

Here Password_versions - Database version in which the password was created or changed To view account status

select username,account_status from dba_users ;

To unlock the user account

==============================

i)Unlock

alter user ram identified by ram account unlock;

ii)Unlock separation

alter user ram identified by ram;

alter user ram account unlock;

To lock the user account

i)Lock

alter user ram password expire account lock;

ii)Lock separation

alter user ram password expire;

alter user ram account lock;

To Check the user account by connection

===============================

SQL> grant connect ,resource to SAM;

Grant succeeded.

SQL> conn SAM/SAM

Connected.

SQL> select * from tab;

no rows selected