Practical commands and Operation Skills in Oracle

ORACLE
1. query related information
SELECT * FROM USER_TABLES
SELECT * FROM USER_INDEXES;
2. Modify Table Structure Information
* Alter table SCHEMA_NAME.TABLE_NAME move tablespace ttprddata storage (INITIAL 64 K)
3. Modify the index structure information
* Alter index SCHEMA_NAME.INDEX_NAME rebuild storage (INITIAL 64 K)
4. query/set the current SCHEMA
* SELECT SYS_CONTEXT (USERENV, CURRENT_SCHEMA) from dual;
* Alter session set CURRENT_SESSION = QUIXD
5. DB LINK
* SELECT * FROM DBA_DB_LINKS;
6. display the table structure information
* Enter desc table tableName on the command;
* Select * from col where tname = upper (wbts_tmp );
* Select * from user_tab_columns where table_name = upper (Table Name );
7. oracle error message prompts and keywords
* DUP_VAL_ON_INDEX (oracle internal exception dup_val_on_index problem)
* NO_DATA_FOUND (no information found)
* OTHERS (Other Cases)
* SQLCODE/SQLERRM is used to obtain error information. If you want to obtain specific information, use SQLERRM (SQLCODE)
* DUP_VAL_ON_INDEX this exception is thrown if the primary key is repeated.
**************************************** ***
* EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR (-20103, In adding Non Standard template name | SQLERRM );
8. Oracle Functions
* SUBSTR
* SIGN eg: select sign (900-800) from dual; Value: 1
* DECODE (replace the if-then-elsif-then-else or switch statement)
*-1, 0, and 1 respectively indicate smaller than, equal to, and greater
* NVL cannot be null. If it is null: nvl (field name, 0), this field is displayed as 0 even if it is null, of course, this 0 can also be replaced with other things, such as: 1, 2, 3 ......
9. dynamically Execute SQL statements
* V_ SQL: = SELECT * FROM | V_TBNAME;
Execute immediate V_ SQL;
10. Oracle escape
* Select * from dba_tables where table_name like U/_ % ESCAPE/ESCAPE v_ SQL in single quotes: = select * from tb where t_co = | value |;
11. Tables frequently used by the ORacle System
* Dba_segments, DBA_INDEXES, DBA_TABLES ....;
12. FUNCTIONAL-BASED-INDEX
* SELECT * FROM DBA_INDEXES where owner = ttprd and INDEX_TYPE = FUNCTION-BASED NORMAL
13. Methods for exporting objects
* Select dbms_metadata.get_ddl (INDEX, QUIX_LEGACY_ID_XREF_IDX02) from dual;
14. Create a History Table & insert data into the History Table
* Execute the statement: create table new TABLE name as select * FROM old TABLE name;
* INSERT data: insert into WBTS_T2 SELECT * FROM WBTS_T1;
15. Define a parameter as the cursor type
* TYPE quixnotescurtype is ref cursor;
* TYPE trenddates is record (closedate DATE, submitdate DATE );
* TYPE trendtable is table of trenddates index by BINARY_INTEGER;
16. Reset the User Password
* Alter user system identified by NewPasswordHere;
* Note: Remember username and password values shocould add ""
17. INSTR (source string, target string, start position, matching serial number)
* For example, In INSTR (reset ate FLOOR, OR, 3, 2), the source string is reset ate FLOOR, the target string is OR, the start position is 3, and the position of 2nd matching items is obtained.
The default search order is left to right. When the start position is negative, search from the right.
So the result of select instr (effecate FLOOR, OR,-1, 1) "Instring" from dual is
Instring
14
18. Loop Methods (If you want to exit the loop, you can by the key word "exit "):
* LOOP
Exit when p_cursordata % NOTFOUND;
FETCH p_cursordata INTO v_trendtable (v_ I );
V_ I: = v_ I + 1;
End loop;
* FOR I IN 1 .. v_trendtable.LAST
LOOP
IF (I = 1)
THEN
V_mindate: = v_trendtable (I). submitdate;
ELSIF (v_mindate> v_trendtable (I). submitdate)
THEN
V_mindate: = v_trendtable (I). submitdate;
End if;
End loop;
19. Trunc (for number)
* Grammar: TRUNC (number [, decimals])
Decimals indicates the number of digits after the decimal point to be retained. Optional. If this parameter is ignored, all decimal parts are truncated.
The usage of this function is as follows:
TRUNC (89.985, 2) = 89.98
TRUNC (89.985) = 89
TRUNC (89.985,-1) = 80
Note: The second parameter can be a negative number, indicating that the part after the specified number of digits on the left of the decimal point is truncated.
20. Round (for number)
* Grammar: ROUND (number, decimal_places)
Select round (123.456, 0) from dual; return 123
Select round (123.456, 3) from dual; return 123.456
21. dynamically Execute SQL statements
* Execute immediate select ts_name_new from yan. ts_users_reform t where ts_id =: vts_id
INTO sts_name
USING nts_id; (variable value assignment)
22. Union vs Union All === Intersect vs Minus
* UNION filters out duplicate records after table links are established.
* In the query, union all is encountered. Its usage is the same as union, except that union contains the distinct function. It removes duplicate records from two tables, but union all does not, therefore, in terms of efficiency, union all is a little higher, but not a lot
**************************************** * ********************************** 8
* The operations of ntersect and Minus are basically the same as those of Union. Here we will summarize the following:
* Union: Union of two result sets, excluding duplicate rows, and sorting by default rules;
* Union All: Union operations are performed on two result sets, including duplicate rows without sorting;
* Intersect: intersection of two result sets, excluding duplicate rows, and sorting by default rules;
* Minus performs the Difference Operation on two result sets, excluding duplicate rows and sorting by default rules.

You can specify the Order by clause in the last result set to change the sorting method.
23. Exists

24. ALL_USERS vs DBA_USERS vs USER_USERS
* Related Views
* DBA_USERS describes all users of the database, and contains more columns than ALL_USERS.
* USER_USERS describes the current user, and contains more columns than ALL_USERS.
* ALL_USERS lists all users of the database visible to the current user. This view does not describe the users (see the related views ).
25. INITCAP converts the first letter into uppercase letters.
26. select * from tb for update, select * from tb, td for update of tb. field1 (only the table tb is locked)
* About NOWAIT (If you must use for update, I suggest adding NOWAIT)
* If for update is added, this statement is used to lock specific rows (if a where clause exists, it is the rows that meet the where condition ). When these rows are locked, other sessions can select these rows, but they cannot be changed or deleted until the transaction of the statement ends with the commit statement or rollback statement.
Because the for update clause acquires locks, COMMIT releases these locks. When the lock is released, the cursor is invalid.
27. Sotre procedure/Function diff
* Function and procedure comparison
-- You can use the out parameter to return more than one value.
-- It is better to use procedure when multiple values are returned.
-- It is better to use the function when a value is returned.
-- Function can be called in SQL statements, while procedure cannot.
28. Jump to loop ----- >>> continue terminate loop ------> return
29. In Oracle, A Varchar2 field field1 is separately stated, and the defined length should be <= 32767
If you define a column column1 of the Varchar2 type in a table (or view), its maximum length cannot exceed 4000.

30. The function LENGTHB method cannot operate on the Clob field. To obtain the length of the Clob field, use the function DBMS_LOB.getlenght (CLOB)
32. In oracle. & is a key word. if using it. you shoshould use ascii as chr (39)
33. Set table's column datatype: usage> ALTER TABLE
Wbts_tmp MODIFY
(
STATUS varchar2 (1) not null
)
34. Pls get the first five places via SQL: YOU NEED USE RANK FUNCTION
* SELECT * FROM
(Select rank () OVER (partion by gender by grade) RK,
ST. * FROM TS_STUDENT ST)
Where rk <= 5;

* Row_number () Distribute sequences in sequence, such as: 1, 2, 3, 4, 5
* The rank () Peer ranking will take the next ranking, for example, 1, 2, 2, 4, 5.
* Dense_rank () won't take one place, for example, 1, 2, 2, 3, 4, 5.
35. Unlock a user account
* Command: alter user username account unlock;
36. Make sure the output words can display in command window, y