Oracle Database SQL and PL/SQL Tricks and Tips

Oracle Database Tricks & Tips

People in the Oracle technologies they always dealing with Oracle Database related queries, need some time help, best practices should be implement in the daily activities. Here are some common daily using SQL queries which work on 12c to 19c and later as well. 

SQL commands run on Oracle Database using sqlplus, sqldeveloper

 

How to check user exists or not on Oracle DATABASE? 

 
select username from DBA_USERS;

Find connected to which user? 

SQL> show user 

How to search for specific user in the Oracle Database?

 SELECT username from DBA_USERS where username like '%DEMO%';

 USERNAME 

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

DEMOADM 

How do you know RCU users account_status expired? 

Here we can query the username with 'PREFIX' containing word, and to know about EXPIRED account_status.

 
set pagesize 100
column username format a25

Select username, account_status from dba_users 
where username like '%DEMO%' and ACCOUNT_STATUS <> 'OPEN';

How do you reset the DB users account_status expired to OPEN? 

Here we have the solution we need to UNLOCK account and RESET the password with the following SQL qyery.

 
ALTER USER DEMO_OPSS  IDENTIFIED BY welcome1;

Note this will work for any Oracle DB user.

 

How to switch from CDB to PDB? 

Assuming you are already login to the CDB and the PDB name as MYPDB1 then following SQL command wilL help to switch to PDB.

alter session set container=MYPDB1;

How to check the number of PDBs present in CDB? 

Connect to the CDB with sys user as sysdba run the following: 

 SQL> show pdbs

 Note that if the same command executed inside the PDB it will show a single PDB to which you have connected or altered session.

How to restart a PDB?

To do this first you need to connect to your CDB and then issue the following SQL command in sqlplus prompt 

alter pluggable database PDBNAME close immediate;
alter pluggable database PDBNAME open;

How to check the number of processes running in your Oracle Database?

In SQLPLUS you can runt he following SHOW command:

show parameter processes

How to check the number of OPEN cursors Configured?

The following SQL command will help you to get the how many OPEN cursor can be used from your Database.

  show parameter open_cursors
  

How change the open cursors limit?

How to change the processes limit?

How to shutdown the Oracle  Database from Oracle SQLPLUS prompt?

We can use 'shutdown' command with options most commonly we use 'immediate' option in non-production Databases, we can also use 'shut' short command

shutdown immediate
or 
shut immediate

Note that never leave shut command without any argument which may leads you to wait indefinite period of time. 

How to clear the multiple break lines in the SQLPLUS? or How you can set page size?

set pages 200

Here 200 is random value you can change according to your need.

How create a  Oracle database as standalone with AL32UTF8 characterSet?

We can create database using dbca command utility, which we can run in CLI mode by giving arguments as follows:

 

  dbca -silent -createDatabase -templateName General_Purpose.dbc 
  -gdbname DEMODB -sid DEMODB -sysPassword manager -systemPassword manager 
  -emConfiguration none -datafileDestination /opt/oradata/ -redoLogFileSize 100 
  -asmsnmpPassword manager -diskGroupName data -characterSet AL32UTF8 -totalMemory 2048

How to resolve the QUOTA for a user on tablespace?

Issue # Exception was thrown: ORA-01950: no privilleges on tablespace 'USERS' 

Solution: Change the QUOTA in SOME diskspace say 100M for the 'USERS' tablespace to which user you encountered this issue. 

Steps to follow: 

1. First connect to the Database box, and connect as SYS as SYSDBA

sqlplus / as sysdba

2. This will be in CDB we need to switch to our PDB that is ORCLPDB1

alter session set container=ORCLPDB1;

3. allocate the quota space for the USERS tablespace for your user as sample showing here as demouser

alter user demouser QUOTA 100M on USERS;

that's it resolved!

How do you know Oracle APEX instalbashled version?

Login with sys or system user as sysdba run the following SQL query to know the Oracle APEX version from SQL Prompt: 

select VERSION 
 from dba_registry 
 where COMP_NAME='Oracle Application Express';
  

How do you know the APEX installed on CDB with PDB?

Login to the sys as sysdba  into your CDB run the following SQL query:

  select COMP_ID, VERSION, STATUS 
   from CDB_REGISTRY 
   where COMP_ID='APEX' 
   order by CON_ID;

COMP_ID                        VERSION                        STATUS
------------------------------ ------------------------------ -----------
APEX                           20.1.0.00.13                   VALID

To know which PDB contains this APEX installed

select r.COMP_NAME, r.VERSION, c.NAME, c.CON_ID
from CDB_REGISTRY r, V$CONTAINERS c
where r.CON_ID=c.CON_ID and r.COMP_ID='APEX'
order by CON_ID;

Write your recent Database Query that saved your time a lot, Share that which you frequently use to resolve an issuee comments we appreciate your contributions!!!