Five Aspects of Oracle

The new version can bring more efficient technology to the software. Now we can forget five aspects of the Oracle database.

1. only DBA can restore Data

People who work directly with SQL database-DBAs and IT information personnel can cause database damage or data loss due to an unmatched command. In fact, for Oracle, user error operations are the main cause of database downtime. A form obtained from the database rather than the development process can paralyze the operations of the program and all its users. Moreover, an inappropriate update may damage the results of database operations.

Until now, restoring data from these errors is a very time-consuming task and can only be implemented by DBAs. However, since Oracle 9i, you can use SQL commands to correct many such errors. The mechanism introduced the new Oracle 9i Flashback Query feature.

Here is an example of operations in SCOTT schema. In this example, a employee record is deleted and the Change result is submitted:

Delete from e-mapreduce WHERE e-mapreduce = 7934;

COMMIT;

The SELECT statement is lost in the code line, and a ROLLBACK statement cannot be restored. However, a flashback query can display the form content 10 minutes ago:

SELECT * FROM emp

As of timestamp (SYSTIMESTAMP-INTERVAL '10' MINUTE)

WHERE e-mapreduce = 7934;

The SELECT statement can use the subquery of the INSERT statement to load the deleted data. However, note that this INSERT operation must be restricted by the constraints in the form.

To return the entire session to a specific time, you can use the DBMS_FLASHBACK package:

EXEC DBMS_FLASHBACK.ENABLE_AT_TIME (TIMESTAMP 'yyyy-mm-ddhh: mi: ss ');

During a flash query, the accessed data cannot be changed but can only be read. This is a bit like time tracking in science fiction: You can access the past, but you don't change the past. To obtain the previous session, you can type:

EXEC DBMS_FLASHBACK.DISABLE;

In order for Flashback queries to work, the database must use automatic deletion Management (AUM, automatic Undo Management) and create a deletion table. The time limit for users to "flash back" depends on the undo_retention parameter and the initialization of the deleted table size.

Although this feature is a good news for IT consultants, the main objective of Oracle is to allow users to recover from their or her errors by typing SQL directly. Flash-back query is a good function, so it can restore a single form or all forms through system permissions.

FLASHBACK ANY TABLE

But you can still wait: 10 Gb in Oracle will do better. In 9i, flash query is restricted by the DML (Data Manipulation Language) commands, such as SELECT, INSERT, UPDATE, and DELETE. But in 10 Gb, even a dropped table can be restored with a flash.

2. Oracle cannot save the decimal point time

Oracle's DATE data type can only store time data in seconds. Developers who need more accurate time can only use the NUMBER data type. This operation makes it more difficult to calculate the time interval.

Since 9i, Oracle contains a time/date data type compatible with SQL 1999. To declare a Time column, you can use the TIMESTAMP data type and display the required number of decimal places (6 by default ):

Create table event_ts (

Event_id NUMBER (6)

, Event_name VARCHAR2 (40)

, Start_time TIMESTAMP (2)

, Elapsed_time TIMESTAMP (2)

);

For a TIMESTAMP literal, such as DATE, it must be enclosed by a single quotation mark. The following is a literal representation of March 23,200 4 at the half second of midnight:

TIMESTAMP '2017-03-23 00:00:00. 50'

Although the standard DATE literal does not contain time, the standard format of a TIMESTAMP literal must contain time. The format of the NLS_TIMESTAMP_FORMAT parameter is controlled. Similarly, the standard DATE format is set for NLS_DATE_FORMAT. A new conversion function, namely TO_TIMESTAMP, can create a TIMESTAMP from other input formats, and the TO_CHAR function can be used to display the composition of a TIMESTAMP.

To obtain the current date and time in TIMESTAMP format, you can use the iimestamp function instead of the SYSDATE function. For example:

SELECT distinct imestamp from dual;

3. Discard an object for a damaged block.

IT administrators are most afraid of Oracle error messages like ORA-1578, "Oracle data block was upted (Oracle data block corruption )". The internal structure of one of the database blocks cannot be modified. Information can identify block errors by the number of files and the number of blocks. In general, the remedy is to run the following query:

SELECT owner, segment_name, segment_type

FROM dba_extents

WHERE file_id =

AND BETWEEN block_id AND block_id + blocks-1;

Here, And Is the number of errors obtained from the error message. This query shows which objects contain damaged blocks. Then the damaged blocks can be restored. However, the restoration is either direct (for indexing and temporary components) or chaotic (for forms ), either it is very confusing (for dynamic roolback parts and partial data dictionaries ).

However, in Oracle 9i Enterprise Edition, a command named BLOCKRECOVER recovery Manager (RMAN) can correct these blocks without having to discard and recreate the objects involved. After entering RMAN and connecting to the target database, type:

BLOCKRECOVER DATAFILE BLOCK ;

A New View named V $ database_block_upload uption can be used to obtain updates during the RMAN backup process. When a BLOCKRECOVER is executed, the block content can be listed. To recover all damaged blocks, you can use the following RMAN:

Backup validate database;

BLOCKRECOVER upload uption LIST;

This method is useful if you only need to restore several blocks. For large-scale damaged blocks, you need to re-store a previous image of the data file. However, restoring the entire data file is more efficient. For any new features, Please carefully test before using the formal database. This is necessary.

4. Columns cannot be renamed or reorganized.

Rename a table column or change its data type. This usually means creating a new table and copying old data to the new table. The column cannot be renamed at all. The data type can be changed only when no data is available (only NULL values are allowed.

Oracle 9i has two ways to overcome these limitations. Now, the alter table command can rename the column directly:

Alter table books rename column tiitle TO title;

Similarly, a PL/SQL Toolkit named DBMS_REDEFINITION allows a DBA to change the Column Structure of a table. This is a complex process, but it can usually be achieved by following the steps below:

1. Use DBMS_REDEFINITION.CAN_REDEF_TABLE to check that the table meets the requirements of online redefinition, and specify whether the table complies with the primary key or the row Ids.

2. Create an empty table in the same plan, but the structure is the same. Omit the columns you want to discard, including the new columns you want to create.

3. Use DBMS_REDEFINITION.START_REDEF_TABLE to start the redefinition process. Parameters in this process describe the old table, the new table, and how to map existing columns to columns in the new table.

4. create any constraint and index.

5. Use DBMS_REDEFINITION.FINISH_REDEF_TABLE to complete this process. When you redefine the exchange between two tables, regardless of the size of the original table, the table must be locked for a short period of time.

6. Discard the temporary table used in the definition instead of using it.

Of course, redefining a table does not automatically update any program code that can access the table. Programs must be updated and debugged separately. However, what DBMS_REDEFINITION does can do is to shorten the time, which is exactly what users need.

5. Only the table owner can assign the permission to use the table.

When I mention Oracle security features, the customer cannot believe that DBA cannot assign the permission to the table unless the table owner has assigned it to DBA. However, this was previously the case. These restrictions are part of Oracle's design, but they make management more difficult. In Oracle 9i, a new system permission has changed this point.

The current DBA has the system permission grant any object privilege. In the past, such a statement

Grant select on scott. emp TO giselle;

It will fail unless SCOTT has assigned the DBA's SELECT permission to his table with grant option. Now, the same statement can run correctly.

Keep forgetting

As Dee Hock, founder of Visa, said, "The question is not how you get innovative ideas, but how to forget old things ." Therefore, please stay in the forgotten status.

(