gaperi

[gaperi]

https://medium.com/dp6-us-blog/7-advanced-sql-concepts-you-need-to-know-45fa149ba0b0

From

Where

Group by

Having

Select

Distinct

Union

Order by

Limit/Top

[gaperi]

CONNECT BY 

[gaperi]

Need to practice more about it

[gaperi]

GROUP BY....ROLLUP, CUBE, GROUPING function example

[gaperi]

DB Immigration

CTAS (metadata)

RMAN

Data Pump

Data Guard

Golden Gate 

Cross-platform transportable Tablespaces

Hot/cold DB

[gaperi]

Cursor ORACLE

https://www.guru99.com/pl-sql-cursor.html

Oracle PL/SQL offers native support for the execution of static (also referred to as "embedded") SELECT statements. Use a SELECT to query data from one or more tables in the Oracle database, and then deposit that date into variables in your PL/SQL program.

While all querying occurs through the SELECT, PL/SQL offers a number of different ways to construct and execute queries, including:

SELECT-INTO: fetch a single row of data, leaving Oracle to open, fetch and close the cursor.

SELECT-BULK COLLECT INTO: fetch multiple rows of data, leaving Oracle to open, fetch and close the cursor.

Explicit Cursor: declare a CURSOR in a PL/SQL program and then write OPEN, FETCH and CLOSE statements to retrieve the data and manage the cursor.

Cursor FOR loop: use the FOR loop construct with a SELECT statement to fetch multiple rows, leaving Oracle to declare a record, open, fetch and close the cursor.

Cursor Variables: declare a variable, assign a cursor to it with an OPEN FOR statement.

EXECUTE IMMEDIATE-INTO: execute a dynamically-constructed SELECT statement, and fetch the row into variables.

EXECUTE IMMEDIATE-BULK COLLECT INTO: execute a dynamically-constructed SELECT statement, and fetch multiple rows into one or more collections.

[gaperi]

Types of Triggers in Oracle

Triggers can be classified based on the following parameters.

Classification based on the timing

BEFORE Trigger: It fires before the specified event has occurred.

AFTER Trigger: It fires after the specified event has occurred.

INSTEAD OF Trigger: A special type. You will learn more about the further topics. (only for DML )

Classification based on the level

STATEMENT level Trigger: It fires one time for the specified event statement.

ROW level Trigger: It fires for each record that got affected in the specified event. (only for DML)

Classification based on the Event

DML Trigger: It fires when the DML event is specified (INSERT/UPDATE/DELETE)

DDL Trigger: It fires when the DDL event is specified (CREATE/ALTER)

DATABASE Trigger: It fires when the database event is specified (LOGON/LOGOFF/STARTUP/SHUTDOWN)

So each trigger is the combination of above parameters.

[gaperi]

Oracle identity column

Oracle identity column restrictions

The identity columns are subject to the following restrictions:

Each table has one and only one identity column.

The data type of the identity column must be a numeric data type. the user-defined data type is not allowed to use with the identity clause.

The identity column is not inherited by the CREATE TABLE AS SELECT statement.

The identity column cannot have another DEFAULT constraint.

The encryption algorithm for encrypted identity columns can be inferred therefore you should use a strong encryption algorithm.

The inline constraint of the identity column must not conflict with the NOT NULL and NOT DEFERRABLE constraint stated by the identity clause.

[gaperi]

INDEXES - ORACLE

Unlike other database systems, Oracle does not automatically create an index for the foreign keycolumns.

[gaperi]

Introduction to BULK COLLECT

(livesql tutorial)

To take advantage of bulk processing for queries, you simply put BULK COLLECT before the INTO keyword of your fetch operation, and then provide one or more collections after the INTO keyword.

Here are some things to know about how BULK COLLECT works:

It can be used with all three types of collections: associative arrays, nested tables, and VARRAYs.

You can fetch into individual collections (one for each expression in the SELECT list) or a single collection of records.

The collection is always populated densely, starting from index value 1.

If no rows are fetched, then the collection is emptied of all elements.

You can use BULK COLLECT in all these forms:

SELECT column(s) BULK COLLECT INTO collection(s) FETCH cursor BULK COLLECT INTO collection(s) EXECUTE IMMEDIATE query_string BULK COLLECT INTO collection(s)

Here's a block of code that fetches all rows in the employees table with a single context switch, and loads the data into a collection of records that are based on the table.

DECLARE   TYPE employee_info_t IS TABLE OF employees%ROWTYPE;   l_employees   employee_info_t; BEGIN   SELECT *     BULK COLLECT INTO l_employees     FROM employees    WHERE department_id = 50;   DBMS_OUTPUT.PUT_LINE (l_employees.COUNT); END;

If you do not want to retrieve all the columns in a table, create your own user-defined record type and use that to define your collection. All you have to do is make sure the list of expressions in the SELECT match the record type's fields.

DECLARE   TYPE two_cols_rt IS RECORD (      employee_id   employees.employee_id%TYPE,      salary        employees.salary%TYPE   );   TYPE employee_info_t IS TABLE OF two_cols_rt;   l_employees   employee_info_t; BEGIN   SELECT employee_id, salary     BULK COLLECT INTO l_employees     FROM employees    WHERE department_id = 50;   DBMS_OUTPUT.PUT_LINE (l_employees.COUNT); END;

Quick Tip

You can avoid the nuisance of declaring a record type to serve as the type for the collection through the use of a "template cursor." This cursor should have the same select list as the BULK COLLECT query. You can, however, leave off the WHERE clause and anything else after the FROM clause, because it will never be used for anything but a %ROWTYPE declaration. Here's an example:

DECLARE   CURSOR employee_info_c IS      SELECT employee_id, salary        FROM employees ;   TYPE employee_info_t IS TABLE OF employee_info_c%ROWTYPE;   l_employees   employee_info_t; BEGIN   SELECT employee_id, salary     BULK COLLECT INTO l_employees     FROM employees    WHERE department_id = 10; END;

Fill in the Blanks

In the block below replace the #FINISH# tags with code so that the last names of all employees in department 50 are displayed.

DECLARE   #FINISH#   l_names   names_t; BEGIN   SELECT last_name     #FINISH#     FROM employees    WHERE department_id = 50    ORDER BY last_name; END;

[gaperi]

initialization parameters

Oracle Database security can be affected by initialization parameters. Examples of initialization parameters that affect security include, but are not limited to:

O7_DICTIONARY_ACCESSIBILITY

ADG_ACCOUNT_INFO_TRACKING

AUDIT_FILE_DEST

AUDIT_SYS_OPERATIONS

AUDIT_SYSLOG_LEVEL

DBFIPS_140

LDAP_DIRECTORY_ACCESS

LDAP_DIRECTORY_SYSAUTH

REMOTE_LOGIN_PASSWORDFILE

REMOTE_OS_AUTHENT

REMOTE_OS_ROLESSEC_CASE_SENSITIVE_LOGON

SEC_MAX_FAILED_LOGIN_ATTEMPTS

SEC_PROTOCOL_ERROR_FURTHER_ACTION

SEC_PROTOCOL_ERROR_TRACE_ACTION

SEC_RETURN_SERVER_RELEASE_BANNER

SQL92_SECURITY

UNIFIED_AUDIT_SYSTEMLOG

UTL_FILE_DIR

[gaperi]

Install GUI - ORACLE LINUX 8