Oracle碎碎念
1. 設定SQL*Plus提示符
SQL> set sqlprompt "_user'@'_connect_identifier>"
SYS@orcl>
為了對所有的SQL*Plus會話自動設定sqlprompt，将上面的指令放置在ORACLE_HOME/sqlplus/admin目錄中的glogin.sql檔案内
2. 檢視角色RESOURCE被授予的系統權限
SQL> select * from dba_sys_privs where grantee='RESOURCE';
GRANTEE PRIVILEGE ADM
------------------------------ --------------------------------------- ---
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE TABLE NO
檢視SCOTT使用者被授予的對象權限和系統權限
SQL> select * from dba_tab_privs where grantee='SCOTT';
GRANT OWNER TABLE_NAME GRA PRIVILEGE GRA HIE
----- ----- ------------- --- ---------------------------------------- --- ---
SCOTT SYS DATA_PUMP_DIR SYS WRITE NO NO
SCOTT SYS DATA_PUMP_DIR SYS READ NO NO
SQL> select * from dba_sys_privs where grantee='SCOTT';
GRANT PRIVILEGE ADM
----- ---------------------------------------- ---
SCOTT UNLIMITED TABLESPACE NO
3. 檢視系統timestamp
SQL> select systimestamp from dual;
4. 在Automatic Memory Management情況下檢視pga和sga的大小
SQL> select sum(value) from v$sesstat natural join v$statname where name='session pga memory';
SQL> select sum(bytes) from v$sgastat;
5. 識别資料庫中的所有無效對象
SQL> select owner,object_name,object_type from dba_objects where status='INVALID';
6. 檢查資料庫補充日志
SQL> select supplemental_log_data_min "MIN",
supplemental_log_data_pk "PK",
supplemental_log_data_ui "UI",
supplemental_log_data_fk "FK",
supplemental_log_data_all "ALL"
from v$database;
7. 檢視資料庫是否運作在歸檔模式
SQL> select log_mode from v$database;
SQL> archive log list
8. 檢視資料庫的字元集
SQL> select * from nls_database_parameters;
9. 檢視對于表的注釋和列的注釋
user_tab_comments
user_col_comments
10. 檢視行對應的資料塊位址
SQL> select empno,ename,rowid,dbms_rowid.rowid_relative_fno(rowid) || '_' ||dbms_rowid.rowid_block_number
(rowid) || '_' || dbms_rowid.rowid_row_number(rowid) location from emp;
EMPNO ENAME ROWID LOCATION
---------- ---------- ------------------ ---------------
7369 SMITH AAAR3sAAEAAAACXAAA 4_151_0
7499 ALLEN AAAR3sAAEAAAACXAAB 4_151_1
7521 WARD AAAR3sAAEAAAACXAAC 4_151_2
7566 JONES AAAR3sAAEAAAACXAAD 4_151_3
4_151_0表示empno為7369的行記錄實際的實體位置位于4号檔案的第151個資料塊的第0行記錄
11. 如何利用非預設位置的spfile啟動資料庫
大家知道，資料庫啟動時一般是在預設位置查找spfile（即在$ORACLE_HOME/dbs下），那麼如何利用非預設位置的spfile啟動資料庫呢？
一、建立參數檔案pfile.ora,裡面隻有一個參數
vim /home/oracle/pfile.ora
spfile=/home/oracle/spfileorcl.ora
二、利用該參數檔案啟動資料庫
SQL> startup pfile='/home/oracle/pfile.ora';
12. 如何傳回目前會話使用者
SQL> select user,uid from dual;
USER UID
------------- ----------
SCOTT 84
USER returns the name of the session user (the user who logged on) with the data type VARCHAR2.
UID returns an integer that uniquely identifies the session user (the user who logged on).
In a distributed SQL statement, the
UID
USER
functions together identify the user on your local database.
13. 如何檢視閃回恢複區的空間使用情況
SQL> select * from v$flash_recovery_area_usage;
14. 當用shutdown immediate正常關庫時，有時會hang住，觀察告警日志，發現以下資訊：
All dispatchers and shared servers shutdown
Wed Jun 18 00:40:49 2014
SHUTDOWN: Active processes prevent shutdown operation
這不是Oralce bug，而是非正常順序關庫導緻的。原因在于主機上登陸使用者（無論是sys還是scott）通過！或者host指令切換到主機環境了。隻要該使用者退回到oracle環境，并斷開連接配接，shutdown immediate即可繼續正常關庫。
15. 如何建立資料庫連結
SQL> grant create database link to scott; -->> 賦權，由dba權限的使用者操作
SQL> create database link sz connect to scott identified by tiger using 'sz'; -->>在scott使用者下操作
SQL> select db_link,username from user_db_links; -->> 檢視目前使用者擁有的資料庫連結
16. 如何使用DBMS_XPLAY生成執行計劃
SQL> @?/rdbms/admin/utlxplan.sql
Table created.
SQL> explain plan for
2 select * from emp;
Explained.
SQL> select * from table(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 352 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 11 | 352 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected. 上述指令與SQL*Plus下的SET AUTOTRACE TRACE EXPLAIN等價。
17. 角色PUBLIC隻有對象權限，沒有系統權限。可通過下表查詢：
SQL> select grantee,privilege from dba_sys_privs where grantee='PUBLIC';
no rows selected
SQL> select grantee,privilege,table_name from dba_tab_privs where grantee='PUBLIC' and table_name like '%UTL_FILE%';
GRANTEE PRIVILEGE TABLE_NAME
------------ ------------ ------------------------------
PUBLIC EXECUTE UTL_FILE
第二個查詢語句表明角色PUBLIC對UTL_FILE包有執行權限
18. 如何捕捉剛執行的SQL語句
SQL> select sql_text from v$sql where parsing_schema_name='SCOTT' order by last_load_time desc;
SQL_TEXT
--------------------------------------------------------------------------------
DELETE FROM EMP WHERE DEPTNO = :B1
delete from dept where deptno=20
19. 如何重建Scott模式
SQL> @?/rdbms/admin/utlsampl.sql
20. 如何在Linux環境下檢視錯誤代碼
[oracle@node2 ~]$ oerr ora 00001
21. 如何建立HR模式
SQL> @?/demo/schema/human_resources/hr_main.sql
在11g2中，human_resources目錄下隻有一個hr_code.sql，其它相關腳本沒有自帶，可網上下載下傳，并上傳到human_resources目錄下
下載下傳位址：http://pan.baidu.com/s/1o6I6Mzo
22. 非PL/SQL變量（來源于環境或宿主語言如C,java）
SQL> VARIABLE message varchar2(15);
SQL> begin
2 :message :='Hello World';
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> print message
MESSAGE
--------------------------------
Hello World SQL> VARIABLE message varchar2(15);
SQL> begin
2 :message :='Hello World';
3 dbms_output.put_line(:message);
4 end;
5 /
Hello World SQL> VARIABLE message varchar2(15);
SQL> exec :message :='Hello World'
SQL> print message
MESSAGE
--------------------------------
Hello World 22. 如何檢視目前會話生成的redo_size
CREATE OR REPLACE VIEW redo_size
AS
SELECT VALUE
FROM v$mystat, v$statname
WHERE v$mystat.statistic# = v$statname.statistic#
AND v$statname.NAME = 'redo size' 23. autotrace
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
解決：
SQL> @?/sqlplus/admin/plustrce.sql
SQL> grant plustrace to public;
24. Multiple Address Lists in tnsnames.ora
TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node3.being.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node3.being.com)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = node3.being.com)(PORT = 1523))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sz.being.com)
)
) 預設LOAD_BALANCE on 和FAILOVER on
25. how to free up space from recovery area
1> Consider changing RMAN RETENTION POLICY. If you are using Data Guard,then consider changing RMAN ARCHIVELOG DELETION POLICY.
2> Back up files to tertiary device such as tape using RMAN BACKUP RECOVERY AREA command.
3> Add disk space and increase db_recovery_file_dest_size parameter to reflect the new space.
4> Delete unnecessary files using RMAN DELETE command. If an operating system command was used to delete files, then use RMAN CROSSCHECK and DELETE EXPIRED commands.
26. 建立目錄
SQL> create directory tmp as '/home/oracle';
Directory created.
SQL> grant read,write on directory tmp to scott;
Grant succeeded.
SQL> select * from dba_directories;
27. 如何啟動Minimal Supplemental Logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
28. 檢視表的實體位置
SQL> select
2 (select name from v$datafile
3 where file#=e.file_id) file_name,
4 file_id,block_id,blocks
5 from dba_extents e
6 where owner='SCOTT' and segment_name='EMP';
FILE_NAME FILE_ID BLOCK_ID BLOCKS
---------------------------------------- ---------- ---------- ----------
/u01/app/oracle/oradata/sz/users01.dbf 4 144 8 可見，EMP表占用8個連續的資料塊，從144号151号，均在4号資料檔案users01.dbf中
29. 檢視使用者擁有的索引，及建立在哪些字段上
SQL> select i.owner,i.table_name,i.index_name,c.column_name
2 from dba_indexes i,dba_ind_columns c
3 where i.owner=c.index_owner
4 and i.index_name=c.index_name
5 and i.table_owner='SCOTT';
OWNER TABLE_NAME INDEX_NAME COLUMN_NAM
----- ---------- ---------- ----------
SCOTT EMP PK_EMP EMPNO
SCOTT DEPT PK_DEPT DEPTNO 30. 删除資源回收筒對象
SQL> purge table "BIN$Ax1lzh8y4Q/gUKjADQIGqA==$0"; --删除一個特定的資源回收筒對象
SQL> purge user_recyclebin; --删除目前使用者的資源回收筒的所有對象
SQL> purge tablespace users; --删除USERS表空間中的所有的資源回收筒對象
SQL> purge dba_recyclebin; --删除資料庫中所有的資源回收筒中的所有對象
31. 如何在SCN和時間戳之間進行轉換
SQL> select dbms_flashback.get_system_change_number scn from dual;
SCN
----------
1086480
1 row selected.
SQL> select scn_to_timestamp(1086480) from dual;
SCN_TO_TIMESTAMP(1086480)
---------------------------------------------------------------------------
16-SEP-14 04.49.19.000000000 AM
1 row selected.
SQL> select timestamp_to_scn(to_timestamp('2014-09-16 04:44:15','yyyy-mm-dd hh24:mi:ss')) scn from dual;
SCN
----------
1086363
1 row selected. 32. 在資料泵(Data Pump)中如何使用 / as sysdba
[oracle@node3 ~]$ expdp \"/ as sysdba\" dumpfile=scott.dmp schemas=scott
33. Oracle Database 11g Release 2 Examples下載下傳位址
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
選擇相應的平台，點選See All
34. GUID(Globally Unique Identifier)，全局唯一辨別符
SQL> select sys_guid() from dual;
SYS_GUID()
--------------------------------
040E20AF25012D8EE050A8C00D0205B1
GUID由16個位元組組成的RAW資料類型，由Oracle通過目前的日期和時間、主伺服器名稱和程序辨別符來唯一生成。這個辨別符可以是在Streams複制中定義為表的主鍵或是主鍵的一部分。當插入一行内容到表中時，這個辨別符可以用一個觸發器觸發生成。
35. RMAN中清除已手工删除的日志資訊
RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;
36. 如何完全删除流複制
SQL> EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)