| Share this post! | Vote this! |
|
|
|
How To Specify Unique Key/Primary Key Constraints In DB2
Query Analysis and Index Tuning
| Share this post! | Vote this! |
|
After you have implemented your system, a year or so down the live you generally hear users stating that reports are taking longer and the system is a little sluggish, in this section we will take a look at how to address these issues which generally all relate to concurrency, more users using the system
- The query cache is not being utilized properly
- The query contains subqueries or unoptimized subqueries
- The table contains large amounts of unnecessary data more...
Partitioning
| Share this post! | Vote this! |
|
Partitioning is dividing up data in the database into distinct independent elements, partitioning serves three main purposes
- data manageability
- performance
- availability. more...
Backups and Recovery
| Share this post! | Vote this! |
|
This is the most important task of an database administrator, you must protect your data at all costs, this means regular backups and regular restores even to another system just to check the integrity of those backups. There is no point in putting yourself in a position where you are holding your breathe when a restore is happening only to find out that the backup is corrupt, try if possible to perform regular restores if not then at least you should be performing a disaster recovery test once per year. Not being able to restore could be a disaster for your company and your job. more...
SQL Query Optimization
| Share this post! | Vote this! |
|
Performance tuning focuses on writing efficient SQL, allocating
computer resources and analyzing wait events and contention in the
system. The design approach to a database is critical to ensuring the
best performance from a database, here are the steps when designing a
database:
- Design the application correctly
- Tune the application SQL code
- Tune memory
- Tune I/O
- Tune contention and other issues
Oracle : FlashBack Architecture
| Share this post! | Vote this! |
|
There are a number of flashback levels
row level flashback query, flashback versions query, flashback transaction query
table level flashback table, flashback drop
database level flashback database
Oracle 10g has several error-correction techniques that use undo data, however they are only available if you use automatic undo management (AUM),
row level flashback query, flashback versions query, flashback transaction query
table level flashback table, flashback drop
database level flashback database
Oracle 10g has several error-correction techniques that use undo data, however they are only available if you use automatic undo management (AUM),
- Flashback query - retrieves data from a past point in time
- Flashback versions query - shows you different versions of data rows, plus start and end times of a particular transaction that created that row
- Flashback transaction query - lets you retrieve historical data for a given transaction and the SQL code to undo the transaction.
- Flashback table - recovers a table to its state at a past point in time, without having to perform a point in time recovery. more...
Oracle Regular Expression : Complete Tutorial
| Share this post! | Vote this! |
|
Regular expressions allow you to perform powerful context searches in
variable-length strings. They provide a a powerful set of pattern
matching capabilities by combining the following
Character Classes are groups of possible characters at a point in the search
Collation Classes are sets of characters and are treated like a range
Metacharacters are operators that specify search algorithms
Metasequences are operators created by two metacharacters or literals
Literals are characters, character sets and words more...
Character Classes are groups of possible characters at a point in the search
Collation Classes are sets of characters and are treated like a range
Metacharacters are operators that specify search algorithms
Metasequences are operators created by two metacharacters or literals
Literals are characters, character sets and words more...
Processes in Oracle
| Share this post! | Vote this! |
|
Oracle server processes perform all the database operations such as
inserting and deleting data. The oracle processes working with the SGA
(oracle memory structure) manage the database.
There are two types of Oracle process
There are two types of Oracle process
- User process - Responsible for running the application that connects to the database
- Oracle server process - Perform oracle tasks that manage the database.
Handling Oracle Large Objects
| Share this post! | Vote this! |
|
LOB's can store text, images, music and video inside the database.
You can define BLOB, CLOB and NCLOB columns, you can also secure these
with securefiles.
LOB's can store a maximum of 8 to 128 terabytes, depending on how you configure the database, you can call get_storage_limit using the dbms_lob package to get the maximum size.
get maximum LOB size : declare
var1 clob := 'some string';
var2 blob := hextoraw('43'||'41'||'52');
begin
result := dbms_lob.get_storage_limit(var1);
dbms_output.put_line('CLOB maximum limit: ' || result);
dbms_output.put_line('CLOB length: ' || dbms_lob.getlength(var1)); more...
LOB's can store a maximum of 8 to 128 terabytes, depending on how you configure the database, you can call get_storage_limit using the dbms_lob package to get the maximum size.
get maximum LOB size : declare
var1 clob := 'some string';
var2 blob := hextoraw('43'||'41'||'52');
begin
result := dbms_lob.get_storage_limit(var1);
dbms_output.put_line('CLOB maximum limit: ' || result);
dbms_output.put_line('CLOB length: ' || dbms_lob.getlength(var1)); more...
Oracle : Intersession Communication
| Share this post! | Vote this! |
|
Intersession communication is the ability to communicate between two
different user connections, you have two ways in which you can
communicate between sessions
In order to communicate between two session without using a permanent structure and use memory instead (SGA) you can use and do the following: more...
- DBMS_PIPE
- DBMS_ALERT
In order to communicate between two session without using a permanent structure and use memory instead (SGA) you can use and do the following: more...
Guide To Oracle Installation
| Share this post! | Vote this! |
|
Oracle is very simple to install, it can be installed on a number of
different operating systems. Basically when on installing on to a Unix
server a number of pre-install steps must be taken before you install
Oracle.
The minimum disk and memory requirements are 1.5-2GB of disk space and at least 256MB RAM, however for more serious applications you would need a lot more than this (8GB - 32GB are not uncommon).
As with any software you should consult the documentation for best practices and to get the best performance out of the software, Oracle is no different in that a good installation plan is required before you attempt to install and configure Oracle, however i will not explain how to do this, it would be best left to the Oracle documentation, which i highly recommend you read, especially the installation documentation and with each different Oracle version different settings are required. more...
The minimum disk and memory requirements are 1.5-2GB of disk space and at least 256MB RAM, however for more serious applications you would need a lot more than this (8GB - 32GB are not uncommon).
As with any software you should consult the documentation for best practices and to get the best performance out of the software, Oracle is no different in that a good installation plan is required before you attempt to install and configure Oracle, however i will not explain how to do this, it would be best left to the Oracle documentation, which i highly recommend you read, especially the installation documentation and with each different Oracle version different settings are required. more...
Oracle Enterprise Manager (OEM)
| Share this post! | Vote this! |
|
Oracle Enterprise Manager is Oracle's GUI-based database management
tool. It is possible to manage the oracle environment using SQL and
PL/SQL but OEM gives you a console based framework to help you with
almost all of your day-to-day management activities, including tasks
such as backup, recovery, export, import and data loading, it also makes
it easier to use the oracle advisors.
There are two version of OEM - a single instance version called Database Control and a system wide version that lets you manage all your systems called Grid Control. Most dba's do not like using GUI's but it makes sense to use the GUI for the easier day-to-day administration tasks like adding users, resizing tablespaces, etc, I think that knowing both SQL and PL/SQL and the GUI will make you a better dba in the long run.
The benefits of using the OEM more...
There are two version of OEM - a single instance version called Database Control and a system wide version that lets you manage all your systems called Grid Control. Most dba's do not like using GUI's but it makes sense to use the GUI for the easier day-to-day administration tasks like adding users, resizing tablespaces, etc, I think that knowing both SQL and PL/SQL and the GUI will make you a better dba in the long run.
The benefits of using the OEM more...
Oracle : How To Recover Critical Files
| Share this post! | Vote this! |
|
Recovering critical files would include control files
Recover/Re-create a controlfile
There a number of ways to recover a controlfile, restore of a backup (rman, user managed) or re-create the control. If you were to lose a controlfile while the database is up, just re-create it, no data should be lost. more...
Recover/Re-create a controlfile
There a number of ways to recover a controlfile, restore of a backup (rman, user managed) or re-create the control. If you were to lose a controlfile while the database is up, just re-create it, no data should be lost. more...
Oracle : Object Types
| Share this post! | Vote this! |
|
Object types define how to store data and define API operations, also
known as method functions or procedures. Object types are also known as
classes in many OO programming languages. Object types mirror that of
an PL/SQL package, an object body implements the object type just as a
package body implements a package specification. The object type hides
the operation details known as encapsulation and also masks the complexity known as abstraction. The current method for visually representing object types is generally done in UML (Unified Modeling Language).
OO programming has two types of API interfaces in object types
Static : static methods allow you to access object type variables and methods without creating an instance of a class, static vaiables in PL/SQL are not available. you can implement static methods like package functions and procedures. more...
OO programming has two types of API interfaces in object types
Static : static methods allow you to access object type variables and methods without creating an instance of a class, static vaiables in PL/SQL are not available. you can implement static methods like package functions and procedures. more...
Incomplete Database Recovery
| Share this post! | Vote this! |
|
Incomplete recovery can mean restoring a database to a specific
point in time (date and time), a specific SCN (system change number) or
sequence number (archive log). You normally restore all the datafiles
and controlfile (only required if physical structure has changed), any
archived redo logs but you do not restore the redo log files (not
normally backed up).
If you lose all copies of the current logfile group perform a incomplete recovery upto the last log switch, also if you recover tablespaces that are in a different time to other tablespaces, they must be all the same time.
Incomplete recovery is always done in mount mode and will use the controlfile, a incomplete recovery can only be performed if you have SYSDBA privilege. Use v$log_history to obtain the archive redo logs thread and sequence numbers if performing a sequence number recovery.
The sequence for incomplete recovery recover is: more...
If you lose all copies of the current logfile group perform a incomplete recovery upto the last log switch, also if you recover tablespaces that are in a different time to other tablespaces, they must be all the same time.
Incomplete recovery is always done in mount mode and will use the controlfile, a incomplete recovery can only be performed if you have SYSDBA privilege. Use v$log_history to obtain the archive redo logs thread and sequence numbers if performing a sequence number recovery.
The sequence for incomplete recovery recover is: more...
Oracle Memory Architecture
| Share this post! | Vote this! |
|
Oracle uses three kinds of memory structures
SGA (System Global Area) : is a large part of memory that all the oracle background processes access.
PGA (Process Global Area) : This is memory that is private to a single process or thread and is not accessible by any other process or thread
UGA (User Global Area) :
This is memory that is assoicated with your session, it can be found in the PGA or SGA depending on whether you are connected to the database via shared server
Shared Server - the UGA will be in the SGA
Dedicated Server - the UGA will be in the PGA more...
SGA (System Global Area) : is a large part of memory that all the oracle background processes access.
PGA (Process Global Area) : This is memory that is private to a single process or thread and is not accessible by any other process or thread
UGA (User Global Area) :
This is memory that is assoicated with your session, it can be found in the PGA or SGA depending on whether you are connected to the database via shared server
Shared Server - the UGA will be in the SGA
Dedicated Server - the UGA will be in the PGA more...
PL/SQL Packages
| Share this post! | Vote this! |
|
Packages are stored libraries in the database, they are owned by the
user schema where they're created, like table and views, this ownership
makes packages schema-level objects in the database catalog, like
standalone functions and procedures. Users who wish to use the package
must have execute privilege on the package. You define package
only-scope functions and procedures in package bodies, package only
scope functions and procedures can access anything in the package
specification. Normally you declare identifiers in the following order:
datatypes, variables, exceptions, functions and procedures.
A PL/SQL package is away to group a set of program units (procedures/functions) together, a package is divided into two parts
A PL/SQL package is away to group a set of program units (procedures/functions) together, a package is divided into two parts
- package header - lists the interface to the package, variables/constants that are visible to the outside world
- package body - contains the PL/SQL code more...
PL/SQL Advanced Programming
| Share this post! | Vote this! |
|
It is possible to create dynamic SQL on the fly, you have two
architectures that apply in both cases, you can glue strings together or
you can implement placeholders. The gluing of strings is susceptible to
SQL injection attacks, implementing placeholders (bind variables) makes
your dynamic SQL immune to these attacks. They act as formal parameters
to dynamic statements.
The process of running a dynamic statement involves four steps:
The process of running a dynamic statement involves four steps:
- First, the statement is parsed
- Second, the statement with placeholders map the actual parameters to the formal parameters.
- Third, it executes the statement
- Fourth, it returns values to the calling statement
PHP and MySQL User Registration
| Share this post! | Vote this! |
|
This tutorial will demonstrate how to implement a user login/membership
system in your website. Though implementing login/membership system
appears to be a herculean task, it is actually lot more simpler than you
think. In fact, all you need is a single table in your database, and a
PHP-supported Server. To begin with, here is an outline of what this
tutorial will show you how to do:
- Gather username, password, email, and store in table
- User login
WAMP Server : Complete Tutorial
| Share this post! | Vote this! |
|
Table of Contents
WAMP Server Setting up WAMP SERVER Exploring WAMP Server WAMP Server Menu OptionsWAMP SERVER
WAMP is an acronym for Windows, Apache, MySQL and PHP. It is a combination of independently created software’s bundled together into a SINGLE installation package to set up a SERVER on your machine with out any hassles. It comes with the GPL License.Apache HTTP Server:
Widely know as Apache Server is the most widely used webserver out there. Apache is developed and maintained by an open community of developers under the guidance of the Apache Software Foundation. It is available for wide variety of operating systems like Windows, Mac OS X, Linux, Unix, FreeBSD etc.more...
