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.
Note this will work for any Oracle DB user.
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 promptalter 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 immediateNote 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 200Here 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 sysdba2. 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 VALIDTo 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;
