Oracle碎碎念~1
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 and 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();
