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();

 

 

相关文章