[Oracle 12c] Find Default and TemporaryTablespaces and size of Database Schemas

This is an example on how to find the default and temporary table space details of a particular schema. This comes in handy when debugging tablespace exceeded errors like ORA-01536 and ORA-00059. This types of errors are often common in data warehouses full base load scenarios.

To find the Default Tablespace and Temporary Tablespace of a particular database schema, you need to have an user with DBA/SYS privileges. After you have logged in with this particular user, run the following SQL query,

SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME IN('Schema Name Here')

Temporary Tablespace Size

Next to find the size of the temporary tablespace, To find the size of the temporary tablespace we need to query the system table DBA_TEMP_FILES, Find a sample query below

SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 TABLESPACE_SIZE_MB
FROM DBA_TEMP_FILES
WHERE TABLESPACE_NAME IN ('Schema Name Here')
GROUP BY TABLESPACE_NAME

Temporary Tablespace Size.

Default Tablespace Size

To find Default Tablespace size you need to query the system table DBA_DATA_FILES. See a sample query syntax below, 

SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 TABLESPACE_SIZE_MB
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME IN ('EXAMPLE')
GROUP BY TABLESPACE_NAME

Default Tablespace Size.

Data warehouse basics: What is Incremental Data Load ?

So we are back again to brush the basics. I have always felt that for a BI/DW developer to become successful he/she should have a very strong base about the basics. Here is one basic concept we hear often, Incremental load.

So what exactly is Incremental data load ?

Incremental data load is a type of data load scenario in a Data warehouse environment where the data that is updated/new are inserted to the target data warehouse with each scheduled run.

Imagine a sales person table as below which will be our source table in this case.

On the first day of loading to an EDW table, all the above three records would be moved.  The target table would be same as above. 

Now a day after they have added two new employees and also made a change to first name of a previous employee. Refer the screenshot below, 

Here with incremental update the two new records are considered and written to the target table. Also checks the source if the already existing records in EDW has any changes in source. There is the first record where FST_NAME was changed from Steve to Steven, hence this record is also considered and the change is updated in the target table. 

The advantages of incremental loads are the following,

1. Reduces a major chunk I/O operations between source and target. 
2. Faster than a full base load.

Find Indexes created on Oracle Database Schema/Table

Hey everyone, here is a small tip on finding and managing Oracle indexes in a database or schema. Below are a set of queries that can be used for the same. Note that you need to have SYS/DBA privilege on the database to perform the same. This is intended for people who are new to Oracle databases. 

All information related to indexes on an oracle database are stored in the DBA_INDEXES table that belongs to SYS schema. Below are a few common scenarios when related to querying from this table. You may replace the predicates accordingly. 

Find all indexes defined on a database,

SELECT * FROM DBA_INDEXES

DBA_INDEXES table in Oracle 12c. 

Find all indexes on a particular schema, 

SELECT * FROM DBA_INDEXES WHERE OWNER IN ('HR')

Find Indexes on Schema.

Find the indexes defined on a specific table, 

SELECT * FROM DBA_INDEXES WHERE OWNER IN ('HR') AND TABLE_NAME IN ('EMPLOYEES')

Indexes defined on a table.

Find Unique indexes in a database,

SELECT * FROM DBA_INDEXES WHERE UNIQUENESS IN ('UNIQUE')

Find number of indexes defined on a database schema by index type,

SELECT OWNER, INDEX_TYPE, COUNT(*) INDEX_CNT FROM DBA_INDEXES GROUP BY OWNER, INDEX_TYPE

Find the number of Indexes on a particular table,

SELECT OWNER, TABLE_NAME, INDEX_TYPE, COUNT(*) INDEX_CNT FROM DBA_INDEXES
WHERE OWNER IN ('HR') 
AND TABLE_NAME IN ('EMPLOYEES')
GROUP BY 
OWNER, 
TABLE_NAME,
INDEX_TYPE

Indexing Strategy in DW: What are Indexes, Types and Significance ?

We are starting off a new series on Indexing strategies in an Oracle Data warehouse. Before going into detail let us first see what an Oracle Index is. 

A database index is a database object that is created to optimize the performance of a read/select statement on a particular table. They greatly reduce the I/O operations on a table by reducing the rows processed. 

Oracle defines Indexes as below, 

An index is an optional structure, associated with a table or table cluster, that can sometimes speed data access. By creating an index on one or more columns of a table, you gain the ability in some cases to retrieve a small set of randomly distributed rows from the table. Indexes are one of many means of reducing disk I/O.

'Indexing' is a term coined for the strategy used for indexing in a particular relational DBMS. Let us see the advantages of indexing

1. Avoid full table scans and there by reducing the cost and time to retrieve from a database table.
2. They avoid sorting of oracle queries. which in turn reduces the time for retrieval of data. 

There are different kinds of Indexes available in Oracle and they are the following, 

• B-Tree or Normal Indexes
• Bitmap indexes
• Function based indexes

B-Tree Indexes are the most common indexes in oracle database. by default a B-Tree index is generated. Here values are stored as leafs. There are branch blocks and leaf blocks. Branch blocks contain the leaf blocks where actual pointer values are stored. Here one entry in an index points to one row.

Bitmap indexes are usually created for low carnality columns like Gender, flags etc. In Bitmap indexes one index entry points to multiple rows. 

Function based indexes are indexes created on columns with a function. If one was to apply calculations on a particular column then function based indexes are created on that particular column. 

OBIEE Analysis View Display Error Maximum cells exceeded Error EY692ZW9 [Solved]

Issue: When trying to export a OBIEE Analysis to 

View Display Error Maximum total number of cells exceeded (Configured Limit: 50000). Error Codes: EY692ZW9

Cause: Max rows parameter and tag not set at instanceconfig.xml file.

Solution: In the instance config file at 

/user/app/oracle/product/fmw/12.2.1/user_projects/domains/bi/config/fmwconfig/biconfig/OBIPS add a sub tag under <pivot> and <table> tags.

Set this parameter accroding to your requirement. We wanted to take some very big excel files and hence set the same as below,

<MaxCells>Value</MaxCells>

For the same to get effected you must once restart the BI Presentation service from Enterprise Manager. 

What are Columnar Databases and it's significance in Analytics/Data warehousing ?

I was recently watching a session on Amazon Redshift from Youtube, when I came to know that Redshift is a columnar database. Which Amazon boasted optimizes performance and storage to a great deal. This ended up in me wanting to know more and hence the post.

So what is a columnar database ?

Typical databases like Oracle, mySQL etc store information as rows, i.e for example the details of a particular students is stored in a row.  In columnar databases the information is stored as columns. Data in a particular column is grouped together unlike other databases where row wise data is grouped. 

Say for example I have the following raw data, 
Name: Athul
Age: 26
Sex: Male 
Location: Trivandrum

Name: Rahul
Age: 43
Sex: Male
Location Bangalore

In typical databases the data would be stored as below, 

Athul26MaleTrivandrum
Rahul43MaleBangalore

However when it comes to a columnar database, data is grouped in Columnar fashion, i.e

AthulRahul|2643|MaleMale|TrivandrumBangalore

Each column data here is stored in a separate blocks. The advantage they say is that data of same datatype are grouped together and hence storage and retrieval is easier. This makes it great for Analytical data storage. However operations like insert and update are costlier. Aggregations again are great because of this columnar structure. Data compression also is greatly increased in columnar architecture.  This being the case it is very advisable to consider columnar databases if you are dealing with huge volumes of data. Another advantage is that your databases need parse whole rows to read data as most queries are limited to a subset of the table's actual number of columns. 

Examples of Columnar databases are Apache HBase, Amazon Redshift etc. Below is a good youtube video explanation for columnar database.

   Columnar Databases

courtesy: StackOverflow