1. 如何查看表的列名及類型

SQL> select column_name,data_type,data_length from all_tab_columns where owner='SCOTT' and table_name='EMP';COLUMN_NAME DATA_TYPE DATA_LENGTH --------------- ---------- ----------- EMPNO NUMBER 22 ENAME VARCHAR2 10 JOB VARCHAR2 9 MGR NUMBER 22 HIREDATE DATE 7 SAL NUMBER 22 COMM NUMBER 22 DEPTNO NUMBER 228 rows selected.

2. 如何查看表的約束及建立在何列上

SQL> select * from user_cons_columns;OWNER CONSTRAINT TABLE COLUMN_NAME POSITION ----- ---------- ----- --------------- ---------- SCOTT FK_DEPTNO EMP DEPTNO 1 SCOTT PK_DEPT DEPT DEPTNO 1 SCOTT PK_EMP EMP EMPNO 1 SQL> select owner,constraint_name,constraint_type,table_name from user_constraints;OWNER CONSTRAINT C TABLE ----- ---------- - ----- SCOTT FK_DEPTNO R EMP SCOTT PK_EMP P EMP SCOTT PK_DEPT P DEPT

3. 如何查看哪個用戶對具體目錄的操作權限

SQL> select * from dba_tab_privs where table_name='TMP';GRANT OWN TAB GRA PRIVILEGE GRA HIE ----- --- --- --- ------------------------ --- --- SCOTT SYS TMP SYS WRITE NO NO SCOTT SYS TMP SYS READ NO NO

4. 如何查看用戶擁有的對象權限和系統權限

SQL> select * from user_tab_privs;GRANT OWN TAB GRA PRIVILEGE GRA HIE ----- --- --- --- ------------------------- --- --- SCOTT SYS TMP SYS WRITE NO NO SCOTT SYS TMP SYS READ NO NOSQL> select * from user_sys_privs;USERNAME PRIVILEGE ADM ------------------------------ ------------------------ --- SCOTT UNLIMITED TABLESPACE NO

5. 如何判斷數據庫啟動用的是pfile還是spfile

? ?SQL> show parameter spfile?

? ?若有值，則是用spfile啟動，若為空，則是用pfile啟動

6.?When SQL*Plus starts, and after CONNECT commands, the site profile (e.g. $ORACLE_HOME/sqlplus/admin/glogin.sql) and the user profile (e.g. login.sql in the working directory) are run. The files may?contain SQL*Plus commands.

? ? 這兩個文件可以自定義SQL*Plus命令

7. sqlplus -L?

? ?-L代表Attempts to log on just once, instead of?reprompting on error.即只判斷一次用戶名和密碼是否匹配，倘若不匹配，則立即退出，如果不加L，則有三次嘗試機會

[oracle@node3 ~]$ sqlplus -L scott/123 SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 18 04:07:25 2014 Copyright (c) 1982, 2009, Oracle. All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus

8. 密碼文件

? ?密碼文件的默認位置為:$ORACLE_HOME/dbs

? ?密碼文件的查找順序：orapw<sid> --> ?orapw ?--> Failure

? ?所以在創建密碼文件時filename只能為orapw<sid>或者orapw

9. 查看誰被授予了SYSDBA或者SYSOPER權限

? ? ?SQL> select * from v$pwfile_users;? ??

10. 查看數據字典中有關權限的表

? ? ?SQL> select * from dict where table_name like '%PRIVS%';

11. 批量插入

? ? ? insert all
? ? ? ? ? into test1
? ? ? ? ? into test2
? ? ? select * from dba_objects;

12. beq

在sqlplus中輸入conn / as sysdba,這時候由sqlplus啟動一個服務器進程，通過ps命令查看到這這個服務器進程的protocol=beq 而不是TCP，beq是bequeath protocol的簡稱，是一個sql*net protocol有點類似于IPC,僅僅是作為本地連接才能使，即客戶機和服務器程序在同一個服務器上。beq可以在不需要listener的情況一下，建立一個專用連接。? 如下所示： oracle ? ?1777 ?1776 ?0 18:04 ? ? ? ? ?00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) 如果是走網絡連接，則顯示如下： oracle ? ?1791 ? ? 1 ?2 18:05 ? ? ? ? ?00:00:00 oracleorcl (LOCAL=NO) 13. Oracle 11g下的ipcs -m 在11g下，通過ipcs -m觀察的結果如下： [root@node2 oracle]# ipcs -m------ Shared Memory Segments -------- key shmid owner perms bytes nattch status 0x00000000 32768 oracle 640 4096 0 0x00000000 65537 oracle 640 4096 0 0x00000000 98306 oracle 640 4096 0 0x00000000 131075 oracle 640 4096 0 0xefc07fc8 163844 oracle 640 4096 0

bytes列均為4096,而在10g下，owner列只有一個oracle用戶，且bytes是一個具體的數值。為什么11g下的bytes為4096且nattch為0，具體原因可參考：http://www.oracledatabase12g.com/archives/11g%E5%86%85%E5%AD%98%E7%AE%A1%E7%90%86%E6%96%B0%E7%89%B9%E6%80%A7%E7%9A%84internal%E8%A1%A8%E7%8E%B0.html

14. 如何查看當前會話的SID

? ? ?SQL> select sid from v$mystat where rownum=1;

15. 如何查看連接到當前數據庫的會話

? ??SQL> select sid,username,status from v$session;

16. 如何kill掉會話

SQL> select sid,serial# from v$session where username='SCOTT';SID SERIAL# ---------- ----------19 109SQL> alter system kill session '19,109';System altered.
SQL> select sid,serial#,status from v$session where username='SCOTT';SID SERIAL# STATUS ---------- ---------- --------19 109 KILLED

?關于kill會話，可參考?http://www.cnblogs.com/kerrycode/p/4034231.html

17. 如何查看oracle用戶密碼

? ? ?SQL> select name,password from sys.user$;

18. 不知道某個用戶的密碼，又想用這個用戶做一些操作，又不能去修改掉這個用戶的密碼

? ? ?以test用戶為例，原來test用戶的密碼為test

SQL> conn test/test Connected. SQL> conn /as sysdba Connected. SQL> select name,password from sys.user$ where name='TEST';NAME PASSWORD ------------------------------ ------------------------------ TEST 7A0F2B316C212D671 row selected.SQL> alter user test identified by hello;User altered.SQL> conn test/hello Connected. SQL> select * from tab;TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- CHAINED_ROWS TABLE EMPLOYEES TABLE EMPLOYEES_TMP TABLE3 rows selected.SQL> conn /as sysdba Connected. SQL> alter user test identified by values '7A0F2B316C212D67';User altered.SQL> conn test/test Connected.

19. 如何查看數據庫屬性

SQL> select property_name,property_value from database_properties;

20. 如何查看特定分區的值

? ? ?SQL> select count(*) from test partition(p1);

21. 查看tablespace的數據文件

? ? ?SQL> select tablespace_name,file_name from dba_data_files;

22. 查看用戶被賦予的角色

? ? ?SQL> select * from dba_role_privs where grantee='SCOTT';

23. 在PL/SQL Developer中如何查看表結構

? ? ? CTRL+表名

24. 如何原樣輸出字符串

SQL> select q'([@#$%^&*~?/" '])' from dual;Q'([@#$%^&*~?/" --------------- [@#$%^&*~?/" ']SQL> select q'2I'm Victor2' from dual;Q'2I'MVICT ---------- I'm Victor

注意： ?1> Q'后跟起始分隔符，起始分隔符后的字符串原樣輸出，起始分隔符必須有配對的結束分隔符。

? ? ? ? ? 2> 分隔符可以為數字、字母、特殊字符。但'&'不能作為分隔符，因為'&'意思是傳入參數。

? ? ? ? ? 3> '['、'('、'{'作為分隔符，必須以']'、')'、'}'結束。

25. TWO_TASK??

TWO_TASK lets you silently support a tns network connect string, that is, instead of: sqlplus scott/tiger@tnsname you can: export TWO_TASK=tnsname sqlplus scott/tiger <<<== that has an implied @tnsname oracle_sid is used for a local connection - direct to the server - without using a listener the environment variable name is LOCAL on windows NT. set LOCAL=service_name

? ? ?TWO_TASK是環境變量，通過將其設置為網絡字符串，用戶通過網絡登錄時，可不用指定網絡字符串，譬如，sqlplus scott/tiger@orcl,通過將TWO_TASK設置為orcl，用sqlplus scott/tiger可直接登錄。

? ? ?注意：Linux和Windows下的設置方式不同，Linux下為export TWO_TASK=orcl，Windows下為set LOCAL=orcl

26.?wmsys.wm_concat

? ? ?該函數可以把列值以","號分隔起來,并顯示成一行

SQL> select wmsys.wm_concat(dname) from dept;WMSYS.WM_CONCAT(DNAME) -------------------------------------------------------------------------------- ACCOUNTING,RESEARCH,SALES,OPERATIONS,IT

--將逗號替換為'|'號 SQL> select replace(wmsys.wm_concat(dname),',','|') from dept;REPLACE(WMSYS.WM_CONCAT(DNAME),',','|') -------------------------------------------------------------------------------- ACCOUNTING|RESEARCH|SALES|OPERATIONS|IT

27. 如何將一行的值轉換為列

? ? ?原始輸出如下：

SQL> select * from dept where deptno=10;DEPTNO DNAME LOC ---------- -------------- -------------10 ACCOUNTING NEW YORK

? ? ?那么如何將這一行的值轉換為列呢？

SQL> with t as 2 ( select deptno,dname,loc from dept where deptno=10)3 select to_char(deptno) from t4 union all5 select dname from t6 union all7 select loc from t;TO_CHAR(DEPTNO) ---------------------------------------- 10 ACCOUNTING NEW YORK

28. dual

SQL> create table dual as (select * from dual union all select * from dual);Table created.SQL> select * from dual;D - X XSQL> select sysdate from dual;SYSDATE --------- 10-JUN-15 10-JUN-15

29. 生成AWR報告的三個條件，以test用戶為例

? ? ? grant create session to test;
? ? ? grant select any dictionary to test;
? ? ? grant execute on dbms_workload_repository to test;

30. 單引號對應的是chr(39)

? ? ? 39其實是單引號的ASCII碼

31. 如何查找堵塞會話并殺掉它

SQL> select sid,serial#,blocking_session from v$session where username='SCOTT'; SID SERIAL# BLOCKING_SESSION ---------- ---------- ----------------125 7 142SQL> select sid,serial#,username from V$session where sid=142;SID SERIAL# USERNAME ---------- ---------- ------------------------------142 27 SYSSQL> alter system kill session '142,27';System altered.

32. 如何設置sqlprompt

首先利用define命令顯示預定義的SQL*Plus變量列表

SQL>define DEFINE _DATE = "16-JUL-15" (CHAR) DEFINE _CONNECT_IDENTIFIER = "test" (CHAR) DEFINE _USER = "SCOTT" (CHAR) DEFINE _PRIVILEGE = "" (CHAR) DEFINE _SQLPLUS_RELEASE = "1102000400" (CHAR) DEFINE _EDITOR = "vim" (CHAR) DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR) DEFINE _O_RELEASE = "1102000400" (CHAR)

設置sqlprompt

SQL>set sqlprompt &_USER@&_CONNECT_IDENTIFIER> SCOTT@test>

33. 如何查看后臺進程的作用

? ? ?SQL> select name,description from v$bgprocess;

34. 如何查看Oracle的版本號

? ? ?SQL> select * from v$version;

35.?Database Sample Schemas

? ? ??http://docs.oracle.com/cd/E11882_01/server.112/e10831/toc.htm

36. 如何找出給定數之間的最大值和最小值? ? ?

SQL> select least(1,2,3) from dual;LEAST(1,2,3) ------------1SQL> SELECT greatest (1,2,3) from dual ;GREATEST(1,2,3) ---------------3

37. 用shell腳本獲取用戶連接數

? ? ?剛開始是這樣：

#!/usr/bin/sh export ORACLE_HOME=/opt/app/oracle/product/11.2.0/ export TNS_ADMIN=/home/monitor sqlplus='/opt/app/oracle/product/11.2.0/bin/sqlplus' output=`$sqlplus -s test/test@orcl << EOF set feedback off; set heading off; select count(*) from v$session where status='ACTIVE'; EOF` echo $output

? ? 但是執行的時候會報如下錯誤：

select count(*) from v where status='ACTIVE' ERROR at line 1: ORA-04044: procedure, function, package, or type is not allowed here

? ?原因在于$session當做變量了，當然為空了。

? ?根據網上的方案，$前面加個轉義符，即v$session修改為v\$session，但還是沒有效果。

? ?解決方法，在前面定義了一個session變量

#!/usr/bin/sh export ORACLE_HOME=/opt/app/oracle/product/11.2.0/ export TNS_ADMIN=/home/monitor session='$session' sqlplus='/opt/app/oracle/product/11.2.0/bin/sqlplus' output=`$sqlplus -s audit_vces/audit_vces@orcl << EOF set feedback off; set heading off; select count(*) from v$session where status='ACTIVE'; EOF` echo $output

?

? ??

? ?

?

? ? ?

?

?

?

?

? ??

轉載于:https://www.cnblogs.com/ivictor/p/4118304.html

總結

以上是生活随笔為你收集整理的Oracle碎碎念~2的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。