Oracle FAQs. A list of common Oracle problems previously collected, I forgot where the problem about SELECTN came from. I was inspired by a list of common Oracle problems that some netizens have consulted and discussed several times to select certain data rows, I forgot where it came from.
About SELECT N
Some netizens have repeatedly consulted and discussed the selection of some specified row data. I have written the following simple description. please correct me.
The select n described here includes the following situations:
1. select top n rows of records
2. select N1-N2 row Records
3. select foot n rows of records
Of course, we need to consider whether there is an order by clause. in the next interview, we will take the system view CAT as an example to describe it separately.
Note: A. There is no ORDER
B. ORDER
1. select top n rows of 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 ROWNUM B. 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
The number of records in the record set is unknown. if the number 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 test passed on ORACLE8.1.5 for Windows2000pro
-- End --
Oracle FAQ (1) from chao_ping
1. quickly organize broken tables (this can be used in Oracle8i)
Alter table table_name MOVE (tablespace xxx );
How to move the tablespace of a table
Method 1:
1. Export table
2. Drop this table
3. Create table xxx tablespace xxx;
4. Imp Ignore = y
Note that all PL/SQL Stored procedures to read this table will be invalid. Need to be re-compiled.
1. SELECT * FROM DBA_OBJECTS where status = 'invalid ';
2. recompile these packages, functions, and procedures.
Method 2:
Applicable only to Oracle8i.
Use the following statement:
Alter table table_name MOVE
TABLESPACE new_tablespace;
In this way, all constraints, indexes, and triggers will not be affected.
However, you need to rebuild all indexes on this tag.
2. how to directly access SQL * plus without entering the user name and password:
Sqlplus/nolog;
Sqlplus username/password @ connect_string
3. how to quickly reconstruct the index:
Alter index xxx rebuild storage ();
Alter index xxx coalesce;
4. Why can't I see the result of dbms_output?
SET SERVEROUTPUT ON
5. after a large transaction has been committed, why is my rollback segment still that large?
Because no OPTIMAL value is set, it will not automatically contract.
You can use alter rollback segment shrink to Xm; to manually contract.
6. why VARCHAR2 instead of CHAR?
A. CHAR only supports the length of 2000 bytes, while VARCHAR2 supports the length of 4000 bytes, providing better applicability.
B. CHAR occupies more storage space and defines how long it will take, and the inserted characters are automatically filled with spaces. VARCHAR2 only uses the actual length of the inserted characters regardless of the length.
7. Why does the table/index occupy different space in 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 DBA_TABLES in the first database View refers to the number of BLOCKS actually used. Some BLOCKS are occupied, but no data exists and are not included. In the database view of DBA_SEGMENTS, the BLOCKS column refers to the total number of BLOCKS occupied by the table, including the total number of BLOCKS with and without data. The sum of BLOCKS and EMPTY_BLOCKS in the first view is equal to the size of the BLOCKS column in the second view.
8. how to save one or more tables in the database as a common text file?
You can use the SPOOL command in SQL * Plus to save the selected data to 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 repeated records:
SQL> SELECT COUNT (*), EMP_ID, OFFICE_ID
FROM EMP
Group by EMP_ID, OFFICE_ID
Having count (*)> 1;
The result is 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 can I 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 directly Drop a column. Syntax: alter table table_name drop column_name;
However, 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 the transaction?
Set transaction use rollback segment rbs_name;
15. how do I know who permissions are granted to a table and what permissions are granted to them?
Select * from dba_tab_privs where table_name = 'Table name ';
16. How did you find out who locked the table you needed?
Select object_id from v $ locked_object;
Select object_name, object_type from dba_objects where object_id = '';
Each time a table is cleared (with truncate), the storage parameter NEXT of this table is automatically reset to the last deleted extent size. Similarly, if space is explicitly released from a table, the NEXT parameter is automatically set to the last extent size to be released.
In SQL * Plus, you can specify a rollback segment for a transaction: This is useful when a large transaction is about to happen. Use the following statement to specify a rollback segment for the transaction:
SQL> SET TRANSACTION USE ROLLABCK SEGMENT rollback SEGMENT name;
You can also specify a rollback segment for a transaction in PL/SQL (without using dynamic SQL statements ). This must use the package provided by Oracle: DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT ('rollback segment name ');
On some platforms, Oracle automatically generates a sgadefSID. dbf at startup. you can use this file to determine whether an instance is running. This file contains the address of the SGA in the memory. When the database is closed, Oracle automatically deletes the file. However, in Oracle8i, this file no longer exists. A new method is required 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. filext $ this table is retrieved, but in Oracle8, you can check 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 logon, logout, and other transactions, which can trigger this event and make some records. Triggers defined at the database level are triggered when all user-related events occur, while triggers defined at the Schema level are triggered only when the corresponding events of a specific user occur.
From Oracle8i, there is another way to shut down the database: shutdown transactional. This method allows all users to submit their work. However, once committed, the connection will be cut off immediately. when all users have completed their respective transactions, shutdown will start.
You can create temporary tables starting from Oracle8. the definitions of these tables are visible to all sessions of this user, however, the data queried, inserted, and deleted by each session is irrelevant to the data queried, inserted, and deleted by other sessions. Just as every session has such a table.
From Oracle8i, you can quickly reorganize tables that are not partitioned without IMP/EXP. However, this requires two times the table space capacity. This statement is:
Alter table TB_NAME move tablespace TS_NAME;
You can create reverse indexes in Oracle8i. (Create index I ON t (a, B, c) REVERSE ;). Because the adjacent key values of reverse indexes are not stored in physical adjacent locations, only full index scans or statements that use a single column can effectively use these indexes. This type of reverse index can better coordinate the changes made to the database by different instances on the Oracle Parallel Server, which can improve the system performance to a certain extent.
Starting from Oracle8, the $ instance view can find many useful information, such as the host name, instance name, start time, and version number.
The temporary segments created in the temporary tablespace are released only when the shutdown is performed.
However, the temporary segments created in the permanent tablespace are released after the end of a transaction, and the Smon process is used to complete this task.
Oracle FAQ (2) from chao_ping
About OPTIMAL Parameters
Optimal is a storage parameter used to limit the size of Rollback segments. After a long transaction is executed, the rollback segments used by the transaction will be relatively large. after the Optimal parameter is set, once the transaction is committed, the rollback segment is automatically reduced to the size specified by Optimal.
If your system has many long-running transactions, set the Optimal parameter of the rollback segment to a larger value. This helps to maintain the continuity of the tablespace in the rollback segment. Otherwise, constant expansion and contraction will further break the tablespace.
If the main transactions in the system are short-lived, you should set the rollback segment to a smaller value, so that the information in the rollback segment can be stored in SGA, to improve system performance.
The Optimal parameter of the rollback segment can be specified when the rollback segment is created.
Alter rollback segment SEGMENT_NAME optimal xx m; to reset.
In Oracle8i
Alter session set CURRENT_SCHEMA =
It can be used to change the current user mode.
Oracle has declared that it no longer supports server manager. This tool has been the main tool for managing Oracle databases since Oracle 6.0. Currently, SQL * Plus replaces Server Manager. Therefore, all functions of Server Manager are integrated into SQL * Plus.
The newly added SQL * Plus commands include startup, shutdown, archive log, and recover. Of course, the standard SQL syntax is still supported, such as a series of CREATE and ALTER statements. However, some of these changes have also been made, such as sentences such as alter database open, alter database mount, and alter database backup, which were not previously supported.
Some new options are also added for the SET command, such as automatic recovery. The SHOW command can also be used to directly display parameters such as show parameter and show sga. These functions are only available in Server Manager.
Oracle8i still keeps a well-known INTERNAL account, but remember that this is mainly for backward compatibility. The functions of the INTERNAL account are now supported by the SYSDBA and SYSOPER roles. The INTERNAL/SYS password can be modified using the following method:
O/S Prompt> orapwd password =
When the database was just created, SYS's password defaults to change_on_install, while SYSTEM's password is manager, while INTERNAL has no password at all. Therefore, after creating a database, the first thing to do is to change the passwords of the above three users. INTERNAL passwords can be changed using the methods mentioned earlier, while SYSTEM and SYS can be changed directly using alter user username identified by password. Note: at Oracle8i, alter user sys identified by password also changes the INTERNAL password. if you set a password for INTERNAL, the two roles of SYSOPER are granted to the users responsible for managing the database. Some modifications should be made to the scripts that use INTERNAL to connect to the database.
Start and close the database:
To start the database from SQL * Plus, follow these steps:
O/S Prompt> sqlplus/nolog
SQL> connect scott/tiger as SYSDBA
SQL> startup
If you want to use a parameter different from the default parameter to start the database, you can use the following parameter-based startup command:
SQL> startup PFILE =
Sometimes you need to start the database, but do not let normal users enter. for example, to balance IO, you need to move the location of a data file. in this case, you need to change the default Startup option:
SQL> startup mount
After the maintenance task is completed, you can choose to close the database and then open it again in the normal way, or directly enter the following command in SQL * Plus to start normal operation of Oracle:
SQL> alter database open
Sometimes you need to create a new database or recreate the control file, you need to use the following statement:
SQL> startup nomount
Sometimes, if the database is difficult to start normally, you can consider the following method to FORCE the start: using the FORCE option, startup force is actually equivalent to a shutdown abort and then STARTUP process.
SQL> startup force
The SHUTDOWN command also has several parameters to choose from:
Normally, the system is shut down after all users exit the system. This is an ideal way to close a database. Generally, this method should be used to close the database normally.
SQL> shutdown
At Oracle8i, a new shutdown transactional option is added. This allows all users to complete their transactions. once the transaction is committed, the connection is immediately disconnected. This not only ensures that the user will not lose their transactions, but also ensures that the database can be closed in time for necessary maintenance operations. If this method is disabled, you do not need to perform instance-level recovery after the next system startup. It is better than the other two methods mentioned below.
SQL> shutdown transactional
Shutdown immediate is to stop the current transaction of the user immediately and roll back the current transaction of the user without waiting for the transaction to be completed. However, if some transactions have not been committed for a long time, shutdown immediate may not be as IMMEDIATE. It may also take a lot of time to roll back these transactions.
SQL> shutdown immediate
In Oracle8i, the last method is shutdown abort. There is not much difference between the power-off method and the power-off method. Any user connected to the instance is immediately disconnected. when the instance is started again, the instance must be restored at the instance level to roll back uncommitted transactions.
SQL> shutdown abort
After alter table table_name MOVE, the index mark of the TABLE is UNUSABLE?
From Oracle8i, you can directly use alter table table_name move [tablespace tablespace_name];
To move a table to another table space or reorganize the storage of the table to reduce fragments. However, after this is used, all the indexes on this table will be marked as unusable. This is because after moving a table, the physical location of the columns in the table is changed, that is, the ROWID of all rows is changed, and the ROWID of the row is used for the index of the table. Since Oracle does not automatically update the ROWID corresponding to the index, the ROWID on the index points to the wrong place. Therefore, the index is marked as UNUSABLE. At this time, you need to manually recreate the index. You can use the following syntax to recreate the index:
Alter index index_name REBUILD; of course, you can also specify specific storage parameters for the INDEX to optimize the storage of the INDEX. Perhaps Oracle does not automatically maintain indexes so that you can specify appropriate storage parameters for indexes.
How to remotely install Oracle:
If you need to install the Oracle system on Unix from The X-window client on a PC, pay attention to the following: Oracle8i uses the Universal Installer and uses the Java technology, must be installed on the GUI. For remote installation, you must set somewhere to display the GUI of the Universal Installer: use
$ DISPLAY = workstation_name: 0.0
$ Export DISPLAY
For example, if the IP address of your PC is 150.150.4.128 and the Machine name is test, you can use the following syntax to prepare for installation:
$ DSIPLAY = 150.150.4.128: 0.0
$ Export DISPLAY
You can also use the following syntax, but you must write the information of the test machine in the hosts file:
DSIPLAY = test: 0.0
$ Export DISPLAY
Explain's questions about select n are introduced by some netizens who have repeatedly consulted and discussed the selection of certain specified row data...
