Oracle FAQ_PHP Tutorial-PHP Tutorial-php.cn

I have collected a list of Oracle's common problems before. I forgot where it came from. Regarding the SELECT N problem, some netizens have repeatedly consulted and discussed the problem of selecting certain specified rows of data. 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. Select FOOT N row records. Of course, you need to consider whether there is an ORDER BY clause, as follows The interview uses the system view CAT as an example to explain respectively. Note: A. It is the case without ORDER BY B. The case with ORDER BY 1. Select TOP N row records A. SELECT * FROM CAT WHERE ROWNUM ( 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 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 number of BLOCKs actually used. 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 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 duplicates Record: 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 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 directly Drop a column. 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 (using truncate), the storage parameter NEXT of this table is automatically reset to the last deleted extent. size. 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 (without using dynamic sql statements). This requires the use of the package provided by Oracle: DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT('Rollback segment name'); Oracle on some platforms will automatically generate a sgadefSID.dbf when starting. You can use whether this file exists to 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.fileext$ 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 and shutdown, user login, logout and other transactions, which can trigger the occurrence of this event and thereby 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 the same as the data queried, inserted, and deleted in other sessions. Not relevant. 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.However, 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 a storage parameter used to limit the size of the rollback segment. 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, the rollback segment should be set smaller. This will help the information in the rollback segment be stored in the SGA to improve system performance. The Optimal parameter of the rollback segment can be created in