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.