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