Q&As: Fast Recovery Area (III)



(Q) What types of database files can be stored in the FAST RECOVERY AREA?

  • control file, online redo logs, archived redo logs, flashback logs, and RMAN backups


(Q) What two types of files, with respect to their use by the database, are kept in the FAST RECOVERY AREA?

  • Permanent files – Active files used by the database instance (control file, online redo logs)
  • Transient files – backups that may be deleted according to the retention policy



(Q) What happens if the instance cannot write to a multiplexed copy of the control file stored in the fast recovery area?

  • The instance fails.
  • Failure occurs EVEN if accessibel multiplexed copies are accessible outside the recovery area.


(Q) What is the difference between FLASH RECOVERY AREA and FAST RECOVERY AREA?

  • Just the name. FLASH became FAST with 11g


(Q) When configuring the FAST RECOVERY AREA, what happens if you specify DB_RECOVERY_FILE_DEST but DO NOT specify DB_RECOVERY_FILE_DEST_SIZE?

  • Specifying DB_RECOVERY_FILE_DEST without specifying DB_RECOVERY_FILE_DEST_SIZE is not allowed.





DataGuard: Configuring a Physical Standby


Configure Primary Server





Primary Server Setup


(1) Is database in Archivelog Mode?
Home -> Availability -> Recovery Settings
SQL> SELECT log_mode FROM v$database;

LOG_MODE
------------
ARCHIVELOG

If database is in Noarchivelog mode, you need to switch it to Archivelog mode.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;


(2) Is Force Logging enabled?
SQL> select force_logging from v$database;
FOR
---
NO

SQL> alter database force logging;
Database altered.


(3) Edit initialization parameters for the primary role
db_name db_unique_name log_archive_config log_archive_dest_1
log_archive_dest_2 log_archive_dest_state_1 log_archive_dest_state_2 fal_server
db_file_name_convert log_file_name_convert remote_login_passwordfile

Home -> Server -> Initialization Parameters
SQL> select name, db_unique_name from v$database;

NAME   DB_UNIQUE_NAME
--------- ------------------------------
DG11G   atlanta

SQL> show parameter db_name;

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
db_name         string  dg11g

SQL> show parameter db_unique_name;

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
db_unique_name        string  atlanta

SQL> alter system set log_archive_config='dg_config=(atlanta, fresno)';  
System altered.    
    
SQL> show parameter log_archive_config    
    
NAME                         TYPE    VALUE    
---------------------------- ------- ------------------------------    
log_archive_config           string  dg_config=(atlanta, fresno)    
    
Obs.    
   In this configuration, primary=> atlanta; standby=> fresno    


SQL> show parameter log_archive_dest_

NAME        TYPE  VALUE
---------------------------- ----------- ----------------------------
log_archive_dest_            string  location=use_db_recovery_file_
             dest valid_for=(all_logfiles,
      all_roles) db_unique_name=atla
      nta
...
NAME              TYPE    VALUE
--------------------------- ----------- ------------------------------
log_archive_dest_2          string  service=fresno async valid_for
      =(online_logfiles, primary_rol
      e) db_unique_name=fresno
...
Home -> Server -> Initialization Parameters


SQL> show parameter fal_server 
 
NAME         TYPE  VALUE 
------------------------------------ ----------- ------------------------------ 
fal_server        string  fresno 
Home -> Server -> Initialization Parameters


SQL> show parameter remote_login_pa

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile      string  EXCLUSIVE


SQL> alter system set db_file_name_convert='fresno', 'atlanta' scope=spfile; 
System altered. 

SQL> alter system set log_file_name_convert='fresno', 'atlanta' scope=spfile; 
System altered. 

SQL> alter system set standby_file_management=auto; 
System altered. 
Home -> Server -> Initialization Parameters -> Standby Database


(4) Configure Oracle Net for Redo Transport
Redo Transport moves archived redo logs from the primary to the standby database.
The two databases need to be connected through Oracle Net.
For this you can edit tnsnames.ora files in each server. i.e.
# Generated by Oracle configuration tools.

ATLANTA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oe5srv.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = DG11G)
    )
  )

FRESNO =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oe5clt.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = DG11G)
    )
  )


(5) create a backup copy of the primary database datafiles
Using RMAN:

Home -> Server -> Availability -> Schedule Backup
  • Select a whole Database backup and enter host credentials

Home -> Server -> Availability -> Schedule Customized Backup -> Options

Home -> Server -> Availability -> Schedule Customized Backup -> Settings

Home -> Server -> Availability -> Schedule Backup -> Schedule

Home -> Server -> Availability -> Schedule Backup -> Review

Home -> Server -> Availability -> Schedule Backup -> Summary


(6) Create a Control file for the Physical Standby Database
SQL> alter database create standby controlfile as '/tmp/fresno_stdby.ctl';

Database altered.


(7) Create a Parameter File for the Physical Standby Database.
Edit the Parameter file to configure the settings for the Standby
(3.1) Create a PFILE from the SPFILE used by the Primary DB

SQL> create pfile= '/tmp/initfresno.ora' from spfile;
File created.

(3.2) Modify the PFILE to be used on a Physical Standby
$ cat initfresno.ora 
dg11g.__db_cache_size=67108864
dg11g.__java_pool_size=4194304
dg11g.__large_pool_size=4194304
dg11g.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dg11g.__pga_aggregate_target=150994944
dg11g.__sga_target=205520896
dg11g.__shared_io_pool_size=0
dg11g.__shared_pool_size=117440512
dg11g.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/fresno/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/fresno/control01.ctl',
                '/u01/app/oracle/flash_recovery_area/fresno/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='atlanta', 'fresno'
*.db_name='dg11g'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.db_unique_name='fresno'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dg11gXDB)'
*.fal_server='atlanta'#In case of a switchover, fetch records from the fal_server
*.log_archive_config='dg_config=(atlanta, fresno)'#db_unique_names of the databases in the DG configuration
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles, all_roles) 
                            db_unique_name=fresno'#configure local redo log archiving. Use Fast recovery Area
*.log_archive_dest_2='service=atlanta async valid_for=(online_logfiles, primary_role) 
        db_unique_name=atlanta'#configure remote log archiving. Configure redo transport to physical standby
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='atlanta', 'fresno'
*.memory_target=356515840
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'


Standby Server Setup


(1) Create appropriate directories in the Standby server. Copy files from the prymary to the standby system.
(8.0) Create directories for fresno database files
$ mkdir -p oradata/fresno
$ mkdir -p flash_recovery_area/fresno
$ mkdir -p admin/fresno/adump

(8.1) Copy the standby control files to all locations (in the same server)
$ scp oracle@oe5srv:/tmp/fresno.ctl /u01/app/oracle/oradata/fresno/control01.ctl
$ cp /u01/app/oracle/oradata/fresno/control01.ctl /u01/app/oracle/flash_recovery_area/fresno/control02.ctl

(8.2) Copy Archivelogs and backups (in the same server)
$ scp -r oracle@oe5srv:/u01/app/oracle/flash_recovery_area/ATLANTA/archivelog  /u01/app/oracle/flash_recovery_area/fresno
$ scp -r oracle@oe5srv:/u01/app/oracle/flash_recovery_area/ATLANTA/backupset  /u01/app/oracle/flash_recovery_area/fresno

(8.3) Copy passwordfile and parameter file
$ scp -r oracle@oe5srv:$ORACLE_HOME/dbs/orapwdg11g $ORACLE_HOME/dbs
$ scp -r oracle@oe5srv:/tmp/initfresno.ora /tmp/initfresno.ora

(2) Create SPFILE from the edited parameter file
oracle@oe5clt:$ echo $ORACLE_SID  
dg11g

oracle@oe5clt:$ sqlplus / as sysdba 
SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 8 00:37:44 2012
Copyright (c) 1982, 2009, Oracle.  All rights reserved.  
  
Connected to an idle instance.  

SQL> create spfile from pfile='/tmp/initfresno.ora';
  
File created.  

(3) Restore backup
RMAN> startup mount; 
 
Oracle instance started 
database mounted 

RMAN> restore database; 
 
Starting restore at 08-MAR-12 
using channel ORA_DISK_1 
 
channel ORA_DISK_1: starting datafile backup set restore 
channel ORA_DISK_1: specifying datafile(s) to restore from backup set 
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/fresno/system01.dbf 
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/fresno/sysaux01.dbf 
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/fresno/undotbs01.dbf 
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/fresno/users01.dbf 
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/FRESNO/backupset/o1_mf_nnndf_BACKUP_ATLANTA_000_0_7ojnc7g1_.bkp 
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/FRESNO/backupset/o1_mf_nnndf_BACKUP_ATLANTA_000_0_7ojnc7g1_.bkp tag=BACKUP_ATLANTA_000_030712102725 
channel ORA_DISK_1: restored backup piece 1 
channel ORA_DISK_1: restore complete, elapsed time: 00:02:37 
Finished restore at 08-MAR-12 

(4) Create Online and Standby Redo logs on the Standby Database
SQL> alter system set standby_file_management=manual;

System altered.

SQL> alter database add logfile('/u01/app/oracle/oradata/fresno/online_redo01.log') size 50m;

Database altered.

SQL> alter database add logfile('/u01/app/oracle/oradata/fresno/online_redo02.log') size 50m;

Database altered.

SQL> alter database add logfile('/u01/app/oracle/oradata/fresno/online_redo03.log') size 50m;

Database altered.

SQL> alter system set standby_file_management=auto;

System altered.


SQL> alter database add standby logfile('/u01/app/oracle/oradata/fresno/standby_redo01.log') size 50m;

Database altered.

SQL> alter database add standby logfile('/u01/app/oracle/oradata/fresno/standby_redo02.log') size 50m;

Database altered.

SQL> alter database add standby logfile('/u01/app/oracle/oradata/fresno/standby_redo03.log') size 50m;

Database altered.

SQL> alter database add standby logfile('/u01/app/oracle/oradata/fresno/standby_redo04.log') size 50m;

Database altered.


Start Apply Service

SQL> Alter database recover managed standby database disconnect from session;

Database altered.




















SQL Functions (11gR2)





Single-row F Aggregate F Analytic F
Object Reference F Model F OLAP F


Single-row functions

  •  Numeric functions  (26, 11gR2)
  • ABS
  • ACOS
  •  ASIN
  • ATAN
  • ATAN2
  • BITAND
  • CEIL
  • COS
  • COSH
  • EXP
  • FLOOR
  • LN
  • LOG
  • MOD
  • NANVL
  • POWER
  • REMAINDER
  • ROUND (number)
  • SIGN
  • SIN
  • SINH
  • SQRT
  • TAN
  • TANH
  • TRUNC (number)
  • WIDTH BUCKET 
  •  Character F returning character values (22, 11gR2)
  • CHR
  • CONCAT
  • INITCAP
  • LOWER
  • LPAD
  • LTRIM
  • NCHR
  • NLS_INITCAP
  • NLS_LOWER
  • NLS_UPPER
  • NLSSORT
  • REGEXP_REPLACE
  • REGEXP_SUBSTR
  • REPLACE
  • RPAD
  • RTRIM
  • SOUNDEX
  • SUBSTR
  • TRANSLATE
  • TREAT
  • TRIM
  • UPPER
  •  Character F returning number values (5, 11gR2)
  • ASCII
  • INSTR
  • LENGTH
  • REGEXP_COUNT
  • REGEXP_INSTR

  •  NLS Character F (3, 11gR2)
  • NLS_CHARSET_DECL_LEN
  • NLS_CHARSET_ID
  • NLS_CHARSET_NAME

  • Datetime F (28, 11GR2)
  •  ADD_MONTHS
  • CURRENT_DATE
  • CURRENT_TIMESTAMP
  • DBTIMEZONE
  • EXTRACT (datetime)
  • FROM_TZ
  • LAST_DAY
  • LOCALTIMESTAMP
  • MONTHS_BETWEEN
  • NEW_TIME
  • NEXT_DAY
  • NUMTODSINTERVAL
  • NUMTOYMINTERVAL
  • ORA_DST_AFFECTED
  • ORA_DST_CONVERT
  • ORA_DST_ERROR
  • ROUND (date)
  • SESSIONTIMEZONE
  • SYS_EXTRACT_UTC
  • SYSDATE
  • SYSTIMESTAMP
  • TO_CHAR (datetime)
  • TO_DSINTERVAL
  • TO_TIMESTAMP
  • TO_TIMESTAMP_TZ
  • TO_YMINTERVAL
  • TRUNC (date)
  • TZ_OFFSET

  • General comparison F (2, 11gR2)
  • GREATEST
  • LEAST

  •  Conversion F (38, 11gR2)
  • ASCIISTR
  • BIN_TO_NUM
  • CAST
  • CHARTOROWID
  • COMPOSE
  • CONVERT
  • DECOMPOSE
  • HEXTORAW
  • NUMTODSINTERVAL
  • NUMTOYMINTERVAL
  • RAWTOHEX
  • RAWTONHEX
  • ROWIDTOCAR
  • ROWIDTONCHAR
  • SCN_TO_TIMESTAMP
  • TIMESTAMP_TO_SCN
  • TO_BINARY_FLOAT
  • TO_BINARY_DOUBLE
  • TO_BLOB
  • TO_CHAR (character)
  • TO_CHAR (datetime)
  • TO_CHAR (number)
  • TO_CLOB
  • TO_DATE
  • TO_DSINTERVAL
  • TO_LOB
  • TO_MULTI_BYTE
  • TO_NCHAR (character)
  • TO_NCHAR (datetime)
  • TO_NCHAR (number)
  • TO_NCLOB
  • TO_NUMBER
  • TO_SINGLE_BYTE
  • TO_TIMESTAMP
  • TO_TIMESTAMP_TZ
  • TO_YMINTERVAL
  • TRANSLATE ... USING
  • UNISTR

  •  Large Object F (3, 11gR2)
  • BFILENAME
  • EMPTY_BLOB
  • EMPTY_CLOB

  •  Collection F (5, 11gR2)
  • CARDINALITY
  • COLLECT
  • POWERMULTISET
  • POWERMULTISET_BY_CARDINALITY
  • SET

  •  Hierarchical F (1, 11gR2)
  • SYS_CONNECT_BY_PATH
  •  Data Mining F (12, 11gR2)
  • CLUSTER_ID
  • CLUSTER_PROBABILITY
  • CLUSTER_SET
  • FEATURE_ID
  • FEATURE_SET
  • FEATURE_VALUE
  • PREDICTION
  • PREDICTION_BOUNDS
  • PREDICTION_COST
  • PREDICTION_DETAILS
  • PREDICTION_PROBABILITY
  • PREDICTION_SET

  •  XML F (26, 11gR2)

  • Encoding and Decoding F (4, 11gR2)
  • DECODE
  • DUMP
  • ORA_HASH
  • VSIZE

  • NULL-related F (6, 11gR2)
  • COALESCE
  • LNNVL
  • NANVL
  • NULLIF
  • NVL
  • NVL2

  • Environment and Identifier F (6, 11gR2)
  • SYS_CONTEXT
  • SYS_GUID
  • SYS_TYPEID
  • UID
  • USER
  • USERENV


    Aggregate Functions
    (41, 11gR2)
    Operate on groups of rows
    Appear in SELECT, ORDER BY and HAVING

    • AVG
    • COLLECT
    • CORR
    • CORR *
    • COUNT
    • COVAR POP
    • COVAR SAMP
    • CUME DIST
    • DENSE RANK
    • FIRST
    • GROUP_ID
    • GROUPING
    • GROUPING_ID
    • LAST
    • LISTAGG
    • MAX
    • MEDIAN
    • MIN
    • PERCENT_RANK
    • PERCENTILE_CONT
    • PERCENTILE_DISC
    • RANK
    • STATS_BINOMIAL_TEST
    • STATS_CROSSTAB
    • STATS_F_TEST
    • STATS_KS_TEST
    • STATS_MODE
    • STATS_MW_TEST
    • STATS_ONEW_WAY_ANOVA
    • STATS_T_TEST *
    • STATS_WSR_TEST
    • STDDEV
    • STDDEV_POP
    • STDDEV_SAMP
    • SUM
    • SYS_XMLAGG
    • VAR_POP
    • VAR_SAMP
    • VARIANCE
    • XMLAGG
    • Linear Regression Functions (9, 11gR2)
    • REGR_SLOPE
    • REGR_INTERCEPT
    • REGR_COUNT
    • REGR_R2
    • REGR_AVGX
    • REGR_AVGY
    • REGR_SXX
    • REGR_SYY
    • REGR_SXY


    Analytic Functions
    (32, 11gR2)
    Commonly used on Data Warehousing environments
    Analytic functions are the last set of operations performed in a query, followed only by the ORDER BY clause
    All joins and the WHERE, GROUP BY and HAVING clauses are completed prior to the analytic functions are processed
    Good to compute cumulative, moving, centered, and reporting aggregates

    • AVG *
    • CORR *
    • COUNT *
    • COVAR_POP *
    • COVAR_SAMP *
    • CUME_DIST
    • DENSE_RANK
    • FIRST
    • FIRST_VALUE *
    • LAG
    • LAST
    • LAST_VALUE *
    • LEAD
    • LISTAGG
    • MAX *
    • MIN *
    • NTH VALUE *
    • NTILE
    • PERCENT_RANK
    • PERCENTILE_CONT
    • PERCENTILE_DISC
    • RANK
    • RATIO_TO_REPORT
    • ROW_NUMBER
    • STDDEV *
    • STDDEV_POP *
    • STDDEV_SAMP *
    • SUM *
    • VAR_POP *
    • VAR_SAMP *
    • VARIANCE *
    • Linear Regression Functions *

    • AVG



    (a) Reporting  each employee's salary and the average salary of the employees within the same department.
    
    SELECT first_name || ' ' || last_name AS emp_name,
           department_id,
           salary,
           AVG (salary) OVER (PARTITION BY department_id) AS avg_dept_sal
      FROM hr.employees;
    
    EMP_NAME       DEPARTMENT_ID   SALARY  AVG_DEPT_SAL
    ------------------  -------------  ------- ------------
    Adam Fripp     50  8200 3475.55556
    Alana Walsh     50  3100 3475.55556
    Alberto Errazuriz 80  12000 8955.88235
    Alexander Hunold 60  9000 5760
    Alexander Khoo  30  3100 4150
    Alexis Bull  50  4100 3475.55556
    Allan McEwen  80  9000 8955.88235
    Alyssa Hutton  80  8800 8955.88235
    ...
    


    (a) Lists manager_id, employee's name, hire_date, salary and the average salary of all employees that have the same manager
    
    -- note the AVG is calculated over all rows within each partition.
    -- no windowing clause is present
      SELECT manager_id,
             last_name,
             hire_date,
             salary,
             AVG (salary) OVER (PARTITION BY manager_id) AS c_mavg
        FROM hr.employees
    ORDER BY manager_id, hire_date, salary;
    
    MANAGER_ID LAST_NAME                 HIRE_DATE     SALARY     C_MAVG
    ---------- ------------------------- --------- ---------- ----------
           100 De Haan                   13-JAN-01      17000      11100
           100 Raphaely                  07-DEC-02      11000      11100
           100 Kaufling                  01-MAY-03       7900      11100
           100 Hartstein                 17-FEB-04      13000      11100
           100 Weiss                     18-JUL-04       8000      11100
           ...
           100 Zlotkey                   29-JAN-08      10500      11100
           101 Mavris                    07-JUN-02       6500     8983.2
           ...
           101 Whalen                    17-SEP-03       4400     8983.2
           102 Hunold                    03-JAN-06       9000       9000
           103 Austin                    25-JUN-05       4800       4950
           ...
    
    
    
    (b)Lists employee salary and average salary of the employees reporting to the same manager who were hired in the range just before through just after the employee.
    
    -- note the query has
    -- 1. a partition clause (partition rows by manager)
    -- 2. a windowing clause (within each partition, AVG considers only the rows immediately preceding and following each employees' row).
    
      SELECT manager_id,
             last_name,
             hire_date,
             salary,
             AVG (
                salary)
             OVER (PARTITION BY manager_id
                   ORDER BY hire_date
                   ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
                AS c_mavg
        FROM hr.employees
    ORDER BY manager_id, hire_date, salary;
    
    MANAGER_ID LAST_NAME                 HIRE_DATE     SALARY     C_MAVG
    ---------- ------------------------- --------- ---------- ----------
           100 De Haan                   13-JAN-01      17000      14000
           100 Raphaely                  07-DEC-02      11000 11966.6667
           ...
           100 Cambrault                 15-OCT-07      11000 7766.66667
           100 Mourgos                   16-NOV-07       5800       9100
           100 Zlotkey                   29-JAN-08      10500       8150
           101 Mavris                    07-JUN-02       6500       8250
           101 Baer                      07-JUN-02      10000 9502.66667
           ...
           101 Whalen                    17-SEP-03       4400       8204
           102 Hunold                    03-JAN-06       9000       9000
           103 Austin                    25-JUN-05       4800       4800
           ...
    




    • FIRST_VALUE and LAST_VALUE
    (a) For each employee, return employee salary and the name of the employee that works in the same department and has the lowest salary 
    
      SELECT department_id,
             last_name,
             salary,
             FIRST_VALUE (last_name)
             OVER (PARTITION BY department_id
                   ORDER BY salary ASC, hire_date
                   ROWS UNBOUNDED PRECEDING)
                AS lowest_sal
        FROM hr.employees
    ORDER BY department_id, last_name;
    
    DEPARTMENT_ID LAST_NAME                     SALARY LOWEST_SAL               
    ------------- ------------------------- ---------- -------------------------
               10 Whalen                          4400 Whalen                   
               20 Fay                             6000 Fay                      
               20 Hartstein                      13000 Fay                      
               30 Baida                           2900 Colmenares               
               30 Colmenares                      2500 Colmenares               
               30 Himuro                          2600 Colmenares               
               30 Khoo                            3100 Colmenares               
               30 Raphaely                       11000 Colmenares               
               30 Tobias                          2800 Colmenares               
               40 Mavris                          6500 Mavris                   
     ...
               80 Zlotkey                        10500 Kumar                    
               90 De Haan                        17000 De Haan                  
               90 King                           24000 De Haan                  
               90 Kochhar                        17000 De Haan                  
              100 Chen                            8200 Popp                     
              100 Faviet                          9000 Popp                     
              100 Greenberg                      12008 Popp                     
              100 Popp                            6900 Popp                     
              100 Sciarra                         7700 Popp                     
              100 Urman                           7800 Popp                     
    ...
    
    
    (b) For each employee of department 50, return salary of employees on the previous and following rows of the record set.
     SELECT department_id,
             last_name,
             salary,
             FIRST_VALUE (salary)
             OVER (ORDER BY salary ASC
                   ROWS between 1 preceding and current row) as previous_sal,
             LAST_VALUE (salary)
             OVER (ORDER BY salary ASC
                   ROWS between current row and 1 following) as next_sal
        FROM hr.employees
    where department_id =50;
    
    DEPARTMENT_ID LAST_NAME                     SALARY PREVIOUS_SAL   NEXT_SAL
    ------------- ------------------------- ---------- ------------ ----------
               50 Olson                           2100         2100       2200
               50 Philtanker                      2200         2100       2200
               50 Markle                          2200         2200       2400
               50 Gee                             2400         2200       2400
               50 Landry                          2400         2400       2500
               50 Patel                           2500         2400       2500
               50 Vargas                          2500         2500       2500
               50 Marlow                          2500         2500       2500
               50 Perkins                         2500         2500       2500
               50 Sullivan                        2500         2500       2600
    ...
    







    • RANK
    SELECT department_id,
           last_name,
           salary,
           commission_pct,
           RANK ()
           OVER (PARTITION BY department_id
                 ORDER BY salary DESC, commission_pct DESC)
              "Rank"
      FROM hr.employees
     WHERE department_id = 80;
    


    Object Reference Functions
    • DEREF
    • MAKE_REF
    • REF
    • REFTOHEX
    • VALUE

    Model Functions
    • CV
    • ITERATION_NUMBER
    • PRESENTNNV
    • PRESENTV
    • PREVIOUS

    OLAP Functions
    • CUBE_TABLE

    Data Cartridge Functions
    • DATAOBJ_TO_PARTITION


    User-Defined Functions