set lines 500; set pages 500; set long 1000000; SELECT X.SQL_ID, X.CPU_TIME, X.EXECUTIONS, T.SQL_TEXT FROM DBA_HIST_SQLTEXT T, ( SELECT S.SQL_ID SQL_ID, SUM(S.CPU_TIME_DELTA/1000000) CPU_TIME, SUM(S.EXECUTIONS_DELTA) EXECUTIONS FROM DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT P…
Read Moreset lines 300; set pages 500; set long 1000000; select A.hash_value, A.text_subset, A.module, trunc((B.cpu_time-A.cpu_time)/1000) “CPU_TIME(ms)”, B.executions-A.executions executions, trunc(decode(B.executions-A.executions, 0, 0, (B.cpu_time-A.cpu_time)/(B.executions-A.executions))/1000) “CPU_TIME_PER_EXEC(ms)” from STATS$SQL_SUMMARY A, STATS$SQL_SUMMARY B where A.hash_value =…
Read Moreselect x.job_name,b.state,b.job_mode,b.degree , x.owner_name,z.sql_text, p.message , p.totalwork, p.sofar , round((p.sofar/p.totalwork)*100,2) done , p.time_remaining from dba_datapump_jobs b left join dba_datapump_sessions x on (x.job_name = b.job_name) left join v$session y on (y.saddr…
Read Moreset lines 150 pages 100 numwidth 7 col program for a38 col username for a10 col spid for a7 select to_char(sysdate,’YYYY-MM-DD HH24:MI:SS’) “DATE”, s.program, s.sid, s.status, s.username, d.job_name, p.spid, s.serial#,…
Read MorePlease use the below query. SELECT DISTINCT(TRUNC(last_refresh)) FROM dba_snapshot_refresh_times; select owner, mview_name, last_refresh_type, last_refresh_date from dba_mviews;
Read MorePlease use the below query. SELECT name FROM sys.user$ WHERE ext_username IS NOT NULL AND password = ‘GLOBAL’;
Read MoreSet verify off Set space 0 Set line 120 Set heading off Set feedback off Set pages 1000 Spool analyze.sql SELECT ‘Analyze cluster “‘||cluster_name||'” validate structure cascade;’ FROM dba_clusters WHERE…
Read MorePlease use the below query to check the same. column object_name format a30 select object_name, object_type from dba_objects where object_name||object_type in (select object_name||object_type from dba_objects where owner = ‘SYS’) and…
Read MoreDownload sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip set db environment cd /home/oracle unzip sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip cd sqlt/install sqlplus / as sysdba sql> start sqlcreate.sql Enter password for SQLTXPLAIN Enter Tablespace name Enter Temp tablespace name…
Read MoreINTRODUCTION The below steps showes us how to migrate a 19c database using Transportable Tablespace. MIGRATION FROM LINUX TO WINDOWS MIGRATION FROM LINUX TO WINDOWS BY TRANSPORTABLE TABLESPACE CREATED A TABLESPACE CREATED A TABLE BY HAVING DEFAULT TABLESPACE AS TBS(CREATED TABLESPACE), EXECUTED THE DBMS_TTS.TRANSPORT_SET_CHECK FOR TRANSPORTING TABLESPACE TBS THAT IS TRUE, ALSO CHECKED IF THERE IS ANY VIOLATIONS CHECKED THE ENDIAN FORMAT ON BOTH THE PLATFORMS PERFORMED EXPORT BY DATAPUMP FOR TRANSPORT TABLESPACE DATABASE IN THE WINDOWS PLATFORM PERFORMED AN IMPORT IN THE TARGET PLATFORM AFTER THE IMPORT HECK WHETHER THE TABLESPACE IS AVAILABLE AND MAKE IT READ WRITE, CHECK FOR THE TABLE CREATED WITH DEFAULT TABLESPACE(TBS)
Read More