oracle知识

1.启动监听器：

    lsnrctl start./stop

2.启动企业管理数据库控制台：

    emctl start/stop dbconsole.

3.访问企业管理控制台：

    http://localhost:1158/em

4.使用sqlplus访问ORACLE数据库：

   sqlplus /nolog

   connect / as sysdba

   (sqlplus

    user: sys as sysdba

   password:)

5.查看某个表空间中哪些用户拥有哪些表：

   select owner,table from  dba_tables where table_name='EXAMPLE';

6.自动清除归档日志

   /*autodelete.bat:

     set oracle_sid=orcl
     rman target / log c:/%date:~0,10%.log cmdfile c:/autodelete.rman

   autodelete.rman

     sql 'alter system archive log current';
     sql 'alter system archive log current';
     sql 'alter system archive log current';
     RUN {
       CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
       backup archivelog all format 'f:/database_backup/%U' delete all input;
       backup current controlfile format 'f:/database_backup/%U';
       delete backup of archivelog all completed before 'sysdate-1';
       delete obsolete;
     }*/

7.DATABASE LINK的创建

 

CREATE DATABASE LINK <Link Name>
 CONNECT TO<Schema Name>
  IDENTIFIED BY<PWD>
  USING '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <Host Name>)(PORT = <port#>))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = <Database Name>)
    )
  )' ;

 或者

 

CREATE DATABASE LINK <Link Name>
 CONNECT TO<Schema Name>
  IDENTIFIED BY<PWD>
  USING connection_string;

8.NO_DATA_FOUND和%NOTFOUND的区别

    一般初学者对Oracle的NO_DATA_FOUND和%NOTFOUND两个保留字的用法不够了解，主要是它们从意义上是有些类似。其实它们的用法是有区别的，小结如下：
 SELECT  .  .  .  INTO  语句触发  NO_DATA_FOUND；
 当一个显示光标的 where 子句未找到时 触发 %NOTFOUND；
 当UPDATE或DELETE 语句的where 子句未找到时 触发 SQL%NOTFOUND；
 在光标的提取(Fetch)循环中要用 %NOTFOUND 或%FOUND 来确定循环的退出条件，不要用 NO_DATA_FOUND。

9.关于dba_constraints/dba_ind_columns和user_constraints/user_ind_columns表的数据存储及用户权限管理

  用connect / as sysdba登录，select distinct owener from dba_constraints包含所有的拥有者(用户），而此时查询select distinct owner from user_constraints的结果中有且只有sys，因为此时你是用系统用户sys登录的，那么user_constraints表中的owner有且只有一个用户sys。

 同理查询dba_ind_columns和user_ind_columns系统表中的table_owner也是一个道理。

  用connct 用户/密码登录， select distinct owener from dba_constraints会提示“表或视图不存在”，除非系统用户为当前用户给了查询此系统表的权限！当你查询select distinct owner from user_constraints的结果中有且只有当前用户，不会有其他用户在里面，从中可以看出，ORACLE对用户的权限管理的是非常严格的。

  同理查询dba_ind_columns和user_ind_columns系统表中的table_owner也是一个道理。

  dba(user)_ind_columns和dba(user)_constraints两个表中存储的数据也是不一样的。

  dba(user)_constraints中仅存储约束（主键等），而dba(user)_ind_columns中不仅存储索引（主键也是一种索引，叫唯一索引）还存储有“列是否非空”等约束信息。

10.系统表DBA_USERS

  ColumnDatatypeNULLDescriptionUSERNAMEVARCHAR2(30)NOT NULLName of the userUSER_IDNUMBERNOT NULLID number of the userPASSWORDVARCHAR2(30)Encrypted passwordACCOUNT_STATUSVARCHAR2(32)NOT NULLAccount status:

• OPEN

• EXPIRED

• EXPIRED(GRACE)

• LOCKED(TIMED)

• LOCKED

• EXPIRED & LOCKED(TIMED)

• EXPIRED(GRACE) & LOCKED(TIMED)

• EXPIRED & LOCKED

• EXPIRED(GRACE) & LOCKED

LOCK_DATEDATEDate the account was locked if account status was LOCKEDEXPIRY_DATEDATEDate of expiration of the accountDEFAULT_TABLESPACEVARCHAR2(30)NOT NULLDefault tablespace for dataTEMPORARY_TABLESPACEVARCHAR2(30)NOT NULLName of the default tablespace for temporary tables or the name of a tablespace groupCREATEDDATENOT NULLUser creation datePROFILEVARCHAR2(30)NOT NULLUser resource profile nameINITIAL_RSRC_CONSUMER_GROUPVARCHAR2(30)Initial resource consumer group for the userEXTERNAL_NAMEVARCHAR2(4000)User external name相关命令和表（视图）：

   

  alter user <user_name> account unlock;  –解锁 

  alter user <user_name> password expire;  –密码过期 

  alter user  <user_name>  identified by <password>; –重设密码 

  或者 alter profile default limit failed_login_Attempts unlimited; --对DEFAULT配置的失败登录次数不限制。

  11G中查询用户是否修改过默认值，可使用DBA_USERS_WITH_DEFPWD视图，如果用户在这个表中，说明此用户未曾修改过默认密码。

  11G以前是查看DBA_USER中的PASSWORD字段，具体方法如下：

      创建一个自定义表，首先记录下用户默认密码的HASH值，然后将用户现在的密码与之前自定义表中的PASSWORD是不是相同的，如果相同则表明此用户未曾修改过默认密码，否则视为修改过。

查看角色所包含的权限

  select * from role_sys_privs where role='RESOURCE';

  select * from role_sys_privs where role='CONNECT';

start with ... connect by prior的用法举例

select * from base_dept_info
          start with deptno = '0431000000'
          connect by prior deptno = updeptno

查询结果：

1 0431000000 吉林省 1 0000000000 0
2 0431043100 长春市 2 0431000000 0
3 0431043200 吉林市 2 0431000000 0
4 0431043300 延边朝鲜族自治州 2 0431000000 0
5 0431043400 四平市 2 0431000000 0
6 0431043500 通化市 2 0431000000 0
7 0431043600 白城市 2 0431000000 0
8 0431043700 辽源市 2 0431000000 0
9 0431043800 松原市 2 0431000000 0
10 0431043900 白山市 2 0431000000 0
11 0431043911 白山市下面的县市 3 0431043900 1

select * from base_dept_info
          start with deptno = '0431000000'
          connect by prior updeptno = deptno

查询结果：

1 0431000000 吉林省 1 0000000000 0
       2 0000000000 全国总机构 0 9999999999 0
存取路径分为全表扫描和索引扫描、快速索引全扫描

CREATE TABLE FRANK.BIG1 AS SELECT * FROM DBA_OBJECTS;

CREATE INDEX FRANK.BIG1_IDX ON FRANK.BIG1(OBJECT_ID);

EXECUTE DBMS_STATS.GATHER_TABLE_STATS('FRANK,'BIG1',CASCADE=>TRUE);

全表扫描：当没有其他的存储路径可以使用时，这时只要将表中所有用过的数据块都读到缓冲区中，再从这些数据块中找到符合条件的数据行，也可以得到所需要的数据行，这种方法便是全表扫描。一次读多少数据块，取决于DB_FILE_MULTIBLOCK_READ_COUNT参数的设置。

索引扫描：研发索引这种结构，便是将数据行的某些字段值作为索引值，并将索引值与此数据行的标识码结合在一起，组成索引项，将索引项存储在索引段中。SET AUTOTRACE  {OFF | ON  |TRACEONLY} [EXPLAIN] [STATISTICS],若使用SET AUTOTRACE ON,SQL语句被执行时，将会呈现执行结果、执行计划与执行后的统计数据；若使用SET AUTOTRACE TRACEONLY {EXPLAIN | STATISTICS},将只返回执行计划与执行后的统计数据。但是SQL还是被执行了，只是不呈现执行结果而已；若使用SET AUTOTRACE ON {EXPLAIN| STATISTICS},则要求呈现执行结果与执行后的统计数据。

/*+ FULL（表名）*/ HINT要求使用全表扫描存取表。

快速全索引扫描（FAST FULL INDEX SCAN):如果索引可以作为存储路径，当需要同时存取索引的大多数块时，便可以使用快速全索引扫描的方式存取索引。将全表扫描的读取方法用在索引扫描上，让索引扫描时也能够一次读取多个索引块，这种存取路径称为快速全索引扫描。例：SELECT COUNT(OBJECT_ID) FROM FRANK.BIG1(在OBJECT_ID字段上已建索引），这时会用到快速全索引扫描

id                                   operation                                    name

0                                SELECT STATEMENT               

1                               SORT AGGREGATE

2                             INDEX FAST FULL SCAN             BIG_IDX

 

建分区表时，如果在表名后指定表空间，如果分区属性没有指定表空间的话，那么此分区的默认表空间即为建表时指定的表空间。