User Administration

Creating User : 

SQL> show user;
USER is "SYS"

SQL> create user sairam identified by saipassword;
User created.

SQL> select default_tablespace from dba_users where username='SAIRAM';
DEFAULT_TABLESPACE
------------------------------

SYSTEM

SQL> alter user sairam default tablespace xxtrainee;
User altered.

SQL> select default_tablespace from dba_users where username='SAIRAM';
DEFAULT_TABLESPACE
------------------------------

XXTRAINEE

SQL> select max_bytes from dba_ts_quotas where username='SAIRAM';

no rows selected

SQL> alter user sairam quota 100m on xxtrainee;

User altered.

SQL> select max_bytes from dba_ts_quotas where username='SAIRAM';

 MAX_BYTES

----------

 104857600

SQL> create user sairam identified by saipassword default tablespace xxtrainee quota 150m on xxtrainee;

User created.

Once we create a user account we need to specify the previleges. If no privilege is given to the user. He can't connect to the DB.

[oravis@test ~]$ sqlplus sairam/saipassword;

ERROR:

ORA-01045: user SAIRAM lacks CREATE SESSION privilege; logon denied

Granting Privileges :

SQL> grant create session, create table to sairam;

Grant succeeded.

SQL> select privilege from dba_sys_privs where grantee='SAIRAM';

PRIVILEGE

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

CREATE TABLE

CREATE SESSION

CREATE SESSION:  

SQL> connect sairam/saipassword

Connected.

SQL> show user

USER is "SAIRAM"

[oravis@test ~]$ sqlplus sairam/saipassword

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 31 12:26:53 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Tue Jul 31 2018 12:26:34 +03:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show user

USER is "SAIRAM"

CREATE TABLE:

SQL> create table business (customer_id number(10),customer_name varchar2(20),corporate_name varchar2(20),limit_iqd number(15),password varchar2(10),card_type varchar2(10));

Table created.

SQL> desc business;

 Name                                      Null?    Type

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

 CUSTOMER_ID                                        NUMBER(10)

 CUSTOMER_NAME                                      VARCHAR2(20)

 CORPORATE_NAME                                     VARCHAR2(20)

 LIMIT_IQD                                          NUMBER(15)

 PASSWORD                                           VARCHAR2(10)

 CARD_TYPE                                          VARCHAR2(10)

SQL> insert into business (customer_id,customer_name,corporate_name,limit_iqd,password,card_type) values (999,'zashwanth','sanielsoft',50621,'pass542','corp');

1 row created.

SQL> select *from business;

CUSTOMER_ID CUSTOMER_NAME        CORPORATE_NAME        LIMIT_IQD PASSWORD

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

CARD_TYPE

----------

999     zashwanth      sanielsoft    50621     pass542     corp

SQL> commit;

Commit complete.

SQL> select tablespace_name from user_segments where segment_name='BUSINESS';

TABLESPACE_NAME

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

XXTRAINEE

SQL> show user

USER is "SAIRAM"

SQL> select tablespace_name,extents from user_segments where segment_name='BUSINESS';

TABLESPACE_NAME                   EXTENTS

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

XXTRAINEE                               1

SQL> select extent_id,bytes from user_extents where segment_name='BUSINESS';

EXTENT_ID      BYTES

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

         0      65536

(Default extent size is - 8blocks=64kb)

Changing User Password:

SQL> alter user sairam identified by sai123;
User altered.

To make user to change password

SQL> alter user sairam password expire;

User altered.

when user logs in next session will be prompted for new password

[oravis@test ~]$ sqlplus sairam/sai123

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 31 13:55:01 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:

ORA-28001: the password has expired

Changing password for sairam

New password:

Retype new password:

Password changed

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter user sairam quota 5m on test;

User altered.

SQL> select default_tablespace from dba_users where username='SAIRAM';

DEFAULT_TABLESPACE

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

XXTRAINEE

SQL> select tablespace_name,bytes,max_bytes from dba_ts_quotas where username='SAIRAM';

TABLESPACE_NAME                     BYTES  MAX_BYTES

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

TEST                                    0    5242880

XXTRAINEE                               0  157286400

SQL> create table t1 (n number) tablespace test;

Table created.

SQL> desc t1;

 Name                                      Null?    Type

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

 N                                                  NUMBER

SQL> insert into t1 values(340);

1 row created.

SQL> select segment_name,tablespace_name from user_segments;

SEGMENT_NAME

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

TABLESPACE_NAME

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

T1

TEST

SQL> select tablespace_name,bytes,max_bytes from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES

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

TEST                                65536    5242880

XXTRAINEE                               0  157286400

sys SQL> select tablespace_name,bytes,max_bytes from dba_ts_quotas where username='SAIRAM';

TABLESPACE_NAME                     BYTES  MAX_BYTES

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

TEST                                65536    5242880

XXTRAINEE                               0  157286400

SQL> alter user sairam quota 0m on test;

User altered.

SQL> select tablespace_name,bytes,max_bytes from dba_ts_quotas where username='SAIRAM';

TABLESPACE_NAME                     BYTES  MAX_BYTES

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

XXTRAINEE                               0  157286400

SQL> create table t2(n number) tablespace test;

Table created.

SQL> insert into t2 values (100);

insert into t2 values (100)

*ERROR at line 1:

ORA-01536: space quota exceeded for tablespace 'TEST'

Lock/Unlock User :

sys SQL> alter user sairam account lock;

User altered.

SQL> conn sairam/sai123

ERROR:

ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.

sys SQL> select account_status from dba_users where username='SAIRAM';

ACCOUNT_STATUS
--------------------------------
LOCKED

SQL> alter user sairam account unlock;
User altered.

SQL> conn sairam/sai123;

Connected.

SQL> select default_tablespace,temporary_tablespace,created,account_status from dba_users where username='SAIRAM';

DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED

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

ACCOUNT_STATUS

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

XXTRAINEE                      TEMP                           31-JUL-16

OPEN

Drop User :

SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"

SQL> drop user sairam ;
User dropped.

ORA-01922: CASCADE must be specified to drop 'SAIRAM'

SQL> drop user sairam cascade;
User dropped.

                                                

Undo Tablespace

Undo Consists records of the actions of transactions, primarily before they are committed. These information is used to rollback, or undo, changes to the database.

Undo records are used to:

• Roll back transactions when a ROLLBACK statement is issued 
• Recover the database
• Provide read consistency
• Analyze data as of an earlier point in time by using Oracle Flashback Query
• Recover from logical corruptions using Oracle Flashback features

When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the data files. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.

Undo_Retention:  A database can use only one active Undo TS at a time. Undo data must be retain till commit or rollback the transaction. After the completion of transaction it may overwrites the previous instructions. It is done automatically by Oracle only. In this case we can retain the data by using Undo_Retention time.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled          boolean     TRUE
undo_management            string        AUTO
undo_retention                  integer      900

undo_tablespace               string        APPS_UNDOTS1


SQL> create undo tablespace undots2 datafile '/u03/oravis/VIS/data/undots2.dbf'  size 50m autoextend on;


Tablespace created.

SQL> select file_name,tablespace_name from dba_data_files where tablespace_name='UNDOTS2';                     
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u03/oravis/VIS/data/undots2.dbf

UNDOTS2

SQL> show parameter undo;

NAME                                 TYPE        VALUE

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

temp_undo_enabled                    boolean     TRUE

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      APPS_UNDOTS1

SQL> alter system set undo_tablespace=undots2 scope=both;

System altered.

SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     TRUE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTS2 

Temporary Tablespace

Temporary Tablespace

Each database should have one temporary tablespace that is created when the database is created. Temporary tablespaces are used for special operations, particularly for sorting data results on disk and for hash joins in SQL. For SQL with millions of rows returned, the sort operation is too large for the RAM area and must occur on disk.

The only other difference is that a temporary tablespace uses temporary files (also called tempfiles) rather than regular datafiles. Thus, instead of using the datafiles keyword you use the tempfiles keyword when issuing a create, drop or alter tablespace command.

SQL> select name from v$tempfile;

NAME

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

/u03/oravis/VIS/data/temp01.dbf

/u03/oravis/VIS/data/temp02.dbf

/u03/oravis/VIS/data/temp0001.dbf

SQL> select tablespace_name,contents from dba_tablespaces;

TABLESPACE_NAME                CONTENTS

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

SYSTEM                         PERMANENT

SYSAUX                         PERMANENT

TEMP                             TEMPORARY

XXTRAINEE                    PERMANENT

ASSOCIATES                  PERMANENT

UNDO                            UNDO

27 rows selected.

SQL> select property_name,property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME

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

PROPERTY_VALUE

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

DEFAULT_TEMP_TABLESPACE

TEMP

Create Temp TS:

SQL> create temporary tablespace temp2 tempfile '/u03/oravis/VIS/data/temp1.dbf' size 50m;

Tablespace created.

Default Temp TS:

SQL> alter database default temporary tablespace temp2;

Database altered.

SQL> select property_name,property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME

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

PROPERTY_VALUE

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

DEFAULT_TEMP_TABLESPACE

TEMP2

SQL> select name from v$tempfile;

NAME

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

/u03/oravis/VIS/data/temp01.dbf

/u03/oravis/VIS/data/temp02.dbf

/u03/oravis/VIS/data/temp0001.dbf

/u03/oravis/VIS/data/temp1.dbf

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> select property_name,property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME

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

PROPERTY_VALUE

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

DEFAULT_TEMP_TABLESPACE

TEMP

SQL> select file_name,tablespace_name from dba_temp_files;

FILE_NAME

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

TABLESPACE_NAME

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

/u03/oravis/VIS/data/temp01.dbf

TEMP

/u03/oravis/VIS/data/temp02.dbf

TEMP

/u03/oravis/VIS/data/temp0001.dbf

TEMP

FILE_NAME

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

TABLESPACE_NAME

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

/u03/oravis/VIS/data/temp1.dbf

TEMP2

drop tablespace temp TS
SQL> drop tablespace temp including contents and datafiles;

SQL> drop tablespace temp;

drop datafile from temp TS

SQL> alter database tempfile '/u03/oravis/VIS/data/temp0001.dbf' drop including contents and datafiles;

Big File Tablespace

Bigfile Tablespace

Bigfile Tablespace 

• An Oracle database can contain both bigfile and smallfile tablespaces. 

• System default is to create the traditional smallfile tablespace. 

• The SYSTEM and SYSAUX tablespaces are always created using the system default type.

• Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space management.

• Locally managed undo tablespace and Temporary tablespace

Bigfile Tablespace Benefits :

• The bigfile tablespace simplifies large database tablespace management by reducing the number of datafiles needed. 

• The bigfile tablespace simplifies datafile management with Oracle-managed files and Automated Storage Management (ASM) by eliminating the need for adding new datafiles and dealing with multiple files. 

• The bigfile tablespace allows you to create a bigfile tablespace of up to eight exabytes (eight million terabytes) in size, and significantly increase the storage capacity of an Oracle database. 

• The bigfile tablespace follows the concept that a tablespace and a datafile are logically equivalent.

A bigfile tablespace (BFT) is a tablespace containing a single file that can have a very large size. The traditional tablespace is referred to as a smallfile tablespace (SFT). A smallfile tablespace contains multiple, relatively small files. The bigfile tablespace has the following characteristics:

There are two exceptions when bigfile tablespace segments are manually managed: They are 

Bigfile tablespaces are intended to be used with Automated Storage Management (ASM) (see Chapter 1) or other logical volume managers that support RAID.

However, you can also use The bigfile tablespace without ASM.

Bigfile tablespace has the following benefits:

SQL> create bigfile tablespace bigts datafile '/u03/oravis/VIS/data/bigts01.dbf' size 500m;
Tablespace created.

We can't add another data file to this tablespace. We can create another bigfile tablespace but only one bigfile tablespace can be set as default bigfile tablespace.

DataFile Operations

               Datafile Operations

Resize:

SQL> select file_name,bytes from dba_data_files where tablespace_name='XXTRAINEE';

FILE_NAME

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

     BYTES

----------

/u03/oravis/VIS/data/xxtrainee01.dbf

 209715200

/u03/oravis/VIS/data/xxtrainee02.dbf

 209715200

SQL> alter database datafile '/u03/oravis/VIS/data/xxtrainee01.dbf' resize 250m;

Database altered.

SQL> select file_name,bytes from dba_data_files where tablespace_name='XXTRAINEE';

FILE_NAME

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

     BYTES

----------

/u03/oravis/VIS/data/xxtrainee01.dbf

 262144000

/u03/oravis/VIS/data/xxtrainee02.dbf

 209715200

We can also decrease the size but it should have free space

Autoextensible:

SQL> select file_name,bytes,autoextensible from dba_data_files where tablespace_name='XXTRAINEE';

FILE_NAME

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

     BYTES AUT

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

/u03/oravis/VIS/data/xxtrainee01.dbf

 262144000 NO

/u03/oravis/VIS/data/xxtrainee02.dbf

 209715200 NO

SQL> alter database datafile '/u03/oravis/VIS/data/xxtrainee01.dbf' autoextend on maxsize 250m;

Database altered.

SQL> select file_name,bytes,autoextensible from dba_data_files where tablespace_name='XXTRAINEE';

FILE_NAME

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

     BYTES AUT

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

/u03/oravis/VIS/data/xxtrainee01.dbf

 262144000 YES

/u03/oravis/VIS/data/xxtrainee02.dbf

 209715200 NO

Offline/Online:

SQL> alter database datafile '/u03/oravis/VIS/data/xxtrainee02.dbf' offline;

Database altered.

SQL> select file_name,online_status from dba_data_files where tablespace_name='XXTRAINEE';

FILE_NAME

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

ONLINE_

-------

/u03/oravis/VIS/data/xxtrainee01.dbf

ONLINE

/u03/oravis/VIS/data/xxtrainee02.dbf

RECOVER

SQL> recover datafile '/u03/oravis/VIS/data/xxtrainee02.dbf';

Media recovery complete.

SQL> select file_name,online_status from dba_data_files where tablespace_name='XXTRAINEE';

FILE_NAME

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

ONLINE_

-------

/u03/oravis/VIS/data/xxtrainee01.dbf

ONLINE

/u03/oravis/VIS/data/xxtrainee02.dbf

OFFLINE

SQL> alter database datafile '/u03/oravis/VIS/data/xxtrainee02.dbf' online;

Database altered.

SQL> select file_name,online_status from dba_data_files where tablespace_name='XXTRAINEE';

FILE_NAME

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

ONLINE

/u03/oravis/VIS/data/xxtrainee01.dbf

ONLINE

/u03/oravis/VIS/data/xxtrainee02.dbf

ONLINE

Conditions to Drop a Datafile from Tablespace:

·         It should not be the first Datafile

·         It should be Empty

Renaming Datafile : 

Relocating Data file is introduced in 12c

SQL> alter database move datafile '/u03/oravis/VIS/data/xxtrainee01.dbf' to '/u03/oravis/VIS/data/train01.dbf';

Database altered.

SQL> select file_name from dba_data_files where tablespace_name='XXTRAINEE';

FILE_NAME

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

/u03/oravis/VIS/data/train01.dbf

/u03/oravis/VIS/data/xxtrainee02.dbf

SQL> alter database move datafile '/u03/oravis/VIS/data/train01.dbf' to '/u03/oravis/VIS/data/xxtrainee01.dbf';

Database altered.

SQL>  select file_name from dba_data_files where tablespace_name='XXTRAINEE';

FILE_NAME

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

/u03/oravis/VIS/data/xxtrainee01.dbf

/u03/oravis/VIS/data/xxtrainee02.dbf

Relocating Datafile :

 SQL> alter database move datafile '/u03/oravis/VIS/data/xxtrainee01.dbf' to '/u03/oravis/VIS/xxtrainee01.dbf';

Database altered.

SQL> select file_name from dba_data_files where tablespace_name='XXTRAINEE';

FILE_NAME

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

/u03/oravis/VIS/data/xxtrainee02.dbf

/u03/oravis/VIS/xxtrainee01.dbf

SQL>  alter database move datafile '/u03/oravis/VIS/xxtrainee01.dbf' to '/u03/oravis/VIS/data/xxtrainee01.dbf';

Database altered.

SQL> select file_name from dba_data_files where tablespace_name='XXTRAINEE';

FILE_NAME

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

/u03/oravis/VIS/data/xxtrainee01.dbf

/u03/oravis/VIS/data/xxtrainee02.dbf

We can also move and rename datafile at a time by giving location Path and newName