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;

Basics queries on Oracle Block

Query to identify header file and  header block


SQL> select segment_name,header_file,header_block from dba_segments where segment_name='EMP';

SEGMENT_NAME                                                                      HEADER_FILE HEADER_BLOCK
--------------------------------------------------------------------------------- ----------- ------------

EMP                                                                                         4          138

Get the dump of the block you need

===================================

SQL> Alter system dump datafile 4 block 138;

System altered.

the above query will produce a block dump in default trace directory [UDUMP] of the db.below is the output.

*** 2015-06-01 08:24:44.708

Start dump data blocks tsn: 4 file#:4 minblk 138 maxblk 138

Block dump from cache:

Dump of buffer cache at level 4 for tsn=4, rdba=16777354

BH (0x3d3f55c4) file#: 4 rdba: 0x0100008a (4/138) class: 4 ba: 0x3d2ee000

  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 382,28

  dbwrid: 0 obj: 74741 objn: -1 tsn: 4 afn: 4 hint: f

  hash: [0x3d3f5a64,0x51ab3cd0] lru: [0x3d3f5744,0x3d3f559c]

  ckptq: [NULL] fileq: [NULL] objq: [NULL]

  st: CR md: NULL tch: 1

  cr: [scn: 0x0.149710],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.149710],[sfl: 0x0],[lc: 0x0.0]

  flags:

  cr pin refcnt: 0 sh pin refcnt: 0

BH (0x3d3f59e8) file#: 4 rdba: 0x0100008a (4/138) class: 4 ba: 0x3d2f8000

  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 382,28

  dbwrid: 0 obj: 74741 objn: -1 tsn: 4 afn: 4 hint: f

  hash: [0x3ebfaab8,0x3d3f5640] lru: [0x3d3f5b68,0x3d3f59c0]

  ckptq: [NULL] fileq: [NULL] objq: [NULL]

  st: CR md: NULL tch: 1

  cr: [scn: 0x0.1496ea],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.1496ea],[sfl: 0x0],[lc: 0x0.0]

  flags:

  cr pin refcnt: 0 sh pin refcnt: 0

BH (0x3ebfaa3c) file#: 4 rdba: 0x0100008a (4/138) class: 4 ba: 0x3ebba000

  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 382,28

  dbwrid: 0 obj: 74741 objn: -1 tsn: 4 afn: 4 hint: f

  hash: [0x3ebfa9e4,0x3d3f5a64] lru: [0x3ebfaa14,0x3ebfabbc]

  ckptq: [NULL] fileq: [NULL] objq: [NULL]

  st: CR md: NULL tch: 1

  cr: [scn: 0x0.149697],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.149697],[sfl: 0x0],[lc: 0x0.0]

  flags:

  cr pin refcnt: 0 sh pin refcnt: 0

BH (0x3ebfa968) file#: 4 rdba: 0x0100008a (4/138) class: 4 ba: 0x3ebb8000

  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 382,28

  dbwrid: 0 obj: 74741 objn: -1 tsn: 4 afn: 4 hint: f

  hash: [0x3ebfad34,0x3ebfaab8] lru: [0x3ebfa798,0x3ebfaae8]

  ckptq: [NULL] fileq: [NULL] objq: [NULL]

  st: CR md: NULL tch: 1

  cr: [scn: 0x0.149688],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.149688],[sfl: 0x0],[lc: 0x0.0]

  flags:

  cr pin refcnt: 0 sh pin refcnt: 0

BH (0x3ebfacb8) file#: 4 rdba: 0x0100008a (4/138) class: 4 ba: 0x3ebc0000

  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 382,28

  dbwrid: 0 obj: 74741 objn: -1 tsn: 4 afn: 4 hint: f

  hash: [0x51ab3cd0,0x3ebfa9e4] lru: [0x3ebfae38,0x3ebfac90]

  ckptq: [NULL] fileq: [NULL] objq: [NULL]

  st: CR md: NULL tch: 1

  cr: [scn: 0x0.149679],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.149679],[sfl: 0x0],[lc: 0x0.0]

  flags:

  cr pin refcnt: 0 sh pin refcnt: 0

Block dump from disk:

buffer tsn: 4 rdba: 0x0100008a (4/138)

scn: 0x0000.000db7b9 seq: 0x01 flg: 0x04 tail: 0xb7b92301

frmt: 0x02 chkval: 0x8872 type: 0x23=PAGETABLE SEGMENT HEADER

Hex dump of block: st=0, typ_found=1

Dump of memory from 0x00878200 to 0x0087A200

878200 0000A223 0100008A 000DB7B9 04010000  [#...............]

878210 00008872 00000000 00000000 00000000  [r...............]

878220 00000000 00000001 00000008 00000A9C  [................]

878230 00000000 00000004 00000008 0100008C  [................]

878240 00000000 00000000 00000000 00000004  [................]

878250 00000000 00000000 00000000 00000000  [................]

878260 00000004 00000008 0100008C 00000000  [................]

878270 00000000 00000000 00000004 01000088  [................]

878280 01000088 00000000 00000000 00000000  [................]

878290 00000000 00000000 00000000 00000000  [................]

        Repeat 3 times

8782D0 00000001 00002000 00000000 00001434  [..... ......4...]

8782E0 00000000 01000089 00000001 01000088  [................]

8782F0 01000089 00000000 00000000 00000000  [................]

878300 00000000 00000000 00000001 00000000  [................]

878310 000123F5 10000000 01000088 00000008  [.#..............]

878320 00000000 00000000 00000000 00000000  [................]

        Repeat 152 times

878CB0 01000088 0100008B 00000000 00000000  [................]

878CC0 00000000 00000000 00000000 00000000  [................]

        Repeat 151 times

879640 00000000 00000000 01000089 00000000  [................]

879650 00000000 00000000 00000000 00000000  [................]

        Repeat 185 times

87A1F0 00000000 00000000 00000000 B7B92301  [.............#..]

  Extent Control Header

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

  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 8     

                  last map  0x00000000  #maps: 0      offset: 2716  

      Highwater::  0x0100008c  ext#: 0      blk#: 4      ext size: 8     

  #blocks in seg. hdr's freelists: 0     

  #blocks below: 4     

  mapblk  0x00000000  offset: 0     

                   Unlocked

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

  Low HighWater Mark : 

      Highwater::  0x0100008c  ext#: 0      blk#: 4      ext size: 8     

  #blocks in seg. hdr's freelists: 0     

  #blocks below: 4     

  mapblk  0x00000000  offset: 0     

  Level 1 BMB for High HWM block: 0x01000088

  Level 1 BMB for Low HWM block: 0x01000088

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

  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0      

  L2 Array start offset:  0x00001434

  First Level 3 BMB:  0x00000000

  L2 Hint for inserts:  0x01000089

  Last Level 1 BMB:  0x01000088

  Last Level II BMB:  0x01000089

  Last Level III BMB:  0x00000000

     Map Header:: next  0x00000000  #extents: 1    obj#: 74741  flag: 0x10000000

  Inc # 0 

  Extent Map

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

   0x01000088  length: 8     

  

  Auxillary Map

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

   Extent 0     :  L1 dba:  0x01000088 Data dba:  0x0100008b

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

  

   Second Level Bitmap block DBAs 

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

   DBA 1:   0x01000089

  

End dump data blocks tsn: 4 file#: 4 minblk 138 maxblk 138

Simple example to get the table name from the block dump information.

SQL> select name from sys.obj$ where obj#=74741;

NAME

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

EMP

Script for Oracle Table size

select
segment_name Table_Name,
sum(bytes)/(1024*1024) table_size_in_MB
from user_extents
where segment_type='TABLE'
and segment_name = '&Table_Name'
group by segment_name;

SQL * Plus Copy Utility

Copy tables from one database to another using copy utility.

connect to sql * plus 

command:
copy from scott/oracle@ORCL-
to rajiv/rajiv@ORCL1-
create new_emp1–
using select * from emp;

----------------------------------------
connect to the schema where the table present then  copy the table to another schema  of other Database.

copy to rajiv/rajiv@ORCL1-
create new_emp2–
using select * from emp;
--------------------------------------------------
Description :

The copy command copies data from one Oracle instance to another.   The data is simply copied directly from a source to a target.  The format of the copy command is:

COPY FROM database TO database action -
  destination_table (column_name, column_name...) USING query

The action can include:
 create – If the destination table already exists, copy will report an error, otherwise the table is created and the data is copied. 
replace – If the destination table exists, copy will drop and recreate the table with the newly copied data. Otherwise, it will create the table and populate it with the data. 
insert – If the destination table exists, copy inserts the new rows into the table. Otherwise, copy reports an error and aborts.
append– Inserts the data into the table if it exists, otherwise it will create the table and then insert the data.

Data type Supports: CHAR,DATE,LONG,NUMBER,VARCHAR2 

More Details:

LINK  : http://docs.oracle.com/cd/B19306_01/server.102/b14357/apb.htm

CDB VS PDB [Oracle 12C]

CDB is an acronym for “Container Database” and PDB is an acronym for “Pluggable Database”. I think it will be easier to explain with a metaphor for DBAs. Think of a freight train with many cars up to 250. Each container could be having different contents, with delivery target for different customers and completely packed/sealed independently with customer options but the entire freight is carried by a single engine or carrier at the front. It will be stupid enough to run 250 freight trucks but rather it is efficient to consolidate them into a single freight train. When running independently we will spend on gas, drivers’ expenses and much more complicated to manage them. The freight train is basically the CDB and each car is the PDB.
PDB is fully backward compatible to pre-12.1 database releases. There is nothing different from a developer or application connectivity perspective. Everything stays the same but the PDB will belong to a single CDB. When application connects to the PDB, it will specify the destination PDB via a database service. All home-grown or third party applications typically will have connectivity defined out of the application so it is easier to just change the service name outside of the application code. So all database connectivity should use “database service” rather than using the legacy approach of ORACLE_SID based connectivity. ORACLE_SID ties the application connectivity to a specific database instance and does not give the scalability or high availability. You can have many pluggable databases in 12.1.you can have up to 250 pluggable databases or PDBs within one container database or CDB. It is clear that there is one-to-many relationship between CDB and PDBS.

Move indexes from one Tablespace to another Tablespace

Method 1:


Select ' ALTER INDEX ' || owner || '.'||INDEX_NAME ||' rebuild tablespace [
 new_tablespace_name];' from DBA_indexes
 where index_name in ( select segment_name from
 dba_segments where tablespace_name='USERS')

Method 2:

declare

type vt is table of varchar2(64);
l_in vt;
begin
-- fetching all names to avoid ORA-01555
select index_name 
bulk collect into l_in
from dba_indexes where tablespace_name='<current index tablespace>';

for i in 1..l_in.count loop
execute immediate 'alter index '||l_in(i)||' rebuild tablespace <new index tablespace>';
end loop;
end;
/

"EXP-00091 Exporting questionable statistics" Error

The "EXP-00091 Exporting questionable statistics" error happens when you export table data with its related optimizer statistics, and Oracle cannot verify the currency of these statistics.  When CBO statistics are created/updated with dbms_stats, the time is noted, but it’s always a good idea not to export statistics since they should be recalculated after import.
You can remove the EXP-00091 error by exporting with "statistics="none" or by setting the client character set

(e.g. export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1).
The Oracle docs note this on the EXP-00091 error:

EXP-00091 Exporting questionable statistics

Cause: Export was able to export statistics, but the statistics may not be usable  The statistics are questionable because one or more of the following happened during export: 

- A row error occurred, client character set or NCHARSET does not match with the server, a query clause was specified on export,

- Only certain partitions or sub partitions were exported, or a fatal error occurred while processing a table.

Action: To export non-questionable statistics, change the client character set or NCHARSET to match the server, export with no query clause, or export complete tables. 

If desired, import parameters can be supplied so that only non-questionable statistics will be imported, and all questionable statistics will be recalculated.

STEPS TO GENERATE AWR REPORT

Privileges:

STEP-1
Please connect to sys and give these privileges to Scott.
GRANT SELECT ON SYS.V_$DATABASE TO SCOTT;
GRANT SELECT ON SYS.V_$INSTANCE TO SCOTT;
GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO SCOTT;
GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO SCOTT;
GRANT ADVISOR TO SCOTT;
GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO SCOTT;

STEP-2 :

SET LINES 400
SET PAGES 400
COL BEGIN_INTERVAL_TIME FOR A40
COL END_INTERVAL_TIME FOR A40
Spool d:\awr.txt
SELECT DBID,INSTANCE_NUMBER,SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME FROM DBA_HIST_SNAPSHOT ORDER BY 5 DESC;
Spool off;
Note: Use above query to get low snap, high snap details. DBID and Instance number

STEP 3:
Query to Generate AWR report:

SELECT OUTPUT FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML (DBID,INSTANCE_NUMBER, LOW_SNAP_ID,HIGH_SNAP_ID,8));
Note : Please put all the details which you have got from step :2

Generate the AWR report on HTML format using below query:
Ex:
SET HEADING OFF;
SET FEEDBACK OFF;
SET LINESIZE 1500;
PROMPT GETTING HTML FORMAT REPORT:
SPOOL d:\AWR_REPORT_HTML.HTML;

SELECT OUTPUT FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML (1318580148,1,798,800,8));
SPOOL OFF;

METHOD 2:

Also we can run awrrpt.sql which is located in ORACLE_HOME\RDBMS\ADMIN location.

Datapump jobs

Identifying datapump jobs

TTITLE 'Currently Active DataPump Operations'
COL owner_name FORMAT A06 HEADING 'Owner'
COL job_name FORMAT A20 HEADING 'JobName'
COL operation FORMAT A12 HEADING 'Operation'
COL job_mode FORMAT A12 HEADING 'JobMode'
COL state FORMAT A12 HEADING 'State'
COL degree FORMAT 9999 HEADING 'Degr|(Num|Wrkers)'
COL attached_sessions FORMAT 9999 HEADING 'Sess'
SELECT
owner_name
,job_name
,operation
,job_mode
,state
,degree
,attached_sessions
FROM dba_datapump_jobs;

Killing or stopping a running datapump job


The difference between Kill and Stop is simple to explain. When killing a job, you won’t be able to resume or start it again. Also logs and dumpfiles will be removed!
When exporting (or importing), press Ctrl-c to show the datapump prompt and type KILL_JOB or STOP_JOB[=IMMEDIATE]. You will be prompted to confirm if you are sure…
Adding ‘=IMMEDIATE‘ to STOP_JOB will not finish currently running ‘sub-job’ and must be redone when starting it again.

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
[Ctrl-c]
Export> KILL_JOB
..or..
Export> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([yes]/no): yes


Resuming a stopped job

Now we can ATTACH to the job using it as a parameter to the expdp or impdp command, and a lot of gibberish is shown:
> expdp system ATTACH=EXP_FULL


(Re)start the job with START_JOB, use ‘=SKIP_CURRENT‘ if you want to skip the current job. To show progress again, type CONTINUE_CLIENT (Job will be restarted if idle).

Export> START_JOB[=SKIP_CURRENT]
Export> CONTINUE_CLIENT

GENERATE SCRIPT TO CLEAN THE OBJECTS IN A SCHEMA.

set heading off;
set feedback off;
SELECT 'DROP '||object_type||' '|| object_name||  DECODE(object_type,'TABLE',' CASCADE CONSTRAINTS;',';') FROM user_objects ORDER BY object_id DESC;

SELECT 'PURGE RECYCLEBIN;' FROM dual;
SELECT 'PROMPT After cleanup, number of objects remaining in schema' FROM dual;
SELECT 'SELECT COUNT(*) FROM user_objects;' FROM dual;

LOGIN SYSTEM or SYS :

SELECT 'DROP '||object_type||' '|| object_name||  DECODE(object_type,'TABLE',' CASCADE CONSTRAINTS;',';') FROM dba_objects where owner='SCOTT' ORDER BY object_id DESC;

NOTE : Here owner= 'SCOTT' , You can change schema name which you want to clean.

ALTERNATIVE QUOTE (q) Operator

Many SQL statements use character literals in expressions or conditions. If the literal itself contains a single quotation mark, you can use the quote (q) operator and choose your own quotation mark delimiter.
You can choose any convenient delimiter, single-byte or multibyte, or any of the following character pairs: [], {}, (), or <>.
EX:   select q'<Oracle's quote operator>' from dual;
         select q'#Oracle's quote operator#' from dual;
         select q'(Oracle's quote operator)' from dual;
         select dname ||q'{ it's assigned manager id :}'|| mgr from emp,dept where emp.deptno=dept.deptno

DBMS_RANDOM

This package is very useful to generate random number as well as  random date and text values.

NUMBER:


EXAMPLE: 


SELECT DBMS_RANDOM.VALUE FROM DUAL;


SELECT TRUNC(DBMS_RANDOM.VALUE(0, 100)) FROM DUAL;

TEXT:


EXAMPLE:

SELECT DBMS_RANDOM.STRING('A', 20) FROM DUAL;


Q : Generate 100 random strings of 15 characters by using DUAL table.

 SELECT DBMS_RANDOM.STRING('X',15) FROM DUAL CONNECT BY ROWNUM<101;


DATE:



SELECT TO_CHAR(TO_DATE('01/01/12','mm/dd/yy'),'J')
FROM DUAL;


NOTE :Take the low_value from the above query and use it in the below query to generate random dates.



SELECT TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2455928,2455928+364)),'J')
 FROM DUAL;

Email From Oracle PL/SQL (UTL_SMTP)

Simple Emails: (connect as sys)



CREATE OR REPLACE PROCEDURE send_mail (p_to        IN VARCHAR2,
                                       p_from      IN VARCHAR2,
                                       p_message   IN VARCHAR2,
                                       p_smtp_host IN VARCHAR2,
                                       p_smtp_port IN NUMBER DEFAULT 25)
AS
  l_mail_conn   UTL_SMTP.connection;
BEGIN
  l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
  UTL_SMTP.helo(l_mail_conn, p_smtp_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);
  UTL_SMTP.data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
  UTL_SMTP.quit(l_mail_conn);
END;
/


The code below shows how the procedure is called.



BEGIN
  send_mail(p_to        => 'me@mycompany.com',
            p_from      => 'hr@mycompany.com',
            p_message   => 'This is a test message.',
            p_smtp_host => 'smtp.mycompany.com');
END;
/
-----------------------------------------------------------------------------------------------------
Multi-line messages can be written by expanding the UTL_SMTP.DATA command using the UTL_SMTP.WRITE_DATA command as follows. This is a better method to use as the total message size is no longer constrained by the 32K limit on a VARCHAR2 variable. In the following example the header information has been included in the message also.
---------------------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE send_mail (p_to        IN VARCHAR2,
                                       p_from      IN VARCHAR2,
                                       p_subject   IN VARCHAR2,
                                       p_message   IN VARCHAR2,
                                       p_smtp_host IN VARCHAR2,
                                       p_smtp_port IN NUMBER DEFAULT 25)
AS
  l_mail_conn   UTL_SMTP.connection;
BEGIN
  l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
  UTL_SMTP.helo(l_mail_conn, p_smtp_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);

  UTL_SMTP.open_data(l_mail_conn);
 
  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf || UTL_TCP.crlf);
 
  UTL_SMTP.write_data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
  UTL_SMTP.close_data(l_mail_conn);

  UTL_SMTP.quit(l_mail_conn);
END;
/
The code below shows how the procedure is called.



BEGIN
  send_mail(p_to        => 'me@mycompany.com',
            p_from      => 'hr@mycompany.com',
            p_subject   => 'Test Message',
            p_message   => 'This is a test message.',
            p_smtp_host => 'smtp.mycompany.com');
END;
/

Script to find locks in the database

select
 (select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
 a.sid || ', ' ||
 (select serial# from v$session where sid=a.sid) sid_serial,
 ' is blocking ',
 (select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
 b.sid || ', ' ||
 (select serial# from v$session where sid=b.sid) sid_serial
 from v$lock a, v$lock b
 where a.block = 1
   and b.request > 0
   and a.id1 = b.id1
   and a.id2 = b.id2;