Row Count from all tables

This script will count the rows of all tables from a schema (prod).

 conn prod/pass

CREATE TABLE TABLECNT (TABLE_NAME VARCHAR2(40 CHAR), NUM_ROWS INTEGER);

declare row_cnt number;
begin 
for x in (select table_name from user_tables order by table_name) 
loop 
 execute immediate 'select count(*) from ' ||x.table_name into row_cnt; 
 insert into tablecnt values(x.table_name,row_cnt); 
 end loop;
end;
/

select * from TABLECNT order by NUM_ROWS desc;

Q: How to Enable Archive Log?

When you enable this mode redo logs will be archived instead of overwritten. The archivelogs are stored in a seperate place usually can backed up regularly by your standard filesystem backup system. Archive logs are utilized by RMAN, Data Guard, Flashback and many others. If you are going to enable archivelog mode on a production database, I recommend shutting down the database and take a cold backup (Keeping a "final noarchivelog mode backup" which is to be a good and excepted practice).Enabling archive mode is simple, set the parameter LOG_ARCHIVE_DEST then connect to your database in mounted but closed mode (startup mount) and alter the database.

oracle@saturn:~$ echo $ORACLE_SID
DBTARGET

Connect as sysdba:
SQL> select LOG_MODE from v$database;
LOG_MODE
---------------------
NOARCHIVELOG

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 2074664 bytes
Variable Size 180357080 bytes
Database Buffers 423624704 bytes
Redo Buffers 6311936 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
SQL> conn tnahar
Enter password:
Connected.

SQL> select LOG_MODE from v$database;

LOG_MODE
------------------------
ARCHIVELOG

SQL> show parameter log_archive_dest

NAME TYPE VALUE
------------------------------------ ----------------- ------------------------------
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable


SQL> alter system set log_archive_dest_1='location=/dump/DBTARGET_ARCHIVELOG/';

SQL> archive log list;Database log mode Archive Mode
Automatic archival Enabled
Archive destination /dump/DBTARGET_ARCHIVELOG/
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7

SQL>exitDisable ARCHIVELOG Mode

Disabling archive mode is simple, connect to your database in mounted but closed mode (startup mount) and alter the database.

SQL> shutdown immediate;SQL> startup mountSQL> alter database noarchivelog; Database altered.SQL> alter database open; Database altered.

There are several system views that can provide you with information reguarding archives, such as:

V$DATABASE:

Identifies whether the database is in ARCHIVELOG or NOARCHIVELOG mode and whether MANUAL (archiving mode) has been specified.

V$ARCHIVED_LOG:

Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.

V$ARCHIVE_DEST:

Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations.

V$ARCHIVE_PROCESSES:

Displays information about the state of the various archive processes for an instance.

V$BACKUP_REDOLOG:

Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information.

V$LOG: Displays all redo log groups for the database and indicates which need to be archived.

V$LOG_HISTORY:

Contains log history information such as which logs have been archived and the SCN range for each archived log.

Basics about indexes in oracle

INDEX

Index provides a faster access path to table data.
 
Indexes are logically and physically independent of the data in the associated table. Being independent structures, they require storage space. 

You can create or drop an index without affecting the base tables, database, applications, or other indexes. 

The database automatically maintains indexes when you insert, update, and delete rows of the associated table. If you drop an index, all applications continue to work. However, access to previously indexed data might be slower.

Indexes in columns containing character data are based on the binary values of the characters in the database character set.

For a unique index, one rowid exists for each data value. 

For a nonunique index, the rowid is included in the key in sorted order, so nonunique indexes are sorted by the index key and rowid. Key values containing all nulls are not indexed, except for cluster indexes. Two rows can both contain all nulls without violating a unique index.

Oracle sometimes creates indexes for you. When you define a primary key constraint, Oracle will create an index for you. When you define a unique constraint, Oracle will also create an index for you.

Different Types of Index in Oracle

There are several types of index available for use in Oracle databases, the most common ones are B-tree (balanced tree) indexes, function-based indexes and bitmap indexes. B-tree indexes are more common in databases supporting OLTP systems and Bitmap indexes are more commonly used in data warehouses. 

1. Normal Index

By default, Oracle Database creates B-tree indexes/Normal index.

Ascending Index : default type
Descending Index: If in the sql, first column always order by descending.

2. BITMAP Index
- Used in data warehouses
- Compact; work best for columns with a small set of values
- Where repeating values arise, bitmap would be a good choice in OLAP.

3. PARTITIONED Index
Partitioned index consist of partitions containing an entry for each value that appears in the indexed column(s) of the table. Types -

Global indexes
Local indexes

4. FUNCTION based Index
Which are based on expressions. They enable you to construct queries that evaluate the value returned by an expression, which in turn may include built-in or user-defined functions.

5. DOMAIN Index
Application-specific index.

6. CLUSTER Index
B-tree cluster indexes: defined specifically for cluster
Hash cluster indexes:  defined specifically for a hash cluster

There are more indexes like -
Key compressed Index
Reverse key index : Mostly useful for Oracle RAC applications
Index Organized Table

SYNTAX

Create Index

Create B-tree or default Index on single column -
CREATE INDEX emp_deptno ON empinfo (deptno); 

CREATE INDEX emp_deptno ON empinfo (deptno) TABLESPACE INDX02_16K; 

Multi column -

CREATE INDEX emp_seniority ON empinfo (deptno, hire_date); 

Unique Index -

CREATE UNIQUE INDEX dname_uix ON dept (dname);

Alter Index

Rebuilding Index - 
ALTER INDEX LB_SUBMITTER REBUILD;

Moving Index to a new tablespace -
ALTER INDEX test REBUILD TABLESPACE INDX02_16K;  

Renaming Index -

ALTER INDEX SYS_C007109 RENAME TO LOGIN_ID; 

Quick-and-Dirty rebuild -

ALTER INDEX uniq_payroll_id COALESCE;

Dropping Index
DROP INDEX Index_name;  

 

Data dictionary Views for Indexes

ALL_INDEXES SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME
FROM all_indexes 

WHERE TABLE_NAME='LOGIN';

USER_INDEXES
SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME

FROM user_indexes  

WHERE TABLE_NAME='NY_IP';

USER_IND_COLUMNS SELECT * 

FROM user_ind_columns ic 

WHERE ic.table_name='LB_LOG_BOOK';

Linux Newbie

Q: How to zip file?

zip: nahar@mf01:~$ gzip df_prod_flexdoc_nahar_201001012.dmp
zip: nahar@mf01:~$ gunzip df_prod_flexdoc_nahar_20101012.zip

Q: How to zip folder?

zip -9 -r

Q: How to copy in current directory from Remote server?

scp remote_server_host_name:file_name.ext
nahar@debian:~$ scp remote_server_host_name:file_name.ext /home/nahar

Another example:
nahar@debian:~$ scp ms0001.thrp.net:/u/nahar/ds0003-ndstate-metadata1-20101104.dmp /home/nahar .


Q: How to find IP?

nahar@debian:~$ su
Password:
debian:/home/nahar# ifconfig

Q: How to change pass of oracle server?

passwd

Q: How to calculate file size?

du -sh
du -sh *.dmp

Q: How to move, copy file from one folder to another?

mv filename foldername/
or
cp old_file_name directory/new_file_name

Q: How to make unzip and untar folder?

tar -xvzf folder_name.tar.gz

Example-1:
file: tar -cvf 1_sylhet_boishak07_gram_picasa.tar sylhet_boishak07_gram_picasa
gzip 1_sylhet_boishak07_gram_picasa.tar

Example-2:
nahar@debian:~$ unzip oracle-script.zip
Archive: oracle-script.zip
replace 122.sh? [y]es, [n]o, [A]ll, [N]one, [r]ename: oracle
error: invalid response [o]
replace 122.sh? [y]es, [n]o, [A]ll, [N]one, [r]ename: r
new name: oracle
inflating: oracle
nahar@debian:~$

Another way -
1. To make ur that folder tar: tar -cvf new_folder_name.tar folder_name
2. To make your folder zip: gzip folder_name.tar"

STRAGG

This shows how to collect values from multiple rows into a single, comma delimited string. This is also known as "string aggregation".

I need to create this on "nddba" schema.

--------------------------- Comma Separated ----------------------------------------------
--========================================================

CREATE OR REPLACE TYPE nddba.stragg_type as object (
string varchar2 (4000),
static function ODCIAggregateInitialize
( sctx in out stragg_type )
return number ,
member function ODCIAggregateIterate
( self in out stragg_type ,
value in varchar2
) return number ,
member function ODCIAggregateTerminate
( self in stragg_type,
returnvalue out varchar2,
flags in number
) return number ,
member function ODCIAggregateMerge
( self in out stragg_type,
ctx2 in stragg_type
) return number
);
/


CREATE OR REPLACE TYPE body nddba.stragg_type
is
static function ODCIAggregateInitialize
( sctx in out stragg_type )
return number
is
begin
sctx := stragg_type( null ) ;
return ODCIConst.Success ;
end;
member function ODCIAggregateIterate
( self in out stragg_type ,
value in varchar2
) return number
is
begin
self.string := self.string || ',' || value ;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate
( self in stragg_type ,
returnvalue out varchar2 ,
flags in number
) return number
is
begin
returnValue := ltrim( self.string, ',' );
return ODCIConst.Success;
end;
member function ODCIAggregateMerge
( self in out stragg_type ,
ctx2 in stragg_type
) return number
is
begin
self.string := self.string || ctx2.string;
return ODCIConst.Success;
end;
end;
/

CREATE OR REPLACE FUNCTION nddba.stragg
(input varchar2 )
return varchar2
deterministic
parallel_enable
aggregate using stragg_type
;
/



-------------------------------- 'br/' Separated ----------------------------------------------
--=======================================================


Step-1: Create STRAGG Type

CREATE OR REPLACE TYPE ndstate7.stragg_type as object
(
string varchar2(4000),

static function ODCIAggregateInitialize
( sctx in out stragg_type )
return number ,

member function ODCIAggregateIterate
( self in out stragg_type ,
value in varchar2
) return number ,

member function ODCIAggregateTerminate
( self in stragg_type,
returnvalue out varchar2,
flags in number
) return number ,

member function ODCIAggregateMerge
( self in out stragg_type,
ctx2 in stragg_type
) return number
);
/


Step-2: BODY

CREATE OR REPLACE TYPE BODY stragg_type IS STATIC FUNCTION ODCIAggregateInitialize ( sctx IN OUT stragg_type )
RETURN NUMBER IS
BEGIN
sctx := stragg_type ( NULL );
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate ( self IN OUT stragg_type,value IN VARCHAR2 )
RETURN NUMBER IS
BEGIN
self.string := self.string || 'br/' || value;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate ( self IN stragg_type,returnvalue OUT VARCHAR2,flags IN NUMBER )
RETURN NUMBER IS
BEGIN
returnValue := ltrim ( self.string,'br/' );
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge ( self IN OUT stragg_type,ctx2 IN stragg_type )
RETURN NUMBER IS
BEGIN
self.string := self.string || ctx2.string;
RETURN ODCIConst.Success;
END;
END;
/




Step-3: Create STRAGG Function

CREATE OR REPLACE FUNCTION ndstate.stragg
( input varchar2 )
return varchar2
deterministic
parallel_enable
aggregate using stragg_type
;
/

VVI: Have to add <> with all br/, in the single-quote.

ORA-00054: resource busy and acquire with NOWAIT specified

Cause of the Problem:
Whenever you try to do any structural changes on a table oracle try to lock the table exclusively with NOWAIT option(this is in 10.2g while in 11g you can change the wait timeout). If oracle fails to lock the table exclusively then ORA-00054 will occur.

Solution of the Problem
In 10.2g you are limited to several choices to solve the problem. To avoid it-
-Re run the DDL at a later time when the database become idle.
-or, Kill the sessions that are preventing the exclusive lock.
-or, Prevent end user to connect to the database and then run the DDL.

Kill the sessions:
set long 30000
set lines 200 pages 200
col username format a20
col sess_id format a20
col mode_held format a20
col object format a30

SELECT oracle_username || ' (' || s.osuser || ')' username ,(s.sid || ',' || s.serial#) as sess_id
,s.STATUS "Session status"
,owner || '.' || object_name object,object_type
,decode( l.block,0,'Not Blocking',1,'Blocking', 2,'Global') status
,decode(v.locked_mode,0,'None',1,'Null',2,'Row-S (SS)',3,'Row-X (SX)',4,'Share',5,'S/Row-X (SSX)',6,'Exclusive',TO_CHAR(lmode)) mode_held
FROM v$locked_object v, dba_objects d, v$lock l, v$session s
WHERE v.object_id = d.object_id and v.object_id = l.id1 and v.session_id = s.sid
ORDER BY username, session_id;

SQL> alter system kill session '142, 232';

After killing all the locked sessions, I connected to that schema where the row lock arise and press commit.

set lines 200 pages 200
col ses_serial format a30

Also have to see-
SELECT a.sid||','||a.serial# ses_serial,a.username,a.osuser
,to_char(a.logon_time, 'hh24:mi dd/mm/yy') login_time
FROM v$session a,v$process b
WHERE a.username IS NOT NULL AND a.paddr=b.addr AND a.type='USER'
AND a.logon_time> = sysdate- 1/48 --last 30 min
Order by a.sid,a.username;

Kill from OS level
select vs.sid, vs.username, vs.osuser, vs.process fg_pid,
vp.spid bg_pid
from v$session vs, v$process vp
where vs.paddr = vp.addr and vs.sid=319
order by vs.sid;

SID ---------- USERNAME ----------OSUSER ----------FG_PID ----------BG_PID
---------- ------------------------------ ------------------------------ ------------------ -----------------------------
319 -------------------------------------------oracle -------------17314 --------------17314

bash-3.00$ kill -9 17314

Indexing SQLs for DBA

Q: How to check if indexes are used by an application or not?

select INDEX_NAME,INDEX_TYPE,TABLE_NAME 
from user_indexes 
where TABLE_NAME='LB_LOG_BOOK'; --SYS_C007158

To reset the values in the v$object_usage view, disable index monitoring and re-enable it:
ALTER INDEX LB_SUBMITTER NOMONITORING USAGE;
ALTER INDEX LB_SUBMITTER MONITORING USAGE;

SELECT table_name, index_name, monitoring, used FROM v$object_usage;

ANALYZE INDEX LB_SUBMITTER COMPUTE STATISTICS;


Q: Find out which Indexes are workable?

First of all, gather table statistics -
exec dbms_stats.gather_table_stats( ownname => 'PROD7',tabname => 'LB_LOG_BOOK', cascade => true);

Then "Index Usage Count" column will show the index usage -
select p.object_name "Object Name",p.operation "Operation",p.options "Option"
, count(p.object_name) "Index Usage Count"
from dba_hist_sql_plan p, dba_hist_sqlstat s, dba_indexes di
where p.object_owner = 'PROD7'and p.operation like '%INDEX%'
and p.sql_id = s.sql_id and p.object_name = di.index_name and di.table_name='LOGIN'
group by p.object_name, p.operation, p.options
order by 1,2,3;


Q: which column for which index?
 
select * from user_ind_columns ic where ic.table_name='LOGIN';



Indexing on Foreign Key Referenced Columns

Non indexed Foreign key can lock child table(s) when we need to delete from parent table.
This script will generate a log file from where we can find all referenced columns of a table (like-individual), and the "create index" script.

set pages 200 lines 200
SPOOL INDIVIDUAL_FK_INDEXING.sql

SELECT 'CREATE INDEX ' || a.table_name || '_' || c.column_name || '_I ON ' || a.table_name || '(' || c.column_name
|| ') ONLINE TABLESPACE INDX01_16K COMPUTE STATISTICS;'
FROM user_constraints a, user_constraints b, user_cons_columns c
WHERE a.r_constraint_name = b.constraint_name 
AND a.constraint_name = c.constraint_name
AND a.constraint_type='R' 
AND b.table_name='INDIVIDUAL';

SPOOL OFF;
 
We may need to change the index names if it crosses the maximum length for naming.
 

Finding the Non-Indexed Foreign Keys

COL table_name format A20 head 'TABLE_NAME'
COL constraint_name format A20 head 'CONSTRAINT_NAME'
COL table2 format A20 head 'TABLE_TO_BE_INDEXED'
COL column_name format A20 head 'COLUMN_TO_BE_INDEXED'


SELECT t.table_name,c.constraint_name,c.table_name table2,acc.column_name
FROM all_constraints t, all_constraints c, all_cons_columns acc
WHERE c.r_constraint_name = t.constraint_name
AND c.table_name =acc.table_name
AND c.constraint_name = acc.constraint_name
AND NOT EXISTS ( 
  SELECT '1' 
  FROM all_ind_columns aid
  WHERE aid.table_name = acc.table_name
  AND aid.column_name = acc.column_name
  )
AND t.table_name='CLIENT'
ORDER BY c.table_name; 


Partition Index rebuild - PL/SQL 

DECLARE
tab_name VARCHAR2(30);
stmt VARCHAR2(500);
sqlstr VARCHAR2(800);
TYPE TabCurTyp IS REF CURSOR;
ind_cursor TabCurTyp;
indx_name VARCHAR2(35);
part_name VARCHAR2(35);

BEGIN
tab_name := UPPER('&tab_name');
sqlstr:='SELECT distinct uip.index_name, uip.partition_name
FROM USER_IND_PARTITIONS uip, USER_TAB_PARTITIONS utp, USER_INDEXES ui
WHERE uip.partition_name= utp.partition_name
AND ui.index_name = uip.index_name
AND ui.table_name = utp.table_name
AND utp.table_name LIKE '''||tab_name||'%''';

OPEN ind_cursor FOR sqlstr;
LOOP
FETCH ind_cursor INTO indx_name,part_name;
EXIT WHEN ind_cursor%NOTFOUND;
stmt := 'ALTER INDEX ' || indx_name || ' REBUILD PARTITION ' ||part_name ||' ONLINE';
DBMS_OUTPUT.PUT_LINE(stmt);
EXECUTE IMMEDIATE stmt;
END LOOP;
END;
/

Here you only have to put the table name.


Single Syntax:
ALTER INDEX index_nm REBUILD PARTITION partition_name;

How to make a Read Only Database User ?

Q: How to prevent any kind of DDL from any schema?

To prevent any kind of DDLs, this trigger will be stored on proper schema always and if any DDL issueed in the presence of the trigger, it will give us an error message.

CREATE OR REPLACE TRIGGER ddl_restrict_trigger
BEFORE ALTER OR CREATE OR DROP OR TRUNCATE
ON SCHEMA
BEGIN
RAISE_APPLICATION_ERROR(-20001,' You are not authorized to perform DDL. Please contact DBA Team.');
END;
/
Q: How to prevent any kind of DML from any table?

You can restrict the user on DML activity on tables other than select with a DML trigger. The following trigger won't allow to insert or delete or update on the table test.

create or replace trigger t1
before insert or update or delete on test for each row
begin
if inserting or updating or deleting then
raise_application_error(-20001,'Not Allowed');
end if;
end;

Q: STOP DML & DDL by making Sequence.

All tables are in nddba4@jupiter. I want to restrict the developers on DML activity on the data of nddba4. So am making another user nddba5. In nddba5, am making all the synonyms of the tables of nddba4 . Now giving the devels access on nddba5. Here they only see the synonyms.

--fake user nddba5@jupiter, real user nddba4@jupiter
create user nddba5 identified by nddba5 default tablespace states;
grant connect, create synonym to nddba5;
grant select any table to nddba5;
grant select any dictionary to nddba5;

--at nddba5@jupiter, run this

SET SERVEROUT ON;
DECLARE
stmt varchar2(300);

BEGIN
FOR dr IN (select table_name from dba_tables where owner='NDDBA4')
LOOP

stmt := 'CREATE SYNONYM ' || dr.table_name || ' FOR NDDBA4.' || dr.table_name;
DBMS_OUTPUT.PUT_LINE(stmt);
EXECUTE IMMEDIATE stmt;
END LOOP;
END;
/

Notes

1. Constraints will not break when you rename tables.

2. You can back deleted datafiles but there is no guaranty that the object stored on the deleted datafile are available when it brought back

3. You can recover dropped tables by using FLASHBACK TABLE table_name TO BEFORE DROP;

4. Moving table to new tablespace will make all the indexes on the table unusable and prevent any DML activity on the table. Then you have to rebuild index.

Update

Q: Update an existing table by adding a column, with a specific order?

I had a table(nd_osp_outcome) with data like -

OSP_ID ID DESCRIPTION RESPONSIBILITY

--- ------ ----------- ---------------

101 100 ddd rr

101 101 ddd rr

250 250 ddd rr

250 251 ddd rx

250 252 ddd rp

3100 3100 ddd rt

3100 3101 ddd ry

Have to update this like -

OSP_ID ID DESCRIPTION RESPONSIBILITY IDX

--- ------ ----------- --------------- ------

101 100 ddd rr 0

101 101 ddd rr 1

250 250 ddd rr 0

250 251 ddd rx 1

250 252 ddd rx 2

3100 3100 ddd rt 0

3100 3101 ddd ry 1

--

So, I am creating a temp table with the idx column -

CREATE TABLE temp_nd_osp_outcome AS

SELECT osp_id,id,rank()over ( partition by osp_id order by id) -1 idx

FROM nd_osp_outcome;

Then updating the existing table -

alter table nd_osp_outcome add idx number (10,0);

UPDATE nd_osp_outcome x

SET x.idx= (SELECT idx FROM temp_nd_osp_outcome y WHERE x.osp_id=y.osp_id AND x.ID=y.ID)

WHERE EXISTS (SELECT 1 FROM temp_nd_osp_outcome y WHERE x.osp_id=y.osp_id AND x.ID=y.ID);

select * from nd_osp_outcome order by osp_id,idx;

Q: Update records in one table based on values in another table?

alter table individual add constraint u_clnt_num_nd unique(CLNT_NUMBER_ND);

update (
select qer.INDIVIDUAL_ID, indv.id
from leg_qer qer, individual indv
where qer.client_number_nd = indv.clnt_number_nd )
set INDIVIDUAL_ID=id;

Q: How to add a Unique column on a table?

ALTER TABLE PROVIDER_SERVICE1 ADD PS_IDENTIFIER NUMBER(10);
UPDATE PROVIDER_SERVICE1 SET PS_IDENTIFIER=HIBERNATE_SEQUENCE.nextval;

Convert Varchar to Clob

desc feedback;

alter table feedback add (description2 clob);

update feedback set description2 = description;

alter table feedback drop column description;

alter table feedback rename column description2 to description;

desc feedback;

Duplicate Row

Q: How to delete multiple duplicate row?

--Find out no of duplicate rows
select q.qtxt_identifier,count(*) as cnt from question_text q
group by q.qtxt_identifier having count(*)>1 order by q.qtxt_identifier; --54 duplicate rows found

select sum(cnt) from( select q.qtxt_identifier,count(*) as cnt from question_text q
group by q.qtxt_identifier having count(*)>1 order by q.qtxt_identifier)A; --113

--So, I have to delete 113-54=59 rows
--Delete duplicate rows
DELETE FROM question_text WHERE rowid NOT IN
(SELECT max(rowid) FROM question_text GROUP BY qtxt_identifier); --59 deleted



Q: I have 2 same rows in a table. All fields are same. I have to keep 1 and delete 1.

select * from client c where c.clnt_number =332096; --2 same rows returns
DELETE FROM client
WHERE rowid =
(SELECT MIN(rowid)
FROM client
WHERE clnt_number =332096);
commit;

select * from client c where c.clnt_number =332096; --1 row returns

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