What is Tableau ?

Tableau is a visualization tool created by tableau software .Visualization is nothing but the visually showing information .

• Data visualization tool.
• Connect to several data sources.
• Create dashboards on minutes.
• Easy to Use.

Tableau is a software tool that basically connects easily to several data sources, it allows for rapid insight by transforming data into dashboards that look amazing and are also interactive that recreation takes minutes and can be done with simple drag and drop of data field. It does have some advanced capabilities that required experience and training to blow the software that helps people see and understand their data quickly and easily.
Tableau software products they basically have five main products and those are 

• Tableau Desktop.
• Tableau Server.
• Tableau Online.
• Tableau Reader.
• Tableau Public.

Tableau  Reader and Tableau  Public are free to use.
Tableau Desktop and Tableau Server come with a 14 day fully functional free trail period after which the user needs to start paying for the software, Tableau Desktop comes in both a professional and  a lower cost personal Edition so you can look into that on top of those website.
Tableau Online is available with annual subscription for a single user and it scales to support thousand of users.

Find the Object Dependencies

When we are going to change any table design then You might want to find out –

• which packages refer to this table? 
• Are there views created on it? 
• If I change the table design, how many and which objects will be affected?

Above information we will get it from ALL_DEPENDENCIES view. The view ALL_DEPENDENCIES stores information about dependencies between procedures, packages, functions, package bodies, and triggers accessible to the current user.

There are two related views:

DBA_DEPENDENCIES: describes all dependencies between objects in the database. This view may only be accessible to users with DBA rights.

USER_DEPENDENCIES: describes dependencies between objects only in the current user’s schema. This view does not have the column OWNER, as it is contained in the view definition.

 Check the below example:

SELECT name, referenced_owner, type, referenced_name, referenced_type
  FROM all_dependencies
 WHERE referenced_name = 'EMP';  

Collection element_type as CURSOR

Element_type : The data type of the collection element is any PL/SQL data type except REF CURSOR. Check the below example where we are using collecting element type as cursor.

element_type:

{ cursor_name%ROWTYPE
| db_table_name{%ROWTYPE | .column_name%TYPE}
| object_name%TYPE
| [REF] object_type_name
| scalar_datatype_name
| record_name[.field_name]%TYPE
| record_type_name
| variable_name%TYPE
}

Check The Below Example:

DECLARE

/*Declare Cursor*/

CURSOR all_emp_dat_cur

IS

SELECT * FROM EMP;

/*Declare table type, element as CURSOR*/

TYPE all_emp_dat_tbl 
IS TABLE OF all_emp_dat_cur%ROWTYPE INDEX BY PLS_INTEGER;

v_all_emp_dat_tbl  all_emp_dat_tbl ;

BEGIN

OPEN all_emp_dat_tbl;

FETCH all_emp_dat_tbl  BULK COLLECT INTO v_all_emp_dat_tbl;

CLOSE all_emp_dat_tbl;

/*write your logic */

END;

Cursor-Based Records

Cursor based  record is a record whose structure is drawn from the SELECT list of the cursor. The %ROWTYPE is used to declare the table record also used to declare a record for an explicit declare cursor. Check the below example

DECLARE
/*Define the cursor*/
CURSOR emp_details_cursor
IS
SELECT * from emp;

/*Create a record for this cursor*/
emp_details_rec  emp_details_cursor;

BEGIN
OPEN emp_details_cursor;
LOOP
        FETCH emp_details_cursor INTO emp_details_rec ;
        EXIT WHEN emp_details_rec%NOTFOUND;
        /*write your logic*/
END LOOP;
CLOSE emp_details_rec ;

END;

PIVOT and UNPIVOT

Bothe the operators are used for transposing rows into column and vice versa.
PIVOT : rows to column
UNPIVOT :column to rows.

SYS_REFCURSOR and REF CURSOR

REF CURSOR one of the most powerful,flexible and scalable ways to retuen query result from an Oracle Database to client application.

A FER CURSOR is a PL/SQL data type whose value is the memory address of a query work area on a database. In other works it's a pointer or handler to the result on the database.

While the REF CURSOR is returned to the client, the actual data is not returned until the client opens the REF CURSOR and requests the data. Note the data is not be retrived until the user attempts to read it.



SYS_REFCURSOR TYPE
Oracle introduced the pre-defined SYS_REFCURSOR type,
meaning that we can directly use it without declaring the ref cursor type every time.
variable_name SYS_REFCURSOR;

Ref Cursor Declaration

TYPE type_name IS REF CURSOR;
variable_name type_name;

Function Return Multiple Values and Rows

Return multiple values and rows in a Function:
Steps:

• Create a object type , with rwquired column attributes.
•  Create a nested table based on the object created.
•  Create a function that returns the nested table type.
•  Call the function.

1. Create a object type:

 CREATE OR REPLACE TYPE EMP_OBJ_TYP

 AS OBJET

(ENAME VARCHAR2(100),

 EMPNO NUMBER,

 DEPTNO NUMBER);

 

 Objecty will give the ability to hold the multiple attributes like ename,empno,deptno.

 

 2. Create a nested table:

 CREATE OR REPLACE TYPE EMP_TBL_TYP

 IS TABLE OF EMP_OBJ_TYP;

 

 

 Nested table will be perfect to srore the fetched data, as nested table is made fform object type, so it will hold multiple columns datain a single key value pair.

 

 3. Create a function:

 CREATE OR REPLACE FUNCTION RETURN_MULTIPLE_VAL_ROW(V_DEPTNO NUMBER)

 RETURN EMP_TBL_TYP

 AS

 EMP_DATA EMP_TBL_TYP:=EMP_TBL_TYP();

 BEGIN

 SELECT EMP_OBJ_TYP(ENAME,EMPNO,DEPTNO) 

 BULK COLLECT 

 INTO EMP_DATA 

 FROM EMP WHERE EMPNO=V_DEPTNO;

 

 RETURN EMP_DATA;

 END;

 

 4. Call the function

  SELECT * FROM TABLE (RETURN_MULTIPLE_VAL_ROW(20));

Temporary Tablespace

A temporary tablespace contains transient data that persists only for the duration of the session. Temporary tablespaces can improve the concurrency of multiple sort operations that do not fit in memory and can improve the efficiency of space management operations during sorts.

Recreate Temporary Tablespace:

  1. Login to oracle database as # sqlplus / as sysdba
  2. Find the temporary tablespace path by using below command

        SQL > select * from v$tempfile;

 3. Create new temporary table space by passing the path and proper file name.
      SQL > create temporary tablespace TEMP2 tempfile '/home/oracle/app/oracle/oradata/db11gr2/temp02.dbf' size 100M reuse autoextend on;

 4. Now make it as default temporary tablespace.
      SQL > alter database default temporary tablespace TEMP2;
  
 5. Now restart DB

 6. Drop old temporary tablespace by passing the path and proper file name.

SQL > alter database tempfile '/home/oracle/app/oracle/oradata/db11gr2/temp01.dbf' drop including datafiles;

Check Free Space,Used Space,Total Space,Instance Name and Host Name by running below query.

select tablespace_name,(free_blocks*8)/1024/1024 "Free Space in GB",(used_blocks*8)/1024/1024 "Used Space in GB",
(total_blocks*8)/1024/1024 "Total Space GB",
t.instance_name,
t.host_name 
from gv$sort_segment ss,gv$instance t 
where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY') and t.inst_id=ss.inst_id;

OR run the below query

select (select sum(Bytes)/1024/1024/1024 from dba_temp_files) "TOTAL SPACE in GB",(select decode(sum(blocks),null,0,sum(blocks)*8192/1024/1024/1024) from v$sort_usage) "CURRENT USAGE in GB",
(select count(*) from v$sort_usage where blocks>(100000000/8192))"SESSIONS USING > 1000MB" 
from dual;

Long Vs Lob Data Type

What is difference between LONG and LOB data types?

Differences between LONG and LOB data type are listed below:

---

LOB

 1) The maximum size is 4GB.

 2) LOBs (except NCLOB) can be attributes of an object type.

 3) LOBs support random access to data.

 4) Multiple LOB columns per table or LOB attributes in an object type.

LONG

 1) The maximum size is 2GB.

 2) LONGs cannot.

 3) LONGs support only sequential access.

 4) Only one LONG column was allowed in a table

Truncate Vs Delete

What is the difference between truncate and delete?

1.  Truncate deletes all records at once unconditionally, whereas delete can delete the records conditionally or unconditionally.
2. Deleted data by truncate command can not be rolled back, whereas deleted data by delete command can be rolled back.
3. Memory will be released after deletion of record by truncate command whereas memory will not be released after deletion of record by deleted command.
4. Truncate is a DDL command, whereas delete is DML command.
5. Trigger does not get fired in case of  TRUNCATE whereas triggers get fired in case of a DELETE command.
6. We can not TRUNCATE a table if table have any foreign key constraint. We need to remove the constraint  then truncate the table and recreate the constraint.

Relational Vs Nested Table

What is the difference between relational table and nested table?

• A relational table can have virtual column, whereas nested table can not have virtual column.
• Primary key, foreign keys are allowed on a relational table, whereas constraints are not allowed on nested table.

ORA-06548: no more rows needed

ERROR: ORA-06548: no more rows needed

CAUSE: The caller of a pipelined function does not  need more rows to be produced by the pipelined function.

ACTION: Catch the NO_DATA_NEEDED exception in an exception handling block.
Catching the NO_DATA_NEEDED exception inside the pipelined function allows the function to perform any clean-up needed after the loop.

Example:
The table function returns 1000 rows, but the client (which communicates using ODBC) only fetches 200 at a time. If all the rows are fetched,
then there is no issue; however, if only a subset are fetched before another command is executed, the exception gets raised.

DECLARE

  --...

BEGIN

 ....

EXCEPTION

WHEN no_data_needed
THEN
RETURN;   --clean up the resource

END;

/

ORA-01008 : Not all variables bound

ERROR: ORA-01008 : Not all variables bound

CAUSE : A SQL statement (or Dynamic SQL ) containing substitution variables was executed without all variables bound.

ACTION: All substitution variables must have a substituted value before the SQL statement is executed.

what to look for to help performance

Identify the solution for what problem by looking the TKPROF.

If parsing numbers are high: The SHARED_POOL_SIZE may need to be increased.

If Disk reads are very high: Indexes are not being used or may not exist

If the QUERY or CURRENT memory reads are very high: Indexes may be on columns with low cardinality (columns where an individual value generally makes up a large percentage of the table; like a y/n field). Removing/suppressing the index or using histograms or a bitmap index may increase performance. A poor join order of tables or bad order in a concatenated index may also cause this.

If parse elapse time is high: There may be a problem with the number of open cursors.

If number of rows processed by a row in the EXPLAIN PLAN is high compared to the other rows: This could be a sign of an index with a poor distribution of distinct keys (unique values for a column). This could also be a sign of a poorly written statement.

If the number of rows processed by a row in the EXPLAIN PLAN is high compared to the other rows: This indicates that the statement had to be reloaded. You may need to increase the SHARED_POOL_SIZE in the init.ora file or do a better job of sharing SQL.

Details Of TRACE and TKPROF Output

SQL TRACE has multiple sections including SQL Statements, Statistics, information and EXPLAIN PLAN.

1. SQL Statements: The first part of a TKPROF statement is the SQL  Statement . This statement will be exactly the same as the statement that was executed. If there were any hints or comments in the statement, they would be retained in this output.

2. Statistics:  It has all the Statistics for this SQL Statements. It has eight columns 

• call Statistics for each cursor's activity are divided in to 3 areas: Parse,Execute and Fetch. A total is also calculated.

      Parse: statistics from parsing the cursor. This includes information for plan generation etc.

Execute: statistics for the execution phase of a cursor

Fetch: statistics for actually fetching the rows

• Count number of times each individual activity has been performed on this particular CALL.
• CPU time used by this CALL.
• ELAPSED time for this CALL(includes the CPU time).
• DISK this indicates the number of blocks read from disk. Generally it would be preferable for blocks to be read from the buffer cache rather than disk.
• QUERY the total number of data buffers retrieved from memory for this type of call. SELECT statements usually retrieve buffers in this mode. This is the number of consistent gets.
• CURRENT the total number of data buffers retrieved from memory for this type of call. UPDATE, INSERT, or DELETE usually access buffers in this mode, although SELECT statements may use a small number of buffers in this mode also. This is the number of db block gets.
• ROWS the total number of rows processed by this statement. The rows processed for SELECT statements will appear in the row of Fetch statistics. Inserts, updates, and deletes will appear in the Execute row.

3. Information: It contains information about the number of library cache misses from parse and execute calls. If the number of misses is high, there may be a problem with the size of the shared pool. It also contains the username of  the last user to parse this statement.   

4. EXPLAIN PLAN : This most useful section of  the TKPROF. The first column of this section is the number of rows processed by each line of the execution plan. We can identify how slow a statement is. If the total number of rows in the fetch statistics is low compared to the number of rows being processed by each line of the EXPLAIN PLAN.