How to make my username and password do not leak?
=====================
Under UNIX, I use SQLPlus Sys / Sys to log in, other users can easily see my password: What should I do?
$ PS-EF | GREP SQLPLUS
Oracle 3787 3781 1 22:05:34 PTS / 3 0:00 SQLPlus Sys / SYS
Oracle 3789 3772 0 22:05:44 PTS / 2 0:00 Grep Sqlplus
SQLPLUS / NOLOG
SQL> Connect Sys / Sys, so other users can't see your password.
How to generate a complete DDL statement for the table?
====================
Use exp, then iMP, show = y can be seen.
Or use some Oracle gadgets, such as Quest's toad and sql * navigator.
What is the difference between Truncate Table and DELETE TABLE?
==========================
Truncate: DDL, NO Rollback Possibility and no rollback segment usage, quick, release space used by the table.
DELETE: DML, Can Rollback, Uses Rollback Space, Not Release Space, Slow, Delete Large Table May Cause ORA-1555 Error.
How to delete a repeated record:
=============
The first method: 1. Generate a full DDL statement of the table and generate Tab_BAK's table name.
2. INSERT INTO TAB_BAK SELECT DISTINCT * from Tab_Name
3. DROP TABLE TAB_NAME, RENAME TAB_BAK TO TAB_NAME
Second approach:
Delete from table_name a where rotid>
(SELECT MIN (ROWID) from Table_name B
Where a.key_values = B.KEY_VALUES);
Third approach:
Delete from my_table where rotod not in
(SQL> SELECT MAX (ROWID) from my_TABLE
Group by my_column_name);
Fourth Measures:
Delete from my_table t1
WHERE EXISTS (SELECT 'x' from my_table t2
Where t2.key_value1 = T1.Key_Value1
And t2.key_value2 = t1.key_value2
And t2.rowid> t1.rowid); How to quickly add a primary key for existing tables?
=====================
Plus a non-empty column, such as SEQNO, then:
Update table_name set seqno = rownum;
or:
Create Sequence Testseq Start with 1 Increment By;
Update table_name set seqno = testseq.nextval;
SQL Sort Question: How can I choose to order after sorting a column?
=====================================
In SQL * Server, you can use this statement: SELECT TOP 10 Col1, Col2 from Table_Name;
Start with Oracle8i, support such syntax (using the order by statement in the subquery)
Select * from (select col1, col2 from table_name order by col1, col2)
WHERE ROWNUM <11;
This can play the same effect.
In Oracle8 or below, you can do this:
Select Col1, Col2 from
(SELECT / * INDEX_DESC (Table_name Index_name) * / COL1, Col2 from Table_Name)
WHERE ROWNUM <6;
Using the prompts to let Oracle sorted before the subquery returns the result, usually use HintEx_Desc (Table_name, INDEX_NAME) to play this role.
We can view two SQL execution plans separately:
Scott @ TestDB> Select * from sort_sample;
ID Name
---------- --------------------------------------------------------------------------------------------------------------------------------
1 aa
5 33
90 23s
23 fdisk
746 2343
24 Format
3 Low Format
7 rows selected.
Scott @ testdb> create index sort_id_idx on sort_sample (id);
Index created.
Scott @ testdb> set autotrace on explain
Scott @ TestDB> --way 1:
Scott @ testdb> select * from (select * from sort_sample order by id desc) Where rownum <3;
ID Name
---------- --------------------------------------------------------------------------------------------------------------------------------
746 2343
90 23s
Execution Plan
-------------------------------------
0 Select Statement Optimizer = Choose
1 0 count (stopkey)
2 1 view
3 2 Sort (Order by Stopkey)
4 3 Table Access (Full) of 'Sort_SAMPLE'
Scott @ Testdb> --way 2: Wrong Result
Scott @ Testdb> Select * from sort_sample where rownum <3;
ID Name
---------- --------------------------------------------------------------------------------------------------------------------------------
1 aa
5 33
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose
1 0 count (stopkey)
2 1 Table Access (Full) of 'Sort_Sample'
Scott @ Testdb> Analyze Table Sort_sample Compute Statistics;
Table analyzed.
Scott @ testdb> analyze index sort_id_idx compute statistics
Index analyzed.
Scott @ testdb> --way 3: can work in oracle8 and oracle7
Scott @ testdb> select * from (Sort_Sample Sort_ID_IDX) * / * from sort_sample)
2 WHERE ROWNUM <3;
ID Name
---------- --------------------------------------------------------------------------------------------------------------------------------
1 aa
5 33
// Cause: col Sort_id_idx is listed as NULLABLE, so CBO cannot be determined, plus the Not Null constraint to achieve the purpose.
Use group by to generate sorting from a small start:
Scott @ Testdb> Select ID, Name from
2 (Select ID, Name, Count (*) from sort_sample group by id, name)
3 WHERE ROWNUM <3;
ID Name
---------- --------------------------------------------------------------------------------------------------------------------------------
1 aa
3 Low Format
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose (COST = 3 Card = 7 Bytes = 175)
1 0 count (stopkey)
2 1 View (COST = 3 card = 7 bytes = 175)
3 2 sort (group by stopkey) (COST = 3 card = 7 bytes = 56)
4 3 Table Access (Full) of 'Sort_Sample' (COST = 1 Card = 7
6. How do you get a record every N? For example, 3rd, 6, 9, etc.
=========================================== CHAO @ ping> select * from testseq;
ID Name
---------- -------------------------------------
1 this is 1th Record
2 this is 2th record
3 this is 3th record
4 this is 4th record
5 this is 5th record
6 this is 6th record
7 this is 7th record
8 this is 8th record
9 this is 9th record
10 this is 10th record
10 rows selected.
Chao @ ping> SELECT ID, NAME FROM
2 (SELECT ID, NAME, ROWNUM RZ from Testseq) TEMP
3 WHERE MOD (RZ, 3) = 0;
ID Name
---------- -------------------------------------------------------------------------------------------------------------------------------- ---
3 this is 3th record
6 this is 6th record
9 this is 9th record
Chao @ ping>
How to delete a column?
===========
Starting from Oracle8i, Oracle supports the deletion of a column, the syntax is as follows:
Alter Table Tab_name Drop Column COL1;
7. How to rename a column?
==============
Chao @ ping> Create Table Testrename (ID Number, Nama Varchar2);
Table created.
Chao @ ping> Begin
2 for x in 1..10 loop
3 INSERT INTO TESTRENAME VALUES (X, 'this is' || to_char (x) || 'TH Record');
4 end loop;
5 End;
6 /
PL / SQL Procedure SuccessFully Completed.
Chao @ ping> commit;
COMMIT COMPLETE.
Chao @ ping> ALTER TABLE TESTRENAME ADD NAME VARCHAR2 (30);
Table altered.
Chao @ ping> Update testrename set name = nama;
10 rows updated.
Chao @ ping> ALTER TABLE TESTRENAME DROP Column Nama;
Table altered.
Chao @ ping> select * from testrename;
ID Name
---------- -------------------------------------------------------------------------------------------------------------------------------- -----
1 this is 1th Record
2 this is 2th record3 this is 3th record
4 this is 4th record
5 this is 5th record
6 this is 6th record
7 this is 7th record
8 this is 8th record
9 this is 9th record
10 this is 10th record
10 rows selected.
8. Strong Decode Usage: Control IF-THEN-ELSE in SQL?
=========================================
Chao @ ping> Create Table TestDecode (ID Number, SEX Char, Name Varchar2 (20));
Table created.
Chao @ ping> INSERT INTO TESTDECode Values (1, 'f', 'this is a woman ");
1 row created.
Chao @ ping> INSERT INTO TESTDECode Values (1, 'M', 'this is a man');
1 row created.
Chao @ ping> commit;
COMMIT COMPLETE.
Chao @ ping> SELECT DECODE (SEX, 'F', 'Female', 'M', 'Men,' Unknown ') AS Gender, Name AS Name from TestDecode;
Gender name
-------- ----------------------------------------
THIS I Woman
Male THIS A Man
9. How to choose some data from the table? (Oracle8i / 9i support) - Sample statement
=================================================================================================================================================================================
Scott @ qingh> Create Table TestSample (a number);
Table created.
Scott @ qingh> Begin
2 for x in 1..1000 loop
3 INSERT INTO TESTSAMPLE VALUES (X);
4 end loop;
5 End;
6 /
PL / SQL Procedure SuccessFully Completed.
Scott @ qingh> commit;
COMMIT COMPLETE.
Scott @ qingh> select * from testsample sample (1);
A
------------
8
156
234
373
416
469
494
603
714
827
829
A
------------
925
12 rows selected.
Restriction: only can be used for a single table
About data type conversion;
Long-clob: to_lob
Long-varchar: in SQL * Plus, Set CopyTypeCheck OFF, AND USE COPY COMMAND.
CLOB-> varchar
SQL> CREATE TABLE LOB (A CLOB);
Table created.
SQL> INSERT INTO LOB VALUES ('this is for test');
1 row created.
SQL> commit;
COMMIT COMPLETE.
SQL> SELECT DBMS_LOB.GETLENGTH (A) from LOB;
DBMS_LOB.GETLENGTH (A)
---------------------
16
SQL> DECLARE
2 x long;
3 y clob;
4 Begin
5 SELECT A INTO Y from Lob;
6
7 x: = dbms_lob.substr (y, dBMS_LOB.GETLENGTH (Y), 1);
8 dbms_output.put_line (x);
9 End;
10 /
this is for test
PL / SQL Procedure SuccessFully Completed.
Inside SQLPLUS, return the execution result of the function?
===========================
Scott @ qinghai> Create Or Replace Function Test (InputVar In Varchar)
2 Return Varchar
3 IS
4 Begin
5 Return Upper (InputVar);
6 End;
7 /
Function created.
Scott @ qinghai> Declare X varchar2 (20);
2 y varchar2 (20);
3 Begin
4 x: = 'THISTEST';
5 SELECT test (x) INTO Y from Dual;
6 dbms_output.put_line (y);
7 End;
8 /
THisTest
PL / SQL Procedure SuccessFully Completed.
or:
SQL> EXEC DBMS_OUTPUT.PUT_LINE (TEST ('this is for test');
A big problem for beginners: Date issue in Oracle
===========================
1. NLS_DATE_FORMAT in init.ora almost always does not work.
This is because: the setting of the system parameters, session is preferred in SYSTEM
For almost all clients, NLS_LANGs in Register or Environment Varible have been defined, resulting in NLS_DATE_FORMAT or explicitly defined, or a default value is obtained according to NLS_LANG.
2. In the registry of NT, the value under Oracle / Homex is preferred under Oracle, so you should set it in Oracle / HomeX /. 3. If you still don't work, or you have multiple Oracle HOME, you can set it in the system environment variable, which is equivalent to Unix.profile.
4. Modify the parameters in the registry without having to restart NT or Oracle, re-establish new session will use new settings.
13. About View: What kind of view allows DML, how to view the source code of View, how to modify View, how to compile View?
Scott @ qingh> CREATE TABLE MyTable (AA Number);
Table created.
Scott @ qingh> Create View TestView As SELECT * from MyTable;
View created.
Scott @ qingh> SELECT OBJECT_NAME, OBJECT_TYPE from User_Objects
Where status = 'invalid';
No rows selected
Scott @ qingh> Drop Table MyTable;
Table Dropped.
Scott @ qingh> SELECT OBJECT_NAME, OBJECT_TYPE from User_Objects
Where status = 'invalid';
Object_name
-----------------------------------------------
TestView
Scott @ qingh> CREATE TABLE MyTable (AA Number);
Table created.
Scott @ qingh> SELECT OBJECT_NAME, OBJECT_TYPE from User_Objects
Where status = 'invalid';
Object_name
-------------------------------------------------- -------------
TestView
Scott @ qingh> ALTER VIEW TestView Compile;
View altered.
Scott @ qingh> SELECT OBJECT_NAME, OBJECT_TYPE from User_Objects
Where status = 'invalid';
No rows selected
Scott @ qingh>
