Oracle Experience Skill Set

oracle| Tips Oracle Experience skill set 1. Delete a table space
DROP tablespace tablespacename [including CONTENTS [and Datafiles]]
2. Delete User
DROP USER user_name CASCADE
3. Considerations for deleting a table
When you delete all the data in a table, you use the TRUNCATE table name, because when you use the drop Table,delete * from the table name, the space occupied by the tablespace table space is not released, and after several drop,delete operations, The space on the tablespace is depleted.
4. Use of HAVING clause
The HAVING clause controls the row groups determined by the GROUP BY clause, which only allow constants, clustered functions, or columns in the GROUP BY clause to be involved in the condition of the HAVING clause.
5. Use of outer join "+"
The outer join "+" is divided by its left and right joins on the left or right side of "=". If a row in a table without the "+" operator does not directly match any row in the table with the "+" budget character, the row of the former matches a blank row in the latter and is returned. If neither of them takes ' + ', Both of which cannot be matched are returned. By using the outer join "+", we can replace the not in operation with very low efficiency and greatly improve the running speed. For example, the following command is slow to execute



Using outer joins to improve query speed of table joins

When making a table connection (commonly used in a view), you often use the following methods 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 Table A has 10,000 records, table B has 10,000 records, it will take 30 minutes to check, mainly because not in to do a comparison of a single, a total need 10000*10000 comparison, to get results. After the outer join, you can shorten the time to about 1 minutes:

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. Use of the SET TRANSACTION command
When performing large transactions, Oracle sometimes reports the following error:

Ora-01555:snapshot Too old (rollback segment too small)

This means that Oracle's randomly allocated rollback segment for this transaction is too small for it to specify a rollback segment that is large enough to ensure the successful execution of the transaction. For example



Set TRANSACTION use rollback segment ROLL_ABC;

Delete from table_name where ...

Commit

The rollback segment ROLL_ABC is assigned to the delete transaction, and the commit cancels the rollback segment designation after the transaction ends.
7. Problems needing attention in database rebuilding



Some views may cause problems when using import for database rebuilding, because the order in which the structure is entered may cause the input of the view to precede the input of its low-level table, and the view will fail. To solve this problem, we can take two steps: first, input structure, then enter the data. Examples of commands are 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=64000

Commit=y Ignore=y

The first command enters all database structures, but no records. Second input structure and data, 64000 bytes submitted once. The IGNORE=Y option guarantees that the second input succeeds even if the object exists.

Select A.empno from emp A where a.empno does not

(select Empno from emp1 where job= ' SALE ');

If you are using an outer join, rewrite the command 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 obviously improved.
8. To create another table from a known table:
CREATE TABLE b

As SELECT * (can be a few columns in table a)

From a

WHERE A.column = ...;
9. Find and delete duplicate records:
Method One: Use the GROUP BY statement to find the quick

Select COUNT (num), max (name) from student--finds duplicates of the NUM column in the table, lists the number of duplicate records, and lists his Name property

GROUP BY Num

Having count (num) >1--After grouping by NUM, find out that the NUM column in the table repeats, which occurs more than once



Delete from student (above select)

In this case, all the duplicates are deleted. -----discreet.



Law II: When the table is larger (for example, more than 100,000), the efficiency of this method is unbearable, need to find another way:

----Execute the following SQL statement to display the same and duplicate records for all drawing and Dsno

SELECT * from Em5_pipe_prefab

WHERE rowid!= (SELECT MAX (ROWID) from Em5_pipe_prefab D--d equivalent to First,second

WHERE Em5_pipe_prefab. Drawing=d.drawing and

Em5_pipe_prefab. DSNO=D.DSNO);



----Execute the following SQL statement to delete all drawing and dsno identical and duplicate records

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. Returns the [N,m] bar record in the table:


Gets 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 5 records:

SELECT * FROM tablename where rownum<6; (or rownum <= 5 or rownum!= 6)

If you want to return the 第5-9条 record:

SELECT * FROM tablename

where ...

and rownum<10

Minus

SELECT * FROM tablename

where ...

and rownum<5

Order BY name

After selecting the results, sort the results by name. (Select and reorder first)



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

SELECT * FROM tablename where rownum!= 10; The first 9 records are returned.

Can't use: >,>=,=,between...and. Because RowNum is a pseudo column that always starts with 1, Oracle thinks that this condition is not valid and that the records are not found.



In addition, this method is faster:

SELECT * FROM (

Select RowNum r,a from yourtable

where RowNum <= 20

Order by name)

where r > 10

So take out the 第11-20条 record! (select first and then sort and then select)

To sort and then select the Select nesting: the inner layer sorting outer selection.



RowNum is generated with the result set, once generated, it will not change, at the same time, the resulting results are accumulator in turn, No 1 will never have 2!

A rownum is a pseudo column that is produced during a query collection, and if a rownum condition exists in the Where condition:

1: If the decision condition is constant, then:

can only rownum = 1, <= is greater than 1 of the natural number, = more than 1 of the number is no result, more than a number is not the result of

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

2: When the decision value is not a constant

If the condition is = Var, then only when the Var is 1 to meet the conditions, there is no stop key, must be full scan, each meet the other where conditions of the data to determine

Select a line to select rownum=2 ...




11. Quickly compile all views


----When you dump a database into a new server (database rebuild), you need to recompile the view, because the connection to the table in the table space view to the other table spaces is problematic, and you can quickly compile with the Pl/sql language features.

SQL >spool on. Sql

SQL >select ' ALTER VIEW ' | | tname| | '

COMPILE; ' From TAB;

SQL >spool off

Then execute the on.sql.

SQL > @ON. Sql

Of course, authorization and creation synonyms can also be done 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



Forget ——— how to cry



Article options:



Lunatic

(Stranger)

06/13/03 11:33

Essence Re:oracle common commands [re:lunatic]






12. Read-write text operating system files
----In versions above Pl/sql 3.3, Utl_file packs allow 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. Using new and old values for columns in database triggers
----The column values of the trigger base table are almost always used in a database trigger, and if a statement requires a value before a column is modified, use: old to do so, using the new value of a column modified to: such as: old. Dept_no,:new. Dept_no.




14. How to move a database file
When you want to move a database file to another directory, you can move with the ALTER DATABASE command (greater applicability 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 MV command in UNIX to move files to a new location.

#mv/ora13/orarun/document.dbf/ora12/orarun

3. Load the database and alter the file name in the database using ALTER DATABASE commands.

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;




15. Connection Query Results:
Table A column A1 A2

Record 1 A

1 b

2 x

2 y

2 Z

Select to select the following results:

1 AB

2 xyz



Here are two examples:

1. Use Pl/sql code implementation, but require you to combine the length can not exceed the limit of Oracle VARCHAR2 length

Create or Replace type strings_table is Table of VARCHAR2 (20);

/

Create or Replace function merge (PV in strings_table) return VARCHAR2

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 (multiset (select name from t where 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 in sql:

Table Dept, EMP

To get the following results

Deptno, Dname, employees

---------------------------------

Ten, accounting, Clark;king;miller

Smith;adams;ford;scott;jones, the

Sales, Allen;blake;martin;james;turners

Each dept's employee is strung together as a record to return



This is example uses a max of 6, and would need more cut n pasting to doing more than that:



Sql> Select Deptno, Dname, Emps

2 from (

3 Select D.deptno, D.dname, RTrim (E.ename | |

4 Leads (e.ename,1) over (partition by D.deptno

5 by E.ename) | | |

6 leads (e.ename,2) over (partition by D.deptno

7 by E.ename) | | |

8 Leads (e.ename,3) over (partition by D.deptno

9 by E.ename) | | |

e.ename,4 (partition by D.deptno)

by E.ename) | | |

Leads (e.ename,5) over (partition by D.deptno

by E.ename), ', ') Emps,

Row_number () over (partition by D.deptno

by E.ename) x

From EMP E, Dept D

where D.deptno = E.deptno

18)

where x = 1

20/



DEPTNO dname EMPS

------- ----------- ------------------------------------------

ACCOUNTING CLARK, KING, MILLER

ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH

SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD






16. Create a field in Oracle that will automatically increase the number to facilitate query


1, establish the sequence:

CREATE SEQUENCE Checkup_no_seq

Nocycle

MAXVALUE 9999999999

START with 2;



2. Set up triggers:

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;



--use the sequence number as the primary key

--for the record being inserted

: new.checkup_no: = Next_checkup_no;

End;




17. View dependencies on objects (such as views and table references)


View view: Dba_dependencies Records related dependencies

Check what view you want to see, you can look in the dba_objects,

Select object_name from dba_objects where object_name like '%role% ' (if viewing role related)

And then desc about it.




18. To find the exact date of all Friday of the month
Select To_char (T.D, ' Yy-mm-dd ') from (

Select Trunc (sysdate, ' MM ') +rownum-1 as D

From Dba_objects

where RowNum < T

where To_char (T.D, ' mm ') = To_char (sysdate, ' mm ')--Find the Friday date of the current month

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, to find the current

The date of every Friday in the first three months of the month.