Hi, Struggling the right syntax in Unix environment for importing a lost view, here is the proper way: impdp user/password@database directory=dir_dump dumpfile=dump.dmp logfile=imp_MY_VIEW.log INCLUDE=VIEW:\”= \’MY_VIEW\’\” job_name=imp_MY_VIEW
Category: SCRIPTS
Tablespace growth in Oracle
— Find Tablespace growth size in number of days. set feed off set pages 1000 lines 180 column “tablespace_name” heading “Tablespace | Name” format a20 select to_char (sp.begin_interval_time,’dd-mm-yyyy’) days, ts.tsname, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_mb, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_mb from dba_hist_tbspc_space_usage tsu, dba_hist_tablespace_stat ts, dba_hist_snapshot sp, dba_tablespaces dt where tsu.tablespace_id= ts.ts# and tsu.snap_id = sp.snap_id and […]
RMAN BACKUP Scripts
~~~~~~~~~~~~~~~~~~~~~~~~ # RMAN backup history ~~~~~~~~~~~~~~~~~~~~~~~~ set lines 150 set pages 900 col start for a20 col end for a20 col status format a11 col input_bytes_display format a10 col output_bytes_display format a10 col status_weight format 99999 SELECT to_char(start_time,’YYYY.MM.DD HH24:MI:SS’) “Start” , to_char(end_time,’YYYY.MM.DD HH24:MI:SS’) “End”, status, status_weight, input_type, input_bytes_display, output_bytes_display FROM V$RMAN_BACKUP_JOB_DETAILS WHERE ( start_time between […]
Extracts DDL statements for specified objects
exec dbms_metadata.set_transform_param( dbms_metadata.session_transform,’SQLTERMINATOR’, TRUE); select dbms_metadata.get_ddl( object_type, object_name, owner ) from all_objects where object_type NOT LIKE ‘%PARTITION’ AND object_type NOT LIKE ‘%BODY’ AND upper(object_name) LIKE upper(CASE WHEN INSTR(‘&1′,’.’) > 0 THEN SUBSTR(‘&1’,INSTR(‘&1′,’.’)+1) ELSE ‘&1’ END ) AND owner LIKE CASE WHEN INSTR(‘&1′,’.’) > 0 THEN UPPER(SUBSTR(‘&1’,1,INSTR(‘&1′,’.’)-1)) ELSE user END /
Displays information on all long operations
COLUMN sid FORMAT 999 COLUMN serial# FORMAT 9999999 COLUMN machine FORMAT A30 COLUMN progress_pct FORMAT 99999999.00 COLUMN elapsed FORMAT A10 COLUMN remaining FORMAT A10 SELECT s.sid, s.serial#, s.machine, ROUND(sl.elapsed_seconds/60) || ‘:’ || MOD(sl.elapsed_seconds,60) elapsed, ROUND(sl.time_remaining/60) || ‘:’ || MOD(sl.time_remaining,60) remaining, ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct FROM v$session s, v$session_longops sl WHERE s.sid = sl.sid AND s.serial# = […]
Lists all locked objects for whole RAC
COLUMN owner FORMAT A20 COLUMN username FORMAT A20 COLUMN object_owner FORMAT A20 COLUMN object_name FORMAT A30 COLUMN locked_mode FORMAT A15 SELECT b.inst_id, b.session_id AS sid, NVL(b.oracle_username, ‘(oracle)’) AS username, a.owner AS object_owner, a.object_name, Decode(b.locked_mode, 0, ‘None’, 1, ‘Null (NULL)’, 2, ‘Row-S (SS)’, 3, ‘Row-X (SX)’, 4, ‘Share (S)’, 5, ‘S/Row-X (SSX)’, 6, ‘Exclusive (X)’, b.locked_mode) […]
FIND THE ARCHIVE LAG BETWEEN PRIMARY AND STANDBY
select LOG_ARCHIVED-LOG_APPLIED “LOG_GAP” from (SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED=’YES’), (SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’);
LAST SEQUENCE RECEIVED AND LAST SEQUENCE APPLIED
SQL> SELECT al.thrd “Thread”, almax “Last Seq Received”, lhmax “Last Seq Applied” FROM (select thread# thrd, MAX(sequence#) almax FROM v$archived_log WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) al, (SELECT thread# thrd, MAX(sequence#) lhmax FROM v$log_history WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) lh WHERE al.thrd = lh.thrd; Thread Last Seq Received Last Seq […]
CHECK THE MESSAGES/ERRORS IN STANDBY DATABASE
set pagesize 2000 set lines 2000 col MESSAGE for a90 select message,timestamp from V$DATAGUARD_STATUS where timestamp > sysdate – 1/6; MESSAGE TIMESTAMP —————————————————————————————— ——— RFS[48]: No standby redo logfiles created 05-AUG-15 Media Recovery Log /uv1010/arch/MRSX/arch_MRSX_779539386_1_49481.log 05-AUG-15 Media Recovery Waiting for thread 1 sequence 49482 (in transit) 05-AUG-15 RFS[48]: No standby redo logfiles created 05-AUG-15 Media […]
CHECK THE NUMBER OF ARCHIVES GETTING GENERATING ON HOURLY BASIS:
SELECT TO_CHAR(TRUNC(FIRST_TIME),’Mon DD’) “DG Date”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’00’,1,0)),’9999′) “12AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’01’,1,0)),’9999′) “01AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’02’,1,0)),’9999′) “02AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’03’,1,0)),’9999′) “03AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’04’,1,0)),’9999′) “04AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’05’,1,0)),’9999′) “05AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’06’,1,0)),’9999′) “06AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’07’,1,0)),’9999′) “07AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’08’,1,0)),’9999′) “08AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’09’,1,0)),’9999′) “09AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’10’,1,0)),’9999′) “10AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’11’,1,0)),’9999′) “11AM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’12’,1,0)),’9999′) “12PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’13’,1,0)),’9999′) “1PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’14’,1,0)),’9999′) “2PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’15’,1,0)),’9999′) “3PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’16’,1,0)),’9999′) “4PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’17’,1,0)),’9999′) “5PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’18’,1,0)),’9999′) “6PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’19’,1,0)),’9999′) “7PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’20’,1,0)),’9999′) “8PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’21’,1,0)),’9999′) “9PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’22’,1,0)),’9999′) “10PM”, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’23’,1,0)),’9999′) “11PM” FROM V$LOG_HISTORY […]