Blog - Our findings and learning

You no need to try to learn something. With the experience of trying, we learn what works and how it works. We share the same with you to learn from our experience.

ORA-12154: TNS: could not resolve the connect identifier specified

WhatDatabase link is an option to connect to a remote database from another database. By using this database link option one can connect to remote database as authorized database user and can perform the operations as in the direct connection. Database link is of two types Public Private HowAdd a entry in tnsnames.ora file to

Read More »

get tables and their number of rows in Oracle Database Schema

Simplest way is to use the data dictionaries provided by Oracle. And you can depend on the results if the schema is up to date with the statistics. Use the following query to get the table list and their number of rows (records). SELECT table_name, num_rows FROM user_tables; OR SELECT table_name, num_rows FROM all_tables WHERE

Read More »

expdp : Exporting datadump in Oracle 11g

The export can be done in two ways from local machine from Server From Local MachineFor Windows operating system Follow the steps# create directorycreate directory dmpdir as ‘c:/temp’;select directory_name,directory_path from dba_directories; # expdp ownercreate user scott identified by tiger;grant connect, resource to scott; grant read, write on directory dmpdir to scott;expdp scott/tiger@xe directory=dmpdir dumpfile=scott.dmp logfile=expdp.log

Read More »

OLAP vs OLTP vs Datawarehouse Database

There is nothing about the database itself that “makes” it an OLAP, or a DW, or an OLTP.That is all a matter of how it is used. Kind of like trying to tell if the Honda Accord parked in my driveway is a family sedan or a pizza delivery vehicle. In fact very few databases

Read More »

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE in PLSQL

In Big/complex applications, it is essential to call Sub-programs (Procedures/Functions) from other sub-programs. In that case it will be difficult to trace out Errors/Exceptions from where it is raised i.e. which sub-program caused the exception. Normally we can use SQLCODE and SQLERRM to get the error/exception, but these may not work in the above case.

Read More »

Histograms in Oracle for Table Columns

Histogram:A histogram is a special type of column statistic that provides more detailed information about the data distribution in a table column. Purpose of Histograms:By default the optimizer assumes a uniform distribution of rows across the distinct values in a column. For columns that contain data skew (a nonuniform distribution of data within the column),

Read More »