Oracle Database Interview Question

1. DATA Doctionary Table:
• USER_TAB_COLUMNS: All table names with column names and data types.
• USER_CONSTRAINTS: Table’s constraints name.
• USER_INDEXES: Table’s index name.
• TTTT
2. FUNCTION:
• GREATEST(): This function will return the greatest value in a list of expressions.
• LEAST(): This function will return the smallest value in a list of expressions.
• YYYYYYYYYY
3. Linewise Total Calculation: Using Analytical Functions, we can achieve this. SELECT EMPLOYEE_ID,SALARY,SUM(SALARY) OVER (ORDER BY EMPLOYEE_ID) LINE_TOTAL FROM EMPLOYEES.
4. Modify the order of columns without dropping the table: ALTER TABLE EMPLOYEES_TEST MODIFY MANAGER_ID INVISIBLE; Alter TABLE EMPLOYEES_TEST MODIFY MANAGER_ID VISIBLE;

5. Suppose we open and start working on a cursor, and in between some records were deleted by another user from the table. Are we still able to see all the selected rows of the cursor? Answer: Yes, we are able to see all the records that are selected in the cursor, because when we open a cursor, then it will load all data into memory, and data will be viewed from memory, not the base table. But if we operate any DML operation using cursor data, then the DML operation will fail because related data will not be available on the base table. We can also prevent DML operations during cursor execution by using the For Update clause within the cursor declaration.

6. Private Procedure Or Private Function: A private procedure or function is only defined within the package body, and no one will be able to access it. Only other package objects will be able to use this type of procedure or function. 

7. What is Pragma: Prgama is a precompiler directive to instruct the compiler to compile the program unit differently for a specific functionality at the runtime. List of pragma available in oracle (Based on 18C): AUTONOMOUS_TRANSACTION,COVERAGE,DEPRECATE,EXCEPTION_INIT,INLINE,RESTRICT_REFERENCES,SERIALLY_REUSABLE,UDF.
8. Table Level & Column Level Constraints: When we define a constraint in the column definition, then it will be column-level constraints, and when we define a constraint in the table level, then it will be table-level constraints. Composite constraints are now allowed in column-level constraints.
9. DELETE command will be fired by DML trigger, but Truncate command will not be fired by DML trigger. The Truncate command will be fired by DDL trigger. DDL trigger will be created on schema level, and we can specify it for specific tables by using the ORA_DICT_OBJ_NAME & ORA_DICT_OBJECT_TYPE function. The syntex will be:
   
   CREATE OR REPLACE TRIGGER T_TRUNCATE_TRG
   BEFORE TRUNCATE ON SCHEMA
   BEGIN
   IF ORA_DICT_OBJ_NAME ='Table_name' and ORA_DICT_OBJECT_TYPE ='TABLE THEN
   RAISE_APPLICATION_ERROR(-20001,'Truncation not allowed on Tables');
   END IF;
   END;
10. Hierarcial Query:
    SELECT FIRST_NAME, LAST_NAME, SYS_CONNECT_BY_PATH(LAST_NAME,'->') PATH, LEVEL,CONNECT_BY_ISLEAF FROM EMPLOYEES START WITH MANAGER_ID IS NULL CONNECT BY PRIOR EMPLOYEE_ID=MANAGER_ID
11. 
    
12. Delete: Delete is a DML operation, and we can delete partial data by using where clause. Deleted data can be recovered by using rollback.No impact on dependent object.This command is slower, because it use undo segment and triggers will get fired (on delete trigger). Deleted data is temporary store in then redo log.
    Truncate: Truncate is DDL (Autocommit) operation.All rows will be removed and cannot use where clause.Data lost forever and no impact dependent object.Truncate command is faster and no trigger will get invoked.
    Drop: DDL Command and removes the dependent object. Data can be recovered if Flashback option is on. Dependent object will have to be created.
    
    
13. Different between Delete,Truncate & Drop.
    Delete: Delete is a DML operation and we can delete partial data by using where clause. Deleted data can be recovered by using rollback.No impact on dependent object.This command is slower, because it use undo segment and triggers will get fired (on delete trigger). Deleted data is temporary store in then redo log.
    Truncate: Truncate is DDL (Autocommit) operation.All rows will be removed and cannot use where clause.Data lost forever and no impact dependent object.Truncate command is faster and no trigger will get invoked.
    Drop: DDL Command and removes the dependent object. Data can be recovered if Flashback option is on. Dependent object will have to be created.
    

14.  

15.