thinkora - Think Oracle

Oracle 10g/11g OCA Preparation Guidelines

2011-07-14T11:35:00.000+04:00

The road map which help the people who are looking for Oracle 1og and 11g Certification exam, where to start. What are the required steps for getting Oracle certified? 1. Select a track 2. Prepare for the test 3. Schedule the test Step 1. Select the track Oracle Database Administrator: Oracle 11g (OCA, OCP, OCM) Oracle 10g DBA (OCA, OCP, OCM) Oracle 9i DBA (OCA, OCP, OCM) Oracle 9i or

Change the Table Column Datatype with data using SQL

2011-07-14T11:33:00.000+04:00

Follow the simple steps to change the column datatype which is also having a data inside. Normally, it’s not possible to change the column datatype with the existing data. Steps: 1. Add New temp Column with desired datatype. SQL> Alter Table Add temp datatype(size); 2. Update the data into new column. SQL> Update set temp = where is not null; 3. Drop the original Column. SQL> Alter

Oracle Database Performance Tuning

2011-07-14T11:31:00.000+04:00

For many people, Oracle database performance tuning is a hard thing and difficult to achieve. Actual user will not see any change done by the experts after the application deployment, So, I will start writing some articles on the Oracle performance tuning, required by any Application built on Oracle Database. Most of issues of performance tuning will auto resolved, if the database design

Close Oracle Reporting Engine Procedure

2011-07-14T11:29:00.000+04:00

In the Application development, during running the oracle report engine every time you run the report. Below is the code to close the reporting engine, if required by the application. You can make this procedure using Oracle Forms Builder 6i ———————————————- PROCEDURE close_rbe IS v_win_handle NUMBER; timer_id TIMER; BEGIN v_win_handle := win_api_session.findAppwindow(’Reports Background

DBMS_METADATA Error

2011-07-14T11:26:00.001+04:00

Applies to: Oracle Server - Enterprise Edition - Version: 9.2.0.7 Information in this document applies to any platform. SQL> select dbms_metadata.get_ddl(’TABLE’,’mytable‘,’myuser‘) from dual; ORA-06502: PL/SQL: numeric or value error LPX-00210: expected '<' instead of 'n' ORA-06512: at "SYS.UTL_XML", line 0 ORA-06512: at "SYS.DBMS_METADATA_INT", line 3688 ORA-06512: at "

ORA-19206: Invalid value for query or REF CURSOR parameter

2008-04-26T09:02:00.005+04:00

Today, while i import the schema into testing db (oracle 9i), it gives me following error,then i realize that i didn't run the catalog scripts after the db creation.So, if you create the database by yourself, then always run these scripts under the sys schema. see the solution below:ORA-19206: Invalid value for query or REF CURSOR parameterWhat causes this error?The queryString argument passed to

Oracle BI Forum in Dubai

2008-04-19T15:07:00.000+04:00

Oracle Business Intelligence ForumLearn More About Oracle’s Business Intelligence Applications At this event you will: Learn how Oracle Business Intelligence delivers intuitive, role-based intelligence for everyone in an organization—from frontline employees to senior management—and enables better decisions, actions, and business processes Get an overview of how Oracle’s hot-pluggable BI

LogMiner session

2008-03-27T08:13:00.002+04:00

Tip # 2: Starting, using, and ending a LogMiner sessionWe are now ready to mine for gold, well, SQL. Suppose Scott calls you and says he deleted rows from his emp table (where empno is greater than 7900). It was a mistake, and he needs the data restored to his table. The first step is to start LogMiner and populate v$logmnr_contents. This view or "table" is what you query against to extract the

Tip # 1: flashback query 9i+

2008-03-27T08:06:00.001+04:00

Tip: Two powerful tools make queries on past data available now (9i+)If you recently upgraded to 9i or 10g, you have powerful new tools for taking snapshots of data from the past.The UNDO tablespace and its associated UNDO_RETENTION setting give you and your users:* The ability to run reports from a certain point in time.* The ability to use a SQL script to re-create accidentally changed or

Oracle 9i Recovery (Loss of Server Parameter File)

2008-03-23T08:56:00.001+04:00

Loss of Server Parameter File (spfile) If your server parameter file (spfile) becomes corrupt, and you haven't been creating a textual init.ora parameter file as a backup, you can pull the parameters from it using the strings command in UNIX to create an init.ora file. You will need to edit the resulting file to get rid of any garbage characters in it (but don't worry about the "*."

Oracle 9i Recovery (Failure During Hot Backup)

2008-03-23T08:55:00.000+04:00

Failure During Hot Backup: If you have a failure while you are doing a hot backup on a tablespace, besides doing any recovery that is needed for the particular failure, you will also need to bring those tablespace datafiles back out of hot backup mode. To do this, while the database is in a mount state, do an "end backup" on each of those datafiles before opening the database. sqlplus "/

2008-03-23T08:53:00.001+04:00

Loss of TEMP Datafile:Symptoms: On large sorts (select distinct, order by, group by, union) that can't be done in memory, the sort will fail with "ORA-01157: cannot identify data file 3 - file not found" if the loss happened in the middle of the sort, or "ORA-01116: error in opening database file 3" if the loss happened before the sort started, along with the file name "ORA-01110: data file 3: '/

Oracle 9i Recovery (Loss of Control Files)

2008-03-23T08:51:00.002+04:00

Loss of Control Files : Symptoms: May be none until you try to shutdown and startup the database. On shutdown, if the control files were deleted, you would get"ORA-00210: cannot open control file 'path/ctrl_PROD_01.ctl'", or, if the control files were overwritten, you would get "ORA-00201: control file version incompatible with ORACLE version" along with their names. On startup for both cases,

Oracle 9i Recovery (Tablespace Recovery)

2008-03-23T08:51:00.001+04:00

3) Tablespace Recovery: The Recover Tablespace option is used to recover all datafiles needing recovery in a tablespace up to the point of failure, synchronizing them with the other datafiles (complete recovery only). C:\> sqlplus "/ as sysdba"SQL> startup mountSQL> alter database datafile '/u03/oradata/PROD/devl_PROD_01.dbf' offline;SQL> alter database open;SQL> alter tablespace

Oracle 9i Recovery (Datafile Recovery)

2008-03-23T08:50:00.002+04:00

2) DATAFILE RECOVERY Recover Datafile is performed either from the MOUNT state (after a shutdown), with the datafile ONLINE or OFFLINE, or from the OPEN state, with the datafile OFFLINE. A bad datafile must be taken offline before the database can be opened. The basic steps for Recover Datafile from the OPEN state (except for SYSTEM): c:\> sqlplus "/ as sysdba"SQL> alter database datafile '/

2008-03-23T08:48:00.000+04:00

1) Database Recovery: First make a BACKUP of your datafiles, control files, archive log files, and initialization parameters file (init.ora) for your database that crashed.A) Complete Recovery: Recover all datafiles needing recovery up to the point of failure. Check the datafiles needed recovery.COL df# FORMAT 999COL df_name FORMAT a20COL tbsp_name FORMAT a10COL status FORMAT

Oracle 9i Disaster Recovery

2008-03-23T08:47:00.000+04:00

Disaster Recovery OverviewThe first thing you should ALWAYS do when your database crashes is to make a BACKUP of your datafiles, control files, archive log files, and initialization parameters file (init.ora) for your database that crashed, either copying those files to tape or to another directory, or zipping them with something like GNU Zip, or making a tar file of them. DO NOT TRY TO RESTART

Backup Files

2008-03-23T08:23:00.001+04:00

What To Back Up: · Datafiles (for all tablespaces)· Control Files (binary and textual versions) · Redo Log Files (cold backups only, not hot backups) · Archive Log Files (archived redo logs, if archivelog

Bullet proof your Oracle database

2008-03-18T15:11:00.003+04:00

Bulletproofing Oracle database:Yes, you really can bulletproof your database so that it is always up and available and you never lose any data from almost any disaster scenario - for a price, whether in dollars, storage, servers, and/or bandwidth. You can do things such as running parallel servers in separate locations with standby databases for quick switchovers using Oracle's Data Guard package

Oracle Backup and Recovery 7-9i

2008-03-18T15:05:00.005+04:00

BackupsThe DBA's primary job is to make sure that the data is available and accessable by the users during those times that they need it, which means that a complete and well-tested backup and recovery procedure is in place and functioning. This section covers what files need to be included in that backup, the types of backups (cold backups and hot backups), and other processing that you could

Group by SQL Query Tuning.

2008-02-28T09:22:00.002+04:00

Comparison of two group by Queries:-- slower querySQL > SELECT prod_id, cust_id, channel_id, SUM(quantity_sold)FROM salesWHERE cust_id <>-- faster querySQL> SELECT prod_id, cust_id, channel_id, SUM(quantity_sold)FROM salesWHERE cust_id <> SQL> select deptno, empno, ename, sum(nvl(sal,0)) from scott.emp group by grouping sets ((deptno),(empno, ename)); -- display totals for the deptno's.SQL>