ORACLE

Total Pageviews

Showing posts with label Tablespace. Show all posts
Showing posts with label Tablespace. Show all posts

Tuesday, 13 June 2023

Send tablespace status report

Send TABLESPACE status report mail in HTML Format
Step 1 : Create SQL script file 
             To Generate HTML file for database status and tablespace report
Step 2 : Spool to shell script file
Step 3 : execute shell script to get tablespace report via mail in HTML format

Step 1 and Step 2
[oracle]$ cat report.sql

 SET head OFF;
 SET echo OFF;
 SET termout OFF;
 SET verify OFF;
 set colsep ,
 set pagesize 0
 set feedback off
spool /home/oracle/Desktop/dilip/MAIL_SCRIPT/test_report.sh
select 'sendmail sample_dbmonitoring@xyz.com <<EOF' from dual;
select 'To: dbmonitoring_group@xyz.com ' from dual;
select 'Subject: Text message' from dual;
select 'Content-Type: text/html; charset="us-ascii"' from dual;
select '<html>' from dual;
select '<body>' from dual;
select '<p>' from dual;
select '<font size="5" face="Courier" color="blue">' from dual;
select 'Tablespace Alert' from dual;
select '</font>' from dual;
select '<font size="2" face="screen" color="green">' from dual;
spool off
SET head on;
set heading on;
set verify on;
set termout on
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<TITLE>DATABASE NAME and STATUS </TITLE> -
<STYLE type='text/css'> -
<!-- BODY {background: white} --> -
</STYLE>" -
BODY "TEXT=blue" -
TABLE "WIDTH='50%' BORDER='5'"
set pagesize 200
spool /home/oracle/Desktop/dilip/MAIL_SCRIPT/test_report.sh append;
prompt 1.DATABASE STATUS
select name as DBNAME,open_mode,database_role,(select startup_time from v$instance) as starttime from v$database;
prompt 2.TABLESPACE STATUS
select a.tablespace_name as  "TSNAME",
        round((b.totalspace - a.freespace),1)"USED_SPACE_MB",
        round(a.freespace,1) "FREE_SPACE_MB",
        round(b.totalspace) "TOTAL_SPACE_MB",
        round(100 * (a.freespace / b.totalspace),5) "%_FREE"
 from
  ( select tablespace_name,
        sum(bytes)/1024/1024 TotalSpace
   from dba_data_files
   group by tablespace_name) b,
 ( select tablespace_name,
    sum(bytes)/1024/1024 FreeSpace
   from dba_free_space
   group by tablespace_name) a
  where b.tablespace_name = a.tablespace_name(+)
  order by 5;
spool off
set markup html off
set heading off
set heading off
SET head OFF;
SET echo OFF;
SET termout OFF;
SET verify OFF;
set colsep ,
set pagesize 0
spool /home/oracle/Desktop/dilip/MAIL_SCRIPT/test_report.sh append;
select '</font>' from dual;
select '</p>' from dual;
select '</body>' from dual;
select '</html>' from dual;
prompt EOF
spool off
exit

--Generated shell script file which will send mail in HTML format of tablespace status

Verify the generated file

[oracle]$ cat test_report.sh
sendmail sample_dbmonitoring@xyz.com <<EOF
To: dbmonitoring_group@xyz.com
Subject: Text message
Content-Type: text/html; charset="us-ascii"
<html>
<body>
<p>
<font size="5" face="Courier" color="blue">
Tablespace Alert
</font>
<font size="2" face="screen" color="green">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=US-ASCII">
<meta name="generator" content="SQL*Plus 11.2.0">
<TITLE>DATABASE NAME and STATUS </TITLE>  <STYLE type='text/css'>  <!-- BODY {background: white} -->  </STYLE>
</head>
<body TEXT=blue>
1.DATABASE STATUS
<br>
<p>
<table WIDTH='50%' BORDER='5'>
<tr>
<th scope="col">
DBNAME
</th>
<th scope="col">
OPEN_MODE
</th>
<th scope="col">
DATABASE_ROLE
</th>
<th scope="col">
STARTTIME
</th>
</tr>
<tr>
<td>
ORACLE
</td>
<td>
READ WRITE
</td>
<td>
PRIMARY
</td>
<td>
25-MAR-21
</td>
</tr>
</table>
<p>
2.TABLESPACE STATUS
<br>
<p>
<table WIDTH='50%' BORDER='5'>
<tr>
<th scope="col">
TSNAME
</th>
<th scope="col">
USED_SPACE_MB
</th>
<th scope="col">
FREE_SPACE_MB
</th>
<th scope="col">
TOTAL_SPACE_MB
</th>
<th scope="col">
%_FREE
</th>
</tr>
<tr>
<td>
SYSTEM
</td>
<td align="right">
     694.6
</td>
<td align="right">
       5.4
</td>
<td align="right">
       700
</td>
<td align="right">
    .76786
</td>
</tr>
<tr>
<td>
SYSAUX
</td>
<td align="right">
     706.4
</td>
<td align="right">
      53.6
</td>
<td align="right">
       760
</td>
<td align="right">
    7.0477
</td>
</tr>
<tr>
<td>
USERS
</td>
<td align="right">
       2.4
</td>
<td align="right">
       2.6
</td>
<td align="right">
         5
</td>
<td align="right">
     51.25
</td>
</tr>
<tr>
<td>
UNDOTBS1
</td>
<td align="right">
      11.3
</td>
<td align="right">
     303.8
</td>
<td align="right">
       315
</td>
<td align="right">
  96.42857
</td>
</tr>
<tr>
<td>
TRAING
</td>
<td align="right">
       2.2
</td>
<td align="right">
    5122.8
</td>
<td align="right">
      5125
</td>
<td align="right">
  99.95732
</td>
</tr>
</table>
<p>
</body>
</html>
</font>
</p>
</body>
</html>
EOF

Step 3 : Execute the shell script

[oracle]$./test_report.sh


at No comments:

Wednesday, 10 February 2016

SHRINK SPACE OF FRAGMENTED TABLE

                                        CREATE TABLE SCOTT.TEST_SHRINK


CREATE TABLE SCOTT.TEST_SHRINK AS SELECT * FROM ALL_OBJECTS;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
COMMIT;


exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'TEST_SHRINK',estimate_percent=>100);




FIND OUT TABLE FULL SIZE ,ACTUAL SIZE  AND PERCENTAGE OF FRAGMENTATION


set lines 300 pages 300 ;
select a.owner, a.table_name, b.bytes/1024/1024/1024 "Size(G)",  (a.avg_row_len*a.num_rows/1024/1024/1024) "Actual(G)",
to_char(last_analyzed,'DD-MON-YY') "LAST ANAL",
trunc((b.bytes/1024/1024/1024) - (a.avg_row_len*a.num_rows/1024/1024/1024)) "Diff(G)" ,
(b.bytes-a.avg_row_len*a.num_rows)*100/ b.bytes "% Frag"
from     dba_tables a,
    dba_segments b
where    a.table_name = b.segment_name
and     a.owner = b.owner
and  a.table_name = 'TEST_SHRINK'
and a.owner='SCOTT';

TAKE TABLE COUNT

select count(1) from scott.TEST_SHRINK;


select num_rows, blocks, empty_blocks, last_analyzed from user_tables where table_name = 'TEST_SHRINK';


DELETE THE DUPLICATE ROWS FROM THE TABLE

DELETE FROM SCOTT.TEST_SHRINK A WHERE ROWID > (
    SELECT min(rowid) FROM SCOTT.TEST_SHRINK B
    WHERE A.OBJECT_ID = B.OBJECT_ID);

select count(1) from scott.TEST_SHRINK;

exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'TEST_SHRINK',estimate_percent=>100);
select num_rows, blocks, empty_blocks, last_analyzed from user_tables where table_name = 'TEST_SHRINK';

FIND OUT TABLE FULL SIZE ,ACTUAL SIZE  AND PERCENTAGE OF FRAGMENTATION

----------------------Use above script

BEFORE TABLE SIZE REPORT

OWNER                          TABLE_NAME                        Size(G)  Actual(G) LAST ANAL    Diff(G)     % Frag
------------------------------ ------------------------------ ---------- ---------- --------- ---------- ----------
SCOTT                          TEST_SHRINK                    2.94244385 .023890277 09-FEB-16          2 99.1880804


CHECK ROW MOVEMENT ENABLE FOR A TABLE TEST_SHRINK

Select owner,table_name, ROW_MOVEMENT, TABLESPACE_NAME from dba_tables where owner=’SCOTT’ and Table_name =’TEST_SHRINK’;


ALTER TABLE SCOTT.TEST_SHRINK Enable row Movement;


CHECK TABLESPACE USED FREE SPACE BEFORE DOING SHRINK

col "NAME" format a30
 set lines 140
 set pages 1000
 select a.tablespace_name "NAME",
 round((b.totalspace - a.freespace),1)"USED_SPACE_MB",
 round(a.freespace,1) "FREE_SPACE_MB",
 round(b.totalspace) "TOTAL_SPACE_MB",
 round(100 * (a.freespace / b.totalspace)) "%_FREE"
 from
  ( select tablespace_name,
 sum(bytes)/1024/1024 TotalSpace
   from dba_data_files
   group by tablespace_name) b,
 ( select tablespace_name,
    sum(bytes)/1024/1024 FreeSpace
   from dba_free_space
   group by tablespace_name) a
  where b.tablespace_name = a.tablespace_name(+)
  order by 1;


ALTER TABLE SCOTT.TEST_SHRINK SHRINK SPACE;

select num_rows, blocks, empty_blocks, last_analyzed from user_tables where table_name = 'TEST_SHRINK';
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'TEST_SHRINK',estimate_percent=>100);


FIND OUT TABLE FULL SIZE ,ACTUAL SIZE  AND PERCENTAGE OF FRAGMENTATION

DISABLE ROW MOVEMENT

ALTER TABLE SCOTT.TEST_SHRINK DISABLE row Movement;

at No comments:

Friday, 16 October 2015

SYSAUX Tablespace purging


First check sysaux table used/free/tolal space 

NAME         USED_SPACE_GB FREE_SPACE_GB TOTAL_SPACE_GB     %_FREE
------------ ------------- ------------- -------------- ----------

SYSAUX                17.9          22.1            40         55
SYSTEM                13.8           6.2            20         31
UNDOTBS01             63.9         161.1           225         72
USERS                 43.5          43.5            87         50
 
1. To know how long statistics available
    select dbms_stats.get_stats_history_availability from dual;

2. exec dbms_stats.purge_stats(sysdate-20);
select dbms_stats.get_stats_history_availability from dual;

 *********************************************************

SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
------------------------------------------
14-SEP-15 07.45.26.010132000 AM +05:30


SQL> SELECT owner,
            segment_name, 
            partition_name,
            bytes/1024/1024/1024 Size_GB
      FROM dba_segments
      WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';

OWNER  SEGMENT_NAME                   PARTITION_NAME                     SIZE_GB
------ ------------------------------ ------------------------------- ----------
SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_169052300_19681     .094604492
SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_169052300_19729     .066589355
SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_169052300_19777     .079528809
SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_169052300_19825     .068359375
SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_169052300_19873     .105102539
SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_169052300_19921     .029907227
SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_169052300_19969     .052124023
SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_169052300_20017      .05456543
SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_169052300_20065     .040649414
SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_169052300_20337     .042358398
SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_169052300_20385      .00012207
SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_169052300_28730     1.24621582
SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_169052300_88323     .000061035
SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_169052300_88706     .000061035
SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_169052300_89090     .000061035
SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_169052300_89521     .000061035
SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_169052300_89905     .000061035
SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_169052300_90289     .000061035
SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_169052300_90674     .000061035
SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_169052300_91057     .000061035
SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_169052300_91537     .000061035
SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_169052300_91970     .000061035
SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_169052300_92348     .000061035
SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_169052300_92732     .000061035
SYS    WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_SES_MXDB_MXSN       .000061035

SQL> select min(snap_id) 
    from sys.WRH$_ACTIVE_SESSION_HISTORY 
    partition(&WRH_ACTIVE_NAME);

Enter value for wrh_active_name: WRH$_ACTIVE_169052300_19681

MIN(SNAP_ID)
------------
       19688

SQL> /
Enter value for wrh_active_name: WRH$_ACTIVE_169052300_19729

MIN(SNAP_ID)
------------
       19729

SQL> /
Enter value for wrh_active_name: WRH$_ACTIVE_169052300_19777

MIN(SNAP_ID)
------------
       19777

SQL> /
Enter value for wrh_active_name: WRH$_ACTIVE_169052300_19825

MIN(SNAP_ID)
------------
       19825

SQL> /
Enter value for wrh_active_name: WRH$_ACTIVE_169052300_19873

MIN(SNAP_ID)
------------
       19873

SQL> /
Enter value for wrh_active_name: WRH$_ACTIVE_169052300_19921

MIN(SNAP_ID)
------------
       19921

SQL> /
Enter value for wrh_active_name: WRH$_ACTIVE_169052300_19969

MIN(SNAP_ID)
------------
       19969

SQL> /
Enter value for wrh_active_name: WRH$_ACTIVE_169052300_20017

MIN(SNAP_ID)
------------
       20017

SQL> /
Enter value for wrh_active_name: WRH$_ACTIVE_169052300_20065

MIN(SNAP_ID)
------------
       20065

SQL> /
Enter value for wrh_active_name: WRH$_ACTIVE_169052300_20337

MIN(SNAP_ID)
------------
       20339

SQL> /
Enter value for wrh_active_name: WRH$_ACTIVE_169052300_20385

MIN(SNAP_ID)
------------
       20385

SQL> /
Enter value for wrh_active_name: WRH$_ACTIVE_169052300_89090

MIN(SNAP_ID)
------------


SQL> /
Enter value for wrh_active_name: WRH$_ACTIVE_169052300_88706

MIN(SNAP_ID)
------------


SQL> /
Enter value for wrh_active_name: WRH$_ACTIVE_169052300_88323

MIN(SNAP_ID)
------------


SQL> /
Enter value for wrh_active_name: WRH$_ACTIVE_169052300_88323

MIN(SNAP_ID)
------------


SQL> /
Enter value for wrh_active_name: WRH$_ACTIVE_169052300_28730

MIN(SNAP_ID)
------------
       28791

SQL> /
Enter value for wrh_active_name: WRH$_ACTIVE_SES_MXDB_MXSN

MIN(SNAP_ID)
------------

SQL> select min(snap_id) from WRH$_ACTIVE_SESSION_HISTORY;

MIN(SNAP_ID)
------------
       19688

SQL> select min(snap_id) from dba_hist_snapshot;

MIN(SNAP_ID)
------------
       19688

SQL> select min(snap_id),MAX(snap_id) from dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
       19688        92858

SQL> select min(snap_id) from sys.WRH$_ACTIVE_SESSION_HISTORY partition (WRH$_ACTIVE_169052300_19681);
select min(snap_id) from sys.WRH$_ACTIVE_SESSION_HISTORY partition (WRH$_ACTIVE_169052300_19729);
select min(snap_id) from sys.WRH$_ACTIVE_SESSION_HISTORY partition (WRH$_ACTIVE_169052300_19777);
select min(snap_id) from sys.WRH$_ACTIVE_SESSION_HISTORY partition (WRH$_ACTIVE_169052300_19825);

MIN(SNAP_ID)
------------
       19688

00:06:59 SQL> select min(snap_id) from sys.WRH$_ACTIVE_SESSION_HISTORY partition (WRH$_ACTIVE_169052300_19873);
select min(snap_id) from sys.WRH$_ACTIVE_SESSION_HISTORY partition (WRH$_ACTIVE_169052300_19921);
select min(snap_id) from sys.WRH$_ACTIVE_SESSION_HISTORY partition (WRH$_ACTIVE_169052300_19969);
select min(snap_id) from sys.WRH$_ACTIVE_SESSION_HISTORY partition (WRH$_ACTIVE_169052300_20017);
select min(snap_id) from sys.WRH$_ACTIVE_SESSION_HISTORY partition (WRH$_ACTIVE_169052300_20065);

MIN(SNAP_ID)
------------
       19729

SQL> select min(snap_id) from sys.WRH$_ACTIVE_SESSION_HISTORY partition (WRH$_ACTIVE_169052300_20337);
select min(snap_id) from sys.WRH$_ACTIVE_SESSION_HISTORY partition (WRH$_ACTIVE_169052300_20385);
select min(snap_id) from sys.WRH$_ACTIVE_SESSION_HISTORY partition (WRH$_ACTIVE_169052300_28730);
select min(snap_id) from sys.WRH$_ACTIVE_SESSION_HISTORY partition (WRH$_ACTIVE_169052300_88323);




MIN(SNAP_ID)
------------
       19777



MIN(SNAP_ID)
------------
       19825

SQL>
MIN(SNAP_ID)
------------
       19873

SQL>
MIN(SNAP_ID)
------------
       19921

SQL>
MIN(SNAP_ID)
------------
       19969

SQL>
MIN(SNAP_ID)
------------
       20017

SQL>
MIN(SNAP_ID)
------------
       20065

SQL>
MIN(SNAP_ID)
------------
       20339

00:06:59 SQL>
MIN(SNAP_ID)
------------
       20385

SQL>select min(snap_id) from sys.WRH$_ACTIVE_SESSION_HISTORY partition (WRH$_ACTIVE_169052300_88706);
MIN(SNAP_ID)
------------
       28791

SQL>select min(snap_id) from sys.WRH$_ACTIVE_SESSION_HISTORY partition (WRH$_ACTIVE_169052300_89090);
MIN(SNAP_ID)
------------


SQL>select min(snap_id) from sys.WRH$_ACTIVE_SESSION_HISTORY partition (WRH$_ACTIVE_169052300_89521);
MIN(SNAP_ID)
------------


SQL>
SQL>select min(snap_id) from sys.WRH$_ACTIVE_SESSION_HISTORY partition (WRH$_ACTIVE_169052300_89905);
MIN(SNAP_ID)
------------


SQL>select min(snap_id) from sys.WRH$_ACTIVE_SESSION_HISTORY partition (WRH$_ACTIVE_169052300_90289);
MIN(SNAP_ID)
------------


SQL>select min(snap_id) 
          from sys.WRH$_ACTIVE_SESSION_HISTORY partition (WRH$_ACTIVE_169052300_90674);
MIN(SNAP_ID)
------------


SQL>select min(snap_id) 
          from sys.WRH$_ACTIVE_SESSION_HISTORY partition (WRH$_ACTIVE_169052300_91057);
MIN(SNAP_ID)
------------

SQL>select min(snap_id) 
          from sys.WRH$_ACTIVE_SESSION_HISTORY partition (WRH$_ACTIVE_169052300_91537);
MIN(SNAP_ID)
------------

SQL>select min(snap_id)
          from sys.WRH$_ACTIVE_SESSION_HISTORY partition (WRH$_ACTIVE_169052300_91970);
MIN(SNAP_ID)
------------

SQL>select min(snap_id)
         from sys.WRH$_ACTIVE_SESSION_HISTORY partition (WRH$_ACTIVE_169052300_92348);
MIN(SNAP_ID)
------------

SQL>select min(snap_id)
          from sys.WRH$_ACTIVE_SESSION_HISTORY partition (WRH$_ACTIVE_169052300_92732);
MIN(SNAP_ID)
------------

SQL>select min(snap_id) from sys.WRH$_ACTIVE_SESSION_HISTORY partition (WRH$_ACTIVE_SES_MXDB_MXSN);
MIN(SNAP_ID)
------------


00:06:59 SQL>
MIN(SNAP_ID)
------------


00:06:59 SQL>

MIN(SNAP_ID)
------------


00:07:01 SQL>

00:07:57 SQL>
00:07:58 SQL>
00:07:58 SQL> select table_name, count(*) 
                          from dba_tab_partitions 
                          where table_name like 'WRH$%' 
                          and table_owner = 'SYS'
                          group by table_name order by 1;00:07:59   2

TABLE_NAME                         COUNT(*)
-------------------------------- ----------
WRH$_ACTIVE_SESSION_HISTORY              25
WRH$_DB_CACHE_ADVICE                     26
WRH$_DLM_MISC                             2
WRH$_EVENT_HISTOGRAM                     25
WRH$_FILESTATXS                          28
WRH$_INST_CACHE_TRANSFER                  2
WRH$_INTERCONNECT_PINGS                   2
WRH$_LATCH                               27
WRH$_LATCH_CHILDREN                       2
WRH$_LATCH_MISSES_SUMMARY                26
WRH$_LATCH_PARENT                         2
WRH$_MVPARAMETER                         13
WRH$_OSSTAT                              25
WRH$_PARAMETER                           25
WRH$_ROWCACHE_SUMMARY                    25
WRH$_SEG_STAT                            25
WRH$_SERVICE_STAT                        25
WRH$_SERVICE_WAIT_CLASS                  25
WRH$_SGASTAT                             25
WRH$_SQLSTAT                             27
WRH$_SYSSTAT                             25
WRH$_SYSTEM_EVENT                        27
WRH$_SYS_TIME_MODEL                      25
WRH$_TABLESPACE_STAT                     25
WRH$_WAITSTAT                            27

25 rows selected.

00:08:00 SQL>
00:08:08 SQL> alter session set "_swrf_test_action"=72;

Session altered.

00:08:11 SQL> select table_name,
                      partition_name 
  from dba_tab_partitions 
  where table_name = 'WRH$_ACTIVE_SESSION_HISTORY';

TABLE_NAME                         PARTITION_NAME
---------------------------------- ------------------------------
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_169052300_19681
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_169052300_19729
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_169052300_19777
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_169052300_19825
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_169052300_19873
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_169052300_19921
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_169052300_19969
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_169052300_20017
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_169052300_20065
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_169052300_20337
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_169052300_20385
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_169052300_28730
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_169052300_88323
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_169052300_88706----
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_169052300_89090
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_169052300_89521
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_169052300_89905
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_169052300_90289
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_169052300_90674
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_169052300_91057
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_169052300_91537
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_169052300_91970
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_169052300_92348
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_169052300_92732
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_169052300_92861
WRH$_ACTIVE_SESSION_HISTORY        WRH$_ACTIVE_SES_MXDB_MXSN

26 rows selected.
SQL> col table_name for a80
SQL> select table_name, 
                     count(*) 
           from dba_tab_partitions 
           where table_name like 'WRH$%' 
           and table_owner = 'SYS' 
           group by table_name order by 1;

SQL>
TABLE_NAME                          COUNT(*)
--------------------------------- ----------
WRH$_ACTIVE_SESSION_HISTORY               26
WRH$_DB_CACHE_ADVICE                      27
WRH$_DLM_MISC                              2
WRH$_EVENT_HISTOGRAM                      26
WRH$_FILESTATXS                           29
WRH$_INST_CACHE_TRANSFER                   2
WRH$_INTERCONNECT_PINGS                    2
WRH$_LATCH                                28
WRH$_LATCH_CHILDREN                        2
WRH$_LATCH_MISSES_SUMMARY                 27
WRH$_LATCH_PARENT                          2
WRH$_MVPARAMETER                          14
WRH$_OSSTAT                               26
WRH$_PARAMETER                            26
WRH$_ROWCACHE_SUMMARY                     26
WRH$_SEG_STAT                             26
WRH$_SERVICE_STAT                         26
WRH$_SERVICE_WAIT_CLASS                   26
WRH$_SGASTAT                              26
WRH$_SQLSTAT                              28
WRH$_SYSSTAT                              26
WRH$_SYSTEM_EVENT                         28
WRH$_SYS_TIME_MODEL                       26
WRH$_TABLESPACE_STAT                      26
WRH$_WAITSTAT                             28

25 rows selected.



 SQL> EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE( 19688,28791,169052300);

PL/SQL procedure successfully completed.

Elapsed: 00:00:42.79

SQL>  select owner,
                      segment_name,
                      round(sum(bytes/1024/1024),2)MB, 
                      tablespace_name from dba_segments 
             where segment_name = upper('WRH$_ACTIVE_SESSION_HISTORY'
             group by owner,segment_name,tablespace_name;

OWNER  SEGMENT_NAME                           MB  TABLESPACE_NAME
------ -------------------------------   -------  ---------------
SYS    WRH$_ACTIVE_SESSION_HISTORY       1926.13  SYSAUX

Elapsed: 00:00:00.11

SQL> alter table WRH$_ACTIVE_SESSION_HISTORY shrink space cascade;
alter table WRH$_ACTIVE_SESSION_HISTORY shrink space cascade
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled


Elapsed: 00:00:00.01
SQL> alter table WRH$_ACTIVE_SESSION_HISTORY enable row movement;

Table altered.

Elapsed: 00:00:00.00
SQL>
SQL> alter table WRH$_ACTIVE_SESSION_HISTORY shrink space cascade;

Table altered.

Elapsed: 00:04:17.55
00:20:31 SQL>
00:24:36 SQL>
SQL> select owner,
                     segment_name,
                     round(sum(bytes/1024/1024),2)MB, 
                     tablespace_name 
           from dba_segments 
           where segment_name = upper('WRH$_ACTIVE_SESSION_HISTORY')
           group up by owner,segment_name,tablespace_name;

OWNER  SEGMENT_NAME                                               MB TABLESPACE_NAME
------ -------------------------------------------------- ---------- ------------------------------
SYS    WRH$_ACTIVE_SESSION_HISTORY                           1026.69 SYSAUX

Elapsed: 00:00:00.13

SQL>

NAME         USED_SPACE_GB FREE_SPACE_GB TOTAL_SPACE_GB     %_FREE
------------ ------------- ------------- -------------- ----------
SYSAUX            16.9          23.1             40         58
SYSTEM            13.8           6.2             20         31
UNDOTBS01         65.5         159.5            225         71
USERS             43.5          43.5             87         50

20 rows selected.

Elapsed: 00:00:00.34
SQL>
SQL>  SELECT owner,
                           segment_name,
                           partition_name,
                           bytes/1024/1024/1024 Size_GB
             FROM dba_segments
             WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';

OWNER  SEGMENT_NAME                    PARTITION_NAME                    SIZE_GB
------ ------------------------------- ------------------------------ ----------
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_169052300_19681    .052368164
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_169052300_19729    .066589355
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_169052300_19777    .079528809
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_169052300_19825    .068359375
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_169052300_19873    .105102539
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_169052300_19921    .029907227
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_169052300_19969    .052124023
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_169052300_20017     .05456543
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_169052300_20065    .040649414
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_169052300_20337    .042236328
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_169052300_20385     .00012207
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_169052300_28730    .410217285
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_169052300_88323    .000061035
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_169052300_88706    .000061035
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_169052300_89090    .000061035
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_169052300_89521    .000061035
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_169052300_89905    .000061035
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_169052300_90289    .000061035
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_169052300_90674    .000061035
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_169052300_91057    .000061035
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_169052300_91537    .000061035
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_169052300_91970    .000061035
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_169052300_92348    .000061035
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_169052300_92732    .000061035
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_169052300_92861    .000061035
SYS    WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_SES_MXDB_MXSN      .000061035

26 rows selected.

Elapsed: 00:00:00.11
04:03:02 SQL>
at No comments:

Friday, 18 November 2011

Cross Platform Transportable Tablespace1

Cross Platform Transportable Tablespace

I am going to write transport tablespace from one plateform to another platform step by step.
Note:This is only for study purpose. If you experiment on production database its own risk.

Scenario

    Source
        Database : Oracle 10gR2        Platform : Linux 32 bit
    Target
        Database : Oracle 10gR2
        Platform : Windows XP 32 bit

Step 1
    The Target and Source db characterset and national characterset should be identical

    Source Database
        SQL> set pages 500
        SQL> set line 100
        SQL> COL PROPERTY_NAME FORMAT A30
        SQL> COL PROPERTY_VALUE FORMAT A30
        SQL> COL DESCRIPTION FORMAT A30

        SQL> SELECT * FROM DATABASE_PROPERTIES WHERE property_name LIKE    '%CHARACTERSET%';
       
        Output

        PROPERTY_NAME                                 PROPERTY_VALUE                 DESCRIPTION
        ------------------------------ ------------------------------ ----------------------------------------
        NLS_CHARACTERSET                          WE8ISO8859P1                        Character set
        NLS_NCHAR_CHARACTERSET          AL16UTF16                              NCHAR Character set


    Target Database
        SQL> set pages 500
        SQL> set line 100
        SQL> COL PROPERTY_NAME FORMAT A30
        SQL> COL PROPERTY_VALUE FORMAT A30
        SQL> COL DESCRIPTION FORMAT A30

        SQL> SELECT * FROM DATABASE_PROPERTIES WHERE property_name LIKE '%CHARACTERSET%';
       
        OUTPUT
        PROPERTY_NAME                              PROPERTY_VALUE                 DESCRIPTION
        ------------------------------ ------------------------------ ------------------------------
        NLS_CHARACTERSET                         WE8ISO8859P1                        Character set
        NLS_NCHAR_CHARACTERSET         AL16UTF16                              NCHAR Character set
   
    If NLS_CHARACTERSET is not same on source and target Database then follow following step on the target database

        SQL> connect sys/**********@ORCL as sysdba

        SQL> SELECT * FROM V$NLS_PARAMATER WHERE PARAMETER = 'NLS_CHARACTERSET';

        SQL> UPDATE PROPS$ SET VALUE$='WE8ISO8859P1' WHERE NAME LIKE 'NLS_CHARACTERSET';

        SQL> COMMIT;
       
        SQL> SHUTDOWN IMMEDIATE

        SQL> STARTUP
       
        SQL> SELECT * FROM V$NLS_PARAMATER WHERE PARAMETER = 'NLS_CHARACTERSET';

STEP 2

    Compatible
   
        Target Database
       
        SQL> SHOW PARAMETER COMPATIBLE;

        Source Database

        SQL> SHOW PARAMETER COMPATIBLE;

    Block Size
   
        From Oracle 9i onwords we can transport tablespace across the database with different standard block size.

STEP 3

    Create tablespace on source database( Linux )

        SQL> connect sys/****** as sysdba
       
        SQL> CREATE TABLESPACE sales_delhi
            DATAFILE '/u03/app/oracle/product/10.2.0/oradata/sales_delhi01.dbf' SIZE 200M;
       
        SQL> CREATE USER dilip IDENTIFIED BY dilip ACCOUNT UNLOCK;

        SQL> GRANT CREATE SESSION TO DILIP;
       
        SQL> GRANT CREATE TABLE, SELECT ANY TABLE TO DILIP;
       
        SQL> GRANT DROP TABLE TO DILIP;
       
        SQL> GRANT UPDATE ANY TABLE TO DILIP;

        SQL> CONNECT DILIP/dilip;

        SQL> CREATE TABLE ORDER( ORDER_ID NUMBER(5) PRIMARY KEY,
                     CUSTOMER_NAME VARCHAR2(20) NOT NULL,
                     ADDRESS VARCHAR2(30),
                     PRODUCT_NAME VARCHAR2(15) NOT NULL,
                     QTY NUMBER(3),
                     ORDER_DATE DATE,
                     SALES_PERSON VARCHAR2(20))
                     TABLESPACE SALES_DELHI;
       
        Enter order data collected by sales person

       
        SQL> commit;

            SQL>select table_name,tablespace_name from dba_tables where tablespace_name like 'SALE_%';
       
STEP 4

    Transport tablespace SALES_DELHI from Linux 32 bit platform to Windows XP 32 bit platform.
    First we validate whether the tablespace SALES_DELHI is self contained or not. Execute following query.

    SQL> EXEC DBMS_TTS.TRANSPORT_SET_CHECK('SALES_DELHI',TRUE,TRUE);
   
    OUTPUT
     PL/SQL procedure successfully complited.

    SQL> SELECT * FROM FROM TRANSPORT_SET_VIOLATIONS;
   
    OUTPUT
     No ROW SELECTED.

STEP 5

    Now I put the tablespace in READ ONLY mode.
   
    SQL> ALTER TABLESPACE SALES_DELHI READ ONLY;

    SQL> SELECT tablespace_name,status FROM DBA_TABLESPACES;

STEP 6

    Now I export tablespace with DATAPUMP(expdp) and I use the TRANSPORT_TABLESPACE and the TRANSPORT_FULL_CHECK attributes.
    Before using DATAPUMP(expdp) I would have to create directory.

    [oracle@dilip~] mkdir -p /u03/app/oracle/transport
   
    [oracle@dilip~] export ORACLE_SID=ORCL

    [oracle@dilip~] sqlplus sys/******@ORCL as sysdba

    SQL> CREATE DIRECTORY TTS_DIR AS '/u03/app/oracle/transport';
   
    SQL> HOST

    [oracle@dilip~] expdp userid=system/****** DUMPFILE=tts.dmp DIRECTORY=tts_dir TRANSPORT_TABLESPACE=sales_delhi TRANSPORT_FULL_CHECK=Y

STEP 7

    Execute following Query on both source and target platform

    Source Platform

        SQL> select * from v$transportable_platform order by platform_id;


        SQL> SELECT tp.platform_name,
                tp.endian_format,
             FROM V$database d, V$transportable_platform tp
             WHERE d.platform_name=tp.platform_name;

        OUTPUT   

        PLATFORM_NAME                                                                                         ENDIAN_FORMAT
        ----------------------------------------------------------------------------------------------------- --------------
        Linux IA (32-bit)                                                                                                  Little



   
    Target Platform
       
        SQL> select tp.platform_name,tp.endian_format
             from v$database d, v$transportable_platform tp
             where tp.platform_name=d.platform_name;

        OUTPUT

        PLATFORM_NAME                                                                                         ENDIAN_FORMAT
        ----------------------------------------------------------------------------------------------------- --------------
        Microsoft Windows IA (32-bit)                                                                            Little


STEP 7
   
    I will use the RMAN's CONVERT COMMAND


    [oracle@dilip~] export ORACLE_SID=orcl

    [oracle@dilip~] rman target /
   
    RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 1;

    RMAN> CONVERT TABLESPACE SALES_DELHI
          TO PLATFORM 'Microsoft Windows IA (32-bit)'
          FORMAT '/u03/app/oracle/transport/%U';

    Enable RMAN Compression Again
   
    RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;

    RMAN> EXIT

STEP 8

    Now I move both tts.dmp (exported by datapump using expdp) and converted datafile ( using RMAN) towords the target platform.
    Any medium we can use to move these file from source to target.
    Here I am using Pen Drive because my tablespace is near about 1GB.


    Source platform

        Copy both files(tts.dmp and rman converted datafile) from /u03/app/oracle/transport and save to pen derive.


    Target Platform

        Create Directory in Target Platform   
       
        C:\> cd oracle
        C:\oracle> mkdir import
       
        Save both files(tts.dmp and RMAN converted datafile) from pen derive to "C:\Oracle\import"
        Change Datafie name which is converted by RMAN. Changed to SALES_DELHI_2011
        C:\oracle> set ORACLE_SID=MYDB
        C:\oracle> sqlplus sys/******@MYDB as sysdba
       
        SQL> create directory imp_dir as 'C:\oracle\import';
       
        SQL> HOST
       
        C:\oracle> cd import

        C:\oracle\import> impdp system/****** DUMPFILE=tts.dmp DIRECTORY=imp_dir TRANSPORT_DATAFILE='C:\oracle\import\sales_delhi_2011'

        C:\oracle\import> sqlplus

        SQL> create user dilip identified by dilip account unlock;

        SQL> alter tablespace sales_delhi_2011 read write;

        SQL> connect dilip/dilip

        SQL> SQL> select table_name,tablespace_name from dba_tables where tablespace_name like 'SALE_%';



   

       









   







       


   
at No comments:
Subscribe to: Posts (Atom)