Oracle中比较实用的命令和操作技巧(转)

ORACLE 
1、查询系统的相关信息 
SELECT * FROM USER_TABLES 
SELECT * FROM USER_INDEXES; 
SELECT SYSTIMESTAMP FROM DUAL;
2、修改表结构信息 

*ALTER TABLE SCHEMA_NAME.TABLE_NAME MOVE TABLESPACE TTPRDDATA STORAGE(INITIAL 64K) 
3、修改索引的结构信息 
*ALTER INDEX SCHEMA_NAME.INDEX_NAME REBUILD STORAGE(INITIAL 64K) 
4、查询/设置当前的SCHEMA 
*SELECT SYS_CONTEXT(USERENV, CURRENT_SCHEMA)FROM DUAL; 
*ALTER SESSION SET CURRENT_SESSION=QUIXD 
5、DB LINK 
*SELECT * FROM DBA_DB_LINKS; 
6、显示表的结构信息 
*在command上输入：desc table tableName; 
*select * from col where tname=upper(wbts_tmp); 
*select * from user_tab_columns where table_name=upper(表名); 
7、oracle错误信息提示及关键字 
*DUP_VAL_ON_INDEX （oracle内部异常dup_val_on_index问题） 
*NO_DATA_FOUND（没有找到信息） 
*OTHERS (其他情况) 
*通过SQLCODE/SQLERRM 来取得错误信息,想得到具体信息 通过 SQLERRM (SQLCODE) 
*DUP_VAL_ON_INDEX 如果主键重复、则抛出此异常 
******************************************* 
*EXCEPTION 
WHEN OTHERS THEN 
ROLLBACK; 
RAISE_APPLICATION_ERROR(-20103,In adding Non Standard template name||SQLERRM); 

9、动态执行SQL语句 
*V_SQL :=SELECT * FROM  ||V_TBNAME; 
    EXECUTE IMMEDIATE V_SQL; 
10、 Oracle转义 
*select * from dba_tables where  table_name like U/_%ESCAPE/ 

  单引号转义v_sql := select * from tb where t_co=||||value||; 
11、ORacle系统经常用到的一些表 
*dba_segments、DBA_INDEXES、DBA_TABLES....; 
12、 FUNCTIONAL-BASED-INDEX 
*SELECT * FROM DBA_INDEXES WHERE OWNER=TTPRD  AND INDEX_TYPE=FUNCTION-BASED NORMAL 
13、导出对象的方法 
* select dbms_metadata.get_ddl(INDEX,QUIX_LEGACY_ID_XREF_IDX02) from dual; 

15、定义一个参数的类型为游标类型 
*TYPE quixnotescurtype IS REF CURSOR; 
*TYPE trenddates IS RECORD (closedate DATE, submitdate DATE); 
*TYPE trendtable IS TABLE OF trenddates INDEX BY BINARY_INTEGER; 
16、重置用户密码 
*alter user system identified by NewPasswordHere; 
*备注：记得username and password values should add "" 

18、动态执行SQL 语句 
*    EXECUTE IMMEDIATE select ts_name_new from yan.ts_users_reform t where ts_id=:vts_id  INTO   sts_name   USING nts_id;（变量赋值） 
19、Union vs Union All ====Intersect vs Minus 
*UNION在进行表链接后会筛选掉重复的记录 
*在查询中会遇到 UNION ALL,它的用法和union一样,只不过union含有distinct的功能,它会把两张表了重复的记录去掉,而union all不会,所以从效率 上,union all 会高一点,但在实际中用到的并不是很多
*************************************************************************8 
*ntersect和Minus的操作和Union基本一致，这里一起总结一下： 
*Union，对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序； 
*Union All，对两个结果集进行并集操作，包括重复行，不进行排序； 
*Intersect，对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序； 
*Minus，对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。

可以在最后一个结果集中指定Order by子句改变排序方式。 

22、INITCAP 将第一个字母转换成大写 
23、select * from tb for update 、select  * from tb, td for update of tb.field1(只锁定tb 这个表) 
*关于NOWAIT(如果一定要用FOR UPDATE，我更建议加上NOWAIT) 
*如果加了for update后 该语句用来锁定特定的行（如果有where子句，就是满足where条件的那些行）。当这些行被锁定后，其他会话可以选择这些行，但不 能更改或删除这些行，直到该语句的事务被commit语句或rollback语句结束为止。
因为FOR   UPDATE子句获得了锁，所以COMMIT将释放这些锁。当锁释放了，该游标就无效了。 

24、Sotre procedure/Function diff 
*function 与 procedure 的比较 
--都可以通过使用out参数返回一个以上的值 
--当返回多个值时,用procedure较好 
--当返回一个值时,用function较好 
--function可以在sql语句中调用,而procedure则不可 

25、跳转loop----->>>>continue               终止loop------>>return 
26、Oracle中，单独申明一个Varchar2类型的字段field1，它定义的长度应为 <=32767 
        如果在表(或者是视图)中定义一个类型为Varchar2的列column1，它的最大长度不能超过4000.

27、函数LENGTHB这个方法不能对Clob的字段进行操作，如果想获取Clob字段的长度应用函数DBMS_LOB.getlenght(CLOB) 
28、In oracle. & is a key word. if using it.you should use ascii as  chr(39) 
29、Set table’s column datatype ：usage>>ALTER TABLE 
wbts_tmp MODIFY 
( 
STATUS varchar2(1) not null 
) 
30、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() 按序分配序列,如: 1, 2 ,3 ,4 ,5 
*rank() 并列排名会占取下一个名次，如： 1, 2, 2, 4, 5 
*dense_rank() 并列名次不会占取下一名次,如：1, 2, 2, 3, 4, 5 
31、Unlock a user account 
*Command ： ALTER USER username ACCOUNT UNLOCK;