OCP学习笔记

OCP学习笔记

第一部份　SQL

第二部份　DBA_1

常用操作：

1、启动em

emctl start | stop dbconsole

http://192.168.5.18:1158/em/

2、启动listener

  lsnrctl start | stop

3、为了使某个用户以 SYSDBA或 SYSOPER 身份登录 iSQL*Plus

必须通过执行以下步骤在 OC4J 用户管理器中设置该用户：

1.  创建一个用户

2.  给该用户授予webDba 角色

 

$ cd$ORACLE_HOME/oc4j/j2ee/isqlplus/application-deployments/isqlplus

$JAVA_HOME/bin/java -Djava.security.properties=$ORACLE_HOME/oc4j/j2ee/home/config/jazn.security.props-jar $ORACLE_HOME/oc4j/j2ee/home/jazn.jar -user "iSQL*Plus DBA/admin"-password welcome -shell

JAZN> adduser "iSQL*Plus DBA" username password  

eg: adduser "iSQL*Plus DBA" wddg wddgmy

JAZN> grantrole webDba "iSQL*Plus DBA" username   

eg:grantrole webDba "iSQL*Plus DBA" wddg

4、创建spfile

create spfile=’/home/oracle/spfiletemp.ora’ from pfile=’/home/oracle/inittemp.ora’

5、查看动态性能视图

select* from v$fixed_table

eg:查看包含session的动态性能视图

select* from v$fixed_table where name like 'V%SESS%' order by name

6、刷新共享池

Altersystem flush shared_pool;

7、重建索引

Alterindex index_name rebuild;

8、查看当前执行的sql

selectsql_text,executions from v$sql

wherecpu_time>200000;

9、查看当前会话

select * fromv$session

where machine=’XZT198’and logon_time>sysdate-1;

10、查看当前锁

select sid,ctime fromv$lock where block>0;

11、强制产生重做日志切换命令

Alter system switch logfile

12、强制产生检查点命令

Alter system checkpoint

13、设置归档模式

Startup mount

Alter database archivelog;

Alter database noarchivelog;

14、查看归档模式

Archive log list;

Select name,log_mode from v$database;

15、显示归档参数

Show parameter;

Show parameter  log_archive_dest;

Show parameter db_recovery_file_dest;

16、查看重做日志配置信息

Select group#,members,bytes,status,archivedfrom v$log;

Select * from v$logfile;

17、查看数据文件

Select name,status from v$datafile;

Selectfile_id,file_name,tablespace_name,status,bytes from dba_data_files;

18、查询Oracle数据库的名字、创建时间、日志模式、数据库状态等(v$database)

selectname,created,log_mode,open_mode from v$database;

19、查询Oracle数据库主机名、实例名、版本信息(v$instance)

Col host_name for a15

Selecthost_name,instance_name,version from v$instance;

20、查询Oracle数据库版本详细信息

Select * from v$version;

21、查询所有表空间及表空间状态(dba_tablespaces)

Col tablespace_name for a15

Selecttablespace_name,block_size,status,contents,logging from dba_tablespaces;

22、查询每个表空间的位置及文件的名字等信息(dba_data_files)

Col file_name for a40

Select file_id,file_name,tablespace_name,status,bytes fromdba_data_files;

23、查询Oracle中有所有用户及创建时间(dba_users)

Select username,createdfrom dba_users;

24、移动表空间中数据文件的路径

--首先确定数据文件的状态 要为OFFLINE

selecttablespace_name,status,contents from dba_tablespaces

---查询数据文件的路径

select file_id,file_name,tablespace_namefrom dba_data_files where file_name like '%ORACLE%' order by file 

--将该表空间修改为OFFLINE

altertablespace USERS offline;

--查看表空间状态确定修改成功

selecttablespace_name,status,contents from dba_tablespaces; 

--移动数据文件

hostcopy D:\oradata\USERS01.DBF d:\oradata\aucdbf

--重新命名 该表空间的路径和名称

alter tablespace users renamedatafile 'd:\oradata\USERS01.DBF' to 'd:\oradata\USERS01.DBF'

--修改表空间的状态为 ONLINE 状态

altertablespace users online

25、移动表和索引到其他表空间

--查询该对象存放在哪个表空间

selectsegment_name,tablespace_name,extents,blocks from dba_segments whereowner='ZHANG'

--2.查询该对象是索引，还是表

selectobject_id,object_name,object_type,status,created from dba_objects whereowner='ZHANG'

--3.查询索引或者表 存放在哪个表空间

selectindex_name,table_name,tablespace_name,status from dba_indexes whereowner='ZHANG'

--4.移动表到另一个表空间

altertable zhang.zzq_1 move tablespace zhang_zzq

--查询该表是否移动到该表空间

selectsegment_name,tablespace_name,extents,blocks from dba_segments whereowner='ZHANG'

--检查表是否有效，状态为 VALID 是有效

selectobject_id,object_name,object_type,status,created from dba_objects whereowner='ZHANG'

--重建索引 并且将索引移动到另一个表空间

alterindex zhang.zzq_index rebuild tablespace zhang_zzq

--查询索引对应的表空间

selectindex_name,table_name,tablespace_name,status from dba_indexes whereowner='ZHANG'

26、查看表空间的使用率

--脚本1

selectf.tablespace_name,a.total,f.free,round((f.free/a.total)*100) "% Free"from

(select tablespace_name,sum(bytes/(1024*1024)) total from dba_data_files group by   tablespace_name) a,

(select tablespace_name,round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name)f

WHEREa.tablespace_name = f.tablespace_name(+)
order by "% Free";

 

--脚本2

select upper(f.tablespace_name)"TS-name",
       d.tot_grootte_mb"TS-bytes(m)",
       d.tot_grootte_mb - f.total_bytes"TS-used (m)",
       f.total_bytes"TS-free(m)",
       to_char(round((d.tot_grootte_mb -f.total_bytes) / d.tot_grootte_mb * 100,2),'990.99') "TS-per"
         from (select tablespace_name,
               round(sum(bytes) / (1024 *1024), 2) total_bytes,
               round(max(bytes) / (1024 *1024), 2) max_bytes
          from sys.dba_free_space
         group by tablespace_name) f,
       (select dd.tablespace_name,
               round(sum(dd.bytes) /(1024 * 1024), 2) tot_grootte_mb
          from sys.dba_data_files dd
         group by dd.tablespace_name) d
where d.tablespace_name = f.tablespace_name
order by 5 desc

 

--脚本3

SELECT D.TABLESPACE_NAME,
       SPACE "SUM_SPACE(M)",
       BLOCKS SUM_BLOCKS,
       SPACE - NVL(FREE_SPACE, 0)"USED_SPACE(M)",
       ROUND((1 - NVL(FREE_SPACE, 0) /SPACE) * 100, 2) "USED_RATE(%)",
       FREE_SPACE"FREE_SPACE(M)"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 *1024), 2) SPACE,
               SUM(BLOCKS) BLOCKS
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) D,
       (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 *1024), 2) FREE_SPACE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F
 WHERE D.TABLESPACE_NAME =F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,
       SPACE "SUM_SPACE(M)",
       BLOCKS SUM_BLOCKS,
       USED_SPACE"USED_SPACE(M)",
       ROUND(NVL(USED_SPACE, 0) / SPACE *100, 2) "USED_RATE(%)",
       NVL(FREE_SPACE, 0)"FREE_SPACE(M)"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 *1024), 2) SPACE,
               SUM(BLOCKS) BLOCKS
          FROM DBA_TEMP_FILES
         GROUP BY TABLESPACE_NAME) D,
       (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES_USED) /(1024 * 1024), 2) USED_SPACE,
               ROUND(SUM(BYTES_FREE) /(1024 * 1024), 2) FREE_SPACE
          FROM V$TEMP_SPACE_HEADER
         GROUP BY TABLESPACE_NAME) F
 WHERE D.TABLESPACE_NAME =F.TABLESPACE_NAME(+)
 ORDER BY 5 DESC

27、查看运行过的SQL语句

SELECT SQL_TEXT FROM V$SQL;

28、检查Oracle初始化文件中相关参数值

Selectresource_name,max_utilization,initial_allocation,limit_value

from v$resource_limit;

29、查询Oracle属性值

Select * from database_properties;

第6章管理用户安全性

1、创建用户

在 Enterprise ManagerDatabase Control 中，选择“Administration >Schema > Users & Privileges > Users（管理 > 方案 > 用户和权限 > 用户）”。 

SQL：

CREATE USER"TRAIN" PROFILE "DEFAULT" IDENTIFIED BY "*******"ACCOUNT UNLOCK

GRANT "CONNECT"TO "TRAIN"

CONNECT角色：只有一个create session权限

2、管理员验证

操作系统安全性

l  DBA 必须具有创建或删除文件的操作系统权限。

l  普通数据库用户不应具有创建或删除数据库文件的操作系统权限。 

管理员安全性

l  通过口令文件或操作系统验证 SYSBA 和 SYSOPER 连接。

l  口令文件验证会按名称记录 DBA 用户。

l  操作系统验证并不记录具体用户。

l  对于 SYSDBA 和 SYSOPER，操作系统验证优先于口令文件验证。

 

操作系统安全性：在 UNIX 和 Linux 中，默认情况下，DBA 属于 install 操作系统组，该组具有创建和删除数据库文件所需的权限。

管理员安全性：只在使用口令文件或操作系统特权与权限完成验证后，才授权建立 SYSBA 和 SYSOPER 连接。如果使用操作系统验证，那么数据库就不 使用提供的用户名和口令。当口令文件不存在，或者该文件中不存在提供的用户名和口令，或者未提供用户名和口令时，就使用操作系统验证。

但是，如果使用口令文件成功完成了验证，则使用用户名记录连接。如果使用操作系统成功完成了验证，则表示这是一个 CONNECT/连接，这种连接不记录具体用户。

注：操作系统验证优先于口令文件验证。特别是，如果您是操作系统中 OSDBA 或 OSOPER 组的成员，而且以 SYSDBA 或 SYSOPER 身份进行连接，则会使用关联的管理权限为您建立连接，不管您指定的用户名和口令是什么。 

密码文件:$ORACLE_HOME/dbs/orapw<SID>

3、权限

l   查询所有系统权限

select * fromsystem_privilege_map order by 2

l  SYSDBA 和 SYSOPER

使用这两个权限可以在数据库中执行关闭、启动、恢复及其它管理任务。用户使用 SYSOPER可执行基本操作任务，但不能查看用户数据。这个权限包括以下系统权限：

STARTUP 和 SHUTDOWN

CREATE SPFILE

ALTER DATABASE OPEN/MOUNT/BACKUP

ALTER DATABASE ARCHIVELOG

ALTER DATABASE RECOVER（仅限完全恢复。任何形式的不完全恢复，

如 UNTILTIME|CHANGE|CANCEL|CONTROLFILE，需要以 SYSDBA 身份建立连接。）

RESTRICTED SESSION

除此之外，SYSDBA 系统权限还可授权执行不完全恢复和删除数据库。用户使用 SYSDBA 系统权限可以 SYS 用户身份有效地建立连接。

l   DROP ANY 对象

用户使用DROP ANY 权限可删除其它用户拥有的对象。

l   CREATE、MANAGE、DROP 和 ALTER TABLESPACE

这些权限用于表空间管理，包括创建、删除和更改表空间的属性。

l  CREATE ANY DIRECTORY

使用Oracle 数据库可以让开发人员在 PL/SQL 内调用外部代码（例如 C 库）。作为一种安全措施，代码所在的操作系统目录必须链接到一个虚拟 Oracle 目录对象。使用 CREATE ANY DIRECTORY 权限时，有可能会调用不安全的代码对象。

用户使用 CREATE ANY DIRECTORY 权限可以在 Oracle 软件所有者能够访问的任何目录中创建目录对象（具有读写访问权限）。这意味着用户可以访问那些目录中的外部过程。用户可以尝试直接读写任何数据库文件，如数据文件、重做日志和审计日志。一定要确保在组织中采用了安全策略，以防止误用类似这种作用很强的权限。

l   GRANT ANY OBJECT PRIVILEGE

使用此权限可以对您未拥有的对象授予对象权限。

l   ALTER DATABASE 和 ALTER SYSTEM

这些权限的作用很强，可用于修改数据库和Oracle 实例，如重命名数据文件或刷新缓冲区高速缓存。

4、锁定与解锁用户

l  解锁：

alter user scottaccount unlock;

l  锁定

alter user scottaccount lock;

5、设置配额（quotas）

alter user scottquota 1M on users;

6、对象权限

(1) 授权

grantselect,update,insert,delete on hr.employess to scott

(2) 撤销授权

revoke select onhr.employees from scott

REVOKE CREATE TABLE FROM jeff

注：

撤销具有with adminoption的系统权限无级联撤销

撤销具有with grantoption的对象权限有级联撤销

 

 

 

7、角色

(1) 预定义角色

CONNECT	CREATE SESSION
RESOURCE	CREATE CLUSTER、CREATE INDEXTYPE、CREATE OPERATOR、CREATE PROCEDURE、CREATE SEQUENCE、CREATE TABLE、CREATE TRIGGER、CREATE TYPE
SCHEDULER_ ADMIN	CREATE ANY JOB、CREATE EXTERNAL JOB、CREATE JOB、EXECUTE ANY CLASS、EXECUTE ANY PROGRAM、MANAGE SCHEDULER
DBA	具有大多数系统权限和其它若干个角色。请不要授予非管理员。
SELECT_ CATALOG_ ROLE	没有系统权限，但是具有关于数据字典的 HS_ADMIN_ROLE 权限和 1,700 多个对象权限。

 

(2) 创建角色

grant role r1 no identified(no identified可不写)

grant role r2 indetified by oracle

(3) 赋予权限

grant create session to r1

(4) 授予角色

grant r1 toscott

grant r2 toscott

取消r2的default选项,对于有密码的角色，不能设置为default启用，否则相当于没设密码

alter user scottdefault role all except r2

(5) 启用带密码的角色

set role r2identified by oracle

 

第7章管理方案对象

1、完整性约束条件 

(1) NOT NULL（非空）

默认情况下，表中的所有列均允许为空值。Null 指没有值。NOT NULL 约束条件要求表列必须包含非空值。例如，可以通过定义 NOT NULL 约束条件来要求在 EMPLOYEES 表的每一行的 LAST_NAME 列中输入值。 oracle中null是不等于null的。

(2) UNIQUE Key（唯一键）

UNIQUE 键完整性约束条件要求一列或一组列（键）中的每个值必须唯一，即，在指定的列或一组列中，表任意两行的值不重复。例如，如果要禁止每行显示重复的部门名称，请对DEPARTMENTS 表的 DEPARTMENT_NAME列定义 UNIQUE 键约束条件。除了特殊情况，这个约束条件与唯一索引一起强制使用。

(3) PRIMARY KEY（主键）

数据库中的每个表最多可包含一个PRIMARY KEY 约束条件。在这个约束条件的限制下，单个列或一组列中的值就构成了行的唯一标识符。事实上，每行的名称均由其主键值确定。

Oracle 服务器实施 PRIMARY KEY 完整性约束条件后可确保实现以下两个条件：

在指定列或一组列中，表的任意两行不出现重复值。

主键列不允许为空。也就是说，每行的主键列必须有一个值。 正常情况下，数据库通过使用索引来强制设置 PRIMARY KEY 约束条件。通过隐式创建以下各项，可强制设置为DEPARTMENTS 表中 DEPARTMENT_ID列创建的主键约束条件： 

该列中的唯一索引

该列的 NOT NULL 约束条件

(4) 引用完整性约束条件

关系数据库中的各个表通过公用列相互关联，因此必须维护用于管理列关系的规则。使用引用完整性规则就可确保保持这些关系。  对于每个表行，引用完整性约束条件要求外键中的值必须与父键中的值匹配。 

例如，对 EMPLOYEES 表的 DEPARTMENT_ID列定义了一个外键。它保证这一列中的每个值一定与 DEPARTMENTS 表主键中的值匹配。因此，在 DEPARTMENTS 表的 DEPARTMENT_ID列中不会存在错误的完整性约束条件。此类外键引用同一表中的父键。

(5) 检查约束条件

单个列或一组列的CHECK 完整性约束条件要求为每个表行指定的条件必须为真或未知。如果数据操纵语言 (DML) 语句对 CHECK约束条件的评估为假，则会回退语句。

2、增加约束

alter table employees add constraintcheck_ttt check(salary>3000)

3、删除约束

alter table employees drop consitraintcheck_ttt

4、延迟约束校验

增加deferrable initially deferred子句

约束校验默认是在每个 DML 语句结束时强制执行的, 又称 即时约束条件

延迟约束条件是提交事务处理（commit）时才检查的约束条件, 如果在提交时检测到任何违反约束条件的情况，则会回退整个事务处理

alter tableemployees add constraint check_ttt check(salary>3000)

deferrableinitially deferred

5、使用 SQL 创建约束条件

alter table employees add(unique(country_name) enable novallidate)

alter table employees add constraint pkprimary key (employee_id)

alter table t1

(

pk numberprimary key,

fk number

c1 number,

c2 number,

constraint riforeign key (fk) references t1,

constraint ck1check (pk>0 and c1>0)

);

6、删除表

DROP TABLE [schema.] table [CASCADECONSTRAINTS] [PURGE]

DROPTABLE 语句的可选子句：

CASCADE CONSTRAINTS：强制删除，删除所有相关的引用完整性约束条件

PURGE：无法闪回

使用 DROP TABLE 的某些注意事项如下： 

l   如果不使用 PURGE 子句，则表定义、关联索引和触发器会被放置在回收站中。表数据仍然存在，但不存在表定义的情况下无法访问。如果使用 Enterprise Manager 删除了表，则不需要使用 PURGE 子句。

l   使用 FLASHBACK TABLE 命令可从回收站恢复方案对象。PURGE RECYCLEBIN 命