Skip to main content

Posts

Showing posts from 2020

How to get SQL ID STAT information in Oracle Database 12c

 How to get SQL ID STAT information in Oracle Database 12c Please use the following Query to get the information of SQL ID statistics in oracle database. It will provide you the information about SQL ID and their Plan hash value information along with execution details. Don't forget to remove comments and format the SQL before running. WITH sql_id_list AS ( SELECT /*+ MATERIALIZE */ regexp_substr(:SQL_ID, '[^,]+', 1, LEVEL) SQL_ID   FROM dual CONNECT BY LEVEL <= length(:SQL_ID) - length(REPLACE(:SQL_ID, ',', '')) + 1 ), sqlstat AS ( SELECT TRUNC(last_active_Time,:TRUNC_DATE_FORMAT) last_active_Time       --,s.instance_number inst_num       ,s.sql_id       ,s.plan_hash_value       ,MAX(s.child_number) max_child_number       ,command_type       ,parsing_schema_name schema       --,module       ,ROUND((SUM(s.elapsed_time)/1000000)/60,2) elapsed_time_minutes ...

How to get SQL Standard Deviation information of SQL ID in Oracle 12c

 How to get SQL Standard Deviation information of SQL ID in Oracle Database. Please user the below SQL to get the information of SQL Standard Deviation information. Don't forget to remove comment lines before running and formatting the sql. WITH sqlstat AS ( SELECT TRUNC(h.begin_interval_time,:TRUNC_DATE_FORMAT) snap_time  -- Change to MI, HH , or DD for a individual sql statement to checking performance over time       ,s.instance_number inst_num       --,MIN(h.begin_interval_time) min_start_date       --,MAX(h.begin_interval_time) max_end_date       --,MAX(s.snap_id) max_snap_id       ,s.sql_id       ,s.plan_hash_value       ,parsing_schema_name schema       ,module       ,ROUND((SUM(s.elapsed_time_delta)/1000000)/60,2) elapsed_time_minutes       ,ROUND(SUM(s.elapsed_time_delta)/1000000,2) elapsed_time_seconds     ...

How to Move objects from one tablespace to another tablespace online

How to Move objects from one tablespace to another tablespace online using procedure. set serveroutput on set lines 300 DECLARE V_CURRENT_TABLESPACE VARCHAR2(50); V_DEST_TABLESPACE VARCHAR2(50); V_SQL VARCHAR2(1000); V_OVERFLOW NUMBER := 0; BEGIN V_CURRENT_TABLESPACE := '&current_tablespace'; V_DEST_TABLESPACE := '&destination_tablespace'; FOR LIST_OWNER IN (SELECT DISTINCT OWNER FROM DBA_SEGMENTS WHERE TABLESPACE_NAME=V_CURRENT_TABLESPACE and OWNER='&my_schema') LOOP DBMS_OUTPUT.PUT_LINE('MOVING TABLES:'); DBMS_OUTPUT.PUT_LINE('------------------'); FOR LIST_TABLES IN (SELECT TABLE_NAME, IOT_NAME FROM DBA_TABLES WHERE OWNER=LIST_OWNER.OWNER AND TABLESPACE_NAME=V_CURRENT_TABLESPACE) LOOP IF  LIST_TABLES.IOT_NAME IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE('MOVING INDEX ORGANIZED TABLE ' || LIST_TABLES.IOT_NAME || '...'); V_SQL := 'ALTER TABLE "' || LIST_OWNER.OWNER  ...

How to add tables to existing Golden Gate Setup Extract/Pump/Replicat

How to add tables to existing Extract/Pump and Replicator Step 1: Stop all Extract, Pump and Replicators Step 2: Add the new tables to extract param file Example : edit params EXT1 Step 3: Login with user id alias   and add schema trandata / table transdata Dblogin useridalias GGADMIN Example: add schematrandata <schema name>, allcols for table: add trandata <schema_name>.<table_name> step 4: start the extract and pump process step 5: Perform the initial load by taking exp backup using flashback scn Select current_scn from v$database; Expdp parfile userid='/ as sysdba' directory=EXPORT_GG dumpfile=<file_name>.dmp logfile=<log_name>.log tables=<’SCHEMA.TABLE_NAME’ flashback_scn=<current_scn from v$database step 6: Move the dumpfile to target server Scp <dump file name> <target server name>:<location> Step 7: Import the table...