Quick Learn | FAQ's | Technical Solutions

Run below queries to check the Inactive sessions in Oracle database. Login from "sys as SYSDBA" option from any client tool(SQL developer) --Below command use to keep password life unlimited, Password won't expire ALTER PROFILE DEFAULT limit password_life_time UNLIMITED; --Increase the process and job queue process values show parameter process; show parameter spfile; show con_name; alter system set PROCESSES=1000 scope = spfile; alter system set job_queue_processes=500 scope = spfile;

Run below queries to check the Inactive sessions in Oracle database. Login from " sys as Sysdba " option from any client tool(SQL developer) select username, program, count(1) as "Inactive_Count" from v$session where status='INACTIVE' group by username, program; select status, count(1) from v$session group by status; SELECT count(sess.process), sess.status, sess.username, sess.schemaname FROM v$session sess, v$sql sql WHERE sql.sql_id(+) = sess.sql_id AND sess.type = 'USER' GROUP BY sess.status, sess.username, sess.schemaname; SELECT sess.*, sql.sql_text FROM v$session sess, v$sql sql WHERE sql.sql_id(+) = sess.sql_id AND sess.type = 'USER'; select p.username "OSUSERNAME", p.terminal,p.program, s.username "DBUSERNAME",s.command,s.status, s.server,s.process,s.machine,s.port,s.terminal,s.program, s.sid,s.serial#,p.spid FROM v$session s,v$process p WHERE p.addr=s.paddr and s.status='INACTIVE' order by 1,4;

STOP Database Service Navigate to database home path e.g. cd /home/oracle/app/oracle/product/12.2.0/dbhome_1/bin ./sqlplus "sys/Password as SYSDBA" SQL> shutdown immediate; STOP Database(DB) listener service ./lsnrctl stop ./lsnrctl status ps -ef | grep java Check there should not be any JAVA processes are running… START Database Service Once unix machine is started back in running mode… START (In below sequence only) cd /home/oracle/app/oracle/product/12.2.0/dbhome_1/bin Check listener services are running or not! ./lsnrctl start ./lsnrctl status Login to DB as sysdba ./sqlplus "sys/Password as SYSDBA" SQL> startup; ORACLE instance started. Total System Global Area     6241124352 bytes Fixed Size                              8634512 bytes Variable Size                         1258295152 bytes Database Buffers    ...

SQL Queries for learning: This application is used to keep track of information about employees of a company. It also stores the information about departments and leaves taken by employees. You are required to create tables (as shown below) and insert data into each of the table. Apart from giving you an idea about how to create tables with constraints, it also enables you to understand how to create queries, pl/sql programs, stored procedures and functions and database triggers. However, note, this sample collection of tables is only for learning purpose. Required Tables The following are the set of tables to be created to store the required information. Table Name Meaning DEPT Stores the details of departments of the company. EMPLOYEE Stores information about all the employees of the company. LEAVES Stores information about types of leaves available EMP_LEAVES Stores information about leaves taken by the employees. Structure of Tables The following is the structure of each of the req...

DECLARE TYPE TT IS TABLE OF EMP%ROWTYPE; L_TAB TT; CURSOR C_DATA IS  SELECT * FROM EMP; BEGIN OPEN C_DATA FETCH C_DATA BULK COLLECT INTO L_TAB LIMIT 10000; EXIT WHEN L_TAB.COUNT = 0; DBMS_OUTPUT.PUTLINE(LTAB.COUNT); END LOOP; CLOSE C_DATA; END; /

Normalization: Its a process of organizing data in database. This includes creating tables and establishing relationship between these tables. The result in database consistency, flexible and reduce redundancy. First Normal Form : Eliminate repeating group Second Normal Form : Eliminate redundant data, related table with foreign key Third Normal Form : Eliminate fields that do not depend on the key Employee & Department table scenario: DEPT (DEPTNO, DNAME, LOC) EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) DDL Create Scripts: create table dept (     deptno     number(2,0),     dname      varchar2(14),     loc        varchar2(13),     constraint pk_dept primary key (deptno)   ); create table emp (     empno    number(4,0),     ename    varchar2(10),     job      varchar2(9), ...