Oracle Experience Tips ---------------
1. Delete table space
DROP TABLESPACE TABLESPACENAME [include Contents [and DataFiles]]]
2. delete users
DROP User_name Cascade
3. Delete table precautions
When you delete all the data in a table, you must use the TRUNCATE TABLE table; The TableSpace is consumed on 100 megabytes.
4. Having clause usage
The Having clause controls the row group determined by the Group By clause, and only columns in a constant, polygroup function, or group BY clause is allowed in the HAVING clause.
5. Usage of external connection " "
External join " " is connected to the left or right side by it in the left or right side of "=". If one of the " " operator is not directly matched in the table with " " budget Any line, the front of the former matches one of the space in the latter and returned. If the two do not bring ' ', the two cannot be matched in both. Use the external connection " ", you can NOT IN operations with a very low alternative efficiency, greatly improve operational speed. For example, the following command is very slow
Use external joints to increase the query speed of the table connection
When making a table connection (commonly used for view), the following methods are often used to query the data:
SELECT PAY_NO, Project_name
From a
WHERE A.PAY_NO NOT IN (SELECT PAY_
NO from b where value> = 120000);
---- But if there is a 10,000 records, Table B has 10,000 records, then use it for 30 minutes to check, mainly because Not in is a comparison, a total of 1000 * 10,000 comparison Can get the result. After this use of an external joint, it can shorten to 1 point:
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 set transaction commands
When performing a big transaction, sometimes Oracle will report the following error:
ORA-01555: Snapshot Too Old (Rollback Segment Too SMALL)
This shows that oracle is too small to randomly assign this transaction. At this time, it can specify a sufficiently large rollback segment to ensure the success of this transaction. For example,
SET Transaction Use Rollback Segment Roll_ABC;
Delete from table_name where ...
COMMIT;
The rollback segment roll_abc is assigned to this delete transaction, and the commit command will cancel the specified of the rollback segment after the end of the transaction. 7. Database reconstruction should pay attention to problems
Some views may bring problems during database reconstruction, because the order of structural inputs may cause the input of the view to the input of its low-level table, so that the view will fail. To solve this problem, Methods to take two steps: First enter the structure, then enter the data. The command is as follows (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 = 64000commit = y ignore = y
The first command enters all database structures, but there is no record. The second input structure and data, 64,000 bytes are submitted once .Ignore = Y option guarantees that the second input is both only in the case of the object.
Select a.empno from Emp a where a.empno not in
SELECT Empno from Emp1 Where Job = 'Sale');
If the external join, 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. Newly built another table from known tables:
CREATE TABLE B
As SELECT * (can be a few columns in Table A)
From a
WHERE a.column = ...
9. Find, delete repeat record:
French 1: This looks quickly with group by statement
Select Count (NUM), Max (Name) from the version of the NUM column repeated, lists the number of records, and lists his Name properties
Group by num
Having Count> 1 - After the NUM group is packet, find out the NUM column repeat in the table, that is, the number of occurrences is greater than once.
Delete from student (SELECT above)
In this way, all repetitions have been deleted. -----careful
Method: When the table is relatively large (for example, more than 100,000), the difference in efficiency of this method cannot endure, need another way:
---- After performing the following SQL statement, you can display all Drawing and DSNO the same and repeated records.
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;
---- You can remove all Drawing and DSNO the same and repeated records after the SQL statement is performed.
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 to the table [N, M] record:
Take the Northern 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 to the top 5 records:
Select * from Tablename WHERE ROWNUM <6; (or rownum <= 5 or rownum! = 6)
If you want to return to Article 5-9 Records:
Select * from TableName
WHERE ...
And Rownum <10
minus
Select * from TableName
WHERE ...
And Rownum <5
ORDER BY Name
After selecting the result, use the NAME to sign the result. (First select again) Note: You can only use the above symbols (<=,! =).
Select * from tablename where rownum! = 10; returned to the top 9 records.
No use:>,> =, =, between ... and. Since Rownum is a pseudo column that always starts from 1, Oracle believes that this condition is not true, not recorded.
In addition, this method is faster:
SELECT * FROM
SELECT ROWNUM R, A from YourTable
WHERE ROWNUM <= 20
ORDER BY NAME)
WHERE R> 10
This takes this 11-20 records! (First choose again to sort and then select)
To sort the selection, you must use the SELECT nested: inner layer sorted out.
Rownum is generated with the results, once it is generated, it will not change; at the same time, the result of the generated is in turn, no 1 will never have 2!
Rownum is a pseudo column generated during the process of query collection, and if there is RownUM condition in the WHERE condition, the ROWNUM condition is:
1: If the judgment condition is constant, then:
Only rownum = 1, <= greater than 1 natural number, = greater than 1, no results, greater than one number is no results
That is, when a ROWNUM does not satisfy the condition, the query ends this is stop key!
2: When the determination value is not constant
If the condition is = var, only when the var is 1, it meets the conditions. At this time, there is no STOP Key, Full Scan must be performed, and the data that meets the other WHERE conditions must be determined.
After selecting a line, you can choose Rownum = 2 ...
11. Quickly compile all views
---- When the database is poured into the new server (database reconstruction), you need to re-compile the view, because the table space view will have problems with the table of other tablespaces, and PL / SQL can be used Language characteristics, fast compilation.
SQL> Spool ON.SQL
SQL> SELECT 'ALTER VIEW' || TNAME || '
'From tab;
SQL> spool off
Then perform ON.SQL.
SQL> @ on.sql
Of course, authorization and creating symbols can also be made quickly, such as:
SQL> SELECT 'GRANT SELECT ON'
|| tname || 'to username;' from tab;
SQL> SELECT 'CREATE SYNONYM
'|| TNAME ||' for username. '|| TNAME ||'; 'from Tab;
--------------------
Let your sky only sweet and beautiful
Forgot - how to cry
Article option:
Lunatic
STRANGER
06/13/03 11:33
[Essence] Re: Oracle Common Command [Re: LUNATIC]
12. Read and write text-type operating system files
---- In the version of PL / SQL 3.3, the UTL_FILE package allows the user to read the operating system file via the 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 value of the column and the old value in the database trigger
---- Almost always use the column value of the trigger base table in the database trigger, if a statement needs a certain value to modify the value before modifying, use: OLD can, use a column modified new value, Use: New is ok. Such as: Old.dept_no,: new.dept_no.
14. Database file mobile method
When you want to move the database file into another directory, you can use the alter Database command to move (strong than the ALTER TABLESPACE):
1. Close the instance using the Server Manager.
SVRMGR> Connect Internal;
SVRMGR> Shutdown;
SVRMGR> EXIT;
2. Use the operating system command to move the database file location (assuming the operating system here is Solaris 2.6). Use the mv command in UNIX to move the file to a new location.
#mv /ora13/orarun/Document.dbf / ora12 / oralun
3. Load the database and use 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 instances.
SVRMGR> ALTER DATABASE OPEN
15. Connection query results:
Table A1 A2
Record 1 a
1 b
2 x
2 Y
2 z
Use SELECT to select the following results:
1 ab
2 xyz
There are two examples below:
1. Use the PL / SQL code to implement, but require your combination of lengths that cannot exceed the length of Oracle Varchar2
Create or Replace Type Strings_Table is Table of Varchar2 (20);
/
Create or Replace Function Merge (PV in strings_table) Return varcha2
IS
Ls varchar2 (4000);
Begin
For i in 1..pv.count loop
Ls: = LS || PV (i);
End loop;
Return LS;
END;
/
Create Table T (ID Number, Name Varchar2 (10));
INSERT INTO T VALUES (1, 'Joan');
INSERT INTO T VALUES (1, 'Jack');
INSERT INTO T VALUES (1, 'Tom');
INSERT INTO T VALUES (2, 'Rose');
INSERT INTO T VALUES (2, 'Jenny');
Column Names Format A80;
Select T0.ID, MERGE (Cast (SELECT NAME AUE T.ID = T0.ID) AS STRINGS_TABLE) NAMES
From (SELECT DISTINCT ID FROM T) T0;
DROP TYPE STRINGS_TABLE;
Drop function merge;
Drop Table T; 2. Purely SQL:
Table DEPT, EMP
To get the following results
Deptno, DName, Employees
---------------------------------
10, Accounting, Clark; King; Miller
20, Research, Smith; Adams; Ford; Scott; Jones
30, Sales, Allen; Blake; Martin; James; Turners
Employee Series each DEPT is back as a record
This Example Uses a max of 6, and would Need More Cut N Pasting to do more That:
SQL> Select Deptno, DName, EMPS
2 from
3 Select D.deptno, D. DName, Rtrim (E.ENAME || ',' ||
4 Lead (E.ename, 1) over (Partition by D.deptno
5 ORDER by E.ename || ',' ||
6 Lead (E.ename, 2) over (Partition by D.deptno
7 Order by E.ename || ',' ||
8 Lead (E.ename, 3) over (Partition by D.deptno
9 ORDER BY E.ename || ',' ||
10 Lead (E.ename, 4) OVER (Partition by D.deptno
11 ORDER BY E.ename || ',' ||
12 Lead (E.ename, 5) over (Partition by D.deptno
13 ORDER BY E.ename), ',') EMPS,
14 row_number () over (Partition by D.deptno
15 Order by E.ename) x
16 from EMP E, DEPT D
17 Where d.deptno = E.DEPTNO
18)
19 where x = 1
20 /
DEPTNO DNAME EMPS
------------------------------------------------ ------------
10 Accounting Clark, KING, MILLER
20 Research Adams, Ford, Jones, Rooney, Scott, Smith
30 Sales Allen, Blake, James, Martin, Turner, Ward
16. Built a number in Oracle will automatically increase the field to facilitate the query
1. Establish a sequence:
CREATE SEQUENCE CHECKUP_NO_SEQ
Nocycle
MaxValue 9999999999
START with 2;
2. Create a trigger:
Create or Replace Trigger set_checkup_no
Before insert on checkup_history
For Each Row
Declare
Next_CHECKUP_NO NUMBER;
Begin
- Get the next checkup number from the sequence
SELECT Checkup_no_seq.nextval
INTO next_CHECKUP_NO
From Dual;
- Ise the sequence number as the primary key
--for the record being inserted: new.checkup_no: = next_CHECKUP_NO;
END;
17. View object dependencies (such as views and table references)
View View: DBA_DependenCIES records related dependencies
Check the West I don't know which view to see, I can see in DBA_OBJECTS,
SELECT Object_name from dba_objects where object_name like '% role%' (if view role)
Then DESC will be generally known.
18. To find all the specific dates of all Friday in the month
SELECT TO_CHAR (T.D, 'YY-MM-DD') from (
SELECT TRUNC (Sysdate, 'mm') ROWNUM-1 AS D
From DBA_Objects
WHERE ROWNUM <32) T
WHERE to_CHAR (T.D, 'mm') = to_char (sysdate, 'mm') - find the date of the current month on Friday
And Trim (TO_CHAR (T.D, 'Day')) = 'Friday'
------------
03-05-02
03-05-09
03-05-16
03-05-23
03-05-30
If the where to_char (t.d, 'mm') = to_char (sysdate, 'mm') is changed to sysdate-90, that is, find the current
The date of the first three months of the month.
