Sunday, November 27, 2011

MySQL tools

  • Percona Toolkit(Maatkit)

Percona Toolkit is a collection of advanced command-line tools used by Percona support staff to perform a variety of MySQL and system tasks that are too difficult or complex to perform manually

http://www.percona.com/software/percona-toolkit/

linux/unix tutorials

Using rsync over ssh
http://oreilly.com/pub/h/38
http://kevin.vanzonneveld.net/techblog/article/synchronize_files_with_rsync/

rsync - exclude files and folders
http://articles.slicehost.com/2007/10/10/rsync-exclude-files-and-folders

Installing ssh and rsync on a Windows machine
http://optics.ph.unimelb.edu.au/help/rsync/rsync_pc1.html

How To scp, ssh and rsync without prompting for password
http://blogs.oracle.com/jkini/entry/how_to_scp_scp_and


Crontab – Quick Reference
http://adminschoice.com/crontab-quick-reference

Quick Logins with ssh Client Keys
http://oreilly.com/pub/h/66

Linux Users and Sudo
http://www.linuxhomenetworking.com/wiki/index.php/Quick_HOWTO_:_Ch09_:_Linux_Users_and_Sudo

Bash scripting Tutorial
http://linuxconfig.org/Bash_scripting_Tutorial

Bourne Shell Reference
http://linuxreviews.org/beginner/bash_GNU_Bourne-Again_SHell_Reference/



Linux User Management commands
http://www.comptechdoc.org/os/linux/commands/linux_cruserman.html

How do I create create a new Linux user account ?
http://www.cyberciti.biz/faq/howto-add-new-linux-user-account/



  • check if a mail server supports SMTP-TLS



$ openssl s_client -connect mail.example.com:25 -starttls smtp

openssl s_client -connect 193.140.13.215:25 -starttls smtp

openssl s_client   -starttls smtp -connect 193.140.13.215:25 -debug


IP v6 telnet
telnet -6 193.140.13.215 25

IP v4 telnet
telnet -4 193.140.13.215 25


find out which SMTP server(s) is responsible for the domain
nslookup -type=mx example.local
nslookup -type=mx mail.google.com

dig example.local mx
dig mail.google.com mx

Verify connectivity to the SMTP server
nc 193.140.13.215 25

telnet vsp1.example.local 25
telnet 193.140.13.215 25


https://halon.io/blog/how-to-test-smtp-servers-using-the-command-line


  • How internet security works: TLS, SSL, and CA

By default, a website is not secure if it uses the HTTP protocol.
If you see a warning sign, which is rare for most publicly facing websites, it usually means that the certificate is expired or uses a self-signed certificate instead of one issued through a trusted CA.
Internet protocols with TLS and SSL
TLS is the current generation of the old Secure Socket Layer (SSL) protocol.
There are six layers that make up the internet as we know it today: physical, data, network, transport, security, and application. The physical layer is the base foundation, and it is closest to the actual hardware. The application layer is the most abstract layer and the one closest to the end user. The security layer can be considered a part of the application layer, and TLS and SSL, which are the cryptographic protocols designed to provide communications security over a computer network, are in the security layer.
Certificate authorities and self-signed certificates
A CA is a trusted organization that can issue a digital certificate.
TLS uses a mechanism called asymmetric encryption, which is a pair of security keys called a private key and a public key
The essential thing to know is that CAs, like GlobalSign, DigiCert, and GoDaddy, are the external trusted vendors that issue certificates that are used to validate the TLS/SSL certificate used by the website.
This certificate is imported to the hosted server to secure the website.
developers and website administrators need a simpler way to test websites before they're deployed to production; this is where self-signed certificates come in.
A self-signed certificate is a TLS/SSL certificate that is signed by the person who creates it rather than a trusted CA.

Open source tools for generating certificates
https://opensource.com/article/19/11/internet-security-tls-ssl-certificate-authority?sc_cid=70160000001273HAAQ


  • Welcome to OpenSSL!

OpenSSL is a robust, commercial-grade, and full-featured toolkit for the Transport Layer Security (TLS) and Secure Sockets Layer (SSL) protocols. It is also a general-purpose cryptography library
https://www.openssl.org/

  • easy-rsa is a CLI utility to build and manage a PKI CA. In laymen's terms, this means to create a root certificate authority, and request and sign certificates, including sub-CAs and certificate revocation lists (CRL).

https://github.com/OpenVPN/easy-rsa

  • CFSSL

CloudFlare's PKI/TLS toolkit
https://github.com/cloudflare/cfssl

  • Lemur

Lemur manages TLS certificate creation. While not able to issue certificates itself, Lemur acts as a broker between CAs and environments providing a central portal for developers to issue TLS certificates with 'sane' defaults
https://github.com/Netflix/lemur

MySQL tutorials

  • Random row selection
SELECT law
FROM murphy
ORDER BY RAND()
LIMIT 1;


http://www.artfulsoftware.com/infotree/queries.php?bw=1680

  • MySQL Tutorial
http://www.tutorialspoint.com/mysql/index.htm

Thursday, November 24, 2011

datawarehouse interview questions

  • What is Data Warehousing?
A data warehouse is the main repository of an organization’s historical data, its corporate memory. It contains the raw material for management’s decision support system. The critical factor leading to the use of a data warehouse is that a data analyst can perform complex queries and analysis, such as data mining

A data warehouse is a electronical storage of an Organization's historical data for the purpose of analysis and reporting


  • What are fundamental stages of Data Warehousing?
Offline Operational Databases – Data warehouses in this initial stage are developed by simply copying the database of an operational system to an off-line server where the processing load of reporting does not impact on the operational system’s performance.
Offline Data Warehouse – Data warehouses in this stage of evolution are updated on a regular time cycle (usually daily, weekly or monthly) from the operational systems and the data is stored in an integrated reporting-oriented data structure.
Real Time Data Warehouse – Data warehouses at this stage are updated on a transaction or event basis, every time an operational system performs a transaction (e.g. an order or a delivery or a booking etc.)
Integrated Data Warehouse – Data warehouses at this stage are used to generate activity or transactions that are passed back into the operational systems for use in the daily activity of the organization.

  • What is the difference between OLTP and OLAP?
OLTP is the transaction system that collects business data. Whereas OLAP is the reporting and analysis system on that data.
OLTP systems are optimized for INSERT, UPDATE operations and therefore highly normalized. On the other hand, OLAP systems are deliberately denormalized for fast data retrieval through SELECT operations


OLTP is abbreviation of On-Line Transaction Processing. This system is an application that modifies data the instance it receives and has a large number of concurrent users
OLAP is abbreviation of Online Analytical Processing. This system is an application that collects, manages, processes and presents multidimensional data for analysis and management purposes
Data Source
OLTP: Operational data is from original data source of the data
OLAP: Consolidation data is from various source.

Process Goal
OLTP: Snapshot of business processes which does fundamental business tasks
OLAP: Multi-dimensional views of business activities of planning and decision making

Queries and Process Scripts
OLTP: Simple quick running queries run by users.
OLAP: Complex long running queries by system to update the aggregated data.

Database Design
OLTP: Normalized small database.This adopts entity relationship(ER) model and an application-oriented database design.
OLAP: De-normalized large database. This adopts star, snowflake or fact constellation mode of subject-oriented database design.

OLTP vs. OLAP
We can divide IT systems into transactional (OLTP) and analytical (OLAP).
In general we can assume that OLTP systems provide source data to data warehouses, whereas OLAP systems help to analyze it.

OLTP (On-line Transaction Processing) is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE).
number of transactions per second is an effectiveness measure

OLAP (On-line Analytical Processing) is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations
a response time is an effectiveness measure
http://datawarehouse4u.info/OLTP-vs-OLAP.html



Key Differences Between OLTP and OLAP

    The point that distinguishes OLTP and OLAP is that OLTP is an online transaction system whereas, OLAP is an online data retrieval and analysis system.
    Online transactional data becomes the source of data for OLTP. However, the different OLTPs database becomes the source of data for OLAP.
    OLTP’s main operations are insert, update and delete whereas, OLAP’s main operation is to extract multi dimensional data for analysis.
    OLTP has short but frequent transactions whereas, OLAP has long and less frequent transaction.
    Processing time for the OLAP’s transaction is more as compared to OLTP.
    OLAPs queries are more complex with respect OLTPs.
    The tables in OLTP database must be normalized (3NF) whereas, the tables in OLAP database may not be normalized.
    As OLTPs frequently executes transactions in database, in case any transaction fails in middle it may harm data’s integrity and hence it must take care of data integrity. While in OLAP the transaction is less frequent hence, it does not bother much about data integrity.


https://techdifferences.com/difference-between-oltp-and-olap.html
  • What is Data Mining?
Generally, data mining (sometimes called data or knowledge discovery) is the process of analyzing data from different perspectives and summarizing it into useful information - information that can be used to increase revenue, cuts costs, or both. Data mining software is one of a number of analytical tools for analyzing data. It allows users to analyze data from many different dimensions or angles, categorize it, and summarize the relationships identified. Technically, data mining is the process of finding correlations or patterns among dozens of fields in large relational databases.
http://www.anderson.ucla.edu/faculty/jason.frand/teacher/technologies/palace/datamining.htm
  1. What is data mart?

Data marts are generally designed for a single subject area. An organization may have data pertaining to different departments like Finance, HR, Marketting etc. stored in data warehouse and each department may have separate data marts. These data marts can be built on top of the data warehouse

  • What is ER model?

ER model is entity-relationship model which is designed with a goal of normalizing the data.


  • What is Dimensional Modeling?
Dimensional data model concept involves two types of tables and it is different from the 3rd normal form. This concepts uses Facts table which contains the measurements of the business and Dimension table which contains the context(dimension of calculation) of the measurements.

  • What is Fact table?
Fact table contains measurements of business processes also fact table contains the foreign keys for the dimension tables. For example, if your business process is paper production” then “average production of paper by one machine” or “weekly production of paper” would be considered as measurement of business process.

  • What is Dimension table?
Dimensional table contains textual attributes of measurements stored in the facts tables. Dimensional table is a collection of hierarchies, categories and logic which can be used for user to traverse in hierarchy nodes

  • What is the difference between etl tool and olap tools?

ETL tools are used to extract transformation and loading the data into data warehouse / data mart
OLAP tools are used to create cubes/reports for business analysis from data warehouse / data mart


  • What is the difference between the terms "business intelligence" and "data warehousing"?


So data warehousing is the foundation that business intelligence is built upon.

People were performing data warehousing (DW) before it had a name.
The term "data warehousing" stemmed from the terms "decision support" and "management reporting" many years ago.
Business intelligence (BI) sought to encapsulate more processes that included data warehousing.
If you notice, many vendors use the term BI to describe their services; to show that they provide more services than just data warehousing.

http://www.information-management.com/news/7260-1.html




Tuesday, November 22, 2011

core java interview questions

  • upcasting vs downcasting

Java permits an object of a subclass type to be treated as an object of any superclass type. This is called upcasting.Upcasting is done automatically

downcasting must be manually

  • What is a JVM?

JVM is Java Virtual Machine which is a run time environment for the compiled java class files.

  • What is a pointer and does Java support pointers?

Pointer is a reference handle to a memory location. Improper handling of pointers leads to memory leaks and reliability issues hence Java doesn't support the usage of pointers.

  • What is the base class of all classes?
java.lang.Object

  • Are arrays primitive data types?
In Java, Arrays are objects.

  • What is difference between Path and Classpath?
Path and Classpath are operating system level environment variales.
Path is used define where the system can find the executables(.exe) files and classpath is used to specify the location .class files.



  • What are local variables?
Local varaiables are those which are declared within a block of code like methods.
Local variables should be initialised before accessing them

  • What are instance variables?
Instance variables are those which are defined at the class level. Instance variables need not be initialized before using them as they are automatically initialized to their default values


  • How to define a constant variable in Java?
The variable should be declared as static and final.
So only one copy of the variable exists for all instances of the class and the value can't be changed also.
static final int PI = 2.14; is an example for constant

  • What is the return type of the main() method?
Main() method doesn't return anything hence declared void.

  • What is the arguement of main() method?
main() method accepts an array of String object as arguement.


  • Can a main() method be overloaded?
Yes. You can have any number of main() methods with different method signature and implementation in the class.

  • Does the order of public and static declaration matter in main() method?
No. It doesn't matter but void should always come before main().

  • Can a source file contain more than one class declaration?
Yes a single source file can contain any number of Class declarations but only one of the class can be declared as public.

  • What is a package?
Package is a collection of related classes and interfaces.
package declaration should be first statement in a java class.

  • Which package is imported by default?
java.lang package is imported by default even without a package declaration.



  • Can a class be declared as protected?
A class can't be declared as protected. only methods can be declared as protected.

  • What is the access scope of a protected method?
A protected method can be accessed by the classes within the same package or by the subclasses of the class in any package.

  • What is the purpose of declaring a variable as final?
A final variable's value can't be changed. final variables should be initialized before using them

  • What is the impact of declaring a method as final?
A method declared as final can't be overridden. A sub-class can't have the same method signature with a different implementation.

  • I don't want my class to be inherited by any other class. What should i do?
You should declare your class as final.
But you can't define your class as final, if it is an abstract class final class can't be inherited, final method can't be overridden and final variable can't be changed.

  • Can a class be declared as static?
No a class cannot be defined as static. Only a method, a variable or a block of code can be declared as static

  • When do you define a method as static?
When a method needs to be accessed even before the creation of the object of the class then we should declare the method as static.

  • What is the importance of static variable?
static variables are class level variables where all objects of the class refer to the same variable.
 If one object changes the value then the change gets reflected in all the objects

  • Can we declare a static variable inside a method?
Static varaibles are class level variables and they can't be declared inside a method.
If declared, the class will not compile.

  • Can a abstract class be declared final?
Not possible. An abstract class without being inherited is of no use and hence will result in compile time error.

  • Can you create an object of an abstract class?
Not possible. Abstract classes can't be instantiated.

  • Class C implements Interface I containing method m1 and m2 declarations. Class C has provided implementation for method m2. Can i create an object of Class C?
No not possible. Class C should provide implementation for all the methods in the Interface I.
Since Class C didn't provide implementation for m1 method, it has to be declared as abstract. Abstract classes can't be instantiated.


  • Can a method inside a Interface be declared as final?
No not possible. Doing so will result in compilation error.
public and abstract are the only applicable modifiers for method declaration in an interface.

  • Which class is extended by all other classes?
The Object class is extended by all other classes.

  • What is casting?
There are two types of casting, casting between primitive numeric types and casting between object references.
Casting between numeric types is used to convert larger values, such as double values, to smaller values, such as byte values.
Casting between object references is used to refer to an object by a compatible class, interface, or array type reference.

  • What do you understand by private, protected and public?
These are accessibility modifiers.
Private is the most restrictive, while public is the least restrictive.
There is no real difference between protected and the default type (also known as package protected) within the context of the same package, however the protected keyword allows visibility to a derived class in a different package.

  • What is a native method?
A native method is a method that is implemented in a language other than Java.

  • Is null a keyword?
The null value is not a keyword.


  • If a class is declared without any access modifiers, where may the class be accessed?

A class that is declared without any access modifiers is said to have package access. This means that the class can only be accessed by other classes and interfaces that are defined within the same package

  • Are true and false keywords?
The values true and false are not keywords.


  • What is the diffrence between inner class and nested class?
When a class is defined within a scope od another class, then it becomes inner class. If the access modifier of the inner class is static, then it becomes nested class.


  • What is the difference between a public and a non-public class?
A public class may be accessed outside of its package. A non-public class may not be accessed outside of its package

  • What is a Java package and how is it used?
A Java package is a naming context for classes and interfaces


  • What is the difference between method overriding and overloading?
Overriding is a method with the same name and arguments as in a parent, whereas overloading is the same method name but different arguments


  • How are this() and super() used with constructors?

this() is used to invoke a constructor of the same class.
super() is used to invoke a superclass constructor

  • What is a transient variable?
Transient variable is a variable that may not be serialized.

  • What is the difference between swing and Awt?
AWT are heavy-weight componenets. Swings are light-weight components. Thus, swing is faster than AWT.

  • What are pass by reference and pass by value?
Pass by reference means passing the address itself rather than passing the value.
On the other hand, pass by value means passing a copy of the value to be passed

  • What is the difference between final, finally and finalize?
final – declare constant
finally – handles exception
finalize – helps in garbage collection

  • Name primitive Java types.
The 8 primitive types are byte, char, short, int, long, float, double, and boolean

  • What is super?

super is a keyword which is used to access the method or member variables from the superclass.
If a method hides one of the member variables in its superclass, the method can refer to the hidden variable through the use of the super keyword


  • What is final modifier?

The final modifier keyword makes that the programmer cannot change the value anymore.
The actual meaning depends on whether it is applied to a class, a variable, or a method.

final Classes- A final class cannot have subclasses.
final Variables- A final variable cannot be changed once it is initialized.
final Methods- A final method cannot be overridden by subclasses


  • What is the difference between static and non-static variables?
A static variable is associated with the class as a whole rather than with specific instances of a class. Non-static variables take on unique values with each object instance.


http://www.interview-questions-java.com/
http://forum.codecall.net/java-tutorials/20719-upcasting-downcasting.html
http://javarevisited.blogspot.com/2011/04/top-20-core-java-interview-questions.html
http://technical-interview.com/Java_Questions_1.aspx


  • What is JNDI for?
JNDI (Java Naming and Directory Interface) enables Java platform-based applications to access multiple naming and directory services.
directory services such as Lightweight Directory Access Protocol (LDAP),
distributed object systems such as the Common Object Request Broker Architecture (CORBA), Java Remote Method Invocation (RMI), and Enterprise JavaBeans (EJB).



  • Classpath

Classpath is a parameter—set either on the command-line, or through an environment variable—that tells the Java Virtual Machine or the Java compiler where to look for user-defined classes and packages.
https://en.wikipedia.org/wiki/Classpath_%28Java%29




  • An application with WEB-INF folder is a web application. Meaning it will be packed as a WAR file and deployed on a server


If you are using an IDE like eclipse, and you export the project as a WAR file, it will automatically take jars under the lib folder and pack them with the WAR and that will make them available for the application running on the server (jars under WEB-INF/lib are included in the application classpath).


If you just put them anywhere else and include them in the build path, when you export the project you have to declare that you want the jars in the build path to be included as well.


Basically, there is no big difference but if you know you need this jar in runtime (i.e. after deploying the application to the server), it is better to put it under the WEB-INF/lib folder.




What do you mean by build path?
Most of the JARs you use need to be available during building (compiling) because your code depends on them - otherwise it won't compile.


The real question is between placing JARs in WEB-INF/lib vs. your container /lib directory
In general you should always put your JARs inside an application in WEB-INF/lib


Placing them globally has several consequences:
singletons are now global to all web applications (one class loader) if multiple deployed
it's easier to introduce memory leak if a library holds a reference to any of your classes (see above)
you don't duplicate the same classes (each web application will reuse the same class as opposed to having a separate copy in each class loader
http://stackoverflow.com/questions/13679112/java-configuring-build-paths-or-web-inf-lib-folder




  • By default, Tomcat container doesn’t contain any jstl library. To fix it, declares jstl.jar in your Maven pom.xml file if you are working in Maven project or add it to your application's classpath


<dependency>
    <groupId>javax.servlet</groupId>
    <artifactId>jstl</artifactId>
    <version>1.2</version>
  </dependency>
http://stackoverflow.com/questions/15113628/java-lang-classnotfoundexception-javax-servlet-jsp-jstl-core-config

Monday, November 21, 2011

oracle interview questions

  • what is   materialized view?
A materialized view is a database object that stores the results of a query (possibly from a remote database). Materialized views are sometimes referred to as snapshots.
Materialized views are disk based and update periodically base upon the query definition.
Views are virtual only and run the query definition each time they are accessed

http://orafaq.com/wiki/Oracle_Materialized_Views
http://stackoverflow.com/questions/93539/what-is-the-difference-between-views-and-materialized-views-in-oracle

  • What are materialized views & where do we use them?

A view which has been materialized to become a "table"
The big difference between a View and a Materialized View is a View executes a query on-the-fly toreturn results.
A Materialized View executes a query but then stores (or caches) the results.
You can then query the MVIEW.

References:
http://www.dwbiconcepts.com/tutorial/10-interview/6-top-50-dwbi-interview-questions-with-answers.html
http://blog.sqlauthority.com/2007/07/26/sql-server-data-warehousing-interview-questions-and-answers-part-1/

  • what is rowid?

For each row in the database, the ROWID pseudocolumn returns the address of the row. Oracle Database rowid values contain information necessary to locate a row:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns008.htm

ROWID is a pseudo column attached to each row of a table.

It is 18 characters long, blockno, rownumber are the components of ROWID


Questions:
How do I limit the number of rows returned by a query?
How do I write a query to get the Top-N salaries from the employee table?
How can I add unique, sequential numbers to an existing table?
How can I differentiate between two completely identical rows?
How can I find a faster way to retrieve a queried row?
How can I find the last row processed in a big batch?

listing highest paid employees

select first_name,last_name,salary
from employee2
where rownum<4
order by salary desc;


ROWID actually represents the physical location of the record/row in the database.
The fastest way to retrieve a particular row. Faster than an index

Can you use ROWID to differentiate between duplicate rows?
Yes, you can
Since it actually represents the physical location of a row, no two rows within the same table will have the same ROWID.
If you're using clustering, two records from different tables could theoretically share the same ROWID.


http://thinkoracle.blogspot.com/2005/10/rownum-and-rowid.html

  • What is the difference between Instance and a Database?


Instance is the memory structures and background processes that are used to interact with Database.
Instance = SGA + Background Process

Database is the physical files that are used to store information.
3 types of physical files are:
Datafiles,
Control File
Redo-Log fileS(minimum 2).



  • What is stucture of Database ?
Oracle database usually contains one database and a single instance.
Oracle 9i, 10g RAC (Real Application Clusters) can have multiple instances to interact with a single database for high availability.

Instance is non-persistent, memory based background processes and structures.
Database is persistent, disk based, data and control files


Physical Structure of Database:
One or more datafiles, control file(s), Redolog file(s) and init.ora file

Logical Structure of Database:
Tablespaces, segments, extents, blocks

Reference:
http://www.geekinterview.com/question_details/16281
http://www.geekinterview.com/question_details/45132



  • What are the components of physical database structure of Oracle database?

Oracle database is comprised of three types of files.
One or more datafiles,
two are more redo log files,
one or more control files


  • What are the components of logical database structure of Oracle database?

tablespaces and database's schema objects.


  • What is a tablespace?

A database is divided into Logical Storage Unit which is called tablespace.
A tablespace is used to group related logical structures



  • What is SYSTEM tablespace and when is it created?

Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created.
The SYSTEM tablespace always contains the data dictionary tables for the entire database.


  • Explain the relationship among database, tablespace and data file

Each databases logically divided into one or more tablespaces one
or more data files are explicitly created for each tablespace


  • What is schema?

A schema is collection of database objects of a user.
The set of objects owned by user account is called the schema.


  • What are Schema Objects?

Schema objects are the logical structures that directly refer to the database's data.
Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links


  • Can objects of the same schema reside in different tablespaces?

Yes


  • Can a tablespace hold objects from different schemas?

Yes


  • What is an Oracle view?

A view is a virtual table. Every view has a query attached to it


  • Do a view contain data?

Views do not contain or store data


  • Can a view based on another view?

Yes


  • What are the advantages of views?

-Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
- Hide data complexity.
- Simplify commands for the user.
- Present the data in a different perspective from that of the base table.
- Store complex queries


  • What is an Oracle sequence?

A sequence generates a serial list of unique numbers for numerical columns of a database's tables


  • What is a synonym?

A synonym is an alias for a table, view, sequence or program unit.Two types:private and public


  • What are synonyms used for?

- Mask the real name and owner of an object.
- Provide public access to an object
- Provide location transparency for tables, views or program units of a remote database.
- Simplify the SQL statements for database users.


  • What is an Oracle index?

An index is an optional structure associated with a table to have direct access to rows,
which can be created to increase the performance of data retrieval.
Index can be created on one or more columns of a table


  • How are the index updates?

Indexes are automatically maintained and used by Oracle.
Changes to table data are automatically incorporated into all relevant indexes



  • What are clusters?

Clusters are groups of one or more tables physically stores together to share common columns and are often used together
Group of tables physically stored together because they share common columns and are often used together is called cluster


  • What is data block?

Oracle database's data is stored in data blocks.
One data block corresponds to a specific number of bytes of physical database space on disk
A data block size is specified for each Oracle database when the database is created.
Block size is specified in init.ora file and can not be changed later.


  • What is an extent?

An extent is a specific number of contiguous data blocks,
obtained in a single allocation and used to store a specific type of information


  • What is a segment?

A segment is a set of extents allocated for a certain logical structure


  • What are the characteristics of data files?

A data file can be associated with only one database.
One or more data files form a logical unit of database storage called a tablespace


  • What is the function of redo log?

The primary function of the redo log is to record all changes that are made to data
The information in a redo log file is used only to recover the database from a system
or media failure prevents database data from being written to a database's data files



  • What is the use of control file?

When an instance of an Oracle database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed.
It is also used in database recovery.



  • What is a database instance?

A database instance (Server) is a set of memory structure and background processes that access a set of database files.
The processes can be shared by all of the users



  • What is SGA?

The System Global Area in an Oracle database is the area in memory to facilitate the transfer of information between users
The structure is database buffers, dictionary cache, redo log buffer and shared pool area.



  • What is a shared pool?

The data dictionary cache is stored in an area in SGA called the shared pool.
This will allow sharing of parsed SQL statements among concurrent users



  • What is meant by Program Global Area (PGA)?

It is an area in memory that is used by a single Oracle user process.


  • What is Database Buffers?

Database buffers are cache in the SGA used to hold the data blocks that are read from the data segments in the database


  • What is dictionary cache?

Dictionary cache is information about the database objects stored in a data dictionary table


  • What is a transaction?

Transaction is logical unit between two commits, commit and rollback

  • What is the usage of SAVEPOINTS?

SAVEPOINTS are used to subdivide a transaction into smaller parts.
It enables rolling back part of a transaction.
Maximum of five save points are allowed


  • What is ON DELETE CASCADE?

When ON DELETE CASCADE is specified, Oracle maintains referential integrity by automatically removing dependent foreign key values if a referenced primary or unique key value is removed.


Reference:
http://www.scribd.com/doc/7196849/Oracle-DBA-Interview-Questions-Nil




  • Explain the difference between a hot backup and a cold backup and the benefits associated with each.

A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode
A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode


  • Where would you look for errors from the database engine?

In the alert log


  • Differentiate between TRUNCATE and DELETE

The Delete command will log the data changes in the log file where as the truncate will simply remove the data without it.
Hence Data removed by Delete command can be rolled back but not the data removed by TRUNCATE.
Truncate is a DDL statement whereas DELETE is a DML statement.


  • Difference between procedure and function

A function always returns a value, while a procedure does not.
When you call a function you must always assign its value to a variable


  • What is a cursor?

A cursor is a mechanism by which you can assign a name to a “select statement” and manipulate the information within that SQL statement.


  • Difference between an implicit and an explicit cursor.

The implicit cursor is used by Oracle server to test and parse the SQL statements
the explicit cursors are declared by the programmers


  • What is difference between UNIQUE and PRIMARY KEY constraints?

An UNIQUE key can have NULL whereas PRIMARY key is always not NOT NULL.
Both bears unique values.



  • What is difference between SQL and SQL*PLUS?

SQL is the query language to manipulate the data from the database.
SQL*PLUS is the tool that lets you use SQL to fetch and display the data


  • What is the difference between alias and synonym ?

Alias is temporary and used with one query. Synonym is permanent and not used as alias.


  • What is the difference between a view and a synonym ?

Synonym is just a second name of table used for multiple link of database.
View can be created with many tables, and with virtual columns and with conditions.
But synonym can be on view


  • What is implicit cursor and how is it used by Oracle ?

An implicit cursor is a cursor which is internally created by Oracle.
It is created by Oracle for each individual SQL



  • What is a transaction ?

A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.


  • What is PL/SQL?

PL/SQL is Oracle's Procedural Language extension to SQL


  • Explain the difference between a FUNCTION, PROCEDURE and PACKAGE

A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task.
While a procedure does not have to return any values to the calling application, a function will return a single value.
A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application


  • Describe what redo logs are.

Redo logs are logical and physical structures that are designed to hold all the changes made to a database and are intended to aid in the recovery of a database.


Reference:
http://www.scribd.com/doc/7196849/Oracle-DBA-Interview-Questions-Nil
http://techpreparation.com/computer-interview-questions/oracle-interview-questions-answers2.htm
http://www.databasejournal.com/features/oracle/article.php/3114381/Oracle-Technical-Interview-Questions-Answered---Part2.htm



  • what is oracle-partitioning?



Oracle Partitioning

Oracle Partitioning, an option of Oracle Database 11g Enterprise Edition, enhances the manageability, performance, and availability of a wide variety of applications. Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity.



Benefits

Improves the performance of certain queries and maintenance operations by an order of magnitude

Greatly reduces the total cost of data ownership using a tiered archiving approach that keeps older information on low cost storage devices



http://www.oracle.com/us/products/database/options/partitioning/overview/index.html




Explain what partitioning is and what its benefit is.
A table partition is also a table segment, and by using partitioning technique we can enhance performance of table access
http://www.orafaq.com/wiki/Interview_Questions

Table Partitioning
Now a days enterprises run databases of hundred of Gigabytes in size. 
These databases are known as Very Large Databases (VLDB)
Oracle has provided the feature of table partitioning i.e. you can partition a table according to some criteria . 
For example you have a SALES table with the following structure
Suppose this table contains millions of records, but all the records belong to four years only i.e. 1991, 1992, 1993  and 1994.  
And most of the time you are concerned about only one year

select sum(amt) from sales where year=1991;

whenever you give queries like this Oracle will search the whole table. 
If you partition this table according to year, then the performance is improve since oracle will scan only a single partition instead of whole table.
http://www.oracle-dba-online.com/sql/oracle_table_partition.htm

Partitioning
Oracle Partitioning is a separately licensed option of the Oracle database that is only available with the Enterprise Edition of the database. Partitioning allows DBAs to split large tables into more manageable "sub-tables", called partitions, to improve database performance, manageability and availability.

Partitioning is basically a divide-and-conquer approach to scale to very large database sizes. It improves manageability by allowing partitions to be added, loaded, indexed and removed while the database in on-line and users are working against these tables. Partitioning can potentially make the database faster the bigger it gets.

http://www.orafaq.com/wiki/Partitioning





  • What are HINTS in ORACLE?


HINTS  are nothing but the comments used in a SQL statement to pass instructions to the Oracle optimizer
The optimizer uses these hints as suggestions for choosing an execution plan for the statement.

A statement block can have only one comment containing hints, and that comment must follow the SELECT, UPDATE, INSERT, or DELETE keyword.
http://www.geekinterview.com/question_details/32950



Hint are used to alter execution plans of SQL statements. by using hints you will be able to choose the execution plans.

or eg. in a SELECT statement your will be able to specify whether an index is to be used or not . If you want to use and index, You can also specify which index is to  be used.

The following query won't use any index even if there is an index on name.

SELECT /*+ FULL(e) */ id, name
  FROM employees e 
  WHERE name LIKE 'J%';
The above query will give you better results if the names starting with 'J' are more than 25% of total rows.

http://www.geekinterview.com/question_details/37579


How to use hints in Oracle sql for performance
With hints one can influence the optimizer. The usage of hints (with exception of the RULE-hint) causes Oracle to use the Cost Based optimizer.
The following syntax is used for hints:

select /*+ HINT */ name
from emp
where id =1;

Where HINT is replaced by the hint text. 
When the syntax of the hint text is incorrect, the hint text is ignored and will not be used.

http://www.oradev.com/hints.jsp


Optimizer hints can be used with SQL statements to alter execution plans.
Hints let you make decisions usually made by the optimizer. As an application designer, you might know information about your data that the optimizer does not know. Hints provide a mechanism to instruct the optimizer to choose a certain query execution plan based on the specific criteria.

http://docs.oracle.com/cd/B19306_01/server.102/b14211/hintsref.htm




  • Decode Function

In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement.

You could use the decode function in an SQL statement as follows:

    SELECT supplier_name,
    decode(supplier_id, 10000, 'IBM',
    10001, 'Microsoft',
    10002, 'Hewlett Packard',
    'Gateway') result
    FROM suppliers;


The above decode statement is equivalent to the following IF-THEN-ELSE statement:

    IF supplier_id = 10000 THEN
         result := 'IBM';

    ELSIF supplier_id = 10001 THEN
        result := 'Microsoft';

    ELSIF supplier_id = 10002 THEN
        result := 'Hewlett Packard';

    ELSE
        result := 'Gateway';

    END IF;


http://www.techonthenet.com/oracle/functions/decode.php


  • how to use wild chars in sql

decode(     xx.column1,0,0,((xx.column2/xx.column3)*100) )  as "BILLING %",



  • list of all users but all users may not have created objects in it.So we can not call them as schema.

select username from dba_users;




  • defining directory output in oracle

CREATE DIRECTORY dir_reports_allparners AS 'c:\reports\mydocz';
GRANT READ, WRITE ON directory DIR_REPORTS_MYDOCZ TO schemaname;
select * from dba_directories;

  • how to backup table with contents
CREATE TABLE schema_name.table1_backup AS SELECT * FROM schema_name.table;


  • how to apply time difference

and trunc(event.creation_date+country.time_diff/24)>='09-07-2009'

trunc(dd.creation_date)>='21-Jan-2018'       and trunc(dd.creation_date)>= trunc(aa.creation_date)


timestamp
AND (event.CREATION_DATE+(CN.TIME_DIFF/24))>= to_timestamp('2018/12/22 00:00:00', 'YYYY/MM/DD HH24:MI:SS')       
AND (event.CREATION_DATE+(CN.TIME_DIFF/24))< to_timestamp('2018/12/28 23:59:59', 'YYYY/MM/DD HH24:MI:SS')



sysdate
AND TRUNC(event.CREATION_DATE+(CN.TIME_DIFF/24))>= trunc(sysdate-7)        
AND TRUNC(event.CREATION_DATE+(CN.TIME_DIFF/24))<  trunc(sysdate) 


  • To_Timestamp Function
In Oracle/PLSQL, the to_timestamp function converts a string to a timestamp.



    to_timestamp('2003/12/13 10:13:18', 'YYYY/MM/DD HH:MI:SS')

would return '13-DEC-03 10.13.18.000000000 AM' as a timestamp value.

    to_timestamp('2003/DEC/13 10:13:18', 'YYYY/MON/DD HH:MI:SS')

would also return '13-DEC-03 10.13.18.000000000 AM' as a timestamp value.

http://www.techonthenet.com/oracle/functions/to_timestamp.php


  • Trunc Function (with dates)

In Oracle/PLSQL Built-In Functions, the trunc function returns a date truncated to a specific unit of measure.

The syntax for the trunc function is:

trunc ( date, [ format ] )


trunc(to_date('22-AUG-03'), 'YEAR') would return '01-JAN-03'
trunc(to_date('22-AUG-03'), 'Q') would return '01-JUL-03'
trunc(to_date('22-AUG-03'), 'MONTH') would return '01-AUG-03'
trunc(to_date('22-AUG-03'), 'DDD') would return '22-AUG-03'
trunc(to_date('22-AUG-03'), 'DAY') would return '17-AUG-03'

http://www.techonthenet.com/oracle/functions/trunc_date.php



  • To_Char Function

In Oracle/PLSQL, the to_char function converts a number or date to a string.

The following are date examples for the to_char function.

    to_char(sysdate, 'yyyy/mm/dd'); would return '2003/07/09'
    to_char(sysdate, 'Month DD, YYYY'); would return 'July 09, 2003'
    to_char(sysdate, 'FMMonth DD, YYYY'); would return 'July 9, 2003'
    to_char(sysdate, 'MON DDth, YYYY'); would return 'JUL 09TH, 2003'
    to_char(sysdate, 'FMMON DDth, YYYY'); would return 'JUL 9TH, 2003'
    to_char(sysdate, 'FMMon ddth, YYYY'); would return 'Jul 9th, 2003'
    
http://www.techonthenet.com/oracle/functions/to_char.php



  • oracle table backup

create table test2 as select * from test1;

  • the list of schemas available
select distinct owner from dba_objects;

  • connect as sys

sqlplus /nolog
connect sys/passwd@db as sysdba;


  • enable archivelog

shutdown immediate

startup mount

alter database archivelog;
alter database open;

  • disable archivelog
shutdown immediate
startup mount
alter database noarchivelog;
alter database open;
archive log list;

to see whether database is in archivelog mode
select log_mode from v$database;
OR
archive log list


  • what is limits of output of between sql query?

employee2 table


02 Alison
03 James
04 Celia
05 Robert
06 Linda
07 David
08 James
13 New





select id,first_name
from employee2 
where id between 4 and 8;



04 Celia
05 Robert
06 Linda
07 David
08 James



  • ROWNUM is a "pseudocolumn" that assigns a number to each row returned by a query

http://stackoverflow.com/questions/174595/in-an-oracle-database-what-is-the-difference-between-rownum-and-row-number

ROWNUM is a pseudo-column that returns a row's position in a result set.
ROWNUM is evaluated AFTER records are selected from the database and BEFORE the execution of ORDER BY clause
http://www.orafaq.com/wiki/ROWNUM


Question:  Does Oracle make a distinction between a ROWID and ROWNUM?  If so, what is the difference between ROWNUM and ROWID?

Answer:  Just as your home address uniquely identifies where you live, an Oracle ROWID uniquely identifies where a row resides on disk.  The information in a ROWID gives Oracle everything he needs to find your row, the disk number, the cylinder, block and offset into the block.
The ROWNUM is a "pseudo-column", a placeholder that you can reference in SQL*Plus.
http://www.dba-oracle.com/t_rownum_rowid_difference.htm




listing highest paid employees

select first_name,last_name,salary
from employee2
where rownum<4
order by salary desc;


ROWID actually represents the physical location of the record/row in the database.
The fastest way to retrieve a particular row. Faster than an index

Can you use ROWID to differentiate between duplicate rows?
Yes, you can
Since it actually represents the physical location of a row, no two rows within the same table will have the same ROWID.
If you're using clustering, two records from different tables could theoretically share the same ROWID.