Ask Anantha

Part 2 – Questions on SQL and SQL *Plus   1. A user is setting up a join operation between tables EMP and DEPT. There are some employees in the EMP table that the user wants returned by the query, but the employees are not assigned to departments yet. Which SELECT statement is most appropriate for this user?   A.       select e.empid, d.head from emp e, dept d; B.       select e.empid, d.head from emp e, dept d where e.dept# = d.dept#; C.       select e.empid, d.head from emp e, dept d where e.dept# = d.dept# (+); D.      select e.empid, d.head from emp e, dept d where e.dept# (+) = d.dept#;   2. Developer ANJU executes the following statement: CREATE TABLE animals AS SELECT * from MASTER.ANIMALS; What is the effect of this statement?   A.       A table named ANIMALS will be created in the MASTER schema with the same d...

Part 1 – Questions on SQL and SQL *Plus   1. Which of the following statements contains an error?   A. SELECT * FROM emp WHERE empid = 493945; B. SELECT empid FROM emp WHERE empid= 493945; C. SELECT empid FROM emp; D. SELECT empid WHERE empid = 56949 AND lastname = 'SMITH';   2. Which of the following correctly describes how to specify a column alias?   A. Place the alias at the beginning of the statement to describe the table. B. Place the alias after each column, separated by white space, to describe the column. C. Place the alias after each column, separated by a comma, to describe the column. D. Place the alias at the end of the statement to describe the table.   3. The NVL function A. Assists in the distribution of output across multiple columns. B. Allows the user to specify alternate output for non-null column values. C. Allows the user to specify alternate output for null column values. D. Nullifies the val...

This section discusses logical storage structures: data blocks, extents, segments, and tablespaces. These logical storage structures enable Oracle Database to have fine-grained control of disk space use.   This section includes the following topics: Oracle Database Data Blocks Extents Segments Tablespaces   Oracle Database Data Blocks At the finest level of granularity, Oracle Database data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk. The standard block size is specified by the DB_BLOCK_SIZE initialization parameter. In addition, you can specify up to four other block sizes. A database uses and allocates free database space in Oracle Database data blocks.   Extents The next level of logical database space is an extent. An extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information.   ...

The following sections explain the physical database structures of an Oracle database, including datafiles, control files, redo log files, archive log files, parameter files, alert and trace log files, and backup files.   This section includes the following topics: Datafiles Control Files Online Redo Log Files Archived Redo Log Files Parameter Files Alert and Trace Log Files Backup Files   Datafiles Every Oracle database has one or more physical datafiles, which contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the datafiles allocated for a database.   Datafiles have the characteristics: A datafile can be associated with only one database. Datafiles can be defined to extend automatically when they are full. One or more datafiles form a logical unit of database storage called a tablespace.   Data in a datafile is read, as needed, durin...

The phrase "all or nothing" succinctly describes the first ACID property of atomicity. When an update occurs to a database, either all or none of the update becomes available to anyone beyond the user or application performing the update. This update to the database is called a transaction and it either commits or aborts. This means that only a fragment of the update cannot be placed into the database, should a problem occur with either the hardware or the software involved. Features to consider for atomicity:     A transaction is a unit of operation - either all the transaction's actions are completed or none are   Atomicity is maintained in the presence of deadlocks   Atomicity is maintained in the presence of database software failures   Atomicity is maintained in the presence of application software fa...

Author: David (DBA) This article makes direct comparison between the Oracle architecture (instance, databases, physical files, network, configuration files, etc) and the DB2 architecture thus allowing Oracle DBAs to understand the similarities and differences between Oracle and DB2. Because of its comparative nature (showing you how the same things work in DB2 compared to Oracle) I think its a great first read if you are an Oracle DBA new to DB2. I do have some comments I would like to add: 1) IBM says: DB2 does contain a binary file known as the system database directory that contains entries of all the databases you can connect from your DB2 machine. This compares best to Oracle tnsnames.ora file. I don't understand why IBM didn't draw this comparison. 2) IBM says: Every Oracle database contains a table space named SYSTEM, which Oracle creates automatically when the database is created. Other table spaces for user, temporary and index data need to be created after the datab...

Author: Nishith Data warehouse vs. database is a long and old debate. In this article I am going to discuss the difference between data warehouse and database. I will also discuss that which one is better to use. Similarities in Database and Data warehouse: Both database and data warehouse are databases. Both database and data warehouse have some tables containing data. Both database and data warehouse have indexes, keys, views etc. Difference between Database and Data warehouse: So is that ‘Data warehouse' really different from the tables in you application? And if the two aren't really different, maybe you can just run your queries and reports directly from your application databases! Well, to be fair, that may be just what you are doing right now, running some end-of-day reports as complex SQL queries and shipping them off to those who need them. And this scheme might just be serving you fine right now. Nothing wrong with that if it works for you...

A constraint is defined by Oracle as a declarative way to define a business rule for a column of a table. An integrity constraint is a statement about a table's data that is always true. The difference between a unique index and a unique key/primary key constraint starts with the fact that the constraint is a rule while the index is a database object that is used to provide improved performance in the retrieval of rows from a table. It is a physical object that takes space and is created with the DDL command: create index or as part of a create table with primary key or unique key constraints or an alter table command that adds these constraints (see SQL Manual). Briefly the constraints are: Not Null - Column value must be present. Unique Key - Column(s) value(s) must be unique in table or null (see note below). Primary Key - Unique key + Not Null which equates to every column in the key must have a value and this value is unique so the primary key uniquely identifies each and ev...

Have you ever wondered after connecting to the database as SYS you wanted to check whether you are connected as regular user SYS or as SYS AS SYSDBA? Well the information is available in view V$SESSION_CONNECT_INFO. The following are the fields available in this view:   Name                        Type ------------------------    ------------------ SID                          NUMBER AUTHENTICATION_TYPE          VARCHAR2( 26) OSUSER                      VARCHAR2( 30) NETWORK_SERVICE_BANNER      VARCHAR2( 4000)     You can execute the following query to know the connection type...

A program global area (PGA) is a memory region that stores the data and control information for the server processes. Each Server process has a non-shared memory created by Oracle when a server process is started. Access to the PGA is exclusive to that server process and it is read and written only by Oracle code acting on its behalf. Broadly speaking, PGA contains a private SQL area and a Session memory area. A private SQL area contains data such as bind information and runtime memory structures. Each session that issues a SQL statement has a private SQL area. Note that the Location of a private SQL area depends on the type of connection established for a session. If a session is connected through a dedicated server, private SQL areas are located in the server process’s PGA. However, if a session is connected through a shared server, part of the private SQL area is kept in the SGA. Session memory is the memory allocated t...

The Oracle HTTP Server(OHS) provides key infrastructure for serving the Internet's HTTP protocol. OHS is used to return responses for both process to process and human generated requests from browsers. Key aspects of OHS are its technology, its serving of both static and dynamic content and its integration with both Oracle and non-Oracle products. Technology - OHS is based on the proven, open source technology of both Apache 1.3 and Apache 2.0. OHS versions based on Apache 2.0 now provide the ability to accommodate the newest version of the Internet Protocol, IPv6. OHS based on Apache 2.0 is available as a standalone product off the Oracle Application Server 10g Companion CD. In addition OHS now provides, via the open source product mod_security, an application firewall capability. Static and Dynamic Content - OHS serves static content directly or via standard interfaces such as WebDAV standard. Great fle...