KTEXPERTS – Page 21 – KNOWLEDGE SHARING PLATFORM

SQL Server Database Architecture

3 years ago by KTEXPERTS TEAMSQL Server DBA

Dear Readers, In this article, we will see SQL Server Database Architecture. SQL Server MDF Files In the SQL Server Primary Data Files are the basic data files which are related to the database for the storing and supervision of the information. MDF file in… Read More

Download and Install Maven Plugin In Jenkins

3 years ago by Ramesh ReddyDevOps

Dear Readers, In this article,we will see Download and Install Maven Plugin In Jenkins. Steps to Follow Logging to Jenkins. Download and Install Maven Plugin. Restart Jenkins. Logging to Jenkins. Verify plugin “maven integration” and “green balls”. First,we need to go Jenkins Logging page… Read More

How to Generate DDL from existing dumpfile

3 years ago by Binnary Ajay KumarOracle Datapump (Export & Imports)

Hello everyone, In this article, we will learn Generating DDL using existing dumpfile. To generate DDL of a schema or table from a dumpfile we use a parameter called SQLFILE. Purpose: SQLFILE is a paramater which can be used in IMPDP utility to generate DDL… Read More

Updating Patches in SQL Server

3 years ago by KTEXPERTS TEAMSQL Server DBA

Dear Readers, In this article, we will see the Upgradation and Data Migration in SQL Server. We have to download the Sql server patches from Sql server Builds site. (https://sqlserverbuilds.blogspot.com/) What is Cumulative updates (CU) 👉 Each new CU contains all the fixes that were… Read More

Oracle (SecureCRT) : Temp Tablespace Monitoring Queries

3 years ago by Vinod SiramSecure CRT

Dear Readers, In this article, we will see Oracle (SecureCRT) : Temp Tablespace Monitoring Queries. Menubar : TEMP Tabname : Def_temp

set linesize 300 
col property_name for a35 
col property_value for a20 
col description for a45

set linesize 300

col property_name for a35

col property_value for a20

col description for a45

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; 

PROPERTY_NAME                       PROPERTY_VALUE       DESCRIPTION
----------------------------------- -------------------- ---------------------------------------------
DEFAULT_TEMP_TABLESPACE             TEMP                 Name of default temporary tablespace

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

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

DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace

Tabname : TS_Temp_Usage

set linesize 300 
SELECT   A.tablespace_name tablespace, D.mb_total, 
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, 
    D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free 
  FROM     v$sort_segment A, 
         ( 
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total 
         FROM     v$tablespace B, v$tempfile C 
         WHERE    B.ts#= C.ts# 
    GROUP BY B.name, C.block_size 
           ) D 
WHERE    A.tablespace_name = D.name 
GROUP by A.tablespace_name, D.mb_total; 
  
TABLESPACE                       MB_TOTAL    MB_USED    MB_FREE
------------------------------ ---------- ---------- ----------
TEMP                                32767          6      32761

set linesize 300

SELECT A.tablespace_name tablespace, D.mb_total,

SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,

D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free

FROM v$sort_segment A,

(

SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total

FROM v$tablespace B, v$tempfile C

WHERE B.ts#= C.ts#

GROUP BY B.name, C.block_size

) D

WHERE A.tablespace_name = D.name

GROUP by A.tablespace_name, D.mb_total;

TABLESPACE MB_TOTAL MB_USED MB_FREE

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

TEMP 32767 6 32761

Tabname : Temp_Space_Usage_SQLID

set lines 232 pages 1000  

column username format a15 
column osuser format a10 
column module format a35 
column program format a20 
column Tablespace format a10

set lines 232 pages 1000

column username format a15

column osuser format a10

column module format a35

column program format a20

column Tablespace format a10

select * from (SELECT S.sid ,S.serial# , S.username, S.osuser, S.module,s.SQL_ID, 
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, 
COUNT(*) statements 
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P 
WHERE T.session_addr = S.saddr 
AND S.paddr = P.addr 
AND T.tablespace = TBS.tablespace_name 
GROUP BY S.sid, S.serial#, S.username, S.osuser,s.sql_id,P.spid, S.module, 
P.program, TBS.block_size, T.tablespace 
ORDER BY username) where mb_used > 1000;

select * from (SELECT S.sid ,S.serial# , S.username, S.osuser, S.module,s.SQL_ID,

P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,

COUNT(*) statements

FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P

WHERE T.session_addr = S.saddr

AND S.paddr = P.addr

AND T.tablespace = TBS.tablespace_name

GROUP BY S.sid, S.serial#, S.username, S.osuser,s.sql_id,P.spid, S.module,

P.program, TBS.block_size, T.tablespace

ORDER BY username) where mb_used > 1000;

Tabname : Temp_sort_users

SET TERMOUT OFF; 
COLUMN current_instance NEW_VALUE current_instance NOPRINT; 
SELECT rpad(instance_name, 17) current_instance FROM v$instance; 
SET TERMOUT ON; 

PROMPT  
PROMPT +------------------------------------------------------------------------+ 
PROMPT | Report   : Temporary Sort Users                                        | 
PROMPT | Instance : &current_instance                                           | 
PROMPT +------------------------------------------------------------------------+ 

SET ECHO        OFF 
SET FEEDBACK    6 
SET HEADING     ON 
SET LINESIZE    180 
SET PAGESIZE    50000 
SET TERMOUT     ON 
SET TIMING      OFF 
SET TRIMOUT     ON 
SET TRIMSPOOL   ON 
SET VERIFY      OFF 

CLEAR COLUMNS 
CLEAR BREAKS 
CLEAR COMPUTES 

COLUMN instance_name      FORMAT a9                 HEADING 'Instance' 
COLUMN tablespace_name    FORMAT a15                HEADING 'Tablespace Name' 
COLUMN sid                FORMAT 99999              HEADING 'SID' 
COLUMN serial_id          FORMAT 99999999           HEADING 'Serial ID' 
COLUMN session_status     FORMAT a9                 HEADING 'Status' 
COLUMN oracle_username    FORMAT a18                HEADING 'Oracle User' 
COLUMN os_username        FORMAT a18                HEADING 'O/S User' 
COLUMN os_pid             FORMAT a8                 HEADING 'O/S PID' 
COLUMN session_terminal   FORMAT a10                HEADING 'Terminal'         TRUNC 
COLUMN session_machine    FORMAT a30                HEADING 'Machine'          TRUNC 
COLUMN session_program    FORMAT a20                HEADING 'Session Program'  TRUNC 
COLUMN contents           FORMAT a9                 HEADING 'Contents' 
COLUMN extents            FORMAT 999,999,999        HEADING 'Extents' 
COLUMN blocks             FORMAT 999,999,999        HEADING 'Blocks' 
COLUMN bytes              FORMAT 999,999,999,999    HEADING 'Bytes' 
COLUMN segtype            FORMAT a12                HEADING 'Segment Type' 

BREAK ON instance_name SKIP PAGE

SET TERMOUT OFF;

COLUMN current_instance NEW_VALUE current_instance NOPRINT;

SELECT rpad(instance_name, 17) current_instance FROM v$instance;

SET TERMOUT ON;

PROMPT

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : Temporary Sort Users |

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

SET ECHO OFF

SET FEEDBACK 6

SET HEADING ON

SET LINESIZE 180

SET PAGESIZE 50000

SET TERMOUT ON

SET TIMING OFF

SET TRIMOUT ON

SET TRIMSPOOL ON

SET VERIFY OFF

CLEAR COLUMNS

CLEAR BREAKS

CLEAR COMPUTES

COLUMN instance_name FORMAT a9 HEADING 'Instance'

COLUMN tablespace_name FORMAT a15 HEADING 'Tablespace Name'

COLUMN sid FORMAT 99999 HEADING 'SID'

COLUMN serial_id FORMAT 99999999 HEADING 'Serial ID'

COLUMN session_status FORMAT a9 HEADING 'Status'

COLUMN oracle_username FORMAT a18 HEADING 'Oracle User'

COLUMN os_username FORMAT a18 HEADING 'O/S User'

COLUMN os_pid FORMAT a8 HEADING 'O/S PID'

COLUMN session_terminal FORMAT a10 HEADING 'Terminal' TRUNC

COLUMN session_machine FORMAT a30 HEADING 'Machine' TRUNC

COLUMN session_program FORMAT a20 HEADING 'Session Program' TRUNC

COLUMN contents FORMAT a9 HEADING 'Contents'

COLUMN extents FORMAT 999,999,999 HEADING 'Extents'

COLUMN blocks FORMAT 999,999,999 HEADING 'Blocks'

COLUMN bytes FORMAT 999,999,999,999 HEADING 'Bytes'

COLUMN segtype FORMAT a12 HEADING 'Segment Type'

BREAK ON instance_name SKIP PAGE

SELECT 
    i.instance_name       instance_name 
  , t.tablespace          tablespace_name 
  , s.sid                 sid 
  , s.serial#             serial_id 
  , s.status              session_status 
  , s.username            oracle_username 
  , s.osuser              os_username 
  , p.spid                os_pid 
  , s.program             session_program 
  , t.contents            contents 
  , t.segtype             segtype 
  , (t.blocks * c.value)  bytes 
FROM 
    gv$instance     i 
  , gv$session      s 
  , gv$process      p 
  , gv$sort_usage   t 
  , (select value from v$parameter 
     where name = 'db_block_size') c 
WHERE 
      s.inst_id = p.inst_id 
  AND p.inst_id = i.inst_id 
  AND t.inst_id = i.inst_id 
  AND s.inst_id = i.inst_id 
  AND s.saddr = t.session_addr 
  AND s.paddr = p.addr 
ORDER BY 
    i.instance_name 
  , s.sid; 

  
Instance  Tablespace Name    SID Serial ID Status    Oracle User        O/S User           O/S PID  Session Program      Contents  Segment Type            Bytes
--------- --------------- ------ --------- --------- ------------------ ------------------ -------- -------------------- --------- ------------ ----------------
ORCL11    TEMP              1513     59797 ACTIVE                       oracle             25309    oracle@server1234567  TEMPORARY LOB_DATA            5,242,880
          TEMP              1513     59797 ACTIVE                       oracle             25309    oracle@server1234567  TEMPORARY LOB_INDEX           1,048,576

Instance  Tablespace Name    SID Serial ID Status    Oracle User        O/S User           O/S PID  Session Program      Contents  Segment Type            Bytes
--------- --------------- ------ --------- --------- ------------------ ------------------ -------- -------------------- --------- ------------ ----------------
ORCL12    TEMP              1513     40323 ACTIVE                       oracle             13260    oracle@serverAB34567  TEMPORARY LOB_DATA            5,242,880
          TEMP              1513     40323 ACTIVE                       oracle             13260    oracle@serverAB34567  TEMPORARY LOB_INDEX           1,048,576

SELECT

i.instance_name instance_name

, t.tablespace tablespace_name

, s.sid sid

, s.serial# serial_id

, s.status session_status

, s.username oracle_username

, s.osuser os_username

, p.spid os_pid

, s.program session_program

, t.contents contents

, t.segtype segtype

, (t.blocks * c.value) bytes

FROM

gv$instance i

, gv$session s

, gv$process p

, gv$sort_usage t

, (select value from v$parameter

where name = 'db_block_size') c

WHERE

s.inst_id = p.inst_id

AND p.inst_id = i.inst_id

AND t.inst_id = i.inst_id

AND s.inst_id = i.inst_id

AND s.saddr = t.session_addr

AND s.paddr = p.addr

ORDER BY

i.instance_name

, s.sid;

Instance Tablespace Name SID Serial ID Status Oracle User O/S User O/S PID Session Program Contents Segment Type Bytes

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

ORCL11 TEMP 1513 59797 ACTIVE oracle 25309 oracle@server1234567 TEMPORARY LOB_DATA 5,242,880

TEMP 1513 59797 ACTIVE oracle 25309 oracle@server1234567 TEMPORARY LOB_INDEX 1,048,576

Instance Tablespace Name SID Serial ID Status Oracle User O/S User O/S PID Session Program Contents Segment Type Bytes

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

ORCL12 TEMP 1513 40323 ACTIVE oracle 13260 oracle@serverAB34567 TEMPORARY LOB_DATA 5,242,880

TEMP 1513 40323 ACTIVE oracle 13260 oracle@serverAB34567 TEMPORARY LOB_INDEX 1,048,576

Tabname : Temp_Space_Usage_SQLID_Login_Active_InActive

set lines 232 pages 1000  

column username format a15 
column osuser format a10 
column module format a35 
column program format a30 
column Tablespace format a10

set lines 232 pages 1000

column username format a15

column osuser format a10

column module format a35

column program format a30

column Tablespace format a10

select * from (SELECT S.sid ,S.serial# , S.username, S.osuser, S.module,s.SQL_ID, 
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,s.logon_time,s.status, 
COUNT(*) statements 
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P 
WHERE T.session_addr = S.saddr 
AND S.paddr = P.addr 
AND T.tablespace = TBS.tablespace_name 
GROUP BY S.sid, S.serial#, S.username, S.osuser,s.sql_id,P.spid, S.module, 
P.program, TBS.block_size, T.tablespace,s.logon_time,s.status 
ORDER BY s.logon_time) where mb_used > 10;

select * from (SELECT S.sid ,S.serial# , S.username, S.osuser, S.module,s.SQL_ID,

P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,s.logon_time,s.status,

COUNT(*) statements

FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P

WHERE T.session_addr = S.saddr

AND S.paddr = P.addr

AND T.tablespace = TBS.tablespace_name

GROUP BY S.sid, S.serial#, S.username, S.osuser,s.sql_id,P.spid, S.module,

P.program, TBS.block_size, T.tablespace,s.logon_time,s.status

ORDER BY s.logon_time) where mb_used > 10;

Tabname : Addspace… Read More

Upgradation and Data Migration in SQL Server

3 years ago by KTEXPERTS TEAMSQL Server DBA

Dear Readers, In this article, we will see the Upgradation and Data Migration in SQL Server. Version upgradation 2014 to 2016 SQL 2014 evaluation edition….. Enterprise edition. In SQL server 2014 standard edition to enterprise edition The upgrading the version in SQL server 2014 it… Read More

Oracle (SecureCRT) : Important Queries for UNDO Management

3 years ago by Vinod SiramSecure CRT

Dear Readers, In this article, we will see the following Oracle (SecureCRT) : Important Queries for UNDO Management. MenuBar : UNDO Tab Name : Undo_Usage

set linesize 200 pagesize 200 
compute sum of "Total Space in GB" on report

1 2	set linesize 200 pagesize 200 compute sum of "Total Space in GB" on report

select tablespace_name, status segment_status, count(extent_id) "Extent Count", sum(blocks) "Total Blocks",
sum(blocks)*8/(1024) "Total Space in MB"
from dba_undo_extents
group by tablespace_name, status
order by tablespace_name
/

TABLESPACE_NAME                SEGMENT_S Extent Count Total Blocks Total Space in MB
------------------------------ --------- ------------ ------------ -----------------
UNDOTBS1                       EXPIRED            173        10264           80.1875
UNDOTBS1                       UNEXPIRED           68         5944           46.4375
UNDOTBS2                       EXPIRED            177         9816           76.6875
UNDOTBS2                       UNEXPIRED           76         6608            51.625

select tablespace_name, status segment_status, count(extent_id) "Extent Count", sum(blocks) "Total Blocks",

sum(blocks)*8/(1024) "Total Space in MB"

from dba_undo_extents

group by tablespace_name, status

order by tablespace_name

TABLESPACE_NAME SEGMENT_S Extent Count Total Blocks Total Space in MB

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

UNDOTBS1 EXPIRED 173 10264 80.1875

UNDOTBS1 UNEXPIRED 68 5944 46.4375

UNDOTBS2 EXPIRED 177 9816 76.6875

UNDOTBS2 UNEXPIRED 76 6608 51.625

Tab Name : Undo_Contetion

SET TERMOUT OFF; 
COLUMN current_instance NEW_VALUE current_instance NOPRINT; 
SELECT rpad(instance_name, 17) current_instance FROM v$instance; 
SET TERMOUT ON;

SET TERMOUT OFF;

COLUMN current_instance NEW_VALUE current_instance NOPRINT;

SELECT rpad(instance_name, 17) current_instance FROM v$instance;

SET TERMOUT ON;

PROMPT  
PROMPT +------------------------------------------------------------------------+ 
PROMPT | Report   : Undo Contention                                             | 
PROMPT | Instance : &current_instance                                           | 
PROMPT +------------------------------------------------------------------------+ 

SET ECHO        OFF 
SET FEEDBACK    6 
SET HEADING     ON 
SET LINESIZE    180 
SET PAGESIZE    50000 
SET TERMOUT     ON 
SET TIMING      OFF 
SET TRIMOUT     ON 
SET TRIMSPOOL   ON 
SET VERIFY      OFF 

CLEAR COLUMNS 
CLEAR BREAKS 
CLEAR COMPUTES 

COLUMN instance_name      FORMAT a10    HEAD 'Instance' 
COLUMN class              FORMAT a18    HEADING 'Class'     
COLUMN ratio                            HEADING 'Wait Ratio'        

BREAK ON instance_name SKIP 2

PROMPT

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : Undo Contention |

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

SET ECHO OFF

SET FEEDBACK 6

SET HEADING ON

SET LINESIZE 180

SET PAGESIZE 50000

SET TERMOUT ON

SET TIMING OFF

SET TRIMOUT ON

SET TRIMSPOOL ON

SET VERIFY OFF

CLEAR COLUMNS

CLEAR BREAKS

CLEAR COMPUTES

COLUMN instance_name FORMAT a10 HEAD 'Instance'

COLUMN class FORMAT a18 HEADING 'Class'

COLUMN ratio HEADING 'Wait Ratio'

BREAK ON instance_name SKIP 2

SELECT 
    i.instance_name                     instance_name 
  , w.class                             class 
  , ROUND(100*(w.count/SUM(s.value)),8) ratio 
FROM 
    gv$instance i 
  , gv$waitstat w 
  , gv$sysstat s 
WHERE 
      i.inst_id = w.inst_id 
  AND i.inst_id = s.inst_id 
  AND w.class IN (  'system undo header' 
                  , 'system undo block' 
                  , 'undo header' 
                  , 'undo block' 
                 ) 
  AND s.name IN ('db block gets', 'consistent gets') 
GROUP BY 
    i.instance_name 
  , w.class 
  , w.count 
ORDER BY 
    i.instance_name 
  , w.class; 
  
Instance   Class              Wait Ratio
---------- ------------------ ----------
ORCL11     system undo block           0
           system undo header          0
           undo block           .0001595
           undo header          .0002848


ORCL12     system undo block           0
           system undo header          0
           undo block          .00018723
           undo header         .00030043

SELECT

i.instance_name instance_name

, w.class class

, ROUND(100*(w.count/SUM(s.value)),8) ratio

FROM

gv$instance i

, gv$waitstat w

, gv$sysstat s

WHERE

i.inst_id = w.inst_id

AND i.inst_id = s.inst_id

AND w.class IN ( 'system undo header'

, 'system undo block'

, 'undo header'

, 'undo block'

)

AND s.name IN ('db block gets', 'consistent gets')

GROUP BY

i.instance_name

, w.class

, w.count

ORDER BY

i.instance_name

, w.class;

Instance Class Wait Ratio

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

ORCL11 system undo block 0

system undo header 0

undo block .0001595

undo header .0002848

ORCL12 system undo block 0

system undo header 0

undo block .00018723

undo header .00030043

Tab Name : Undo_Trend_Today

set linesize 300 
SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME, 
         TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME, 
         UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON", MAXQUERYLEN, TUNED_UNDORETENTION 
         FROM v$UNDOSTAT WHERE rownum <= 144; 
		 
Instance   Class              Wait Ratio
---------- ------------------ ----------
ORCL11     system undo block           0
           system undo header          0
           undo block           .0001595
           undo header          .0002848


ORCL12     system undo block           0
           system undo header          0
           undo block          .00018723
           undo header         .00030044

set linesize 300

SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,

TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,

UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON", MAXQUERYLEN, TUNED_UNDORETENTION

FROM v$UNDOSTAT WHERE rownum <= 144;

Instance Class Wait Ratio

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

ORCL11 system undo block 0

system undo header 0

undo block .0001595

undo header .0002848

ORCL12 system undo block 0

system undo header 0

undo block .00018723

undo header .00030044

Tab Name : Undo_Usage_For_Sessions

set linesize 300 
set pagesize 300 
col sid_serial for a30 
SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial, 
NVL(s.username, 'None') orauser, 
s.program, 
r.name undoseg, 
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo" 
FROM sys.v_$rollname r, 
sys.v_$session s, 
sys.v_$transaction t, 
sys.v_$parameter x 
WHERE s.taddr = t.addr 
AND r.usn = t.xidusn(+) 
AND x.name = 'db_block_size';

set linesize 300

set pagesize 300

col sid_serial for a30

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,

NVL(s.username, 'None') orauser,

s.program,

r.name undoseg,

t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"

FROM sys.v_$rollname r,

sys.v_$session s,

sys.v_$transaction t,

sys.v_$parameter x

WHERE s.taddr = t.addr

AND r.usn = t.xidusn(+)

AND x.name = 'db_block_size';

Tab… Read More

How to utilize PARFILE parameter in DATAPUMP Exports and Imports

3 years ago by Binnary Ajay KumarOracle Datapump (Export & Imports)

Hello Everyone, In this article , we will learn how to utilize PARFILE parameter in DATAPUMP Exports and Imports. what is parfile ? Parfile is a parameter in datapump exports and imports . The name of the parfile can give in any format but extension… Read More

How to Install Terraform On Windows Machine

3 years ago by Ramesh ReddyDevOps

Dear Readers, In this article, we will see Installation of Terraform On Windows Machine. Steps to Follow : Download Terraform software Install Terraform Set the Environment variables 1. Download Terraform Software First, we need to Terraform official website by using below link. https://www.terraform.io/downloads.html Select Windows… Read More

Excluding tables while performing datapump import from Full schema backup

3 years ago by Binnary Ajay KumarOracle Datapump (Export & Imports)

Hello Everyone, In this article , we will learn Excluding tables while performing datapump import from full schema backup. Database : AVPROD Schema : OT Check objects count in OT Schema.

SQL> SELECT  COUNT(*), OBJECT_TYPE,STATUS FROM DBA_OBJECTS WHERE OWNER='&USERNAME' group by object_type,status;
  COUNT(*) OBJECT_TYPE             STATUS
---------- ----------------------- -------
         7 SEQUENCE                VALID
         12 TABLE                  VALID
         12 INDEX                  VALID

SQL> SELECT COUNT(*), OBJECT_TYPE,STATUS FROM DBA_OBJECTS WHERE OWNER='&USERNAME' group by object_type,status;

COUNT(*) OBJECT_TYPE STATUS

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

7 SEQUENCE VALID

12 TABLE VALID

12 INDEX VALID

Take full schema backup using schema=OT

[oracle@ip-172-31-6-16 ~]$ expdp directory=pump dumpfile=OT_tables.dmp logfile=OT_tables.log schemas=OT parallel=3 job_name=OT

1	[oracle@ip-172-31-6-16 ~]$ expdp directory=pump dumpfile=OT_tables.dmp logfile=OT_tables.log schemas=OT parallel=3 job_name=OT

Export: Release 12.1.0.2.0 - Production on Sun Jan 24 16:14:21 2021
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."OT":  /******** AS SYSDBA directory=pump dumpfile=OT_tables.dmp logfile=OT_tables.log schemas=OT parallel=3 job_name=OT 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 768 KB
Processing object type SCHEMA_EXPORT/USER
. . exported "OT"."CONTACTS"                             28.70 KB     319 rows
. . exported "OT"."COUNTRIES"                            6.375 KB      25 rows
. . exported "OT"."CUSTOMERS"                            32.25 KB     319 rows
. . exported "OT"."EMPLOYEES"                            17.35 KB     107 rows
. . exported "OT"."INVENTORIES"                          21.40 KB    1112 rows
. . exported "OT"."LOCATIONS"                            8.382 KB      23 rows
. . exported "OT"."ORDERS"                               9.773 KB     105 rows
. . exported "OT"."ORDER_ITEMS"                          21.20 KB     665 rows
. . exported "OT"."PRODUCTS"                             33.67 KB     288 rows
. . exported "OT"."PRODUCT_CATEGORIES"                   5.585 KB       5 rows
. . exported "OT"."REGIONS"                              5.554 KB       4 rows
. . exported "OT"."WAREHOUSES"                           6.164 KB       9 rows
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Master table "SYS"."OT" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.OT is:
  /u01/oradata/AVPROD/pump/OT_tables.dmp
Job "SYS"."OT" successfully completed at Sun Jan 24 16:15:04 2021 elapsed 0 00:00:27

Export: Release 12.1.0.2.0 - Production on Sun Jan 24 16:14:21 2021

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Starting "SYS"."OT": /******** AS SYSDBA directory=pump dumpfile=OT_tables.dmp logfile=OT_tables.log schemas=OT parallel=3 job_name=OT

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 768 KB

Processing object type SCHEMA_EXPORT/USER

. . exported "OT"."CONTACTS" 28.70 KB 319 rows

. . exported "OT"."COUNTRIES" 6.375 KB 25 rows

. . exported "OT"."CUSTOMERS" 32.25 KB 319 rows

. . exported "OT"."EMPLOYEES" 17.35 KB 107 rows

. . exported "OT"."INVENTORIES" 21.40 KB 1112 rows

. . exported "OT"."LOCATIONS" 8.382 KB 23 rows

. . exported "OT"."ORDERS" 9.773 KB 105 rows

. . exported "OT"."ORDER_ITEMS" 21.20 KB 665 rows

. . exported "OT"."PRODUCTS" 33.67 KB 288 rows

. . exported "OT"."PRODUCT_CATEGORIES" 5.585 KB 5 rows

. . exported "OT"."REGIONS" 5.554 KB 4 rows

. . exported "OT"."WAREHOUSES" 6.164 KB 9 rows

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Master table "SYS"."OT" successfully loaded/unloaded

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

Dump file set for SYS.OT is:

/u01/oradata/AVPROD/pump/OT_tables.dmp

Job "SYS"."OT" successfully completed at Sun Jan 24 16:15:04 2021 elapsed 0 00:00:27

Full schema backup completed ,… Read More