Oracle FAQ_PHP Tutorial

A list of Oracle’s common problems that I collected before, I forgot where it came from

About the SELECT N problem

I am impressed by some netizens’ repeated consultations and discussions on selecting certain specified rows of data. Regarding the problem, I wrote the following simple explanation, please correct me.

The SELECT N described here includes the following situations:
1. Select TOP N row records
2. Select N1 -N2 row records
3. To select FOOT N row records
Of course, you need to consider whether there is an ORDER BY clause. Let’s take the system view CAT as an example to explain respectively.
Note: A. There is no ORDER BY case
B. ORDER BY case

1. Select TOP N row records
A. SELECT * FROM CAT WHERE ROWNUM<=N
B. SELECT * FROM
( SELECT * FROM CAT ORDER BY TABLE_TYPE )
WHERE ROWNUM<=N

2. Select N1-N2 row records
A. SELECT TABLE_NAME,TABLE_TYPE FROM
( SELECT ROWNUM ROWSEQ, TABLE_NAME,TABLE_TYPE FROM CAT )
WHERE ROWSEQ BETWEEN N1 AND N2;
or:
SELECT * FROM CAT WHERE ROWNUM<=N2
MINUS
SELECT * FROM CAT WHERE ROWNUMB. SELECT TABLE_NAME,TABLE_TYPE FROM
( SELECT ROWNUM ROWSEQ,X.* FROM (SELECT * FROM CAT ORDER BY TABLE_TYPE) X)
WHERE ROWSEQ BETWEEN N1+1 AND N2;

3 . Select FOOT N rows of records
This is a case where the number of records in the record set is not known. If it is known, use the method 2 above
A. SELECT TABLE_NAME, TABLE_TYPE FROM
( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT )
WHERE ROWSEQ > ( SELECT COUNT(*)-N FROM CAT )
B. SELECT TABLE_NAME,TABLE_TYPE FROM
( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT ORDER BY TABLE_TYPE)
WHERE ROWSEQ > ( SELECT COUNT(*)-N FROM CAT )
or
SELECT * FROM
( SELECT TABLE_NAME,TABLE_TYPE FROM CAT ORDER BY TABLE_TYPE DESC)
WHERE ROWNUM<=N

The above was tested and passed on ORACLE8.1.5 for Windows2000pro
-- end --

oracle FAQ(1) from chao_ping
1. Quickly sort out broken files Table (can only be used in Oracle8i)

ALTER TABLE table_name MOVE ( TABLESPACE XXX);

How to move the table space where a table is located

Method 1:
1. Export this table
2． Drop this table
3. Create table xxx tablespace xxx;
4. Imp Ignore=y

Another point to note is that all PL/SQL stored procedures that want to read this table will be invalid. Needs to be recompiled.
1． SELECT * FROM DBA_OBJECTS WHERE STATUS = 'INVALID';
2． Recompile these packages, functions, and procedures.
Method 2:
Only applicable to Oracle8i.
Use the following statement:
ALTER TABLE table_name MOVE
TABLESPACE new_tablespace;

In this case, all constraints, indexes, and triggers will not be affected.

But all indexes marked with this need to be rebuilt.

2. How to enter sql*plus directly without entering the username and password:

sqlplus /nolog;

sqlplus username/password@connect_string

3. How to quickly rebuild the index:

alter index xxx rebuild storage();

alter index xxx coalesce;

4. Why can’t I see the results of dbms_output?

SET SERVEROUTPUT ON

5． After a large transaction, it has been COMMITted, but why is my rollback segment still so large?

Because the value of OPTIMAL is not set, it will not shrink automatically.

You can use alter rollback segment shrink to Xm; to shrink manually.

6． Why use VARCHAR2 instead of CHAR?

A. CHAR only supports 2000 bytes in length, while VARCHAR2 supports 4000 bytes in length, which has better applicability

B. CHAR occupies more storage space. It takes up as much space as the length is defined, and spaces are automatically added after the inserted characters; while VARCHAR2 only uses the actual inserted length no matter how long it is defined.

7． Why does the space occupied by tables/indexes differ from different data dictionaries?

SQL> select blocks , empty_blocks from dba_tables where table_name='table name';
BLOCKS EMPTY_BLOCKS
---------- ---------- --
1575 1524

SQL> select bytes,blocks,extents from dba_segments where segment_name='table name';
BYTES BLOCKS EXTENTS
---------- - --------- ----------

6348800 3100 1

This is because the BLOCKS column of the first database view DBA_TABLES refers to the actual The number of BLOCKs used on the system. Although some BLOCKs are occupied, no data exists and are not included in them. In the DBA_SEGMENTS database view, the BLOCKS column refers to the total number of BLOCKs occupied by this table, including the total number of BLOCKs with data and without data. If the sum of BLOCKS and EMPTY_BLOCKS in the first view is added up, it is exactly equal to the size of the BLOCKS column in the second view.

8． How to save one or multiple tables in the database as an ordinary text file?

You can use the SPOOL command in SQL*Plus to save the selected data in the file specified by SPOOL.

9． How to delete duplicate records from a table

SQL> SELECT * FROM EMP;

EMP_ID OFFICE_ID EMPNAME

305 12 ELLISON, GEORGE

305 12 MERCURIO, JASON

128 17 SIMPSON, LINDA

305 22 JACKSON, DREW

Use the following SQL statement to identify those duplicate records:

SQL> SELECT COUNT(*), EMP_ID, OFFICE_ID

FROM EMP

GROUP BY EMP_ID, OFFICE_ID

HAVING COUNT(*) > 1;

The results are as follows:

COUNT(*) EMP_ID OFFICE_ID

2 305 12

Table Example, with duplicate values:

SQL> SELECT * FROM EMP ;

EMP_ID OFFICE_ID EMPNAME

305 12 ELLISON, GEORGE

305 12 MERCURIO, JASON

128 17 SIMPSON, LINDA

305 22 JACKSON, DREW

Use the following statement to delete duplicate records:

SQL> DELETE FROM EMP A WHERE

(EMP_ID, OFFICE_ID, 2) IN

(SELECT EMP_ID, OFFICE_ID, decode(count(*),1,1,2)

FROM EMP B

WHERE A.EMP_ID=B.EMP_ID AND

A.OFFICE_ID = B.OFFICE_ID

GROUP BY EMP_ID, OFFICE_ID);

10. How to insert special characters into the database in SQL*PLUS?

You can use the CHR function.

11． How to delete a column?

In Oracle8i, you can drop a column directly. The syntax is alter table table_name drop column_name;

but be careful to set compatible=8.1.0 or above in initsid.ora.

12． How to rename a column?

1 alter table "table_name" add
(new_column_name data_type);

2 update table_name set new_column_name =
old_column_name where rowid=rowid;

3 alter table table_name drop column
old_column_name;

13. How to quickly clear a table?

Truncate table table_name;

14. How to specify a large rollback segment for a transaction?

Set transaction use rollback segment rbs_name;

15． How do you know who has the permissions on a table and what permissions are given to them?

select * from dba_tab_privs where table_name='table name';

16． How to find out who has locked a table you need?

Select object_id from v$locked_object;

Select object_name, object_type from dba_objects where object_id='';

Every time a table is cleared, (use truncate) , the storage parameter NEXT of this table is automatically reset to the size of the extent that was last deleted. Likewise, if space is explicitly released from a table, the NEXT parameter is automatically set to the size of the last extent released.

In SQL*Plus, you can specify a rollback segment for a transaction: this is still very useful when a large transaction is about to occur. Use the following statement to specify a rollback segment for this transaction:

SQL>SET TRANSACTION USE ROLLABCK SEGMENT rollback segment name;

You can also specify a rollback segment for a transaction in PL/SQL Rollback segment (without using dynamic sql statements). This requires the use of the package provided by Oracle: DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT('Rollback segment name');

On some platforms, Oracle will automatically generate a sgadefSID.dbf when starting. Check whether this file exists. You can determine whether an instance is running. This file contains the address of the SGA in memory. Oracle will automatically delete this file when the database is closed. But in Oracle8i, this file no longer exists. New judgment methods need to be used to determine whether an instance is running. For example, PS command.

In Oracle7, if you want to know whether the data file can be automatically expanded, you must check it from the sys.filext$ table, but in Oracle8, you can know whether the data file can be automatically expanded from dba_data_files. .

Starting from Oracle8i, you can create another type of database-level triggers, such as database startup, shutdown, user login, logout and other transactions, which can trigger the occurrence of this event and make certain records. Triggers defined at the database level will be triggered when the corresponding events occur for all users, while triggers defined at the Schema level will only be triggered when the corresponding events for a specific user occur.

Starting from Oracle8i, there is an additional way to shut down the database: SHUTDOWN TRANSACTIONAL. This approach allows all users to submit their work. But once submitted, the connection will be cut off immediately. When all users have completed their respective transactions, the shutdown will begin.

Starting from Oracle8, temporary tables can be created. The definitions of these tables are visible to all sessions of the user, but the data queried, inserted, and deleted in each session are not the same as those queried, inserted in other sessions. , the deleted data are irrelevant. It's like there is such a table for each session.

Starting from Oracle8i, tables that are not partitioned can be quickly reorganized without IMP/EXP. But this requires twice the table space as the table capacity. This statement is:

ALTER TABLE TB_NAME MOVE TABLESPACE TS_NAME;

Reverse indexes can be created in Oracle8i. (CREATE INDEX i ON t (a,b,c) REVERSE;).Since the adjacent key values ​​​​of the reverse index are not stored in physically adjacent locations, only full index scans or statements such as single column can effectively utilize these indexes. This type of reverse-order index can better coordinate the modifications to the database by different instances on the Oracle parallel server, and can improve system performance to a certain extent.

Starting from Oracle8, the $instance view can retrieve a lot of useful information: such as host name, instance name, startup time, version number, etc.

The temporary segments created in the temporary table space will only be released during shutdown.

But the temporary segment created in the permanent table space will be released after a transaction ends, and the Smon process will complete this task.







oracle FAQ(2) from chao_ping
About the OPTIMAL parameter

optimal is used to limit rollback A storage parameter for the segment size. After executing a long transaction, the rollback segment used by that transaction will be relatively large. After setting the Optimal parameter, once the transaction is committed, the rollback segment will automatically shrink to the size specified by Optimal.

If there are many long-running transactions in your system, the Optimal parameter of the rollback segment should be set larger. This helps maintain the continuity of the rollback segment table space. Otherwise, constant expansion and contraction will cause the table space to become even more fragmented.

If the main transactions in the system are short-term, then the rollback segment should be set smaller. This will help the information in the rollback segment be stored in the SGA to improve the system. performance.

The Optimal parameters of the rollback segment can be specified when creating the rollback segment, or can be reset using

ALTER ROLLBACK SEGMENT SEGMENT_NAME OPTIMAL XX M;.



ALTER SESSION SET CURRENT_SCHEMA=

in Oracle8i can be used to change the current user mode.


Oracle has announced that it will no longer support server manager. This tool has been the main tool for managing Oracle databases since Oracle 6.0. Now, SQL*Plus has replaced Server Manager, so the corresponding functions in Server Manager have also been integrated into SQL*Plus.

The main new commands added to SQL*Plus are startup, shutdown, archive log, and recover. Of course, standard SQL syntax is still supported, such as a series of CREATE, ALTER and other statements. However, some changes have been made to some of them, such as ALTER DATABASE OPEN, ALTER DATABASE MOUNT, ALTER DATABASE BACKUP and other sentences that were not supported originally.

For the SET command, there are also some new options to include automatic recovery, etc. The SHOW command can also be used to directly display parameters SHOW PARAMETER, SHOW SGA, etc. And these are all functions that are only available in Server Manager.



Oracle8i still retains the well-known INTERNAL account, but remember, this is mainly for backward compatibility. The functions of the INTERNAL account are now supported by the two roles of SYSDBA and SYSOPER. The password of INTERNAL/SYS can be modified using the following method:

O/S Prompt> orapwd password=

When the database is just created, the SYS password defaults It is change_on_install, and the password of SYSTEM is manager, while INTERNAL has no password at all. Therefore, after creating the database, the first thing to do is to change the passwords of the above three users. The password of INTERNAL can be changed using the method mentioned earlier, while SYSTEM and SYS can be changed directly using ALTER USER username IDENTIFIED BY password;. Note that starting with Oracle8i, ALTER USER SYS IDENTIFIED BY password will also change the password of INTERNAL. If you set a password for INTERNAL, at the same time, the two roles of SYSDBA and SYSOPER will be immediately granted to the user responsible for managing this database. For those scripts that use INTERNAL to connect to the database, some modifications must be made accordingly.

Starting and shutting down the database:

To start the database from SQL*Plus, please follow the steps below:

O/S Prompt>sqlplus /nolog

SQL> connect scott/tiger as SYSDBA

SQL> startup

If you want to start the database with parameters different from the default, you can use the startup command with the following parameters:

SQL> startup PFILE=

Sometimes it is necessary to start the database but not allow ordinary users to enter. For example, in order to balance IO, the location of a data file needs to be moved. At this time, you need to change the default startup options:

SQL> startup mount

After completing the maintenance task, you can choose to close the database and reopen it in the normal way, or directly in SQL* Enter the following command in Plus, and Oracle can start running normally:

SQL> alter database open

Sometimes you need to create a new database or rebuild the control file, you need to use The following statement:

SQL> startup nomount

Sometimes, the database is difficult to start normally, you can consider using the following method to force startup: Use the FORCE option, STARTUP FORCE is actually equivalent to a SHUTDOWN ABORT and then STARTUP such a process.

SQL> startup force

SHUTDOWN command also has several parameters to choose from:

Normal shutdown is to wait for all users to exit the system before shutting down the system normally. This is the most ideal way to shut down the database. This method should generally be used to shut down the database gracefully.

SQL> shutdown

Starting with Oracle8i, a new shutdown option has been added: SHUTDOWN TRANSACTIONAL. This allows all users to complete their transactions and be disconnected as soon as the transaction is committed. This not only ensures that users will not lose their transactions, but also ensures that the database can be shut down in time for necessary maintenance operations. If shut down in this way, there will be no need to perform instance-level recovery after the next system startup. It is more ideal than the other two methods mentioned below.

SQL> shutdown transactional

SHUTDOWN IMMEDIATE immediately aborts the user's current transactions, and rolls back the current transactions of these users without waiting for these transactions to be completed. But if there are some transactions that have not been submitted for a long time, then SHUTDOWN IMMEDIATE may not be as IMMEDIATE as it sounds. It may also take a lot of time to roll back these transactions.

SQL> shutdown immediate

The last shutdown method in Oracle8i is SHUTDOWN ABORT. This shutdown method is actually not much different from turning off the computer's power directly. Any currently connected users are immediately disconnected, and the next time the instance is started again, instance-level recovery must be performed to roll back uncommitted transactions.

SQL> shutdown abort

ALTER TABLE table_name After MOVE, the index flag on the table is UNUSABLE?

Starting in Oracle8i, you can directly use alter table table_name move [tablespace tablespace_name];

to move a table to another table space, or to reorganize the storage method of the table to reduce fragments. However, after this use, all indexes on this table will be marked as unusable. This is because after MOVE a table, the physical positions corresponding to the columns in the table have changed, that is, the ROWIDs of all rows have changed, and the index of this table uses the ROWIDs of the rows. Since Oracle will not automatically update the ROWID corresponding to the index, at this time, the ROWID on the index points to the wrong place. Therefore, the index is marked UNUSABLE. At this time, you need to manually rebuild the index. You can use the following syntax to rebuild the index:

ALTER INDEX index_name REBUILD; Of course, you can also specify specific appropriate storage parameters for the index to optimize the storage of the index. Perhaps the reason why Oracle does not automatically maintain indexes is so that you can specify appropriate storage parameters for the index.

How to install Oracle remotely:

If you need to install the Oracle system on Unix from the X-window client on the PC, pay attention to the following: Oracle8i uses Universal Installer, and uses Java technology must be installed under the graphical interface. If it is a remote installation, you must set where to display the graphical interface of the Universal Installer: use

$DISPLAY=workstation_name:0.0

$export DISPLAY

For an example, The IP address of your PC is 150.150.4.128, and the machine name is test. Then you can use the following syntax to prepare for installation:

$DSIPLAY=150.150.4.128:0.0

$export DISPLAY

Or use the following syntax, but the test machine information must be written in the hosts file:

DSIPLAY=test:0.0

$export DISPLAY