Oracle experience (1)

1. delete a tablespace
Drop tablespace TableSpaceName [including contents [and datafiles]

2. delete a user
Drop user User_Name CASCADE
3. Notes for deleting a table

When deleting all data in a TABLE, the truncate table name must be used. Because when the drop table or DELETE * from table name is used, the occupied space of the TABLE in the TABLESPACE is not released, after repeated DROP and DELETE operations, the hundred megabytes of space on the TABLESPACE will be exhausted.

4. having clause usage
The having clause controls the row groups determined by the group by clause. The having clause conditions allow only constants, clustering functions, or columns in the group by clause.

5. Usage of external join "+"
External join "+" Join by left or right of "=. if a row in a table without the "+" operator does not directly match any row in the table with the "+" budget operator, then the row of the former matches an empty row in the latter and is returned. if neither of them has '+', none of the matching results will be returned. using External join "+" can replace the inefficient not in operation, greatly improving the running speed. for example, the following command is very slow to execute.

Using External join improves the query speed of table join

When connecting tables (commonly used for views), you can use the following methods to query data:

SELECT PAY_NO, PROJECT_NAME
FROM
Where a. PAY_NO not in (select pay _
No from B WHERE VALUE> = 120000 );
---- However, if Table A has 10000 records and table B has 10000 records, it takes 30 minutes to complete the query. The reason is that NOT IN must be compared one by one, A total of 10000*10000 comparisons are required before the results can be obtained. After the external join is used, the time can be shortened to about 1 minute:

SELECT PAY_NO, PROJECT_NAME
From a, B
Where a. PAY_NO = B. PAY_NO (+)
And B. PAY_NO IS NULL
And B. VALUE >=12000;

6. usage of the set transaction command
Sometimes oracle reports the following error when executing a large transaction:

ORA-01555: snapshot too old (rollback segment too small)

This indicates that the random rollback segment allocated by oracle to this transaction is too small. In this case, you can specify a large rollback segment to ensure the successful execution of this transaction. For example:

Set transaction use rollback segment roll_abc;
Delete from table_name where...
Commit;
The rollback segment roll_abc is specified for this delete transaction. The commit command cancels the rollback segment after the transaction ends.

7. Precautions for database Reconstruction
During database reconstruction using import, some views may cause problems, because the order of Structure Input may lead to the input of the view before its low-level table, so that the creation of the view will fail. to solve this problem, you can take two steps: first enter the structure and then enter the data. command example (uesrname: jfcl, password: hfjf, host sting: ora1, data file: expdata. dmp ):

Imp jfcl/hfjf @ ora1 file = empdata. dmp rows = N
Imp jfcl/hfjf @ ora1 file = empdata. dmp full = Y buffer = 64000
Commit = Y ignore = Y
The first command inputs all database structures, but no records. The second Input Structure and? 64000 bytes submitted once. ignore = Y option ensures that the second input can be successful even if the object exists.

Select a. empno from emp a where a. empno not in (select empno from emp1 where job = 'sale ');
If an external connection is used, the rewrite command is as follows:

Select a. empno from emp a, emp1 B
Where a. empno = B. empno (+)
And B. empno is null
And B. job = 'sale ';
It can be found that the running speed is significantly improved.

8. Create another table from a known table:
Create table B
As select * (several columns in Table a can be used)
FROM
WHERE a. column = ...;

9. Find and delete duplicate records:
Method 1: Use the Group by statement to quickly find

Select count (num), max (name) from student -- find the number of duplicate records in the num column of the table, and list its name attribute
Group by num
Having count (num)> 1 -- group by num to find the num column in the table, that is, more than once
Delete from student (Select above)
In this way, all duplicates are deleted. ----- Exercise caution

Method 2: When the table is relatively large (for example, more than 0.1 million rows), the efficiency of this method is unacceptable. Another method is required:

---- After executing the following SQL statement, all records with the same and repeated DRAWING and DSNO can be displayed.

SELECT * FROM EM5_PIPE_PREFAB
Where rowid! = (Select max (ROWID) FROM EM5_PIPE_PREFAB D -- D is equivalent to First, Second
WHERE EM5_PIPE_PREFAB.DRAWING = D. DRAWING AND
EM5_PIPE_PREFAB.DSNO = D. DSNO );
---- After executing the following SQL statement, you can exclude all records with the same and repeated DRAWING and DSNO

Delete from EM5_PIPE_PREFAB
Where rowid! = (Select max (ROWID) FROM EM5_PIPE_PREFAB D
WHERE EM5_PIPE_PREFAB.DRAWING = D. DRAWING AND
EM5_PIPE_PREFAB.DSNO = D. DSNO );

10. Return [N, M] records in the table:
Obtains the nth row in a column.

Select column_name from
(Select table_name. *, dense_rank () over (order by column desc) rank from table_name)
Where rank = & N;
If you want to return the first five records:

Select * from tablename where rownum <6; (or rownum <= 5 or rownum! = 6)
If you want to return records 5-9:

Select * from tablename
Where...
And rownum <10
Minus
Select * from tablename
Where...
And rownum <5
Order by name
After the results are selected, the results are sorted by name. (Select first and then sort)

Note: Only the above symbols can be used (<, <= ,! = ).

Select * from tablename where rownum! = 10; the first nine records are returned.
Cannot be used:>,> =, =, Between... and. Since rownum is a pseudo column that always starts from 1, Oracle considers this condition invalid and cannot find the record.

In addition, this method is faster:

Select * from (
Select rownum r, a from yourtable
Where rownum <= 20
Order by name)
Where r> 10
In this way, the 11-20 records are retrieved! (Select, sort, and then select)

To sort and then select, select Nesting is required: the outer layer of the inner layer is selected.

Rownum is generated with the result set. Once generated, it will not change. At the same time, the generated results are incremental in sequence, and there will never be 2 without 1!

Rownum is a pseudo column generated when the query set is generated. If the where condition contains the rownum condition, then:

1: If the condition is a constant, then:

Only rownum = 1, <= a natural number greater than 1, = A number greater than 1 has no results, and a number greater than one has no results.

That is, when a rownum does not meet the condition, the query ends. this is stop key!

2: when the judgment value is not a constant

If the condition is = var, the condition is met only when var is 1. In this case, the stop key does not exist. full scan is required to determine the data that meets other where conditions.

Select a row before selecting rows with rownum = 2 ......
11. quickly compile all views

---- After the database is poured into the new server (the database is rebuilt), you need to re-compile the view, because the connection between the table view in the tablespace to other tablespaces may fail, you can use the language features of PL/SQL to quickly compile.

SQL> spool on. SQL
SQL> SELECT 'alter view' | TNAME |'
COMPILE; 'from TAB;
SQL> SPOOL OFF
Run ON. SQL.

SQL> @ ON. SQL

Of course, you can also quickly authorize and create synonyms, such:

SQL> SELECT 'Grant SELECT on'
| TNAME | 'to username;' from tab;
SQL> SELECT 'create SYNONYM
'| TNAME |' for username. '| TNAME |'; 'from tab;

12. Read and Write text-type operating system files
---- In PL/SQL 3.3 or later versions, the UTL_FILE package allows users to read and write operating system files through PL/SQL. As follows:

DECALRE
FILE_HANDLE UTL_FILE.FILE_TYPE;
BEGIN
FILE_HANDLE: = UTL_FILE.FOPEN (
'C:/', 'test. TXT', 'A ');
UTL_FILE.PUT_LINE (FILE_HANDLE ,'
HELLO, IT's a test txt file ');
UTL_FILE.FCLOSE (FILE_HANDLE );
END;

13. Use the new and old values of columns in database Triggers
---- In database triggers, the column value of the trigger base table is almost always used. If a statement requires the pre-modification value of a column, use: OLD, use the NEW value after a column is modified, and use: NEW. For example: OLD. DEPT_NO,: NEW. DEPT_NO.

14. How to move database files
When you want to move the DATABASE file to another directory, you can use the alter database command to move it (more suitable than alter tablespace ):

1. Use server manager to close the instance.

SVRMGR> connect internal;
SVRMGR> shutdown;
SVRMGR> exit;
2. Use the operating system command to move the database file location (assuming the operating system is SOLARIS 2.6). Use the mv command in UNIX to move the file to a new location,

# Mv/ora13/orarun/document. dbf/ora12/orarun
3. Load the database and run the alter database command to change the file name in the database.

SVRMGR> connect internal;
SVRMGR> startup mount RUN73;
SVRMGR> alter database rename file
> '/Ora13/orarun/document. dbf'
> '/Ora12/orarun/document. dbf ';
4. Start the instance.

SVRMGR> alter database open;