Different files involved in Oracle database

As Oracle DBA, we all know Oracle database server is combination of physical files, processes and memory, in this article we are going to discuss about different files involved in oracle database architecture

There are 3 most important files

·       Data files

·       Control files

·       Redo logs file

There are other supplementary files as well - parameter files, password files, archive log files

 

Let’s discuss some more details 

Datafiles 

Data files contains actual data of the database, data files are formatted as per block size designed during database setup.

There are few types of datafiles

 

1.    SYSTEM - this includes system datafile and is critical to database, it includes dictionary of the database

2.    User datafile - this includes application specific datafiles and contains application data and can be customized as per requirement

3.    UNDO datafile - It holds old data while performing activity in database like select, insert, update, delete, this old data can be used to revert the changes in case of recovery

4.    TEMP datafile - In case user orders data based on select query, it needs space to sort the data before returning back to user, first sorting operation is performed in memory and if memory is not sufficient then it moves to temp tablespace.

 

Control Files

Control file contains structure of the database like location of all datafiles, redo logfiles.

This is Critical file to operation of database, and it is one of first file read by instance at startup.

 

Redo logfile

This file contains changes made to the database and can be used for recovery of processes. 

copies of redo logfile are called as archived log files.

 

Other files in oracle database 

Password file - this file authenticates the use who is capable to startup the database.

parameter files- 

    This file contains list of parameters used by instance.  typically, there are two types of parameter files

PFILE - text based parameter file, change to parameter needs to be modified/added manually in file and restart is required to get it into effect. 

SPFILE - binary parameter file, change to the parameter can be carried out on the go. 

 

Read More

SQL Functions - LENGTH, ASCII, SUBSTR

In this article we will discuss few SQL functions useful while working daily. SQL functions are kind of predefined packages/procedure, can be used in different aspects, lets discuss one by one

LENGTH

Syntax - LENGTH(String)

This function returns the total number of characters present in the string

Example

SQL>  SELECT LENGTH('Interest') from dual;

 

LENGTH('INTEREST')

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

                 8

 

ASCII

Syntax – ASCII(char)

This function gives ASCII value of given character

Example

SQL>  SELECT ASCII('B') from dual;

 

ASCII('B')

----------

        66

 

SQL>  SELECT ASCII('I') from dual;

 

ASCII('I')

----------

        73

 

SUBSTR

Syntax - SUBSTR (string, position, sub-string_length)

This substring function return a portion of string, starting at character position, sub-string_length characters long.

SUBSTR calculates length using characters as defined by the input character set

SQL> select SUBSTR('entertainment',4,10) from dual;

 

SUBSTR('EN

----------

ertainment

 

SQL>

Read More

ORA-01950: no privileges on tablespace 'USERS'

 In this article, we will try to resolve error ORA-01950: no privileges on tablespace 'USERS' received during insert command,

I was able to create table under schema TECHNO_USER, but I was not able to insert data into it (as shown below)

 SQL> insert into techno_user.dummy values (10);

insert into techno_user.dummy values (10)

                        *

ERROR at line 1:

ORA-01950: no privileges on tablespace 'USERS'

Then I checked default tablespace and quota on default tablespace or any other tablespace 

SQL> select username, default_tablespace from dba_users where username='TECHNO_USER';

 

USERNAME                       DEFAULT_TABLESPACE

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

TECHNO_USER                    USERS

 

SQL> select distinct tablespace_name, USERNAME from dba_ts_quotas where USERNAME='TECHNO_USER';

 

no rows selected

 

SQL>

 

As per output of above commands, we can say this user does not have quota on any tablespace, insert/update command will require quota on tablespace, so I have granted quota on tablespace USERS with below command 

SQL> alter user TECHNO_USER quota unlimited on USERS;

 

User altered.

 

SQL> select distinct tablespace_name, USERNAME from dba_ts_quotas where USERNAME='TECHNO_USER';

 

TABLESPACE_NAME                USERNAME

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

USERS                          TECHNO_USER

 

Also read changing default tablespace

 

After doing this, I was able to resolve error and was able to insert data in the table

  

SQL> insert into techno_user.dummy values (10);

 

1 row created.

 

SQL> insert into techno_user.dummy values (20);

 

1 row created.

 

SQL>

Read More

Changing default tablespace for user in oracle

In my oracle database, one of the users was having its default tablespace as SYSAUX, and having default tablespace as SYSAUX is not a good idea in the oracle database, here we are going to discuss changing the default tablespace for oracle user

Let’s see how to check default tablespace using dba_users

SQL> select username, default_tablespace from dba_users where username='TECHNO_USER';

 

USERNAME             DEFAULT_TABLESPACE

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

TECHNO_USER          SYSAUX

 

Here we can see, default tablespace is SYSAUX, now change it USERS tablespace

SQL> alter user TECHNO_USER default tablespace USERS;

 

User altered.

 

verify the result

SQL> select username, default_tablespace from dba_users where username='TECHNO_USER';

 

USERNAME             DEFAULT_TABLESPACE

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

TECHNO_USER          USERS

 

In this we can simply alter default tablespace for any user.

Read More

Lock and Unlock table stats in oracle database

There are unlimited features provided by oracle with an upgraded version, one of the feature we are going to discuss is stats lock and unlock in the oracle database,

Assume the application team has set up stats gather job from application end but that causing issue to the database like CPU/Memory utilization getting high during the job execution, being a DBA you can ask them to stop/disable the job but in case if it’s not feasible then you can lock stats on tables and unlock whenever required.

Another scenario- you have locked a good execution plan for ETL/Job run and that should not change with stats gathered, then you can lock the stats for the table.

Below we will discuss how to do this exactly, I have a table called LOCATIONS under user TECHNO, I do not want to gather stats on the same, in the below query, if stats are locked, the output will be ALL if not then it will be blank, you can stats are not locked

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'LOCATIONS' and owner = 'TECHNO';

 

STATT

-----

 

 

SQL>

Now I will lock stats and execute the query again

SQL> exec dbms_stats.lock_table_stats('TECHNO','LOCATIONS');

 

PL/SQL procedure successfully completed.

 

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'LOCATIONS' and owner = 'TECHNO';

 

STATT

-----

ALL

 

SQL>

Now I will try to gather stats on the same table, it should throw error 

SQL> exec dbms_stats.gather_table_stats(ownname=> 'TECHNO', tabname=>'LOCATIONS');

BEGIN dbms_stats.gather_table_stats(ownname=> 'TECHNO', tabname=>'LOCATIONS'); END;

 

*

ERROR at line 1:

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at "SYS.DBMS_STATS", line 40751

 [Also Read - All About Database links]

Now we will see how to unlock stats and gather stats

SQL> exec dbms_stats.unlock_table_stats('TECHNO','LOCATIONS');

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_stats.gather_table_stats(ownname=> 'TECHNO', tabname=>'LOCATIONS');

 

PL/SQL procedure successfully completed.

 

SQL>

Read More

Create local user in oracle pluggable database

In this short article, I will give a brief idea about how to create a local user in a pluggable database and how to check its status as well.

Basically, with this multitenant architecture concept, there are two types of users common user and local user, common users are shared across multiple pluggable databases while local user is specific to a particular database, here we will discuss how to create a local user

SQL> alter session set container=technopdb; 

Session altered.

 

SQL> create user techno_user identified by test123;

 

User created.

 

SQL> grant create session to techno_user;

 

Grant succeeded.

 

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         3 TECHNOPDB                      READ WRITE NO

SQL>

SQL> select username, con_id from cdb_users where username='TECHNO_USER';

 

USERNAME                      CON_ID

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

TECHNO_USER                        3

 

SQL> select d.username, c.con_id,p.name from dba_users d, cdb_users c , v$pdbs p where d.username='TECHNO_USER' and d.username=c.username and p.con_id=c.con_id;

 

USERNAME                      CON_ID NAME

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

TECHNO_USER                        3 TECHNOPDB

 

SQL>

Here we can check, how to connect to pluggable database using  local user

if you are trying it from your local machine/desktop then below command will work fine, you just need to have ip address and port number and if you are using tnsnames then mention the in the connection string

C:\Users\****>sqlplus techno_user/test123@192.168.0.107:1521/technopdb

 

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 15 16:29:48 2021

Version 19.3.0.0.0

 

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

 

 

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 

SQL>

SQL> select * from global_name;

 

GLOBAL_NAME

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

TECHNOPDB

 

SQL> show user

USER is "TECHNO_USER"

SQL>

Read More

Save state of oracle Pluggable database (PDB)

 Oracle has introduced to save state of pluggable database in order to save time, effort and efficiency, suppose you want to keep pluggable database to open state even after restart of root container database then you can simple instruct to database the same.

Let see, how we can do this with example, first we see how to save state for open state and discard it as well

Currently pluggable database is in open mode and we want to save the same state after restart, this how we can do it

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 TECHNOPDB                      READ WRITE NO

SQL> select con_name, instance_name, state from dba_pdb_saved_states;

 

no rows selected

 

SQL> alter pluggable database technopdb save state;

 

Pluggable database altered.

 

SQL> select con_name, instance_name, state from dba_pdb_saved_states;

 

CON_NAME             INSTANCE_NAME                  STATE

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

TECHNOPDB            technodb                       OPEN

Now we will see how it really works and we will restart the database and if see it works

SQL> startup

ORACLE instance started.

 

Total System Global Area 2365584120 bytes

Fixed Size                  9270008 bytes

Variable Size             587202560 bytes

Database Buffers         1761607680 bytes

Redo Buffers                7503872 bytes

Database mounted.

Database opened.

SQL>

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 TECHNOPDB                      READ WRITE NO

SQL> select con_name, instance_name, state from dba_pdb_saved_states;

 

CON_NAME             INSTANCE_NAME                  STATE

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

TECHNOPDB            technodb                       OPEN

 

SQL>

Here we see, pluggable database got started in read write open. 

Now we will see how to discard the state. 

SQL> select con_name, instance_name, state from dba_pdb_saved_states;

 

CON_NAME             INSTANCE_NAME                  STATE

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

TECHNOPDB            technodb                       OPEN

 

SQL>

SQL> alter pluggable database technopdb discard state;

 

Pluggable database altered.

 

SQL> select con_name, instance_name, state from dba_pdb_saved_states;

 

no rows selected

 SQL>

these are mostly steps we need to perform if we need to save state of pluggable database

Read More