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.