List all the patches that applied in oracle database

Opatch command

opatch lsinventory;

SQL Command

select * from sys.registry$history;

Automating Database Startup and Shutdown on Linux

12C

STEP 1:
Once the instance is created, edit the "/etc/oratab" file setting the restart flag for each instance to 'Y'.

ORCL:/u01/app/oracle/product/12.2.0.1/db_1:Y

STEP 2:
Create a file called "/etc/init.d/dbora" as the root user, containing the following code.
The path and instance name should be adjusted to match your installation and instance.

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the 
# Oracle database in ORA_HOME.

ORA_HOME=/u01/app/oracle/product/12.2.0.1/db_1
ORA_OWNER=oracle
export ORACLE_UNQNAME=ORCL

if [ ! -f $ORA_HOME/bin/dbstart ]
then
    echo "Oracle startup: cannot start"
    exit
fi

case "$1" in
    'start')
        # Start the Oracle databases:
        # The following command assumes that the oracle login 
        # will not prompt the user for any values
        # Remove "&" if you don't want startup as a background process.
        su $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME" &
        touch /var/lock/subsys/dbora
        ;;
    'stop')
        # Stop the Oracle databases:
        # The following command assumes that the oracle login 
        # will not prompt the user for any values
        su $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
        rm -f /var/lock/subsys/dbora
        ;;
esac

STEP 3:
Use the chmod command to set the privileges to 750.

chmod 750 /etc/init.d/dbora

STEP 4:
Associate the "dbora" service with the appropriate run levels and set it to auto-start using the following command.

chkconfig --add dbora

The relevant instances should now startup/shutdown automatically at system startup/shutdown.

For other versions and more details, Please refer to the below reference URL:
https://oracle-base.com/articles/linux/automating-database-startup-and-shutdown-on-linux

Automatically start Oracle Database and EM on Linux 7 after server reboot

Environment: Oracle database 11.2.0.4.0, Linux 7

Oracle 11g includes 2 scripts which can be used to start or shut down Oracle databases on Linux. Both scripts are installed in $ORACLE_HOME/bin and are called dbstart and dbshut. However, these scripts are not executed automatically after you reboot your server. I will explain here how you can configure that.
First, you need to make sure that any database instances you want to autostart are set to “Y” in the /etc/oratab file.

Sudo as root user

GPMPERF:/u01/app/oracle/product/11.2.0/db_1:Y

NOTE :GPMPERF is the SID

The /etc/oratab file is normally created by running the root.sh script at the end of the installation. If you don’t have the file, you can always add it to your system by creating it manually (with user root!).
Next, we are going to create 2 scripts under /home/oracle/scripts: ora_start.sh and ora_stop.sh. These scripts will call dbstart and dbshut and will also allow us to add some more actions, for example the start of the Enterprise Manager database control or any other services you might have.
$ su – oracle
$ vi /home/oracle/scripts/ora_start.sh

#!/bin/bash

# script to start the Oracle database, listener and dbconsole

. ~/.bash_profile

# start the listener and the database
$ORACLE_HOME/bin/dbstart $ORACLE_HOME

# start the Enterprise Manager db console
$ORACLE_HOME/bin/emctl start dbconsole

exit 0

$ vi /home/oracle/scripts/ora_stop.sh

#!/bin/bash

# script to stop the Oracle database, listener and dbconsole

. ~/.bash_profile

# stop the Enterprise Manager db console
$ORACLE_HOME/bin/emctl stop dbconsole

# stop the listener and the database
$ORACLE_HOME/bin/dbshut $ORACLE_HOME

exit 0

You see that inside the scripts, we are calling the .bash_profile file of the user “oracle”. This is needed to set the ORACLE_HOME environment variable.
Next, give execute rights to the scripts:
$  chmod u+x ora_start.sh ora_stop.sh
You could now test these scripts to see if they correctly shut down and start up your Oracle database.
We will now create a wrapper script that can be used to schedule as a service.
With user root, create a file called “oracle” under /etc/init.d.
$ vi /etc/init.d/oracle

#!/bin/bash
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.

# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.

ORA_OWNER=oracle
RETVAL=0

case "$1" in
    'start')
        # Start the Oracle databases:
        # The following command assumes that the oracle login
        # will not prompt the user for any values
        su - $ORA_OWNER -c "/home/oracle/scripts/ora_start.sh"
        touch /var/lock/subsys/oracle
        ;;
    'stop')
        # Stop the Oracle databases:
        # The following command assumes that the oracle login
        # will not prompt the user for any values
        su - $ORA_OWNER -c "/home/oracle/scripts/ora_stop.sh"
        rm -f /var/lock/subsys/oracle
        ;;
    *)
        echo $"Usage: $0 {start|stop}"
        RETVAL=1
esac
exit $RETVAL

$ chmod 750 /etc/init.d/oracle

To create a service of this script, run the following command:

$ chkconfig --add oracle

Next, check the script by running “service oracle stop” or “service oracle start” from the command line.

After this, it’s time for the final test: reboot your server and check if your Oracle database is automatically started after the reboot.

Manually sample [Scott] Schema creation

1. Login as sys or system
2.Run "utlsampl.sql" located in ORACLE_HOME/rdbms/admin directory.

Global temporary Table

Oracle allows us to create temporary tables to hold data just for the duration of a session or for a particular transaction.



ON COMMIT DELETE ROWS [The ON COMMIT DELETE ROWS Clause  indicates that the data should be deleted at the end of the transaction ,or end of the session]
======================
Example :

CREATE GLOBAL TEMPORARY TABLE my_test_tab
(
ID NUMBER,
description varchar2(10)
)
on commit delete rows;

SQL> insert into my_test_tab
  2  values (1,'one');

1 row created.

SQL> select count(*) from my_test_tab;

  COUNT(*)
----------
         1

SQL> commit;

Commit complete.

SQL> select count(*) from my_test_tab;

  COUNT(*)
----------
         0

ON COMMIT PRESERVE ROWS [this clause indicates that rows should persist beyond the end of the transaction.they will be removed at the end of the transaction]
========================

CREATE GLOBAL TEMPORARY TABLE test_tab
(
ID NUMBER,
description varchar2(10)
)on commit preserve rows;

SQL> insert into test_tab values(1,'one');

1 row created.


SQL> select count(*) from test_tab;

  COUNT(*)
----------
         1

SQL> commit;

Commit complete.

SQL> select count(*) from test_tab;

  COUNT(*)
----------
         1

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ sqlplus rajiv/rajiv@testdb


[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 31 18:00:14 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn rajiv/rajiv
Connected.
SQL> select count(*) from test_tab;

  COUNT(*)
----------
         0

Getting Disk I/O information

select d.name,t.name,f.phyrds,f.phywrts,f.readtim,f.writetim from
v$datafile d,
v$filestat f,
v$tablespace t
where f.file#=d.file#
and d.ts#=t.ts#

TABLESPACE SIZE DETAILS

BREAK ON REPORT
COMPUTE SUM OF tbsp_size ON REPORT
compute SUM OF used ON REPORT
compute SUM OF free ON REPORT
COL tbspname FORMAT a20 HEADING 'Tablespace Name'
COL tbsp_size FORMAT 999,999 HEADING 'SIZE|(MB)'
COL used FORMAT 999,999 HEADING 'Used|(MB)'
COL free FORMAT 999,999 HEADING 'Free|(MB)'
COL pct_used FORMAT 999 HEADING '%Used'
select df.tablespace_name tbspname,
sum(df.bytes)/1024/1024 tbsp_size,
nvl(sum(e.used_bytes)/1024/1024,0) used,
nvl(sum(f.free_bytes)/1024/1024,0) free,
nvl((sum(e.used_bytes)*100)/sum(df.bytes),0) pct_used
from dba_data_files df,
(select file_id ,sum (nvl(bytes,0)) used_bytes
from dba_extents group by file_id) e,
(select max(bytes) free_bytes,file_id
from dba_free_space group by file_id) f
where e.file_id(+)=df.file_id and
df.file_id=f.file_id(+)
group by df.tablespace_name
order by 5 DESC;


===================================================
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from
(select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files group by tablespace_name
UNION
select tablespace_name, sum(bytes)/1024/1024 tbs_size
from dba_temp_files
group by tablespace_name ) b
where a.tablespace_name(+)=b.tablespace_name;