Index Creation Advanced Options

Nologging
This option Significantly improving performance when u create index. (up to 30% faster)
Other benefits:
* Space is saved in the redo log files.
* The time it takes to create the index is decreased.
* Performance improves for parallel creation of large indexes.

Ex-1:
CREATE INDEX cust_dup_idx
ON customer(sex, hair_color, customer_id)
PARALLEL 35
NOLOGGING;

Ex-2:
CREATE INDEX UA0909_PROV_DT_I
ON THARCH.UA_200909_200910 (PROV_ID, DATESTAMP)
TABLESPACE X_INDX01_16K
NOLOGGING
COMPRESS;


Logging
As long as the indexes are in logging mode immediately before and during a hot backup they will be refreshed properly. 

 
ALTER INDEX UA0909_PROV_DT_I LOGGING;


Compress
This option is used to repress duplication of keys in non-unique indexes.
The compress option can reduce the size of the index by more than half.
The compress option allows you to specify the prefix length for multiple column indexes.

Ex-1:
CREATE INDEX cust_dup_idx
ON Customer (sex,hair_color, customer_id)
PARALLEL 35
NOLOGGING
COMPRESS 2;

Ex-2:
CREATE INDEX UA0909_PROV_DT_I
ON THARCH.UA_200909_200910 (PROV_ID, DATESTAMP)
TABLESPACE X_INDX01_16K
NOLOGGING
COMPRESS;

Different Insert Options

Q: How to Insert all/filtered data from Table1 to Table2?

select count(*) from INDIVIDUAL1;
DROP TABLE INDIVIDUAL2;
create table INDIVIDUAL2 as select * from INDIVIDUAL1 where 1=2; --create table without data (use a invalid condition)

INSERT INTO INDIVIDUAL2 SELECT * FROM INDIVIDUAL1;
COMMIT;
select count(*) from INDIVIDUAL2;

Q: How to Insert all/filtered data from Table2 to Table1, where id will be different?
SQL> show user
USER is "schema1"
SQL> insert into Table1(id,file_name,content,field_name)
(select SEQUENCE.NEXTVAL, file_name, content, NOTES_FIELD_NAME from schema2.table2);

SQL DEVELOPER

Oracle Substitution use at SQL Developer:

To define a character for substitution at sqldeveloper: SET DEFINE &;
here '&' is substitution character. if any insert/update/delete/select statement get '&' then it will ask substitution string.

To off bind or substitution scan at insert/update/delete/select at sqldeveloper use following command:
SET SCAN OFF;

To set a escape character use following command: SET ESCAPE \;

SQL PLUS

Display the Full content of a LOB column:

SQL> set long 30000
SQL> show long
long 30000

SQL*Plus Location: SQL plus is typically located in $ORACLE_HOME/bin/sqlplus.

Important SQL*Plus Commands:

COLUMN: Change display width of a column. Ex: column USERNAME format a10
CONNECT: Connect to a database as a specified user. Ex: connect username/password@SID
COPY: Copy data from a query into a table (local or remote)
EXECUTE: Run a single PLSQL statement
SET: Display or change SQL*Plus settings
SHUTDOWN: To Shutdown a database instance.

SQLPLUS:
STARTUP: Startup a database instance.
SHOW: List the value of a system variable

More commands

Display Current settings:

SQL> show arraysize
SQL> show all

Formating Output:

1. Set
SQL> set pages 20
SQL> set linesize 130

2. Column Formatting
SQL> column USERNAME format a10

SQL> column colum_name alias alias_name
SQL> column colum_name clear
SQL> column colum_name heading header_text
SQL> column colum_name justify left
SQL> column colum_name justify right
SQL> column colum_name justify center
SQL> column colum_name print
SQL> column colum_name noprint
SQL> column colum_name old_value
SQL> column colum_name on
SQL> column colum_name off
SQL> column colum_name truncated

Operating System commands:

HOST command is used to issue commands to the operating system.

SQL> host bash
SQL> host cat update_ua.log

cat is Bash command line for Linux. Which display the contents of a file.

Database initialization parameters:

The initialization parameter values can be viewed with the SQL*Plus show parameter command.

column NAME_COL_PLUS_SHOW_PARAM format a35
column VALUE_COL_PLUS_SHOW_PARAM format a35

SQL> show parameters

To see the parameter's value:
SQL> show parameter parameter_name
SQL> show parameter global_names

More information

SQL SERVER

What is SQL Server CUBE?

In OLAP, data is modeled into cubes.

A cube is a kind of multidimensional framework containing both descriptive data called dimensions and quantitative values known as measures. In a cube, you can not only filtrate and rearrange data, but also shrink or expand the details.

Why CUBE:

As the number of people who are conducting business from outside the corporate workplace increases, the need to access corporate databases using devices such as mobile phones and portable computers also increases. The corporate world invests considerable resources in capturing volumes of data.

Online Analytical Processing (OLAP) is a popular technology used to collate and interpret this data. With OLAP you can sift through large volumes of data and make them meaningful in context to your requirement. So how do we make OLAP services available to users who are not wired in to the corporate database because they are on the move? One way is to put various combinations of data in separate modules that do not require a connection to SQL Server analysis services.

The local cube is the first automated option developed to cater to the specific needs of a customer. It is portable, customizable and supports repeated creation of various combinations of data. As more and more data access begins to take place from the Internet and across networks, local cubes are the perfect way to provide consistent, updated Business Intelligence reports that suit each user's specific needs without their ever having to connect to the analysis server.

If you are a sales manager on the road or an engineer at a remote project site, this instant ability to retrieve and use data intelligently will go a long way to help you make effective, timely decisions.

What is OLAP:

OLAP is an acronym for On Line Analytical Processing. OLAP performs multidimensional analysis of business data and provides the capability for complex calculations, trend analysis, and sophisticated data modeling. It is quickly becoming the fundamental foundation for Intelligent Solutions including Business Performance Management, Planning, Budgeting, Forecasting, Financial Reporting, Analysis, Simulation Models, Knowledge Discovery, and Data Warehouse Reporting. OLAP enables end-users to perform ad hoc analysis of data in multiple dimensions, thereby providing the insight and understanding they need for better decision making.

CUBE operator:

The CUBE operator returns a result set that has one grouping for all possible combinations of columns in the CUBE list and a grand total grouping.

SQL Server CUBE - group-by-cube-rollup-and-sql-server-2005
ORACLE CUBE - http://www.fast-track.cc/cbo_84.htm

SQL LOADER

Load data into table from .csv file

Suppose, You have a excel file (login_match.xls) with 338 rows and three columns:
1.Last Name
2.First Name
3.Title / Position

Step-1: You have to make a login_match.csv from that excel.
Step-2: Create a table in terminus (prod7/thword7):
This table also contains three column because the excel contains three columns. And the data type will be same as the excel file.

CREATE TABLE temp_login
(first_name varchar2(64),
last_name varchar2(64),
title varchar2(64)
);

Step-3: Write a control file. (login_match.ctl)
Step-4: copy .csv and .ctl from home to terminus

scp login_match.ctl login_match.csv oracle@terminus:
password:

Step-5: In terminus-wiki
bash-3.00$ sqlldr USERID=prod7 control=login_match.ctl

--------------------done----------------------------

Control File structure:
LOAD DATA
infile 'login_match.csv' "str '\n'"
INTO TABLE temp_login
fields terminated by ',' optionally enclosed by '"'
(
first_name CHAR,
last_name CHAR,
title CHAR
)

Possible errors are:
- ORA-1401: inserted value too large for column.
- Field in data file exceeds maximum length.


Related: http://www.orafaq.com/wiki/SQL*Loader_FAQ
Interesting: http://neowiki.neooffice.org/index.php/Using_Find_and_Replace

Load Check

To check DB load press: w
To check CPU load press: prstat -t

AWR

AWR report from sqlplus:

oracle@jupiter:~$ cd $ORACLE_HOME
oracle@jupiter:/oracle/app$ cd rdbms/admin
oracle@jupiter:/oracle/app/rdbms/admin$ sqlplus s as sysdba

sql>@awrrpt.sql

Enter value for report_type: html
Enter value for num_days: 1
Enter value for begin_snap: 9014
Enter value for end_snap: 9017
Enter value for report_name: awr_nahar.html

SQL> host
bash-3.00$ ls
bash-3.00$ scp awr_nahar.html nahar@nahar:
-----------------------------------------------------------------------------------------
Example:
I was trying to spool but I had not spooling permission there. So -
Then I run the awrrpt.sql file from my home.

nahar@db01:~$ sqlplus s as sysdba
SQL> @/d00/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/awrrpt.sql

scp mf01.therap.net:/u/nahar/nahar-20111127.html /home/nahar/AWR/
-----------------------------------------------------------------------------------------
AWR (Automatic Workload Repository):

What is AWR?
AWR reports collects database performance statistics and metrics for analysis and tuning, shows the exact time spent in the database, and even saves session information.

What is default interval period between two awr report ?

By default, AWR automatically generates snapshots of the performance data once every hour and retains the statistics in the workload repository for 7 days.

GUI ----> OEM (Oracle Enterprise Manager)
Login to OEM ---> Administration Tag ----> Workload Section --->AUTOMATIC WORKLOAD REPOSITORY

The data in the snapshot interval is then analyzed by the Automatic Database Diagnostic Monitor (ADDM).

How to Manage & change interval period for awr report ?

There is two to modify or changes in AWR report.
1. Manually ---> through DBMS_WORKLOAD_REPOSITORY plsql package

2. GUI ----> OEM (Oracle Enterprise Manager)
Login to OEM ---> Administration Tag ----> Workload Section --->AUTOMATIC WORKLOAD REPOSIT

How to Interpreting with AWR report ?

1. Load Profile
Here we know about Logical Read, Physical Read, Soft Parse, Hard Parse

2. Instance Efficiency Percentages
If your instance percentage show BUFFER CAHCE HIT RATIO is 100% it is not sign for your database is very fast and running smootly.


3. Top 5 Timed Events
It is very important section in AWR report.
through this we can know most five wait event is effecting database performance.

4. SQL Statistics
It this section we will know about Execution time, cpu used, logical & physical read for top most sql statement.


5.Advisory Statistics
In this section we will get advice for PGA, BUFFER CACHE, SHARED POOL, JAVA POOL size for better performance.

Back Deleted Datafiles

Q: How can u back deleted datafiles?

It is possible to brought back deleted datafile. To see how :

SQL> CREATE TABLESPACE test DATAFILE '/backup2/test_01.dbf' SIZE 10M;
SQL> ALTER TABLESPACE test ADD DATAFILE '/backup2/test_02.dbf' SIZE 800M ;
SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME='TEST';

TABLESPACE_NAME FILE_NAME
------------------------------ -----------------------------------------
TEST /backup2/test_01.dbf
TEST /backup2/test_02.dbf

SQL> alter tablespace test drop datafile '/backup2/test_02.dbf';
Tablespace altered.

SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME='TEST';

TABLESPACE_NAME FILE_NAME
------------------------------ -------------------------------
TEST /backup2/test_01.dbf

SQL> ALTER TABLESPACE test ADD DATAFILE '/backup2/test_02.dbf' SIZE 800M REUSE;
SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME='TEST';

TABLESPACE_NAME FILE_NAME
------------------------------ -------------------------------
TEST /backup2/test_01.dbf
TEST /backup2/test_02.dbf

** there is no guaranty that the object stored on the deleted datafile are available when it brought back :)
so when such thing happen the ideal way to use backup dump or other database backup system ...

Autotrace Setting

SET AUTOTRACE TRACEONLY

Autotrace is a very useful feature that used to trace the cost of a sql query and execution plane oracle used for that query.

Any session can run the autotrace by SET AUTOTRACE ON at SQL*PLUS. But you will get the following error if it is not enabled.

sql :> set autotrace on;
SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

To solve this do the followings:

1. Run plustrce.sql. The location of plustrce.sql is:
@$ORACLE_HOME/sqlplus/admin/plustrce.sql
sql> @ .../plustrce.sql

2. Grant PLUSTRACE to the user : Sql> GRANT PLUSTRACE to user_name;

3. Also PLAN_TABLE must exist in the USER’s Schema ,if user want to do autotrace with explain plan . For creation of plan_table , execute UTLXPLAN.sql location of the file is:
ON UNIX :-$ORACLE_HOME/rdbms/admin/utlxplan.sql
sql> @../utlxplan.sql

A User can use the AUTOTRACE options as follows:

sql> SET AUTOTRACE OFF ;
sql> SET AUTOTRACE ON EXPLAIN ;
sql> SET AUTOTRACE ON STATISTICS;
sql> SET AUTOTRACE ON;
sql> SET AUTOTRACE TRACEONLY;
- Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any.

Temp Tablespace

A temporary tablespace contains data that persists only for the duration of the session.

Temporary tablespaces can improve the concurrency of multiple sort operations, reduce their overhead, and avoid Oracle Database space management operations. A temporary tablespace cannot contain permanent objects and therefore no need to back up. Ensure that you create your temporary tablespaces as locally managed instead of dictionary managed.

Q: Why we need Temp tablespace?

For example, If you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.

Assign temporary tablespce to a user:

SQL> CREATE USER prd DEFAULT TABLESPACE data TEMPORARY TABLESPACE temp;

SQL> ALTER USER prd TEMPORARY TABLESPACE temp;

TEMPORARY TABLESPACE Creation:

CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/oradata/mytemp_01.tmp' SIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

For best performance, the UNIFORM SIZE must be a multiple of the SORT_AREA_SIZE parameter.

TEMPFILES:

DROP: ALTER DATABASE TEMPFILE '/oradata/temp02.dbf' DROP INCLUDING DATAFILES;

If you remove all tempfiles from temp tablespace, you may encounter error: ORA-25153: Temporary Tablespace is Empty. Then u have to add a TEMPFILE:

ADD: ALTER TABLESPACE temp ADD TEMPFILE '/oradata/temp03.dbf' SIZE 100M;

MONITORING:

Tempfile: V$TEMPFILE, DBA_TEMP_FILES.
Who occupies the spaceiews: v$sort_usage.
Temporary segments: v$sort_segment

DBA_FREE_SPACE doesn't record free space for temp tablespaces. Use V$TEMP_SPACE_HEADER:

SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

DEFAULT TEMP TABLESPACE:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Q: Find out the default temp tablespace for a database?

SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

The following restrictions apply to default temporary tablespaces:

The Default Temporary Tablespace must be of type TEMPORARY
The DEFAULT TEMPORARY TABLESPACE cannot be taken off-line
The DEFAULT TEMPORARY TABLESPACE cannot be dropped until you create another one.

All new users that are not explicitly assigned a TEMPORARY TABLESPACE, will get the Default Temporary Tablespace as its TEMPORARY TABLESPACE.

DROP Default Temporary Tablespace:

SQL> DROP TABLESPACE temp;

drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

In cases where the temporary tablespace you want to resize (using the drop/recreate method) is the default temporary tablespace for the database, you have several more steps to perform.

Step-1:

The first step you need to perform is create another temporary tablespace (lets call it TEMP2).
SQL> CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u02/oradata/TESTDB/temp2_01.dbf' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Step-2:

Next step is making TEMP2 the default temporary tablespace for the database.
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Step-3:

Drop / recreate the TEMP tablespace to the size you want.
SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

SQL> CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Step-4:

Finally, make the newly created TEMP tablespace your default temporary tablespace for the database and drop the TEMP2 tablespace.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Problem-1: I had a temporary tablepsace TEMP and there no space left on that tablespace.

Solution:

Create a new tablespace:

CREATE TEMPORARY TABLESPACE temp01
TEMPFILE '/dump/TEMP_FILE/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Making the new tablespace default:

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp01;

Remove all the files other than the default file:

SQL> select file_name from dba_temp_files;

FILE_NAME
-----------------------------------------------
/oracle/temp_file/temp02_02.dbf
/oracle/temp_file/temp2_01.dbf
/dump/TEMP_FILE/temp01.dbf

SQL> ALTER DATABASE TEMPFILE '/oracle/temp_file/temp2_01.dbf' DROP INCLUDING DATAFILES;

SQL> ALTER DATABASE TEMPFILE '/oracle/temp_file/temp02_02.dbf' DROP INCLUDING DATAFILES;

ALTER DATABASE TEMPFILE '/oracle/temp_file/temp02_02.dbf' DROP INCLUDING DATAFILES
*
ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time

Reason- Some users are working with this temp file, so it is not deleting.
bash-3.00$ rm temp02_02.dbf

SQL> shutdown immediate;
SQL> startup

How to recover a dropped table?

FLASHBACK TABLE table_name TO BEFORE DROP;

Scenario: One of our developer accidentally dropped a table from local database. And we don't keep any rman backup, or regular dump for our local database. Then I had to recover the table from recyclebin.

There are two recyclebin views: USER_RECYCLEBIN and DBA_RECYCLEBIN.
For convenience, the synonym RECYCLEBIN points to your USER_RECYCLEBIN.

The recyclebin is enabled by default in 10g, but you can turn it on or off with the RECYCLEBIN initialization parameter. When the recyclebin is enabled, any tables that you drop do not actually get deleted. Instead, when you drop a table, Oracle just renames the table and all its associated objects (indexes, triggers, LOB segments, etc) to a system-generated name that begins with BIN$.

Example:
create table nahar_test1 (col varchar2(10), row_chng_dt date);
insert into nahar_test1 values ('Version1', sysdate);
commit;
Drop table nahar_test1;

select object_name, original_name, type
, can_undrop as "UND"
, can_purge as "PUR", droptime
from USER_RECYCLEBIN where original_name='NAHAR_TEST1';

alter session set nls_date_format='HH24:MI:SS';
select * from "BIN$j35MMYy7Q1LgRAAVFyazQA==$0";FLASHBACK TABLE nahar_test1 TO BEFORE DROP; -------------------wow!! The table Backed!!!

It's important to know that after you've dropped a table, it has only been renamed. The table segments are still sitting there in your tablespace, unchanged, taking up space. This space still counts against your user tablespace quotas, as well as filling up the tablespace. It will not be reclaimed until you get the table out of the recyclebin. You can remove an object from the recyclebin by restoring it (by flashback), or by purging (permanent delete) it from the recyclebin.

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime from recyclebin;

OBJECT_NAME ----- -----------------------------ORIGINAL_NAME ----- TYPE ---UND - PUR ----- DROPTIME
---------------------------------------------------------------------------------------------------------------------------------------------------------------
BIN$HGnc55/7rRPgQPeM/qQoRw==$0----- TST -------------------TABLE ----YES -- YES ---006-09-01:16:10:12

PURGE TABLE "BIN$HGnc55/7rRPgQPeM/qQoRw==$0"; --------------Purged/Permanently deleted!

Unless you purge them, Oracle will leave objects in the recyclebin until the tablespace runs out of space. If the tablespace data files are AUTOEXTEND ON, Oracle will purge recyclebin objects before it autoextends a datafile.

CONCLUSION:

The recyclebin may contain several versions of a dropped object. Oracle restores them in LIFO order; you can restore older versions by repeatedly restoring until you get the version you want, or by using the correct version's BIN$... name directly. Oracle drops most dependent objects along with the table, and restores them when the table is restored with FLASHBACK DROP, but does not restore their names. You can purge dependent objects separately to restore the table without them. Even after turning RECYCLEBIN OFF, you can FLASHBACK DROP objects that were already in the RECYCLEBIN.