--MY ORACLE NOTES - STUDY CONTINUESLY AND HARD --
 -------------------------------------------------------------------
1)  *--EXECUTION PLAN OF SQL QUERY
        SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('GV0CHTH71F98M')); --GV0CHTH71F98M - SQL_ID, YOU CAN FIND FROM AWR REPORT.
 ------------------------------------------------------------------
 ------------------------------------------------------------------
2)  *--ADDITIONAL INFORMATION ABOUT RUNNING QUERY
        SELECT * FROM V$SQL WHERE SQL_ID='C7KVGV263AKJX'
        SELECT * FROM V$SQLAREA WHERE SQL_ID='C7KVGV263AKJX'
        SELECT B.SID,B.STATUS,B.SERIAL#,B.LAST_CALL_ET,B.PROGRAM,C.SQL_ID,C.SQL_TEXT
        FROM  V$SESSION B,V$SQLAREA C
        WHERE B.SQL_ID=C.SQL_ID;


        SELECT * FROM  V$SESSION_LONGOPS WHERE SQL_ID='B7UD8SW4U7P7H';--1295	28753
 ------------------------------------------------------------------
 ------------------------------------------------------------------
3)  *--DICTIONARY TABLES FOR INDEXES
        SELECT * FROM ALL_INDEXES WHERE TABLE_NAME='ACCOUNT'
        SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME='ACCOUNT'
        SELECT * FROM ALL_IND_STATISTICS
        SELECT * FROM ALL_IND_PARTITIONS
        SELECT * FROM DICT; -- list of data dictionary tables
        SELECT * FROM V$FIXED_TABLE; -- list of dynamic data dictionaries
 ------------------------------------------------------------------
 ------------------------------------------------------------------
4)  *--ORACLE HINTS
        /*+ APPEND NOLOGGING */        *-- DONT WRITE QUERY DETAILS TO REDO LOGS
        /*+ PARALLEL(N) */             *-- N: 2,4,16 - UP TO RESOURCE PERFORMANCE - FOR RUNNING QUERIES IN PARALLEL this depends on CPU core count
        /*+ INDEX(TABLE INDEX_NAME) */ *-- FORCE THE QUERY FOR USING INDEX ON INDEXED COLUMN
 ------------------------------------------------------------------
 ------------------------------------------------------------------
5)  *--LOCKED TABLES AND UNLOCK
        SELECT blocking_session, S.INST_ID, L.SESSION_ID SID, S.SERIAL#, L.LOCKED_MODE,L.ORACLE_USERNAME,
        L.OS_USER_NAME,S.MACHINE, S.TERMINAL, O.OBJECT_NAME, S.LOGON_TIME
        FROM GV$LOCKED_OBJECT L, ALL_OBJECTS O, GV$SESSION S
        WHERE L.OBJECT_ID = O.OBJECT_ID
        AND L.SESSION_ID = S.SID
        ORDER BY SID, S.SERIAL# ;
        *--ALTER SYSTEM KILL SESSION 'SID,SERIAL#'; --KILL SESSION
        *--ALTER SYSTEM KILL SESSION '1182,7495';
        *--KILL SESSION ON OS:

        SELECT s.INST_ID, l.session_id sid, s.serial#,s.BLOCKING_SESSION, k.SPID,l.locked_mode,l.oracle_username,
          l.os_user_name,s.machine, o.object_name, s.terminal, s.logon_time
          FROM gv$locked_object l, all_objects o, gv$session s, v$process k
          WHERE l.object_id = o.object_id
          AND l.session_id = s.sid
          and s.PADDR=k.ADDR
          ORDER BY sid, s.serial# ;
        *--KILL ON OS LEVEL
        kill -9 spid
 ------------------------------------------------------------------
 ------------------------------------------------------------------
6)  *--MONITOR INDEX USAGE
        ALTER INDEX INDEX_NAME MONITORING USAGE;
        SELECT * FROM V$OBJECT_USAGE WHERE INDEX_NAME IN ('IX_ACCOUNT_CRM_ACCT_ID','IX_ACCOUNT','IX_ACCOUNT_CONTRACTID')
        ALTER INDEX INDEX_NAME NONMONITORING USAGE;
 ------------------------------------------------------------------
 ------------------------------------------------------------------
7)  *--TABLESPACE USAGE:
        SELECT UPPER(F.TABLESPACE_NAME) "TABLESPACE_NAME",
               D.TOT_GROOTTE_MB "TABLESPACE_TOTAL_SIZE(GB)",
               D.TOT_GROOTTE_MB - F.TOTAL_BYTES "USED_SIZE(GB)",
               TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99')||'%' "USED PERCENTAGE(%)",
               F.TOTAL_BYTES "FREE_SIZE(GB)",
               F.MAX_BYTES "MAX_LUMP(GB)"
        FROM (SELECT TABLESPACE_NAME,
                       ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 2) TOTAL_BYTES,
                       ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 2) MAX_BYTES
               FROM SYS.DBA_FREE_SPACE
               GROUP BY TABLESPACE_NAME) F,
             (SELECT DD.TABLESPACE_NAME,
                       ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 2) TOT_GROOTTE_MB
               FROM SYS.DBA_DATA_FILES DD
               GROUP BY DD.TABLESPACE_NAME) D
        WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
        ORDER BY TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99')||'%' DESC;

        SQL> SELECT * FROM DBA_TABLESPACES -- DATA DICTIONARY
        SQL> SELECT * FROM V$TABLESPACE; -- DATA DICTIONARY
 ------------------------------------------------------------------
 ------------------------------------------------------------------
8)  *--ORACLE STATIC FOR PERFORMANCE
        EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'schema_name_here', TABNAME => 'tabe_name_here', DEGREE => 4, ESTIMATE_PERCENT=> 0.01); --last run time: 08/05/2016 11:31

        BEGIN
        DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
        END;

       *-- AFTER RUN STATIC CHECK USER_TABLES FOR CHANGES
 ------------------------------------------------------------------
 ------------------------------------------------------------------
9)  *--GET 'N' HOUR BEFORE FROM SYSDATE
        SELECT  SYSDATE - INTERVAL 'N' HOUR FROM DUAL;
        SELECT  SYSDATE -(N/24) FROM DUAL;
 ------------------------------------------------------------------
 ------------------------------------------------------------------
 10)*--SEGMENT SIZE
        SELECT BYTES/1024/1024/1024  FROM DBA_SEGMENTS WHERE SEGMENT_NAME='TABLE_NAME'
 -------------------------------------------------------------------
 -------------------------------------------------------------------
 11)*--DATA FILES VIEWS
        SQL> SELECT * FROM DBA_DATA_FILES;
        SQL> SELECT * FROM V$DATAFILE;
        SQL> SELECT * FROM DBA_FREE_SPACE; --FREE SPACE IN DATA FILES

        SQL> SELECT * FROM DBA_TEMP_FILES; --TEMP FILES
        SQL> SELECT * FROM V$TEMPFILE;
        SQL> SELECT * FROM V$TEMP_SPACE_HEADER; --FREE SPACE IN TEMP FILES
        SQL> SELECT TABLESPACE_NAME, BYTES_USED/1024/1024/1024, BYTES_FREE/1024/1024/1024 FROM V$TEMP_SPACE_HEADER;
        SQL> SELECT * FROM SYS.TS$
        SQL> ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME DROP_SEGMENTS LEVEL 4'; --level define by F# + 1
        SQL> ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME DROP_SEGMENTS LEVEL 11';--level define by F# + 1

 -------------------------------------------------------------------
 -------------------------------------------------------------------
 12)*--CREATE TABLESPACE
        SQL> CREATE TABLESPACE ICA_LMTS DATAFILE '/U02/ORACLE/ICA/ICA01.DBF' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
            *--AUTOALLOCATE CAUSES THE TABLESPACE TO BE SYSTEM MANAGED WITH A MINIMUM EXTENT SIZE OF 64K. THE ALTERNATIVE TO AUTOALLOCATE IS UNIFORM
        SQL> CREATE BIGFILE TABLESPACE ICA_BIGTBS  DATAFILE '/U02/ORACLE/ICA/BIGTBS01.DBF' SIZE 50G; --BIGFILE TABLESPACE
 -------------------------------------------------------------------
 -------------------------------------------------------------------
 13)*--EXTEND TABLESPACE SIZE
        SQL> ALTER DATABASE ICA DATAFILE ‘/U01/ORACLE/DATA/ICATBS01.DBF’ RESIZE 100M; *-- EXTEND WITH SAME DATA FILE
        SQL> ALTER TABLESPACE ADD DATAFILE  ‘/U02/ORACLE/ICA/ICATBS02.DBF’ SIZE 50M; *-- EXTEND WITH NEW DATA FILE
        SQL> ALTER DATABASE DATAFILE ‘/U01/ORACLE/ICA/ICATBS01.DBF’ AUTO EXTEND ON NEXT 5M MAXSIZE 500M; *-- MAKE EXISTING DATAFILE AUTOEXTEND
        SQL> CREATE TABLESPACE ICA DATAFILE ‘/U01/ORACLE/ICA/ICATBS01.DBF’ SIZE 50M AUTO EXTEND ON NEXT 5M MAXSIZE 500M; *-- MAKE DATAFILE AUTOEXTEND DURING CREATE TABLESPACE

    ADDING NEW RAW FILE INTO DATA FILES: --> Need to create LV
    1. Check the existing raw files
    ls -lrt /dev/raw

    2. Check the raw file size
    blockdev --getsize /dev/raw/raw***

    3. Check the bonding relation
    raw -qa

    4. Check the free space in VG
    vgs -- or vgdisplay

    5. Create LV
    lvcreate –L 15g -n datasda11 datavgsda --lvcreate -L 8g -n sda16 vg_dbfile 

    6. Bond LV to raw
    raw /dev/raw/raw** /dev/datavgsda/datasda11 -- go to /dev/vg_dbfile and find correct path

    7. Change permission of the raw file

    8. Add datafile in oracle --ALTER TABLESPACE TBS_INV_DAT ADD DATAFILE  '/dev/raw/raw67' SIZE 7G;
    go
 -------------------------------------------------------------------
 -------------------------------------------------------------------
 14)*--DECREASE TABLESPACE SIZE
        SQL> ALTER DATABASE DATAFILE ‘/U01/ORACLE/ICA/ICATBS01.DBF’ RESIZE 30M;
 -------------------------------------------------------------------
 -------------------------------------------------------------------
 15)*--COALESCING TABLESPACES
        SQL> ALTER TABLESPACE ICA COALESCE; *--YOU SHOULD OFTEN USE THE 'ALTER TABLESPACE ... COALESCE' STATEMENT TO MANUALLY COALESCE ANY ADJACENT FREE EXTENTS.
 -------------------------------------------------------------------
 -------------------------------------------------------------------
 16)*--MODIFY TABLESPACE
        SQL> ALTER TABLESPACE ICA OFFLINE; *-- MAKE TABLESPACE OFFLINE
        SQL> ALTER TABLESPACE ICA ONLINE; *-- MAKE TABLESPACE ONLINE
        SQL> ALTER DATABASE DATAFILE ‘/U01/ORACLE/ICA/ICA_TBS01.DBF’ OFFLINE; *-- MAKE DATAFILE OFFLINE
        SQL> ALTER DATABASE DATAFILE ‘/U01/ORACLE/ICA/ICA_TBS01.DBF’ ONLINE; *-- MAKE DATAFILE ONLINE
        -- NOTE: YOU CAN’T TAKE INDIVIDUAL DATAFILES OFFLINE IT THE DATABASE IS RUNNING IN NOARCHIVELOG MODE.
        -- IF THE DATAFILE HAS BECOME CORRUPT OR MISSING WHEN THE DATABASE IS RUNNING IN NOARCHIVELOG MODE THEN YOU CAN ONLY DROP IT BY GIVING THE FOLLOWING COMMAND
        SQL> ALTER DATABASE DATAFILE ‘/U01/ORACLE/ICA/ICA_TBS01.DBF’ OFFLINE FOR DROP;

        SQL> ALTER TABLESPACE ICA READ ONLY *-- MAKE TABLESPACE READ ONLY
        SQL> ALTER TABLESPACE ICA READ WRITE; *-- MAKE TABLESPACE AGAIN WRITABLE
        SQL> ALTER TABLESPACE USERS RENAME TO USERSTS; *-- RENAME TABLESPACE
        SQL> DROP TABLESPACE ICA; *-- DROP TABLESPACE
        SQL> DROP TABLESPACE ICA INCLUDING CONTENTS; *--DROP TABLESPACE WITH CONTENTS
        SQL> DROP TABLESPACE ICA INCLUDING CONTENTS AND DATAFILES; *-- DROP TABLESPACE CONTENTS AND DATAFILES
 -------------------------------------------------------------------
 -------------------------------------------------------------------
 17)*--RENAMING OR RELOCATING DATAFILES BELONGING TO A SINGLE TABLESPACE
        SQL> ALTER TABLESPACE USERS OFFLINE; *-- BRING THE TABLESPACE OFFLINE
        $ CP /U01/ORACLE/ICA/USR01.DBF  /U02/ORACLE/ICA/USR01.DBF  *-- COPY THE FILE TO NEW LOCATION
        $ MV /U01/ORACLE/ICA/USR02.DBF /U01/ORACLE/ICA/USERS02.DBF *-- RENAME THE FILE TO NEW NAME
        SQL> ALTER TABLESPACE USERS RENAME FILE  '/U01/ORACLE/ICA/USR01.DBF', '/U01/ORACLE/ICA/USR02.DBF' TO '/U02/ORACLE/ICA/USR01.DBF', '/U01/ORACLE/ICA/USERS02.DBF'; *-- RENAME AND RELOCATE ON ORACLE LEVEL
        SQL> ALTER TABLESPACE USERS ONLINE; *-- BRING THE TABLESPACE ONLINE
 -------------------------------------------------------------------
 -------------------------------------------------------------------
18) *--CREATE TEMP TABLESPACE
        --TEMPORARY TABLESPACE IS USED FOR SORTING LARGE TABLES.
            SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE ‘/U01/ORACLE/DATA/ICA_TEMP.DBF’ SIZE 100M EXTENT MANAGEMENT LOCAL  UNIFORM SIZE 5M;
            SQL> ALTER DATABASE TEMPFILE '/U02/ORACLE/DATA/LMTEMP02.DBF' RESIZE 18M; --INCREASE OR DECREASE TEMP TABLESPACE
            SQL> ALTER DATABASE TEMPFILE '/U02/ORACLE/DATA/LMTEMP02.DBF' DROP  INCLUDING DATAFILES; --DROP TEMP FILE
            SQL> CREATE TEMPORARY TABLESPACE ica_temp2 TEMPFILE '/u02/oracle/ica/ica_temp.dbf' SIZE 50M TABLESPACE GROUP group1; -- TEMP TABLESPACE GROUPS
                        --Temporary tablespace group has the following benefits:
                            --It allows multiple default temporary tablespaces to be specified at the database level.
                            --It allows the user to use multiple temporary tablespaces in different sessions at the same time.
                            --It allows a single SQL operation to use multiple temporary tablespaces for sorting.
            SQL> select * from V$TEMP_SPACE_HEADER; --FREE SPACE IN TEMP FILES
 -------------------------------------------------------------------
 -------------------------------------------------------------------
 19)*--CORRUPTED DATA BLOCKS
        SQL> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
        RESULT: FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
            5       81     4     0                 CORRUPT
    FIND BLOCK:
            select
               segment_name,
               segment_type
            from
               dba_extents
            where
               file_id = 4
            and
               155 between
               (block_id and block_id + blocks - 1);
    *--IF CORRUPTED BLOCK IS INDEX JUST REBUILD IT LIKE BELOW.
            set lines 90;
            set heading off;
            set feedback off;

            spool runme.sql

            select
            'alter index '||owner||'.'||index_name||' rebuild online tablespace sysaux;'
            from
            dba_indexes
            where tablespace_name = 'SYSAUX';

            spool off

            spool rebuild.lst
            @runme.sql
            spool off
 -------------------------------------------------------------------
 -------------------------------------------------------------------
20) *--EXPORT AND IMPORT DATA WITH DATA PUMP FROM ORACLE
        *--EXPORT
            #!/bin/ksh
            expdp system/system directory=directory_name_here tables=schema_name.table_name dumpfile=dump_file_name.dmp CONTENT=data_only  -- for export
        *--IMPORT
            #!/bin/ksh
            impdp system/oracle directory=directory_name_here tables=schema_name.table_name dumpfile=dump_file_name.dmp REMAP_SCHEMA=source_schema:target_schema -- for import
            select * from dba_directories --DIRECTORY FOR DATABASE

      HOW TO STOP EXPDP TASKS
      query the view dba_datapump_jobs to check the running tasks
        select * from dba_datapump_jobs;
      [oracle@database ~]$ expdp sys/oracle as sysdba attach=SYS_EXPORT_SCHEMA_02
      stop_job= IMMEDIATE;
      kill_job;
      select * from dba_datapump_jobs;

 -------------------------------------------------------------------
 -------------------------------------------------------------------
 21)*--MANAGING REDO LOG FILES
            Every Oracle database must have at least 2 redo logfile groups. Oracle writes all statements except, SELECT statement, to the logfiles.
            This is done because Oracle performs deferred batch writes i.e. it does write changes to disk per statement instead it performs write in batches.
            So in this case if a user updates a row, Oracle will change the row in db_buffer_cache and records the statement in the logfile
            and give the message to the user that  row is updated. Actually the row is not yet written back to the datafile
            but still it give the message to the user that row is updated. After 3 seconds the row is actually written to the datafile.
            This is known as deferred batch writes.
             SQL> ALTER DATABASE ADD LOGFILE GROUP 3  ‘/U01/ORACLE/ICA/LOG3.ORA’ SIZE 10M; -- ADD NEW REDO LOG FILE GROUP; max redo log file is bordered by MAXLOGFILES parameter.
             SQL> ALTER DATABASE ADD LOGFILE MEMBER ‘/U01/ORACLE/ICA/LOG11.ORA’ TO GROUP 1; -- ADD NEW REDO LOG FILE INTO GROUP; max group member is bordered by MAXLOGMEMBERS parameter.
            --Important: Is it strongly recommended that you multiplex logfiles i.e. have at least two log members, one member in one disk and another in second disk, in a database.

        *--DROPPING LOG FILES - 1) IF YOU WANT TO DROP MEMBERS FROM THE CURRENT GROUP, FORCE A LOG SWITCH OR WAIT SO THAT LOG SWITCH OCCURS AND ANOTHER GROUP BECOMES CURRENT.
            SQL> ALTER SYSTEM SWITCH LOGFILE; --To force a log switch give the following command
            SQL> ALTER DATABASE DROP LOGFILE MEMBER ‘/U01/ORACLE/ICA/LOG11.ORA’; --command can be used to drop a logfile member
        --NOTE: WHEN YOU DROP LOGFILES THE FILES ARE NOT DELETED FROM THE DISK. YOU HAVE TO USE O/S COMMAND TO DELETE THE FILES FROM DISK.

        *--DROPPING LOG FILE GROUPS - 1)IF THE DATABASE IS HAVING MORE THAN TWO GROUPS AND IF IT IS NOT THE CURRENT GROUP.
            SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
        --NOTE: WHEN YOU DROP LOGFILES THE FILES ARE NOT DELETED FROM THE DISK. YOU HAVE TO USE O/S COMMAND TO DELETE THE FILES FROM DISK.
        --NOTE: YOU CANNOT RESIZE LOGFILES. IF YOU WANT TO RESIZE A LOGFILE CREATE A NEW LOGFILE GROUP WITH THE NEW SIZE AND SUBSEQUENTLY DROP THE OLD LOGFILE GROUP.

        *--RENAMING OR RELOCATING LOGFILES
           --FOR EXAMPLE, SUPPOSE YOU WANT TO MOVE A LOGFILE FROM ‘/U01/ORACLE/ICA/LOG1.ORA’ TO ‘/U02/ORACLE/ICA/LOG1.ORA’, THEN DO THE FOLLOWING
            1. SHUTDOWN THE DATABASE
                SQL> shutdown immediate;
            2. MOVE THE LOGFILE FROM OLD LOCATION TO NEW LOCATION USING OPERATING SYSTEM COMMAND
                $mv /u01/oracle/ica/log1.ora  /u02/oracle/ica/log1.ora
            3. START AND MOUNT THE DATABASE
                SQL> startup mount
            4. NOW GIVE THE FOLLOWING COMMAND TO CHANGE THE LOCATION IN CONTROLFILE
                SQL> alter database rename file ‘/u01/oracle/ica/log1.ora’ to ‘/u02/oracle/ica/log2.ora’;
            5. OPEN THE DATABASE
                SQL> alter database open;

        *--CLEARING REDO LOGFILES
            A redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue.
            In this situation the ALTER DATABASE CLEAR LOGFILE statement can be used reinitialize the file without shutting down the database.

            SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3; --STATEMENT CLEARS THE LOG FILES IN REDO LOG GROUP NUMBER 3;

        *--VIEWING INFORMATION ABOUT LOG FILES FROM SQL
            SQL> SELECT * FROM V$LOG;
            SQL> SELECT * FROM V$LOGFILE;
        ***--SWITCHOVER FREQUENCY OF REDO LOG FILES IN THE ORACLE DATABASE IS ABNORMAL
                --DROP SMALL SIZE LOG FILES WITH THE STATUS "INACTIVE"
                    select GROUP#,THREAD#,BYTES/1024/1024 mb,MEMBERS,STATUS from v$Log order by thread#,group#;
                    select group#,member,type,status from v$Logfile;
                    alter database add logfile thread 2 group 23 '+DG_DATA' SIZE 4G ;
                    alter database add logfile thread 2 group 24 '+DG_DATA' SIZE 4G ; 

                    alter database drop logfile group 3;
                    alter database drop logfile group 4;

                    alter database add logfile thread 1 group 13 '+DG_DATA' SIZE 4G ;
                    alter database add logfile thread 1 group 14 '+DG_DATA' SIZE 4G ;
                    alter database drop logfile group 1;
                    alter database drop logfile group 2;
        *--LOG HISTORY
            select to_char(first_time, 'yyyy/mm/dd:hh24') "Date",
                   to_char(thread#) thread#,
                   count(1) "Archives_in_last_7_days/hour"
            from v$log_history
            where trunc(first_time) in (trunc(sysdate),
                                        trunc(sysdate - 1),
                                        trunc(sysdate - 2),
                                        trunc(sysdate - 3))
             group by to_char(first_time, 'yyyy/mm/dd:hh24'), thread#
             order by thread#, 1;
 -------------------------------------------------------------------
 -------------------------------------------------------------------
 22)*--RECOVER DROPPED TABLE
        SELECT * FROM USER_RECYCLEBIN WHERE ORIGINAL_NAME='ICCID_IMSI' --iF UNDROP IS 'YES' THEN FLASHBACK TABLE.
        FLASHBACK TABLE 'TABLE_NAME' TO BEFORE DROP;
        SELECT * FROM XXXX AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);--(SYSTIMESTAMP - INTERVAL  '1' DAY);
 -------------------------------------------------------------------
 -------------------------------------------------------------------

 23)*--CHECK DB_NAME
        SELECT SYS_CONTEXT('USERENV','DB_NAME') FROM DUAL;
        SELECT ORA_DATABASE_NAME FROM DUAL;
        SELECT * FROM GLOBAL_NAME;
 -------------------------------------------------------------------
 -------------------------------------------------------------------

25)*--DB USER PASSWORD
        ALTER USER user_name IDENTIFIED BY password;
 -------------------------------------------------------------------
 -------------------------------------------------------------------

26)*--SYNOYMS
        SELECT TABLE_OWNER, TABLE_NAME FROM DBA_SYNONYMS WHERE SYNONYM_NAME = 'GV$SESSION'; --THIS WILL HELP YOU TO GRANT SYNONYMS BY THE HELP OF REAL TABLE.
        GRANT SELECT ON GV_$SESSION TO SCOTT;
 -------------------------------------------------------------------
 -------------------------------------------------------------------

 27)*--ORACLE JOB SCHEDULE

    ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN';
    ALTER SESSION SET NLS_TERRITORY= 'AMERICA';

    BEGIN
      DBMS_SCHEDULER.CREATE_JOB (
       job_name           =>  'COLLECT_ORACLE_STATICS',
       job_type           =>  'STORED_PROCEDURE',
       job_action         =>  'P_FORACLE_STATICS',
       start_date         =>  '27-SEP-16 02.05.00 PM', -- this value should be in timestamp format for ex: TO_TIMESTAMP(systimestamp + 8/24)
       enabled            =>   TRUE,
       repeat_interval    =>  'FREQ=MONTHLY; BYMONTHDAY=15;', /* Run twice on every month. */ --first automatic running time is 15 dat later
       comments           =>  'COLLECT ORACLE STATICS');
    END;
    /

    --ENABLE JOB
    BEGIN
    DBMS_SCHEDULER.ENABLE ('COLLECT_ORACLE_STATICS'); -- if you didnt mention enable flag then can be enable like this
    END;
    /

    --RUN JOB
    BEGIN
      DBMS_SCHEDULER.RUN_JOB(
        JOB_NAME            => 'COLLECT_ORACLE_STATICS');
    END;
    /

    --STOP JOB
    BEGIN
    DBMS_SCHEDULER.STOP_JOB('COLLECT_ORACLE_STATICS');
    END;
    /

    --DROP JOB
    BEGIN
    DBMS_SCHEDULER.DROP_JOB ('COLLECT_ORACLE_STATICS'); -- drop oracle job
    END;
    /

    --DATA DICTIONARIES ABOUT JOBS
    SELECT * FROM  DBA_SCHEDULER_JOB_LOG T WHERE T.JOB_NAME='COLLECT_ORACLE_STATICS';
    SELECT * FROM  DBA_SCHEDULER_JOBS T WHERE T.JOB_NAME='COLLECT_ORACLE_STATICS';
    SELECT * FROM  ALL_SCHEDULER_JOB_RUN_DETAILS T WHERE T.JOB_NAME='COLLECT_ORACLE_STATICS';
    SELECT * FROM  DBA_JOBS_RUNNING;

    DBA_AUTOTASK_CLIENT - displays statistical data for each automated maintenance task over 7-day and 30-day periods.
    SELECT * FROM DBA_AUTOTASK_CLIENT
    SELECT * FROM DBA_AUTOTASK_WINDOW_CLIENTS;
    SELECT * FROM DBA_SCHEDULER_WINDOWS

    --AUTOMATED DATABASE MAINTENANCE TASKS
    --Oracle 11g includes three automated database maintenance tasks:
        AUTOMATIC OPTIMIZER STATISTICS COLLECTION - Gathers stale or missing statistics for all schema objects. The task name is 'auto optimizer stats collection'.
        AUTOMATIC SEGMENT ADVISOR                 - Identifies segments that could be reorganized to save space. The task name is 'auto space advisor'.
        AUTOMATIC SQL TUNING ADVISOR              - Identifies and attempts to tune high load SQL. The task name is 'sql tuning advisor'.

    DBMS_AUTO_TASK_ADMIN.DISABLE (
        client_name       IN    VARCHAR2,  -- get client_name  from DBA_AUTOTASK_CLIENT
        operation         IN    VARCHAR2,  -- get operation    from DBA_AUTOTASK_OPERATION      - can be set null
        window_name       IN    VARCHAR2); -- get windows name from DBA_AUTOTASK_WINDOW_CLIENTS

    DBMS_AUTO_TASK_ADMIN.ENABLE (
        client_name       IN    VARCHAR2,  -- get client_name  from DBA_AUTOTASK_CLIENT
        operation         IN    VARCHAR2,  -- get operation    from DBA_AUTOTASK_OPERATION      - can be set null
        window_name       IN    VARCHAR2); -- get windows name from DBA_AUTOTASK_WINDOW_CLIENTS

    begin
        dbms_auto_task_admin.disable(client_name=>'auto optimizer stats collection',operation=>NULL,window_name=>NULL);
        dbms_auto_task_admin.disable;
        dbms_auto_task_admin.disable('auto optimizer stats collection', null,'WEDNESDAY_WINDOW');
        dbms_auto_task_admin.disable('auto optimizer stats collection', null,'FRIDAY_WINDOW');
        dbms_auto_task_admin.disable('auto optimizer stats collection', null,'SATURDAY_WINDOW');
        dbms_auto_task_admin.disable('auto optimizer stats collection', null,'THURSDAY_WINDOW');
        dbms_auto_task_admin.disable('auto optimizer stats collection', null,'TUESDAY_WINDOW');
        dbms_auto_task_admin.disable('auto optimizer stats collection', null,'SUNDAY_WINDOW');
        dbms_auto_task_admin.disable('auto optimizer stats collection', null,'MONDAY_WINDOW');
    end;
 -------------------------------------------------------------------
 -------------------------------------------------------------------

28)*--ACTIVE SESSION TEMP USAGE
    SELECT
        S.sid,
        SUM(T.blocks) * TBS.block_size / 1024 / 1024 / 1024 gb_used,
        S.serial# sid_serial,
        s.INST_ID,
        S.username,
        S.osuser,
        s.status,
        P.spid,
        S.module,
        P.program,
        T.tablespace,
        q.sql_text,
        nvl(S.sql_id, S.PREV_SQL_ID) sql_id
    FROM
        gv$sort_usage    T,
        gv$session       S,
        dba_tablespaces  TBS,
        gv$process       P,
        gv$sql           q
    WHERE T.session_addr = S.saddr
      AND T.INST_ID = S.INST_ID
      AND S.paddr = P.addr
      AND S.INST_ID = P.INST_ID
      AND nvl(S.sql_id, S.PREV_SQL_ID) = q.sql_id
      AND T.tablespace = TBS.tablespace_name
      AND Q.CHILD_NUMBER = S.SQL_CHILD_NUMBER
      AND S.STATUS = 'ACTIVE'
    GROUP BY S.sid,
          S.serial#,
          s.INST_ID,
          S.username,
          S.osuser,
          P.spid,
          s.status,
          S.module,
          P.program,
          TBS.block_size,
          T.tablespace,
          q.sql_text,
          nvl(S.sql_id, S.PREV_SQL_ID)
    HAVING SUM(T.blocks) * TBS.block_size / 1024 / 1024 / 1024 > 2
    ORDER BY 2 DESC;

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

29)*-- CREATE DATABASE LINK AND DROP

    create public database link link2surveydb connect to survey_rep identified by pasword_here using '(DESCRIPTION_LIST =
        (FAILOVER = TRUE)
        (LOAD_BALANCE = FALSE)
        (DESCRIPTION =
        (ADDRESS =
          (PROTOCOL = TCP)
          (HOST = hostname_here)(PORT = port_here))
          (CONNECT_DATA =
            (SERVICE_NAME = service_name_here)
            (SERVER = DEDICATED)
          )
        )
        (DESCRIPTION =
        (ADDRESS =
          (PROTOCOL = TCP)
          (HOST = hostname_here)(PORT = port_here))
          (CONNECT_DATA =
            (SERVICE_NAME = service_name_here)
            (SERVER = DEDICATED)
          )
        )
    )
    '

    DROP PUBLIC DATABASE LINK link2surveydb;
    SELECT * FROM DBA_DB_LINKS;
 -------------------------------------------------------------------
 -------------------------------------------------------------------

30)*-- ADD PARTIITON

    declare
        str_date varchar2(8);
    begin
        str_date := '20161101';
        for i in 1 .. 10 loop
            execute immediate 'alter table schema_name.table_name_here add partition pr_data_day_' || str_date || ' values(' || str_date|| ')';
            str_date := to_char(to_date(str_date, 'yyyymmdd') + 1, 'yyyymmdd');
        end loop;
    end;
    -- MODIFY PARTITIONS
        alter table schema_name.table_name_here add partition pr_data_day_YYYYMMDD values('YYYYMMDD');
        alter table schema_name.table_name_here drop partition pr_data_day_YYYYMMDD;
        alter table schema_name.table_name_here truncate partition pr_data_day_YYYYMMDD;

  create table table_name_here
    (
    data_day VARCHAR2(8),
    sub_id VARCHAR2(50),
    org_id VARCHAR2(50),
    staff_id VARCHAR2(50),
    busi_date VARCHAR2(50),
    source_mark VARCHAR2(50)
    )
    partition by list (data_day)
    (
    partition PR_DATA_DAY_YYYYMMDD values ('YYYYMMDD')
    tablespace tablespace_name_here
    );

    --- CTAS WITH PARITION
    create /*+append nologging*/table table_name_here
    partition by list (data_day)
    (
    partition PR_DATA_DAY_20171204 values ('20171204')
    tablespace tablespace_name_here
    ) as
    select /*+parallel(8)*/* from table_name_here;
    ;

  --- EXCHANGE PARTITION
    ALTER TABLE table_name_here EXCHANGE PARTITION pr_data_day_20171204 WITH TABLE target_table_name_here; -- target table should be non-paritiotioned table(target_table_here)

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

31)*-- RMAN

    $rman target / nocatalog

    RMAN> show all;

    RMAN> report schema;

    RMAN> report obsolete;

    RMAN> report unrecoverable;

    RMAN> list backup;
    RMAN> list backup of database;
    RMAN> list backup of tablespace table_name;
    RMAN> list backup of controlfile;
    RMAN> list backup of spfile;
    RMAN> list backupset id;

    RMAN> crosscheck backup;
    RMAN> crosscheck backup of database;
    RMAN> crosscheck backup of tablespace system;
    RMAN> crosscheck backup of controlfile;
    RMAN> crosscheck backup of spfile;
    RMAN> crosscheck backup of archivelog all;

    --WHEN SOME ARCHIVE FILES DELETED AND RMAN FAILED DUE TO: RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
    RMAN>crosscheck archivelog all;
    RMAN>delete noprompt expired archivelog all;

    Archivelog files
    RMAN> delete archivelog all;

    archivelog files
    RMAN> delete force archivelog until time 'sysdate -1';

    RMAN> delete noprompt force archivelog until time 'sysdate -2';

    Archivelog files
    RMAN> delete expired archivelog all; 

    RMAN> delete obsolete;
    RMAN> delete noprompt obsolete;

    RMAN> delete expired backup;
    --expired
    RMAN> delete expired ;

    RMAN> delete backupset id;
    RMAN> delete backup;

    RMAN> change backupset id unavailable;

    RMAN> change backupset id keep forever logs;
    RMAN> change backupset id keep until time 'sysdate+30' logs;
    RMAN> change backupset id nokeep;

    RMAN>configure retention policy to recovery window of 30 days;

    CONFIGURE RETENTION POLICY TO REDUNDANCY n ;

    CONFIGURE RETENTION POLICY TO NONE;

    set archivelog destination to 'e:\temp';
 -------------------------------------------------------------------
 -------------------------------------------------------------------   

32)*-- CREATE DATABASE
    CREATE DATABASE "TEST1"
    DATAFILE '/u01/app/oracle/oradata/TEST1/system01.dbf'
    SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
    EXTENT MANAGEMENT LOCAL

    SYSAUX DATAFILE '/u01/app/oracle/oradata/TEST1/sysaux01.dbf'
    SIZE 120M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

    SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP
    TEMPFILE '/u01/app/oracle/oradata/TEST1/temp01.dbf'
    SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED

    SMALLFILE UNDO TABLESPACE "UNDOTBS1"
    DATAFILE '/u01/app/oracle/oradata/TEST1/undotbs01.dbf'
    SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED

    DEFAULT TABLESPACE "USERS"
    DATAFILE '/u01/app/oracle/oradata/TEST1/users01.dbf'
    SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED

    EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
    CHARACTER SET UTF8
    NATIONAL CHARACTER SET AL16UTF16

    LOGFILE
    GROUP 1 ('/u01/app/oracle/oradata/TEST1/redo01.log') SIZE 51200K,
    GROUP 2 ('/u01/app/oracle/oradata/TEST1/redo02.log') SIZE 51200K,
    GROUP 3 ('/u01/app/oracle/oradata/TEST1/redo03.log') SIZE 51200K

    SET DEFAULT SMALLFILE TABLESPACE

    USER SYS IDENTIFIED BY mysupersekret
    USER SYSTEM IDENTIFIED BY supersekret;
 -------------------------------------------------------------------
 -------------------------------------------------------------------     

33)*-- CPU USAGE OF RUNNING SQL
    select ss.username,
       se.SID,
       VALUE / 100 cpu_usage_seconds,
       sq.SQL_TEXT,
       sq.SQL_FULLTEXT,
       sq.SQL_ID
    from v$session ss, v$sesstat se, v$statname sn, V$SQLAREA sq
    where se.STATISTIC# = sn.STATISTIC#
        and NAME like '%CPU used by this session%'
        and se.SID = ss.SID
        and ss.SQL_ID = sq.SQL_ID
        and ss.status = 'ACTIVE'
        and ss.username is not null
    order by VALUE desc;
 -------------------------------------------------------------------
 -------------------------------------------------------------------
34)*-- DROP PARTITIONS FOR RANGE

    declare
        cursor drop_partition is
        select *
          from all_tab_partitions t
         where t.table_name = 'table_name_here'
           and t.table_owner = 'schema_name_here'
           AND t.partition_name between 'PR_DATA_DAY_20160101' and
               'PR_DATA_DAY_20161031'
         order by t.partition_name;

        type record_type is table of drop_partition%rowtype;
        rec record_type;
        i_limit number := 30;
    begin
        open drop_partition;
        loop
            fetch drop_partition bulk collect into rec limit i_limit;
            exit when rec.count=0;
            for i in 1 .. rec.count loop
                    --dbms_output.put_line(rec(i).partition_name);
                    execute immediate 'alter table table_name_here drop partition ' ||rec(i).partition_name;
            end loop;
        end loop;
        close drop_partition;
    end;

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

35)*-- ORACLE SEGMENT ADVISORS
    select * from DBA_ADVISOR_USAGE;
    select * from DBA_ADVISOR_FINDINGS;
    select * from DBA_ADVISOR_RECOMMENDATIONS;
    select * from DBA_ADVISOR_ACTIONS;
    select * from DBA_ADVISOR_OBJECTS

36)*-- SHRINK SPACE

    Here is a working example that invokes Segment Advisor to analyze a table and uses the
    Shrink option to reclaim the unused space. Table XX_DATA is a table with several inserts
    and deletes, thus prone to fragmentation. Let’s run Segment Advisor on this table to
    determine if there is any reclaimable space.
    Running Segment Advisor involves four DBMS_ADVISOR procedures. First, define the task,
    then add objects to the task, then define expected recommendations, and finally execute
    the task.

        SQL> EXEC DBMS_ADVISOR.CREATE_TASK (advisor_name=>'Segment Advisor', task_name=> 'XX_DATA_SEG_ADV');
        PL/SQL procedure successfully completed.

        SQL> DECLARE
        objid NUMBER;
        BEGIN
        DBMS_ADVISOR.CREATE_OBJECT (task_name=> 'XX_DATA_SEG_ADV',
        object_type=> 'TABLE',
        attr1 => 'SYSTEM',
        attr2 => 'XX_DATA',
        attr3 => NULL,
        attr4 => 'NULL',
        attr5 => NULL,
        object_id => objid);
        END;
        /
        PL/SQL procedure successfully completed.

        SQL>
        SQL> EXEC DBMS_ADVISOR.SET_TASK_PARAMETER (task_name => 'XX_DATA_SEG_ADV',
        parameter => 'RECOMMEND_ALL', value => 'TRUE');
        PL/SQL procedure successfully completed.

        SQL>
        SQL> EXEC DBMS_ADVISOR.EXECUTE_TASK (task_name => 'XX_DATA_SEG_ADV');
        PL/SQL procedure successfully completed.

        Now, query DBA_ADVISOR_FINDINGS for the findings.
        SQL> SELECT message, more_info
        FROM DBA_ADVISOR_FINDINGS
        WHERE task_name = 'XX_DATA_SEG_ADV';
        MESSAGE
        MORE_INFO
        --------------------------------------------------------------------------------
        Enable row movement of the table SYSTEM.XX_DATA and perform shrink, estimated
        savings is 252134195 bytes.
        Allocated Space:503316480: Used Space:251182285: Reclaimable Space :252134195
        SQL>

        SQL> SELECT benefit_type
        FROM dba_advisor_recommendations
        WHERE task_name = 'XX_DATA_SEG_ADV';
        BENEFIT_TYPE
        --------------------------------------------------------------------------------
        Enable row movement of the table SYSTEM.XX_DATA and perform shrink, estimated
        savings is 252134195 bytes.
        SQL>

        So our expectation was correct. The XX_DATA table does have some reclaimable space.
        Validate the current allocated space and then perform the Shrink Space operation.
        SQL> SELECT bytes FROM dba_segments
        WHERE segment_name = 'XX_DATA';
        BYTES
        ----------
        503316480
        SQL>

        Now, follow the recommendation. First, we will perform the COMPACT to show the impact
        on allocated space, and then we will perform a shrink.
        SQL> ALTER TABLE xx_data ENABLE ROW MOVEMENT;
        Table altered.
        SQL> ALTER TABLE xx_data SHRINK SPACE COMPACT;
        Table altered.
        SQL> SELECT bytes FROM dba_segments
        WHERE segment_name = 'XX_DATA';
        BYTES
        ----------
        503316480
        SQL> ALTER TABLE xx_data SHRINK SPACE;
        Table altered.
        SQL> SELECT bytes FROM dba_segments
        WHERE segment_name = 'XX_DATA';
        BYTES
        ----------
        212598784
        SQL>

37)*-- CHECK ALERT LOG DESTINATION
    show parameter BACKGROUND_DUMP_DEST
    SELECT * FROM V$DIAG_INFO

38)*-- CHECK CONTROL FILES
    select * from v$controlfile;
    select * from v$controlfile_record_section

39)*-- DATA REDACTION
    -- FULL REDACTION
    BEGIN
        DBMS_REDACT.ADD_POLICY (
               object_schema         => 'HR',
               object_name           => 'full_redact_num',
               column_name           => 'code',
               policy_name           => 'Full_num_redact',
               function_type         => DBMS_REDACT.FULL,
               policy_description    => 'Full redaction on num col',
               expression            => '1=1');
    END;
    /
    -- PARTIAL REDACTION
    BEGIN
    DBMS_REDACT.ADD_POLICY(
       object_schema       => 'HR',
       object_name         => 'partial_redact_char',
       column_name         => 'name',
       policy_name         => 'partial_char_redact',
       function_type       => DBMS_REDACT.PARTIAL,
       function_parameters => 'VVVVV,VVVVV,*,2,4',
       expression          => '1=1',
       policy_description  => 'Partially redacts Names');
    END;
    /

40)*-- ROWID DETAILS
    OOOOOFFFBBBBBBRRR
    OOOOO  is the data object number
    FFF    is the relative file number of the database
    BBBBBB is the block number in the file
    RRR    is the row number within the block.
    All four numbers must be in base-64 format

41)* -- EXTERNAL TABLE
  create table DWHODS.DU_W_AALIYEV_201700704_NORMAL
    (
      cdr_id    VARCHAR2(30)
    )
    organization external
    (
      type ORACLE_LOADER
      default directory DATA_PUMP_DIR
      access parameters
      (
      records delimited by newline
      fields terminated by ":" ldrtrim
      (
      cdr_id
      )
      )
      location (DATA_PUMP_DIR:'20170704.txt')
    )
    reject limit 0;

42)* -- CREATE FUNCTION
    CREATE OR REPLACE FUNCTION CUST_ID_GENERATE(CUST_ID IN NUMBER) RETURN VARCHAR2
    IS
    customer_id NUMBER(10);
    new_code varchar2(300);
    new_code2 varchar2(300);
    begin
    customer_id := cust_id;
    FOR i in 1 .. length(customer_id) loop
        SELECT code into new_code from mapping_customer t where t.num = SUBSTR(customer_id,i,1);
         new_code2 := new_code2 || new_code;
    end loop;
      return new_code2;
    end;
    /

43)* -- INFO MASKING
  SET SERVEROUTPUT ON;
  DECLARE

  new_card varchar2(500);
  CURSOR CARDS IS select distinct card from table_name_here;
  TYPE RECORD_TYPE IS TABLE OF CARDS%ROWTYPE;
  REC RECORD_TYPE;
  k number(10);
  j number(10);
  l number;
  prefix char;

  BEGIN

  k := 1000000;
  L := 0;
  OPEN CARDS;
    LOOP
    FETCH CARDS BULK COLLECT INTO REC;
    EXIT WHEN REC.COUNT = 0;
      for i in 1 .. 151866 LOOP
      j := i + k;
      IF L = 3 THEN L := 0; END IF;
      for z in 1 .. 3 loop
      IF L = '1' then prefix := 'A';
      ELSIF L='2' THEN prefix := 'B' ;
      ELSE PREFIX := 'C';
      END IF;
      SELECT REC(I).CARD || ' - ' || prefix || j INTO new_card FROM DUAL;
      insert into NEW_CARD_MASKS_MAIN values(rec(i).card, prefix || j);
      --DBMS_OUTPUT.PUT_LINE(NEW_CARD);
      L := L +1;
      exit;
      END LOOP;
      END LOOP;
    END LOOP;
    commit;
  CLOSE CARDS;

  END;
  /

45)* -- Unable to extend table SYS.AUD$ by 128 in tablespace.SYSTEM
  Cause:
  Oracle system auditing is turned on (This is the default in Oracle 11-up).

  Resolution:
  There are two possible solutions:

  1. Add a datafile to the SYSTEM tablespace (via util o/13/3); or

  2. Truncate the SYS.AUD$ table (and, optionally, disable auditing). Aleph doesnt need these audit records, so SYS.AUD$ can be truncated:

  > su - oracle11 [this may be plain "oracle" on your server]
  Password:
  Execute oracle cshrc
  ...
  oracle11@abcu(aleph20)~>sqlplus / as sysdba

  sys@ALEPH20> truncate table AUD$;

  **If you get a "Resource Busy" message, run $alephe_root/aleph_shutdown and restart Oracle.** Then proceed with the above steps.

  If aleph_shutdown was run, run $alephe_root/aleph_startup. 

  The system should now be functional again.

46)* -- GENERATING AWR REPORT
    set head on
    set array 1
    set linesize 10000
    set pagesize 50000
    set long 10000
    set echo off
    set trimspool on
    set colsep ';'
    set numformat 9999999999999999.99
    set feedback off
    set timing on

    set serveroutput on size unlimited

    spool tmp_batch_awr_control.sql
    declare
    cursor c is
    select to_char(s.startup_time,'dd Mon "at" HH24:mi:ss') instart_fmt
    , di.instance_name inst_name
    , di.instance_number instance_number
    , di.db_name db_name
    , di.dbid dbid
    , lag (s.snap_id,1,0) over (partition by di.instance_number order by s.snap_id) begin_snap_id
    , s.snap_id end_snap_id
    , to_char(s.begin_interval_time,'yyyymmddhh24mi') beginsnapdat
    , to_char(s.end_interval_time,'yyyymmddhh24mi') endsnapdat
    , s.snap_level lvl
    from dba_hist_snapshot s
    , dba_hist_database_instance di
    ,gv$instance i
    ,v$database d
    where s.dbid = d.dbid
    and di.dbid = d.dbid
    and s.instance_number = i.instance_number
    and di.instance_number = i.instance_number
    and di.dbid = s.dbid
    and di.instance_number = s.instance_number
    and di.startup_time = s.startup_time
    --and s.begin_interval_time > trunc(sysdate -1) -- last last 7 days
    and s.snap_id between 19334 and 19343
    order by di.db_name, i.instance_name, s.snap_id;
    begin
    for c1 in c
    loop
    if c1.begin_snap_id > 0 then
    dbms_output.put_line('spool c:\temp\awr\AWR_Rpt_'||c1.inst_name||'_Snap_'
    ||c1.begin_snap_id||'_thru_'||c1.end_snap_id||'.html');
    dbms_output.put_line('select output from table(dbms_workload_repository.awr_report_html( '||c1.dbid||','||
    c1.instance_number||','||
    c1.begin_snap_id||','||
    c1.end_snap_id||',0 ));');
    dbms_output.put_line('spool off');
    end if;
    end loop;
    end;
    /
    spool off;
    set heading off
    set pages 50000
    set linesize 1500
    set trimspool on
    set trimout on
    set term off
    set verify off;
    set feedback off;
    @tmp_batch_awr_control.sql

    Generating an AWR Compare report (Non-RAC) from the SQL*Plus command line:
    1. Generate AWR Reports
      $ sqlplus / as sysdba
      SQL> @?/rdbms/admin/awrddrpt.sql
    2. First, you need to specify whether you want an HTML or a text report.
      Enter value for report_type: html
    3. Specify the number of days for which you want to list snapshot Ids for the First Pair
      Enter value for num_days: 1
    4. After the list displays, you are prompted for the beginning and ending snapshot Id for the First Pair workload repository report.
      Enter value for begin_snap: 22665
      Enter value for end_snap: 22666
    5. Specify the number of days for which you want to list snapshot Ids for the Second Pair
      Enter value for num_days2: 1
    6. After the list displays, you are prompted for the beginning and ending snapshot Id for the Second Pair workload repository report.
      Enter value for begin_snap2: 22667
      Enter value for end_snap2: 22668
    7. Next, accept the default report name or enter a report name. The default name is accepted in the following example:
      The default report file name is awrdiff_1_22665_1_22667.html
      Report written to awrdiff_1_22665_1_22667.html

47)* -- GENERATING ASH
  ALTER SESSION SET NLS_LANGUAGE='AMERICAN';
  ALTER SESSION SET NLS_TERRITORY='AMERICA';
  spool tmp_batch_ash_control.sql
  declare
  c_format   CONSTANT VARCHAR2 (22)  := 'DD/MM/YYYY HH24:MI:SS' ;
  d1 timestamp;
  d2 timestamp;
  begin
  d1:= to_date('13/04/2018 09:00:00',c_format);
  for c in 1 .. 18 loop
  d2:=d1+30/1440;
  dbms_output.put_line('spool ASH_Rpt_'||to_char(d1,'ddmmyyyy_HH24MISS')||'_thru_'||to_char(d2,'ddmmyyyy_HH24MISS')||'.html');
  dbms_output.put_line('select output from table(dbms_workload_repository.ash_report_html( 3854012314,1,to_date('''||to_char(d1,c_format)||''','''||c_format||'''),to_date('''||to_char(d2,c_format)||''','''||c_format||''')));');
  dbms_output.put_line('spool off');
  d1:=d2;
  end loop;
  end;
  /
  spool off;
  set heading off
  set pages 50000
  set linesize 1500
  set trimspool on
  set trimout on
  set term off
  set verify off;
  set feedback off;
  @tmp_batch_ash_control.sql

48)* -- RUNNING SQLHC
  @sqlhc N agux3azmf389w   -- N means no license or license option none / T means Tuning/Performance

49)* -- CHANGE DB SNAPHOT INTERVAL AND RETENTION
  exec dbms_workload_repository.modify_snapshot_settings(interval => 30, retention => 21600) -- 30 MINUTE/21600 MINUTE

  Check snapshot interval:

  select
       extract( day from snap_interval) *24*60+
       extract( hour from snap_interval) *60+
       extract( minute from snap_interval ) "Snapshot Interval",
       extract( day from retention) *24*60+
       extract( hour from retention) *60+
       extract( minute from retention ) "Retention Interval"
from dba_hist_wr_control;

50)* -- ORA-31631: privileges are required
    ORA-39149: cannot link privileged user to non-privileged user when impdp using db link

  RUN BELOW STATEMENTS IN DBLINK USER DATABASE.
  grant imp_full_database,exp_full_database to LINK_USER;
  alter user LINK_USER default role imp_full_database,exp_full_database;

51)* -- MSSQL OBJECT FIND
     select object_id('view_name_here'); --435532635
     select * from sys.sql_modules where object_id='1943014003';

52)* -- OPPOSITE OF LIST AGGREGATION(LISTAGG)
	WITH test AS
          (SELECT col1, col2 FROM table_name_here)
         SELECT regexp_substr(col1, '[^,]+', 1, level) result , col2
         FROM test
         CONNECT BY level = LENGTH(regexp_replace(col1, '[^,]+')) + 1; 
	
53)* -- AUTOMATIC PARTITION BY DAY from NON-PARTITION TO PARTITION	
	create table new_partitioned_table
	partition by range ( trn_dt )
	interval ( NUMTODSINTERVAL(1,'day') )
	( partition p1 values less than ( date '2017-01-01' ))
	as
	select * from non_partition_table t where t.trn_dt between 
        '01-JAN-2017' and '01-JAN-2019';

54)* -- Oracle Tuning Script
Drop Tuning TASKS	
		execute dbms_sqltune.drop_tuning_task('87s8z2zzpsg88_tuning_task11');
		
	Create Tuning TASKS
		DECLARE
		  l_sql_tune_task_id  VARCHAR2(100);
		BEGIN
		  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
								  sql_id      => '03q4gv2af36k0',
								  scope       => DBMS_SQLTUNE.scope_comprehensive,
								  time_limit  => 500,
								  task_name   => '03q4gv2af36k0_tuning_task11',
								  description => 'Tuning task1 for statement 03q4gv2af36k0');
		  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
		END;
		/
		
	Executing Tuning TASKS
		EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '03q4gv2af36k0_tuning_task11'); 
	
	Get Tuning Advisor REPORT	
		set long 65536
		set longchunksize 65536
		set linesize 100
		select dbms_sqltune.report_tuning_task('03q4gv2af36k0_tuning_task11') from dual;

2 Comments

Comments are closed.