Recreate Password file

If you want to reset the sys password, you can try to recreate password.

1. go to d:\oracle\1020\database and rename pwdxxx.ora as pwdxxx.ora.old.

2. Then in command prompt type orapwd file=d:\oracle\1020\database\pwdxxx.ora password=newpassword entries=5

3. Try to login sqlplus by using new password

Lesson 1: Flashback Versions Query

Immediately identify all the changes to a row, with zero setup required.
In Oracle9i Database, we saw the introduction of the "time machine" manifested in the form of Flashback Query. The feature allows the DBA to see the value of a column as of a specific time, as long as the before-image copy of the block is available in the undo segment. However, Flashback Query only provides a fixed snapshot of the data as of a time, not a running representation of changed data between two time points. Some applications, such as those involving the management of foreign currency, may need to see the value data changes in a period, not just at two points of time. Thanks to the Flashback Versions Query feature, Oracle Database 10g can perform that task easily and efficiently.
Querying Changes to a Table
In this example, I have used a bank's foreign currency management application. The database has a table called RATES to record exchange rate on specific times. SQL> desc rates
Name Null? Type
----------------- -------- ------------
CURRENCY VARCHAR2(4)
RATE NUMBER(15,10)
This table shows the exchange rate of US$ against various other currencies as shown in the CURRENCY column. In the financial services industry, exchange rates are not merely updated when changed; rather, they are recorded in a history. This approach is required because bank transactions can occur as applicable to a "past time," to accommodate the loss in time because of remittances. For example, for a transaction that occurs at 10:12AM but is effective as of 9:12AM, the applicable rate is that at 9:12AM, not now.
Up until now, the only option was to create a rate history table to store the rate changes, and then query that table to see if a history is available. Another option was to record the start and end times of the applicability of the particular exchange rate in the RATES table itself. When the change occurred, the END_TIME column in the existing row was updated to SYSDATE and a new row was inserted with the new rate with the END_TIME as NULL.
In Oracle Database 10g, however, the Flashback Versions Query feature may obviate the need to maintain a history table or store start and end times. Rather, using this feature, you can get the value of a row as of a specific time in the past with no additional setup. Bear in mind, however, that it depends on the availability of the undo information in the database, so if the undo information has been aged out, this approach will fail.
For example, say that the DBA, in the course of normal business, updates the rate several times—or even deletes a row and reinserts it: insert into rates values ('EURO',1.1012);
commit;
update rates set rate = 1.1014;
commit;
update rates set rate = 1.1013;
commit;
delete rates;
commit;
insert into rates values ('EURO',1.1016);
commit;
update rates set rate = 1.1011;
commit;
After this set of activities, the DBA would get the current committed value of RATE column by SQL> select * from rates;
CURR RATE
---- ----------
EURO 1.1011
This output shows the current value of the RATE, not all the changes that have occurred since the first time the row was created. Thus using Flashback Query, you can find out the value at a given point in time; but we are more interested in building an audit trail of the changes—somewhat like recording changes through a camcorder, not just as a series of snapshots taken at a certain point.
The following query shows the changes made to the table: select versions_starttime, versions_endtime, versions_xid,
versions_operation, rate
from rates versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME
/
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V RATE
---------------------- ---------------------- ---------------- - ----------
01-DEC-03 03.57.12 PM 01-DEC-03 03.57.30 PM 0002002800000C61 I 1.1012
01-DEC-03 03.57.30 PM 01-DEC-03 03.57.39 PM 000A000A00000029 U 1.1014
01-DEC-03 03.57.39 PM 01-DEC-03 03.57.55 PM 000A000B00000029 U 1.1013
01-DEC-03 03.57.55 PM 000A000C00000029 D 1.1013
01-DEC-03 03.58.07 PM 01-DEC-03 03.58.17 PM 000A000D00000029 I 1.1016
01-DEC-03 03.58.17 PM 000A000E00000029 U 1.1011
Note that all the changes to the row are shown here, even when the row was deleted and reinserted. The VERSION_OPERATION column shows what operation (Insert/Update/Delete) was performed on the row. This was done without any need of a history table or additional columns.
In the above query, the columns versions_starttime, versions_endtime, versions_xid, versions_operation are pseudo-columns, similar to other familiar ones such as ROWNUM, LEVEL. Other pseudo-columns—such as VERSIONS_STARTSCN and VERSIONS_ENDSCN—show the System Change Numbers at that time. The column versions_xid shows the identifier of the transaction that changed the row. More details about the transaction can be found from the view FLASHBACK_TRANSACTION_QUERY, where the column XID shows the transaction id. For instance, using the VERSIONS_XID value 000A000D00000029 from above, the UNDO_SQL value shows the actual statement. SELECT UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID = '000A000D00000029';
UNDO_SQL
----------------------------------------------------------------------------
insert into "ANANDA"."RATES"("CURRENCY","RATE") values ('EURO','1.1013');
In addition to the actual statement, this view also shows the timestamp and SCN of commit and the SCN and timestamp at the start of the query, among other information.
Finding Out Changes During a Period
Now, let's see how we can use the information effectively. Suppose we want to find out the value of the RATE column at 3:57:54 PM. We can issue: select rate, versions_starttime, versions_endtime
from rates versions
between timestamp
to_date('12/1/2003 15:57:54','mm/dd/yyyy hh24:mi:ss')
and to_date('12/1/2003 16:57:55','mm/dd/yyyy hh24:mi:ss')
/
RATE VERSIONS_STARTTIME VERSIONS_ENDTIME
---------- ---------------------- ----------------------
1.1011
This query is similar to the flashback queries. In the above example, the start and end times are null, indicating that the rate did not change during the time period; rather, it includes a time period. You could also use the SCN to find the value of a version in the past. The SCN numbers can be obtained from the pseudo-columns VERSIONS_STARTSCN and VERSIONS_ENDSCN. Here is an example: select rate, versions_starttime, versions_endtime
from rates versions
between scn 1000 and 1001
/
Using the keywords MINVALUE and MAXVALUE, all the changes that are available from the undo segments is displayed. You can even give a specific date or SCN value as one of the end points of the ranges and the other as the literal MAXVALUE or MINVALUE. For instance, here is a query that tells us the changes from 3:57:52 PM only; not the complete range: select versions_starttime, versions_endtime, versions_xid,
versions_operation, rate
from rates versions between timestamp
to_date('12/11/2003 15:57:52', 'mm/dd/yyyy hh24:mi:ss')
and maxvalue
order by VERSIONS_STARTTIME
/
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V RATE
---------------------- ---------------------- ---------------- - ----------
01-DEC-03 03.57.55 PM 000A000C00000029 D 1.1013
01-DEC-03 03.58.07 PM 01-DEC-03 03.58.17 PM 000A000D00000029 I 1.1016
01-DEC-03 03.58.17 PM 000A000E00000029 U 1.1011
Final Analysis
Flashback Versions Query replicates the short-term volatile value auditing of table changes out of the box. This advantage enables the DBA to get not a specific value in the past, but all the changes in between, going as far bask as the available data in undo segments. Therefore, the maximum available versions are dependent on the UNDO_RETENTION parameter.
For more information about Flashback Versions Query, see the relevant section of the Oracle Database Concepts 10g Release 1 (10.1) guide.

Oracle Database 10g: The Top 20 Features for DBAs

Over the last 27 years, Oracle has made tremendous improvements in its core database product. Now, that product is not only the world's most reliable and performant database, but also part of a complete software infrastructure for enterprise computing. With each new release comes a sometimes dizzying display of new capabilities and features, sometimes leaving developers, IT managers, and even seasoned DBAs wondering which new features will benefit them most.
With the introduction of Oracle Database 10g, DBAs will have in their hands one of the most profound new releases ever from Oracle. So, DBAs who take the time to understand the proper application of new Oracle technology to their everyday jobs will enjoy many time-saving, and ultimately, money-saving new capabilities.
Oracle Database 10g offers many new tools that help DBAs work more efficiently (and perhaps more enjoyably), freeing them for more strategic, creative endeavors—not to mention their nights and weekends. Oracle Database 10g really is that big of a deal for DBAs.
Over the new 20 weeks, I will help you through the ins and outs of this powerful new release by presenting what I consider to be the top 20 new Oracle Database 10g features for database administration tasks. This list ranges from the rudimentary, such as setting a default tablespace for creating users, to the advanced, such as the new Automatic Storage Management feature.
In this series, I will provide brief, focused analyses of these interesting new tools and techniques. The goal is to outline the functions and benefits of the feature so that you can put it into action in your environment as quickly as possible.
I welcome your thoughts, comments, and questions about this series. Enjoy!

Schedule
Lesson 1—Flashback Versions Query
Lesson 2—Rollback Monitoring
Lesson 3—Tablespace Management
Lesson 4—Oracle Data Pump
Lesson 5—Flashback Table
Lesson 6—Automatic Workload Repository
Lesson 7—SQL*Plus Rel 10.1
Lesson 8—Automatic Storage Management
Lesson 9—RMAN
Lesson 10—Auditing
Lesson 11—Wait Interface
Lesson 12—Materialized Views
Lesson 13—Enterprise Manager 10g
Lesson 14—Virtual Private Database
Lesson 15—Automatic Segment Management
Lesson 16—Transportable Tablespaces
Lesson 17—Automatic Shared Memory Management
Lesson 18—ADDM and SQL Tuning Advisor
Lesson 19—Scheduler
Lesson 20—Best of the Rest

ORA-30393: A query block in the statement cannot be rewritten

ORA-30393: A query block in the statement cannot be rewritten


There may be situations where you want to stop the query from executing if it did not rewrite.Oracle Database 10g provides a new hint called REWRITE_OR_ERROR. This is a query block-level hint. When the Query cannot be rewritten, the query will throw error and stop.The error is ORA-30393: A query block in the statement cannot be rewrittenREWRITE_ON_ERROR hint is good for when the failed rewrite may cause unexpected lengthy execution. It stop and report error instead

Typically, when the rewrite failed and query stop. You should use EXPLAIN_REWRITE procedure of DBMS_MVIEW PL/SQL package to troubleshoot why the failed mview rewriteExplain the MVIEWSQL> execute dbms_mview.EXPLAIN_REWRITE -( query => 'SELECT s.prod_id -, sum(s.quantity_sold) -FROM sales s -GROUP BY s.prod_id' -, mv => 'SH.SALES_PROD' -, statement_id => 'EXPLAIN_REWRITE demo' -);Check the Explain resultSQL> SELECT messageFROM rewrite_tableWHERE statement_id = 'EXPLAIN_REWRITE demo';

How to drop the database?

To drop the target database using RMAN
--------------------------------------
In 10G, it is possible to drop the target database using
RMAN command "DROP DATABASE".
Its complete syntax is as follows:
DROP DATABASE;
DROP DATABASE NOPROMPT;
DROP DATABASE INCLUDING BACKUPS;
DROP DATABASE INCLUDING BACKUPS NOPROMPT;

Note: When "NOPROMPT" is specified RMAN does not prompt
you for confirmation before deleting the database.
By default, RMAN prompts for confirmation.
Usage of "DROP DATABASE" command:
Without recovery catalog:
-------------------------

1) RMAN> DROP DATABASE;
Deletes the target database.
Following database files are removed at Operating system level:
Datafiles
Logfiles
Controlfiles
Spfile

2) RMAN> DROP DATABASE INCLUDING BACKUPS;
In addition to (1), the above command also removes the following files:
Archivelogs
Backup pieces generated by RMAN for the target database

With recovery catalog:
----------------------
When you executed the "DROP DATABASE" command with RMAN connected to
recovery catalog, RMAN also unregisters the target database.
Restrictions and Usage Notes:
-----------------------------
* Execute this command only at the RMAN prompt.
* You must be connected to the target database, which must mounted
exclusive and not open.
* If you want RMAN to delete archived logs, copies, and backups belonging
to the database, then you must use the DROP DATABASE INCLUDING BACKUPS
syntax of the command.
* This command doesn't remove following files:
init.ora
password file
* If the operating system is Windows NT/2000, then it does not delete the
windows NT service for the target database instance.
.

Getting start with Oracle 10G

I start learning Oracle 10G by attending 'Oracle Database 10g: Administration Workshop I' & 'Oracle Database 10g: Administration Workshop II' organized by Oracle University. If you interest in this, you can go to http://education.oracle.com/ for more information.

Road to OCP: