Oracle works...

Switch to new tempoary tablespace

2011-12-14T10:06:00.000-08:00

Create new temporary tablespace CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/app/db/temp02.dbf' SIZE 20M EXTENT MANAGEMENT LOCAL ; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2; DROP TABLESPACE temp

deleting / closing trace files

2011-11-09T11:45:00.000-08:00

Sometimes the trace files in oracle does not close so using oradebug you can close a trace file identifying the process id having a lock on the trace file, use the following commands below. SQL> oradebug setospid 88874 Statement processed. SQL> oradebug close_trace Statement processed.

Listener log file rename/reset/backup/zip it

2011-08-16T06:28:00.000-07:00

Listener log file backup and zip. Start the listener control utility lsnrctl set current_listener listener_ora show log_status set log_status off ## here you rename the logfile set log_status on show log_status ## Later you can zip the renamed logfile

reset mysql root password

2011-07-05T11:59:00.000-07:00

1) kill mysqld without -9 option 2) start mysqld with --skip-grant-tables option 3) mysql --user=root --port=3306 --host=server1 4) use mysql; update user set password=PASSWORD("letmein") where User='root'; flush privileges; quit OR mysqladmin -u root flush-privileges password "letmein" 5) start mysqld with your regular option

Enabling ODM for Oracle

2011-06-17T07:24:00.000-07:00

Symantec Reference for ODM config for Oracle 10g This example is for 64 bit Oracle 11g * Oracle libraries location oracle@serv1> cd $ORACLE_HOME/lib * Saving old link oracle@serv1> mv libodm11.so libodm11.so.orig * Linking the VERITAS ODM library to Oracle 11g Example for 64 bit Version oracle@serv1> ln -s /usr/lib/amd64/libodm.so libodm11.so oracle@serv1> ls -l $ORACLE_HOME/lib/libodm11.

Reconfiguring the Enterprise Manager service ports

2011-06-15T10:40:00.000-07:00

$ emca -reconfig ports -DBCONTROL_HTTP_PORT 5502 $ emca -reconfig ports -AGENT_PORT 1851 $ emca -reconfig ports -DBCONTROL_HTTP_PORT 5502 $ emca -reconfig ports -RMI_PORT 5520 $ emca -reconfig ports -JMS_PORT 5521 $ emca -reconfig ports -DBCONTROL_HTTP_PORT 5510 -AGENT_PORT 1851 -RMI_PORT 5520

Oracle Enterprise Manager and Real Application Cluster

2011-06-15T10:11:00.000-07:00

Oracle Enterprise Manager and Real Application Cluster some useful commands Status of the services: crs_stat -t Stop the instance: srvctl stop instance –d mydb –i mydb1 –o immediate Start the instance: srvctl start instance –d mydb –i mydb2 Find cluster name: CRS_HOME/bin/cemutlo -n Script to see the services in formated: crs_stat -l | \ awk ' { if ( $0 ~ /NAME/ ) printf "%-45s", $1 ;

Confiure TNS FAILOVER

2011-06-13T09:03:00.000-07:00

Server1 ~~~~~~~~~ alter system set local_listener=listener1 **tnsnames.ora LISTENER1 = (ADDRESS = (PROTOCOL = TCP)(HOST = server1 )(PORT = 1521 )) exec dbms_service.CREATE_SERVICE(SERVICE_NAME=>'dbserv', NETWORK_NAME=>'dbserv') **Create the service on database startup CREATE OR REPLACE TRIGGER SYS.startup_trg after startup on database BEGIN DBMS_SERVICE.START_SERVICE('dbserv'); END; /

Using Multiple text based window session with Screen

2011-06-03T07:15:00.000-07:00

Screen Quickie CTRL-a ? Help Screen CTRL-a ^a Switch between previous Screen/Current Screen CTRL-a " List of Screen use up/down arrow and enter to select CTRL-a c Create a new window CTRL-a spacebar Go to next window CTRL-a backspace or del Go to previous window CTRL-a 2 Go to window 2 Using Screen Options: -a Force all capabilities into each window's termcap. -A -[r|R] Adapt

Oracle Enterprise Manager dbconsole slow

2011-05-21T10:31:00.000-07:00

748251.1 OEM ISSUE exec dbms_stats.DELETE_TABLE_STATS('SYS','X$KCCRSR'); exec dbms_stats.LOCK_TABLE_STATS('SYS','X$KCCRSR'); exec dbms_stats.unLOCK_TABLE_STATS('SYS','X$KCCRSR'); exec dbms_stats.gather_table_stats('SYS','X$KCCRSR'); alter system flush shared_pool;

Drop Enterprise Manager repository manually

2011-05-15T10:27:00.000-07:00

Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management objects: EXEC sysman.emd_maintenance.remove_em_dbms_jobs; EXEC sysman.setEMUserContext('',5); drop user sysman cascade; drop role MGMT_USER; drop user MGMT_VIEW cascade; drop public synonym MGMT_TARGET_BLACKOUTS; drop public synonym SETEMVIEWUSERCONTEXT; Deconfig the dbcontrol emca -deconfig dbcontrol db -repos

Oracle Enterprise Manager 11g Installation.

2011-05-15T09:40:00.000-07:00

Configure your XWindows system if you are installing on Unix/Linux export DISPLAY=your.machine.ip:0.0 1. Install WebLogic Server export JAVA_HOME=/path/to/jdk-1.6 $JAVA_HOME/bin/java -d64 -jar wls1032_generic.jar 2. Setup Database for the Grid Control alter system set session_cached_cursors=250 scope=spfile ; alter system set log_buffer=10489999 scope=spfile ; alter system set

Enterprise Manager dbcontrol for RAC

2011-04-25T10:34:00.000-07:00

emca -config dbcontrol db -cluster -EM_NODE node01 -EM_SID_LIST mydb

Oracle RAC useful commands

2011-04-10T09:36:00.000-07:00

1. Find the Cluster name of the RAC $CRS_HOME/bin/cemutlo -n

Oracle Metadata - Script Creation

2011-04-03T09:33:00.000-07:00

1. Create users tablespace script select dbms_metadata.get_ddl('TABLESPACE','USERS' ) from dual ; 2. Create user creation script select dbms_metadata.get_ddl('USER','SCOTT' ) from dual ; 3. User privs --SYSTEM PRIVS select 'GRANT ' ||PRIVILEGE ||' TO XXX ; ' from dba_sys_privs where grantee='AAA'; --ROLE PRIVS select 'GRANT '||GRANTED_ROLE ||' TO XXX ; ' from dba_role_privs where grantee='

bash shell programing

2011-03-21T08:46:00.000-07:00

bash 3.2 bash shell builtin commands bash reserved words . ! : [[ [ ]] alias { bg } bind case break do builtin done

Using ssh on *nix

2011-03-03T09:24:00.000-08:00

1. Executing sql script on remote host serv1> cat local.sql | ssh oracle@serv2 '. ~/.profile; cat|sqlplus -s / ' 2>/dev/null 2. Generating ssh kesy serv1> ssh-keygen -t dsa # the public and private keys will be created in the ~/.ssh directory 3. Configuring autologin (ssh) to the remote server serv1> cd ~/.ssh ; cat id_dsa.pub | ssh oracle@serv2 'cat >> ~/.ssh/authorized_keys' #This is

Migrate Dictionary managed Tablespaces to locally managed

2011-02-15T08:32:00.000-08:00

Users tablespace should be online. SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('USERS'); Temporary Tablespaces cannot be migrated to locally managed. Special for SYSTEM tablespace SQL> shutdown immediate SQL> startup restrict SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp; SQL> alter tablespace SYSAUX offline; SQL> alter tablespace USERS read only; -- and other users

How to generate XML file and save in Oracle Directory

2011-02-01T07:42:00.000-08:00

Method 1 ~~~~~~~~~ declare refc sys_refcursor; xdoc DBMS_XMLDOM.DOMDocument; begin open refc for select rownum from dual connect by level < 10 ; xdoc := DBMS_XMLDOM.NewDOMDocument(xmltype( refc )); DBMS_XMLDOM.WRITETOFILE(xdoc, 'XML_DOCS/xml1.xml'); end; Method 2 ~~~~~~~~~ declare refc sys_refcursor; BEGIN open refc FOR SELECT rownum FROM dual

Using Crontab Quick Reference

2011-01-27T08:44:00.000-08:00

Crontab commands List the jobs in crontab crontab -l Edit the jobs in crontab crontab -e Delete all the jobs in crontab **carefully use this command** crontab -r Always save the jobs in a file crontab.file crontab -l > crontab.file Edit jobs in crontab.file and create jobs using this file crontab < crontab.file Crontab file format * * * * * "command to be

Useful unix/Linux Commands

2011-01-18T09:29:00.000-08:00

tar cvf - foodir | gzip -9c > foo.tar.gz gunzip -c foo.tar.gz | tar xvf -

Wait Events in Oracle

2011-01-11T13:23:00.000-08:00

Wait Event: db file scattered read Wait Class: User I/O Too many user are requesting data blocks at once. Too many full table scans at the time. Solutions: Reducing parameter multi_block_read_count may help Using faster disks and IO Distribution Avoiding ful table scans Placing small table in Cache. Wait Event: db file sequential read Wait Class: User I/O Index scans are occuring and

Online Table Re-defination of a table

2011-01-11T07:32:00.000-08:00

--Example 1 -- Create a test table create table sstmp AS SELECT * from dba_objects where object_id is not null ; --Add a primary key alter table sstmp add constraint sstmp_pk primary key (object_id) ; -- Check table can be redefined EXEC Dbms_Redefinition.Can_Redef_Table('SANJ', 'SSTMP') --Create a temporary staging table create table sstmp2 as select * from sstmp ; --Start the redefination

Renaming Tablespace

2010-12-23T13:46:00.000-08:00

alter tablespace users1 rename to users

Adding ssh keys on server for automatic login

2010-12-10T13:40:00.000-08:00

Generate rsa key on local server ssh-keygen -t rsa Adding the public key on remoteServer for auto login cat id_dsa.pub ssh oracle@remoteServer 'cat >> ~/.ssh/authorized_keys'

Running AWR Report from Command line SQLPlus

2010-11-08T13:21:00.000-08:00

cd $ORACLE_HOME/rdbms/admin sqlplus @awrrpt.sql

Changing attributes of a partitioned table

2010-10-15T13:16:00.000-07:00

Changing Attributes of a partitioned table Modifying Table Attribute alter table sales.sales_detail modify default attributes tablespace sales_data ; Modifying Table partition Attribute alter table sales.sales_detail modify default attributes for partition Sales_2010_Jan tablespace sales_data ;

HTML report from sqlplus

2010-09-10T13:11:00.000-07:00

Generating HTML Report from SQLPlus. SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON - HEAD "< TITLE>Department Report< /TITLE> - < STYLE type='text/css'> - < !-- BODY {background: #FFFFC6} --> - < /STYLE>" - BODY "TEXT='#EEFFGG'" - TABLE "WIDTH='90%' BORDER='5'"

Sending HTML mail from Unix

2010-08-01T12:59:00.000-07:00

Sending HTML mail from Unix #Script #!/usr/bin/ksh MAILER=/usr/lib/sendmail VERSION=1.0 { ( cat << EOF From: Sanjay To: oracleDBA@myCompany.net Subject: test HTML output from Unix X-Mailer: htmlmail $VERSION Mime-Version: 1.0 Content-Type: text/html; charset=US-ASCII < center > < h1> Test Center Report < /h1> < /center > EOF cat t.log ) } eval $MAILER oracleDBA@myCompany.net ##End of Script

Getting Metadata for Oracle Objects

2010-07-10T12:46:00.000-07:00

Getting User Creation select dbms_metadata.get_ddl('USER','SANJ') from dual; Getting Tablespace Creation select dbms_metadata.get_ddl('TABLESPACE','SYSTEM' ) from dual ; Getting Table Creation select dbms_metadata.get_ddl('TABLE','SOME_SUMMARY','PAYROL' ) from dual ;

Hostname Change in Redhat

2010-06-08T15:17:00.000-07:00

cd /etc/sysconfig vi network Change HOSTNAME in network file vi /etc/hostsChange or add host in the hosts file

Creating Database on Windows system

2010-06-08T09:04:00.000-07:00

Creating Database on Windows system Create directories if not present mkdir C:\app\Oracle\admin\test11g\adump mkdir C:\app\Oracle\admin\test11g\dpdump mkdir C:\app\Oracle\admin\test11g\pfile mkdir C:\app\Oracle\cfgtoollogs\dbca\test11g mkdir C:\app\Oracle\flash_recovery_area mkdir C:\app\Oracle\oradata\test11g mkdir C:\app\Oracle\product\11.1.0\db_1\database Set Env Variables set ORACLE_SID=

ORA-00845: MEMORY_TARGET not supported on this system - Linux Servers

2010-05-06T10:52:00.000-07:00

Increase the size of /dev/shm Edit the /etc/fstab tmpfs /dev/shm tmpfs defaults,size=1024M 0 0 ... OR tmpfs /dev/shm tmpfs defaults,size=1G 0 0 M -> Megabytes G -> Gigabytes mount -o remount /dev/shm man mount for help

Sending email with UTL_SMTP

2010-04-12T06:50:00.000-07:00

Using UTL_SMTP to send email from PL/SQL DECLARE c utl_smtp.connection; PROCEDURE send_header(name VARCHAR2, header VARCHAR2) AS BEGIN utl_smtp.write_data(c,name ||':'|| header || UTL_TCP.CRLF); END; BEGIN c := utl_smtp.open_connection('myserver.local'); utl_smtp.helo(c, 'myserver.local'); utl_smtp.mail(c, 'sanjay@myserver.local'); utl_smtp.rcpt(c, 'sanjay@myserver.local');

Managing Scheduler Jobs

2010-03-23T13:26:00.000-07:00

Creating, viewing and dropping Scheduler Jobs --View jobs created by SYSTEM user select * from dba_scheduler_jobs where owner='SYSTEM' ; --Create a Job BEGIN dbms_scheduler.create_job( job_name => 'TEST', schedule_name =>'sys.MAINTENANCE_WINDOW_GROUP', job_type =>'PLSQL_BLOCK', job_action=> 'begin dbms_stats.gather_DATABASE_stats( options=> ''GATHER AUTO'');end; ', enabled => TRUE,

Managing Optimizer Statistics

2010-03-20T12:23:00.000-07:00

Create scheduler jobs Database Statistics Gathers statistics for all objects in the database. begin dbms_stats.gather_database_stats(options=> 'GATHER AUTO'); end;Dictionary Statistics Objects in 'SYS', 'SYSTEM' and all non-user defined schemas. begin dbms_stats.gather_dictionary_stats(options=> 'GATHER AUTO'); end;Fixed Objects Statistics In-memory structures/variables of the RDBMS that are

Oracle 11g R1 11.1.0.6 patch installation Single Instance

2010-03-20T08:59:00.000-07:00

Oracle 11g R1 11.1.0.6 patch installation Single InstanceUpgrade from 11.1.0.6 to 11.1.0.7 ***set ORACLE_HOME and ORACLE_SID Stop All Services for a Single Instance Installation OracleServiceSIDOracleDBConsoleSIDOracle...TNSListenerLISTNER..Note:The patch installer made me stop "Distriuted Transaction Coordinator" also which isone of the Service in Windows.***Backup your system Oracle Inventory

ORA-00600: internal error code, arguments: [prshntcb-1], [990], ...

2010-03-13T12:46:00.000-08:00

ORA-00600: internal error code, arguments: [prshntcb-1], [990], ...This error is cause by INCORRECT HINT SYNTAX for sql statemtments. Incorrect PARALLEL Hint in the sql.Encountered this error in Version 11.1.0.6 & 11.1.0.7. Apply patch 8705594 if available to your system.See Document ID:975081.1ORA-600

ORA-28056: Writing audit records to Windows Event Log failed

2010-03-03T11:49:00.000-08:00

ORA-28056: Writing audit records to Windows Event Log failedc:\> sqlplus / as sysdbaSQL*Plus: Release 11.1.0.7.0 - Production on .......Copyright (c) 1982, 2008, Oracle. All rights reserved.ERROR:ORA-28056: Writing audit records to Windows Event Log failedOSD-1627319533: Message 1627319533 not found; product=RDBMS; facility=SOSDO/S-Error: (OS 1502) The event log file is full.ORA-28056: Writing

RESIZE REDO LOGFILE

2010-02-10T12:09:00.000-08:00

RESIZE REDO LOGFILE Resize Redo Logfile Alter database add logfile group 4 'D:\Oradata\mydb\REDO04.log' size 1000M reuse;Alter database add logfile group 5 'D:\Oradata\mydb\REDO05.log' size 1000M reuse;Alter database add logfile group 6 'D:\Oradata\mydb\REDO06.log' size 1000M reuse;alter system switch logfile ;alter system switch logfile ;alter system switch logfile ;alter system checkpoint ;

Configuring 9i Database with 11g LISTENER

2010-02-02T06:13:00.000-08:00

Configuring 9i Database To Listen to 11g LISTENER Add SID_LIST_LISTENER entry in the listener.ora file with the respective ORACLE_HOME, SID_NAME and SERVICE_NAME # listener.ora Network Configuration File: D:\app\ORACLE\product\11.1.0\mydb\network\admin\listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = orcl) (ORACLE_HOME = D:\oracle\

Multivolume Export and Import in 9i,10g and 11g

2010-01-16T06:43:00.000-08:00

Multivolume Export and Import in 9i,10g and 11gUsing FILESIZE=10m for 10 Megabytesexp scott/tiger file=part1,part2,part3 filesize=10m 10,485,760 part1.DMP 10,485,760 part2.DMP 843,776 part3.DMPUsing FILESIZE=10240k for 10240 Kilobytesexp scott/tiger file=part1,part2,part3 filesize=10240k 10,485,760 part1.DMP 10,485,760 part2.DMP 843,776 part3.DMPUsing FILESIZE

Data Pump

2010-01-05T06:27:00.000-08:00

PLSQL for DATA Pump Job in Job Scheduler. declareh1 NUMBER; dmp_file varchar2(100) ; log_file varchar2(100) ;begin select 'QA_SCOTT_'||to_char(sysdate,'yyyymmdd_hhmm') ||'_%U.dmp' , 'QA_SCOTT_'||to_char(sysdate,'yyyymmdd_hhmm') ||'.log' into dmp_file, log_file from dual ; h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'SCHEMA', job_name => 'SCOTTEXP',

MONITORING OBJECTS

2009-12-06T07:25:00.000-08:00

MONITORING INDEX--Turn ON monitoringalter index PK_LOCATION_CODE monitoring usage ;--Run various processes using the table related to the index.--Turn OFF monitoringalter index PK_LOCATION_CODE nomonitoring usage ;--Query to see if the index is being used. SELECT table_name, index_name, monitoring, used, start_monitoring, end_monitoringFROM v$object_usageWHERE

SWITCH TO A NEW UNDO TABLESPACE

2009-10-19T07:47:00.000-07:00

SWITCH TO A NEW UNDO TABLESPACE AND DROP OLD UNDO TABLESPACEsqlplus "/ as sysdba"SQL> show parameter undo_tablespaceNAME TYPE VALUE------------------------------------ ----------- ------------------------------undo_tablespace string UNDOTBS1SQL> create undo tablespace undo2 2 datafile 'c:\oracle\oradata\ORCL\undo2.dbf' 3 size

CONFIGURE APEX 3.0 ON ORACLE 11g

2009-10-11T07:07:00.000-07:00

CONFIGURE APEX 3.0 ON ORACLE 11g1) cd $ORACLE_HOME/apex2) sqlplus / as sysdba 3) @apxconf4) Enter Admin password5) Alter user anonymous account unlock;CONFIGURE the HTTP port 1) Connect as sys and CALL dbms_xdb.setHttpPort(80) ; 2) ALTER SYSTEM REGISTER ; OTHERS1) Look of the port for HTTP SELECT dbms_xdb.getHttpPort FROM DUAL ;OR 2) lsnrctl status

RETURNING Clause with BULK COLLECT

2009-10-05T09:34:00.000-07:00

DECLARE TYPE Table_Emp_t IS TABLE OF Emp%ROWTYPE; Emp_Pltbl Table_Emp_t;BEGIN DELETE FROM Emp2; COMMIT; DELETE FROM Emp RETURNING Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno BULK COLLECT INTO Emp_Pltbl; ROLLBACK; Dbms_Output.Put_Line(Emp_Pltbl.COUNT); FORALL i IN 1 .. Emp_Pltbl.COUNT INSERT INTO Emp2 VALUES Emp_Pltbl (i); COMMIT;END;/

CONNECT BY in Oracle 9i and 10g

2009-08-07T09:53:00.000-07:00

CONNECT BY in Oracle 9i and 10g SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle9i Enterprise Edition Release 9.2.0.8.0 - ProductionPL/SQL Release 9.2.0.8.0 - ProductionCORE 9.2.0.8.0 ProductionTNS for 32-bit Windows: Version 9.2.0.8.0 - ProductionNLSRTL Version 9.2.0.8.0 - Production5 rows selected.SQL> -- In Oracle 9i Expecting 4

RMAN Recovery

2009-08-03T09:46:00.000-07:00

Complete recovery run{shutdown immediate;startup mount;restore database;recover database;alter database;}Incomplete recovery until time run{shutdown abort; # Or shutdown immediate startup mount;set until time "to_date('06-AUG-2009 14:22:33' , 'dd-MON-yyyy hh24:mi:ss')";restore database;recover database;alter database open resetlogs;}Incomplete recovery until scnrun{shutdown abort; # Or shutdown

Renaming Datafile or Moving Datafile

2009-07-26T05:59:00.000-07:00

Renaming Datafile or Moving Datafile involving non-system tablespace related.1) Taking Tablespace offline method.SQL> create tablespace test datafile 'c:\test.dbf' size 1m ;Tablespace created.SQL> select name from v$datafile ;NAME--------------------------------------------------C:\ORACLEXE\ORADATA\XE\SYSTEM.DBFC:\ORACLEXE\ORADATA\XE\UNDO.DBFC:\ORACLEXE\ORADATA\XE\SYSAUX.DBFC:\ORACLEXE\ORADATA\XE

RMAN Scripts

2009-07-08T11:07:00.000-07:00

Some RMAN Backup ScriptsORACLE 10g -Full Backup Online with Archive Loggging~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~CONFIGURE RETENTION POLICY TO REDUNDANCY 7;configure channel device type disk format 'c:\oracle\local\RMAN\RMAN_BKP_%d_DB_%u_%s_%p';CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK

CREATING USER CONTEXT

2009-07-07T18:37:00.000-07:00

CREATING USER CONTEXTcreate or replace context emp_context using sys.dbms_session / exec sys.dbms_session.set_context('emp_context', 'name_var', 'SCOTT')/ select sys_context('emp_context', 'name_var') name_var from dual/ create view scott_emp as select * from emp where ename = sys_context('emp_context', 'name_var')/select * from scott_emp /CREATING A PACKAGE FOR MANAGING CONTEXT.create or

STRING TO ROWS

2009-06-28T19:09:00.000-07:00

with t1 as (select level l from dual connect by level <= length(','||'SCOTT,ALLEN,KING')-length(replace('SCOTT,ALLEN,KING',','))), t2 as (select 'SCOTT,ALLEN,KING'||',' c from dual)select regexp_substr(','||c,'[^,]+',1,l) dyn_empfrom t1,t2/DYN_EMP------------------SCOTTALLENKING

PASSING ARRAY TO PROCEDURE

2009-06-27T19:21:00.000-07:00

PASSING ARRAY TO PROCEDURESQL> create or replace PROCEDURE my_proc( 2 p_variable IN sys.ODCIVarchar2List, 3 p_cursor OUT SYS_REFCURSOR) AS 4 BEGIN 5 for r in p_variable.first..p_variable.last loop 6 dbms_output.put(r); 7 dbms_output.put_line('=>'||p_variable(r)); 8 end loop; 9 10 OPEN p_cursor FOR 11 select * from

ORA-00600: internal error code, arguments: [4194]

2009-06-22T11:31:00.000-07:00

Oracle version 9.2.0.8ORACLE 600 ERROR~~~~~~~~~~~~~~~~ORA-01595: error freeing extent (6) of rollback segment (2))ORA-00607: Internal error occurred while making a change to a data blockORA-00600: internal error code, arguments: [4194], [6], [6], [], [], [], [], []Resolved using Recreation of UNDO tablespace UNDO Tablespace Creation and Switching of Undo Tablespace -- Create a new undo tablespace

ROWS TO COLUMN

2009-06-21T18:34:00.000-07:00

STRING AGGREGATION OR ROWS TO COLUMN~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~USING XMLAGG~~~~~~~~~~~~SELECT deptno ,TRIM (XMLAGG(XMLELEMENT(a,ename||',').extract('//text()'))) col_name FROM empGROUP BY deptno/ DEPTNO COL_NAME---------- ------------------------------------------------- 10 CLARK,KING,MILLER, 20 SMITH,FORD,ADAMS,SCOTT,JONES, 30 ALLEN,BLAKE,MARTIN,TURNER

WHERE CLAUSE Queries

2009-06-18T17:47:00.000-07:00

WHERE CLAUSE Queries1) Value Entered is two or three name 'NAME1','NAME2','NAME3', then show records matching the names2) Value Entered is one name 'NAME1', then show record matching the name3) Value Entered is blank, then show all recordsA) Handles case 1 and 2select * from empwhere regexp_like(q'[&nm]',ename) ;Enter value for nm: 'ADAMS','KING','KINGDOM' EMPNO ENAME JOB

SYS_CONTEXT options

2009-06-16T08:33:00.000-07:00

SYS_CONTEXTSELECT SYS_CONTEXT('USERENV', 'TERMINAL') terminal, SYS_CONTEXT('USERENV', 'LANGUAGE') LANGUAGE, SYS_CONTEXT('USERENV', 'SESSIONID') sessionid, SYS_CONTEXT('USERENV', 'INSTANCE') instance, SYS_CONTEXT('USERENV', 'ENTRYID') entryid, SYS_CONTEXT('USERENV', 'ISDBA') isdba, SYS_CONTEXT('USERENV', 'NLS_TERRITORY') nls_territory, SYS_CONTEXT('USERENV

ORACLE regexp_like, regexp_instr, regexp_replace, regexp_substr, regexp_count

2009-06-15T06:27:00.000-07:00

Function Name Description~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~REGEXP_LIKE Similar to the LIKE operator, but performs regular expression matching instead of simple pattern matchingREGEXP_INSTR Searches for a given string for a regular expression pattern and returns the position were the match is foundREGEXP_REPLACE Searches for a

COUNT ALL ROWS IN A SCHEMA

2009-06-11T08:22:00.001-07:00

COUNT ALL THE ROWS IN A SCHEMA SELECT table_name, CAST(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) kount from '||owner||'.' || table_name)), '/ROWSET/ROW/KOUNT') AS NUMBER) COUNT FROM all_tables WHERE owner = 'SCOTT' / TABLE_NAME COUNT ------------------------------ ---------- DEPT 4 BONUS

ALL ABOUT NULLS AND NULL VALUE

2009-06-08T06:30:00.000-07:00

ALL ABOUT NULLS AND NULL VALUESQL> --Displaying NULL ValuesSQL> select ename, sal,comm from emp ;ENAME SAL COMM ---------- ---------- -------- SMITH 800

DYNAMIC IN CLAUSE

2009-06-05T17:44:00.000-07:00

DYNAMIC IN CLAUSEUSING LIKEdeclare myvar varchar2(30):='SCOTT,ALLEN,KINGDOM' ; v_count number:=0;BEGINselect count(*) into v_countfrom emp where ','||myvar||',' LIKE '%,'||ename||',%' ;dbms_output.put_line('V_COUNT is :'|| v_count);end;/ V_COUNT is :2 ---Select exampleselect *from emp where ','||:myvar||',' LIKE '%,'||ename||',%' / EMPNO ENAME JOB MGR HIREDATE

XML and SQL

2009-06-01T11:55:00.000-07:00

1. xmlelementselect xmlelement("name",ename) from emp/XMLELEMENT("NAME",ENAME)--------------------------SMITHALLEN...2. Insert xml Datacreate table pages( id number, content xmltype)/INSERT INTO pages VALUES(1, xmltype.createxml( ' Oracle 10g Oracle Database Administration '))/select EXTRACTVALUE(

Install Oracle 10g with ASM Storage

2009-05-15T20:55:00.000-07:00

Windows --Add CSS Service localconfig add--Create ASM Disk using asmtool asmtool -create E:\ASM\disk1 1024asmtool -create E:\ASM\disk2 1024asmtool -create E:\ASM\disk3 1024--Create ASM instance --init+ASM.ora file INSTANCE_TYPE=ASM_ASM_ALLOW_ONLY_RAW_DISKS = FALSEDB_UNIQUE_NAME = +ASMASM_DISKSTRING ='C:\ASM\*'LARGE_POOL_SIZE = 16MBACKGROUND_DUMP_DEST ='c:\oracle\admin\+ASM\bdump'USER_DUMP_DEST =

HIRERCHIAL QUERIES COLLECTION

2009-05-15T18:55:00.000-07:00

CONNECT STRING TO ROWS======================var myvar varchar2(30);exec :myvar:='SCOTT,ALLEN,KINGDOM,CLARK' ;with t1 as (select level l from dual connect by level < length(:myvar) - length(replace(:myvar,','))+2), t2 as (select :myvar||',' c from dual)select regexp_substr(c,'[^,]+',1,l) empnamefrom t1,t2/EMPNAME---------------SCOTTALLENKINGDOMCLARKwith t1 as (select level l from dual connect by

MERGE Statement

2009-05-10T17:16:00.000-07:00

drop table emp2 purge; create table emp2 as select * from employees where rownum <35 ;MERGE INTO emp2 a USING employees b ON (a.employee_id=b.employee_id)WHEN MATCHED THEN UPDATE SET a.salary = b.salaryWHEN NOT MATCHED THEN INSERT ( EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL ,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY ,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES ( b.EMPLOYEE_ID,b.FIRST_NAME,

INSERT ALL

2009-05-07T16:53:00.000-07:00

drop table emp2 purge ;drop table emp3 purge ;Create table emp2 as select employee_id,first_name,last_name,salary from employees where 1=2;Create table emp3 as select employee_id,salary from employees where 1=2;-- Insert each row in emp2 and emp3 INSERT ALL INTO emp2 VALUES(employee_id,First_name,Last_name,salary) INTO emp3 VALUES(employee_id,salary) SELECT employee_id, first_name,last_name,

Passing Arrays from Java

2009-05-04T13:21:00.000-07:00

create or replace type NUM_ARRAY as table of number;/create or replace and compile java source named "ArrayDemo" as import java.io.*; import java.sql.*; import oracle.sql.*; import oracle.jdbc.driver.*; public class ArrayDemo { public static void passArray() throws SQLException { Connection conn = new OracleDriver().defaultConnection(); int

DATAPUMP export

2009-04-27T11:08:00.000-07:00

CONN sys/password@db10g AS SYSDBAGRANT CREATE ANY DIRECTORY TO scott;Create the directory from scott loginCREATE DIRECTORY exp_dir as 'e:\exp\';expdp SYSTEM/pwd dumpfile=scott.dmp directory=exp_dir schemas=SCOTT job_name=scottexp logfile=scottexp.log

EXPORT script

2009-04-20T11:05:00.000-07:00

@echo offREM get the date and time stampfor /f "tokens=2-9 delims=:.-/ " %%a in ('date/t') do ( set dates=%%c%%a%%b)for /f "tokens=1-9 delims=:.-/ " %%a in ('time/t') do ( set time=%%a%%b%%c)REM set charactersetset NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15exp system/pwd file=E:\exp\exp_%dates%.%time%.dmp owner=scott log=E:\exp_%dates%.%time%.log

EXECUTE OS command from PL/SQL

2009-04-14T17:53:00.000-07:00

EXECUTE OS command from PL/SQLJava ProcedureCREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "OSCommand" ASimport java.io.*;public class OSCommand{public static String Run(String Command){try{Runtime.getRuntime().exec(Command);return("0");}catch (Exception e){System.out.println("Error running command: " + Command +"\n" + e.getMessage());return(e.getMessage());}SQL> CREATE or REPLACE FUNCTION

DATAFILE operations

2009-04-13T10:07:00.000-07:00

Adding a DatafileALTER TABLESPACE usersDATAFILE 'C:\ORACLE\ORADATA\ORCL\USER2.DBF'SIZE 20M;----------------Resizing a DatafileALTER DATABASE DATAFILE 'C:\ORACLE\ORADATA\ORCL\USER2.DBF'RESIZE 50M;----------------Drop a DatafileALTER DATABASE DATAFILE 'C:\ORACLE\ORADATA\ORCL\USER2.DBF'OFFLINE DROP;----------------Auto extending ON OFFALTER DATABASE DATAFILE 'C:\ORACLE\ORADATA\ORCL\USER2.DBF'

Configure Listener to connect to local database with IPC protocol

2009-04-06T09:44:00.000-07:00

Configure Listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost )(PORT = 1521)) ) )Tnsnames.oraLO = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) (CONNECT_DATA = (SERVICE_NAME = LOC) ) )

ORA-00600: internal error code, arguments: [qmxiUnpPacked2], [121], [], [], [],

2009-03-30T09:06:00.000-07:00

ERROR drop user usera cascade;*ERROR at line 1:ORA-01001: invalid cursorORA-00600: internal error code, arguments: [qmxiUnpPacked2], [121], [], [], [],[], [], []SOLUTIONsqlplus / as sysdbaSQL> shutdown immmediate ;SQL> startup MIGRATESQL> @?/rdbms/admin/catpatch.sql....SQL> shutdown immediate ;SQL> startupORA-600

RECOVER TABLE from Block Corruption

2009-03-23T07:53:00.000-07:00

MOS 33405.1Connect as a SYSDBA user and mark the table as needing to skip corrupt blocksexecute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('SCOTT','EMP2');CREATE TABLE recover_emp AS SELECT * FROM EMP2;To clear the attribute for a table useexecute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('SCOTT,'EMP2', flags=>dbms_repair.noskip_flag);

GENERATE Sequential Number Dates Alphabets etc

2009-03-16T07:57:00.000-07:00

Generate Sequential numbersSQL> l1 select n2 from (3* select level n from dual connect by level <= 20 )SQL> /N----------123456789101112131415161718192020 rows selected.Generate One Year of DateSQL> l1 select dt2 from (3 select to_date('31-Dec-2007') + level dt4 from dual5* connect by level <= to_date('31-Dec-2008') - to_date('31-Dec-2007') )SQL>/DT---------01-JAN-0802-JAN-0803-JAN-0804-JAN-0805-

RETURN ARRAY from function

2009-03-09T07:54:00.000-07:00

RETURN ARRAY from Functioncreate or replace type tarr is varray(20000) of number(10) ;/show errorcreate or replace function f_array return tarr as arr tarr:=tarr() ; begin for r in ( select empno from emp2 ) loop arr.extend() ; arr(arr.count()):=r.empno ; end loop ; return arr; end f_array;/show errorsSQL> select * from table(f_array()) ;COLUMN_VALUE------------

SPECIAL:BULT-IN Functions

2009-02-12T19:25:00.000-08:00

bfilename( 'directory', 'filename' )CREATE DIRECTORY ExpDir AS 'd:\export\';SELECT bfilename('ExpDir', 'logo.gif')FROM dual;cardinality( nested table column ) CASE [ expression ] WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 ... WHEN condition_n THEN result_n ELSE result ENDcoalesce( expr1, expr2, ... expr_n )function returns the first non-null

RMAN Basics

2009-02-03T07:57:00.000-08:00

SHOW RMAN Configurationc:\>rmanRecovery Manager: Release 10.2.0.1.0 - Production on Sat May 2 13:12:16 2009Copyright (c) 1982, 2005, Oracle. All rights reserved.RMAN> connect target /connected to target database (not started)RMAN> startupOracle instance starteddatabase mounteddatabase openedTotal System Global Area 146800640 bytesFixed Size 1286220 bytesVariable Size 96473012 bytesDatabase

DYNAMIC IN list from string

2009-01-09T07:58:00.000-08:00

WHEN the value is eg. 'SCOTT,ALLEN,KINGDOM,CLARK'Convert the STRING TO ROWS and use in like a tablevar myvar varchar2(30);exec :myvar:='SCOTT,ALLEN,KINGDOM,CLARK' ;with t1 as (select level l from dual connect by level < length(:myvar) - length(replace(:myvar,','))+2), t2 as (select :myvar||',' c from dual)select regexp_substr(c,'[^,]+',1,l) empnamefrom t1,t2/EMPNAME---------------

GROUPING SETS - Add 01 02 .. after every 5 rows

2009-01-02T07:59:00.000-08:00

ADD 01 02 03 ... after ever 5 rows.WITH t AS(select chr(ascii('a') + rownum - 1) cfrom dualconnect by level < 27)SELECT decode(grouping_id(c),1,to_char(gr + 1, 'fm09'),c)from (select c, trunc((row_number() over (order by c) - 1) / 5) grpfrom t)GROUP BY GROUPING SETS ((gr, c), (gr))ORDER BY grp, decode(grouping_id(c), 1, chr(0), c)/DEC---01abcde02fghij03klmno04pqrst05uvwxy06z32 rows selected.

PLSQL Profiler usage

2008-12-08T08:00:00.000-08:00

Connect as SYSSQL> connect sys/***** as sysdbaCreate Profiler Packages etc.SQL>@?rdbms\admin\profload.sqlCreate tables for the schemaSQL> connect scott/tigerSQL> @?rdbms\admin\proftab.sqlthe script creates PLSQL_PROFILER_DATA, PLSQL_PROFILER_RUNS,PLSQL_PROFILER_UNITSSQL> create table testemp(emp number);SQL>CREATE OR REPLACE PROCEDURE testprocISi NUMBER := 0;vempno NUMBER;BEGINWHILE i <

PLSQL Associative arrays

2008-12-08T07:59:00.000-08:00

Associative array is the new name for index-by tableDeclarationPrior 9.2.0type my_tab_t is table of number index by binary_integer;Post 9.2.0type my_tab_t is table of number index by pls_integer;type my_tab_t is table of number index by varchar2(4000); -- varchar2 can be max 32767General codeidx := the_table.First;while idx is not nullloop-- do somethingidx := the_table.Next(idx);end loop;

MANUALLY CONFIGURE DBCONSOLE

2008-12-03T21:51:00.000-08:00

Enterprise Manager Configuration Agentemca -repos drop (if existing)emca -repos createemca -config dbcontrol dbemca -deconfig dbcontrol db -repos dropemca -config dbcontrol db -repos createemca -config dbcontrol db -repos recreateEnterprise Manager Controlemctl start dbconsoleemctl stop dbconsoleemctl status dbconsole

FUNCTION Returning plsql table type

2008-11-15T08:01:00.000-08:00

create or replace type typRecord as object (inum number,vchr varchar2(30));/create or replace type typNestedTab as table of typRecord;/create or replace function fnMyEmp return typNestedTab asvRet typNestedTab;beginvRet := typNestedTab();for x in ( select empno, ename from emp ) loopvRet.extend;vRet(vRet.count) := typRecord(x.empno ,x.ename );end loop ;return vRet;end fnMyEmp;/

TIMESTAMPS AND TIMEZONES

2008-11-08T08:01:00.000-08:00

The Time zone session timestamp and system timestampSQL> select sessiontimezone, current_timestamp, systimestamp from dual;SESSIONTIMEZONE---------------------------------------------------------------------------CURRENT_TIMESTAMP---------------------------------------------------------------------------SYSTIMESTAMP----------------------------------------------------------------------------04:

RECREATING DATABASE CONTROL dbcontrol

2008-10-10T08:03:00.000-07:00

Dropping the dbcontrolD:\>emca -deconfig dbcontrol db -repos dropSTARTED EMCA at Apr 09, 2008 10:08:31 AMEM Configuration Assistant, Version 10.2.0.1.0 ProductionCopyright (c) 2003, 2005, Oracle. All rights reserved.Enter the following information:Database SID: ORCLListener port number: 1521Password for SYS user:MANAGERPassword for SYSMAN user:MANAGERCreating the dbcontrolD:\>emca -config

BILL OF MATERIALS (BOM)

2008-10-10T08:02:00.000-07:00

Bill of Materials using connect bySQL> create table bill_of_materials2 (3 assembly_id number,4 assembly_name varchar2(30),5 parent_assembly number6 ) ;Table created.SQL>SQL> insert all2 into bill_of_materials values (100,'Pen',null)3 into bill_of_materials values (101,'Refil',100)4 into bill_of_materials values (102,'Ink',100)5 into bill_of_materials values (103,'Tip',101)6 select * from dual;4

SUPERFAST CLONING

2008-09-14T08:03:00.000-07:00

1. TAKING BACKUP 1. a. If your database is not in ARCHIVE LOG mode, take an off-line database backup. 1. b. If in ARCHIVELOG MODE, you can either take an on-line or off-line database backup. 2. CREATING A CONTROLFILE FOR CLONE 2. On production database, connect to "SYS AS SYSDBA" SQL> alter database backup controlfile to trace; 3. Edit the controlfile trace [from $ORACLE_BASE/

SECURING dbconsole ENABLING https

2008-09-10T08:04:00.000-07:00

Securing dbconsole1) Set the environment variable ORACLE_SID to ORCLD:\>set ORACLE_SID=ORCL2) emctl stop dbconsoleD:\>emctl stop dbconsoleOracle Enterprise Manager 10g Database Control Release 10.2.0.4.0Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.http://myhost:5500/em/console/aboutApplicationThe OracleDBConsoleORCL service is stopping.........The OracleDBConsoleORCL service

ANALYTIC FUNCTIONS

2008-08-15T07:47:00.000-07:00

--GET THE FIRST SALARY WITHIN A DEPARTMENT ORDERED BY ASCENDING SALARYselect deptno, ename, sal,first_value(sal) over ( partition by deptno order by deptno) Firstfrom emp/ DEPTNO ENAME SAL FIRST---------- ---------- ---------- ---------- 10 MILLER 1300 1300 10 CLARK 2450 1300 10 KING 5000 1300 20

CHANGE LOST SYS PASSWORD

2008-08-14T07:48:00.000-07:00

WINDOWS1. Backup the %ORACLE_HOME%/database/PWD.ora2. Create new password fileorapwd file=%ORACLE_HOME%/database/PWD.ora password= entries=103. Stop and Start the Oracle service using service controller to take new passwordsc stop OracleServicewait till the service is stopped.sc start OracleSerivcewait till the service is started.4. Login sqlplus with /nologsqlplus /nologSQL> connect sys as

DATE:BULT-IN Functions

2008-08-08T12:36:00.000-07:00

add_months( date1, n ) where n is months, -n to minus monthsALTER SESSION SET TIME_ZONE = '-2:0';current_date shows the time from the time zone set.current_timestamp shows the timestampdbtimezone shows database timezonelocaltimestamp local timestampfrom_tz( timestamp_value, time_zone_value )select from_tz(TIMESTAMP '2007-09-11 01:20:42', '5:00')from dual;last_day( date ) shows last day of the

SQLite Commands

2008-07-08T17:40:00.001-07:00

SQL commands list * ALTER TABLE --- rename table and add columns * ANALYZE --- generate statistics for index and tables. * ATTACH DATABASE --- can attach to 10 database files. * BEGIN TRANSACTION --- begin , end , commit , rollback and sql statements can be used. * comment -- /* this is a multiline comment */ * COMMIT TRANSACTION -- * CREATE INDEX -- Create unique and

SQLite

2008-07-08T17:40:00.000-07:00

Create table CREATE TABLE t1( a INTEGER, b INTEGER, c VARCHAR(200) ); Create table with autoincrement primary key CREATE TABLE t1( a INTEGER PRIMARY KEY, b INTEGER, c VARCHAR(200) );Insert rows in the table ( insert null in primary key to insert the next number and autoincrement) INSERT INTO TABLE t1(NULL, 1234,'abcde'); Datatype used in SQLITE NULL. The value is a NULL value.

RECOVERY from ORA-01157 errors

2008-07-07T07:47:00.000-07:00

Resolve Errors: ORA-01157: cannot identify/lock data file 14 - see DBWR trace fileDatabase may be in Archivelog or Noarchive log modeSome data may be lost in Noarchive log mode.Lost datafile C:\ORACLE\ORADATA\ORCL\TEST.DBFRE-CREATE a datafile for RECOVERYSQL> select * from v$recover_file;FILE# ONLINE ONLINE_ ERROR CHANGE# TIME---------- ------- ------- ------------------ ---------- ---------14

CHANGING modes of DATABASE

2008-07-07T07:46:00.000-07:00

Archivelog to NoArchiveLogLOG_ARCHIVE_START parameter is obsolete in 10g and aboveSHUTDOWN ;STARTUP MOUNT EXCLUSIVE;ALTER DATABASE NOARCHIVELOG ;ALTER DATABASE OPEN ;select log_mode from v$database ;NoArchivelog to ArchiveLogSHUTDOWN ;STARTUP MOUNT EXCLUSIVE;ALTER DATABASE ARCHIVELOG ;ALTER DATABASE OPEN ;select log_mode from v$database ;TO Delete LOG_ARCHIVE_START parameter SQL> alter system

Microsoft SQL Server 2005

2008-06-08T17:35:00.000-07:00

Aggregate - CREATE AGGREGATE - DROP AGGREGATE Application Role - CREATE APPLICATION ROLE - ALTER APPLICATION ROLE - DROP APPLICATION ROLEAssembly - CREATE ASSEMBLY - ALTER ASSEMBLY - DROP ASSEMBLYALTER AUTHORIZATIONBACKUPBACKUP CERTIFICATEBEGIN [DIALOG [CONVERSATION]]Certificate - ALTER CERTIFICATE - CREATE CERTIFICATE

REFCURSOR example

2008-06-07T07:45:00.000-07:00

--REFCURSOR examplecreate or replace package typesastype cursorType is ref cursor;end ;/create or replace function fempCreturn types.cursortypeasl_cursor types.cursorType;beginopen l_cursor for select * from emp ;return l_cursor;end ;/declarel_cursor types.cursorType;my_emp emp%rowtype;beginl_cursor := fempC();loopfetch l_cursor into my_emp ;exit when l_cursor%notfound;dbms_output.put_line(

DATABASE level Triggers

2008-06-07T07:44:00.000-07:00

ON SHUTDOWNcreate or replace trigger tr_on_shutdownbefore shutdown on databasebegin--- do something send message etc...--- EXECUTE IMMEDIATE ....end;/CREATE ON DATABASECREATE OR REPLACE TRIGGER SYSTEM.tr_db_objs AFTER CREATE ON DATABASEDECLARE....BEGIN--- do something send message etc...--- EXECUTE IMMEDIATE ....END ;/LOGON DATABASECREATE OR REPLACE lo_tr AFTER LOGON DATABASEdeclare.....BEGIN---