Oracle碎碎念~1

    SQL> select sum(bytes) from v$sgastat;

      USER returns the name of the session user (the user who logged on)
with the data type VARCHAR2.

     SQL> select grantee,privilege,table_name from dba_tab_privs
where grantee=’PUBLIC’ and table_name like ‘%UTL_FILE%’;  

    SQL> select supplemental_log_data_min “MIN”, 

      USER                    UID
     ————-           ———-
      SCOTT                  84

  Table created.

16.
怎么着行使DBMS_XPLAY生成执行安排

       4_151_0表示empno为7369的行记录实际的大体地方位于4号文件的第叁56个数据块的第0行记录

14.
当用shutdown
immediate寻常关库时,有时会hang住,观望告警日志,发现以下新闻:

    SQL> select log_mode from v$database;

5.
识别数据库中的全体无效对象

   SQL> select * from dba_sys_privs where grantee=’RESOURCE’;


   SCOTT SYS DATA_PUMP_DIR SYS WRITE NO NO
   SCOTT SYS DATA_PUMP_DIR SYS READ NO NO

    SQL> select owner,object_name,object_type from dba_objects
where status=’INVALID’;

     SQL> grant create database link  to scott;   –>>
赋权,由dba权限的用户操作

     SQL> select db_link,username from user_db_links;  
 –>> 查看当前用户拥有的数据库链接

    SQL> select sum(value) from v$sesstat natural join
v$statname  where name=’session pga memory’;

    user_col_comments

   
为了对具备的SQL*Plus会话自动安装sqlprompt,将上边的命令放置在ORACLE_HOME/sqlplus/admin目录中的glogin.sql文件内


     一 、创设参数文件pfile.ora,里面唯有一个参数

      UID returns an integer that uniquely identifies the session user
(the user who logged on).

    SYS@orcl>

   
 我们知道,数据库运营时一般是在暗许地方查找spfile(即在$ORACLE_HOME/dbs下),那么什么样使用非默许地方的spfile运维数据库呢?

15. 什么成立数据库链接

6. 反省数据库补充日志

                      supplemental_log_data_all “ALL” 

8. 翻看数据库的字符集

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> select user,uid from dual;

   GRANTEE                               PRIVILEGE                      
                  ADM
   ——————————  

              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. 在Automatic Memory
Management意况下查看pga和sga的高低

2.
查看剧中人物RESOURubiconCE被赋予的系统权限

7. 查看数据库是还是不是运维在归档形式

     no rows selected

    SQL> select systimestamp from dual;

     第三个查询语句申明角色PUBLIC对UTL_FILE包有实施权限

     SQL> select grantee,privilege from dba_sys_privs where
grantee=’PUBLIC’;

    
 SQL> select sql_text from v$sql where
parsing_schema_name=’SCOTT’ order by last_load_time desc;

      SQL_TEXT

   查看SCOTT用户被予以的目的权限和系统权限

     GRANTEE PRIVILEGE TABLE_NAME
     ———— ———— ——————————
     PUBLIC EXECUTE UTL_FILE

    SQL> archive log list

    SQL> set sqlprompt “_user’@’_connect_identifier>”

   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

     SQL> select
empno,ename,rowid,dbms_rowid.rowid_relative_fno(rowid) || ‘_’
||dbms_rowid.rowid_block_number

      SQL> select * from v$flash_recovery_area_usage;

                      supplemental_log_data_pk “PK”, 

             from v$database;

          spfile=/home/oracle/spfileorcl.ora

     SQL> create database link sz connect to scott identified by
tiger using ‘sz’;    –>>在scott用户下操作

     二 、利用该参数文件运转数据库

      上述命令与SQL*Plus下的SET AUTOTRACE TRACE EXPLAIN等价。

18. 如何捕捉刚实施的SQL语句

    SQL> select * from nls_database_parameters;

10. 查看行对应的多少块地方

          vim /home/oracle/pfile.ora

   GRANT PRIVILEGE ADM
   —– —————————————- —
   SCOTT UNLIMITED TABLESPACE NO

3. 查看系统timestamp

17.
剧中人物PUBLIC只有对象权限,没有系统权限。可透过下表查询:

      All dispatchers and shared servers shutdown
      Wed Jun 18 00:40:49 2014
      SHUTDOWN: Active processes prevent shutdown operation

    user_tab_comments

  SQL> @?/rdbms/admin/utlxplan.sql

      In a distributed SQL statement, the UID and USER functions
together identify the user on your local database. 

      那不是Oralce
bug,而是难堪顺序关库导致的。原因在于主机上登陆用户(无论是sys依旧scott)通过!只怕host命令切换来主机环境了。只要该用户退回到oracle环境,并断开连接,shutdown
immediate即可继续健康关库。

     

      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.  什么创立H奥迪Q3方式

      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表占用玖个一而再的数据块,从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;
 –删除USERubiconS表空间中的全数的回收站对象

     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.
LX570MAN中清除已手工业删除的日志消息

     RMAN> crosscheck archivelog all;

     RMAN> delete expired archivelog all;

36. 怎么样完全除去流复制

     SQL>  EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();

 

 

             (rowid) || ‘_’ || dbms_rowid.rowid_row_number(rowid)
location from emp;

11.
如何行使非暗中认可地方的spfile运行数据库

                      supplemental_log_data_ui “UI”,

                      supplemental_log_data_fk “FK”, 

9. 查看对于表的注释和列的诠释

13.
怎么着查看闪回复苏区的长空利用情状

   SQL> select * from dba_sys_privs where grantee=’SCOTT’;

   GRANT OWNER TABLE_NAME GRA PRIVILEGE GRA HIE
   —– —– ————- —

          SQL> startup pfile=’/home/oracle/pfile.ora’;

1. 设置SQL*Plus提示符

12. 什么回到当前对话用户

   SQL> select * from dba_tab_privs where grantee=’SCOTT’;

相关文章