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
Dear Readers, In this article, we will see the following Oracle (SecureCRT) : Important Queries for UNDO Management. MenuBar : UNDO Tab Name : Undo_Usage
|
1 2 |
set linesize 200 pagesize 200 compute sum of "Total Space in GB" on report |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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
|
1 2 3 4 |
SET TERMOUT OFF; COLUMN current_instance NEW_VALUE current_instance NOPRINT; SELECT rpad(instance_name, 17) current_instance FROM v$instance; SET TERMOUT ON; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
PROMPT PROMPT +------------------------------------------------------------------------+ PROMPT | Report : Undo Contention | PROMPT | Instance : ¤t_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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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
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
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
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.
|
1 2 3 4 5 6 |
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
|
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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
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 |
Full schema backup completed ,… Read More
Dear Readers, In this article, we will see the following Install PostgreSQL12 On SUSE Linux. Steps to Follow : Logging to AWS Account Launch PostgreSQL EC2 Instance Connect to Linux EC2 Instance by Using Putty Add PostgreSQL Repository Install PostgreSQL 12 Enable the PostgreSQL Start… Read More
Dear Readers, In this article, we will see the following Installation of SQL Server On AWS Cloud. This provides a step by step procedure for installing a new instance of SQL server 2014 by using the SQL server setup installation wizard. Now I will show… Read More
Hello Everyone, In this article , we will learn how to Attach and de-attach the expdp/impdp datapump jobs. EXPDP and IMPDP is used to take the logical backup of full database, tablespace ,schema,table and query level. we can attach or deattach the datapump jobs using… Read More
Dear Readers, In this article, we will see the SQL Joins. Joins A join is a query that combines rows from two or more tables Join Conditions Most join queries contain WHERE clause conditions that compare two columns, each from a different table. Such a… Read More
Dear Readers, In this article, we will see the following Introduction to SQL Server. DBMS: Human needs have increased tremendously. Now people are doing much more composite tasks than ever before. Society has become very complex if a person has to work in a huge… Read More
