oracle11g数据库的管理 学习笔记

原文地址：http://polop777.blog.163.com/blog/static/82453986201141110243264/

一、oracle数据库结构 

1.查看所有表空间及对应的数据文件 
sqlplus /nolog 
conn /as sysdba
col file_name for a50; 
set linesize 140; 
select file_name,tablespace_name,bytes from dba_data_files;
2.临时表空间与临时文件 
select tablespace_name,file_name from dba_temp_files;
3.控制文件 
desc v$controlfile; 
set linesize 120; 
col name for a100; 
select name,status from v$controlfile;
4.日志文件 
重做日志文件 
col member for a50; 
select group#,status,member from v$logfile;
归档日志文件 
查询是否是归档模式 
select dbid,name,log_mode from v$database;
显示归档文件路径 
show parameter log_archive_dest;
5.密码文件/跟踪文件/警告日志 
创建密码文件 
orapwd file=$oracle_home/oracle/intra.passwd
警告日志文件路径 
select value from v$parameter where name=’background_dump_dest’;
用户跟踪文件 
select value from v$parameter where name=’user_dump_dest’;
6.查看实例进程信息，默认启动200多个后台进程 
set pagesize 50; 
select name,description from v$bgprocess;
7.显示pga,sga信息 
set line 160; 
show parameter pga; 
show parameter sga;
8.数据字典 
构成 
user_    记录用户对象信息 
all_    记录用户所有对象信息 
dba_    数据库实例的所有对象信息 
v$_    动态视图，系统管理和优化使用的视图 
gv_    分布式环境下的动态视图
dba_tables 
dba_views
======================================================
二、管理控制文件 
1.查询控制文件状态和路径 
set linesize 120; 
col name for a60; 
select * from v$controlfile;
2.备份控制文件 
alter database backup controlfile to ‘/oracle/backup/control.bak’;
3.恢复控制文件 
关闭数据库，将备份好的控制文件覆盖坏的即可，然后重新启动数据库
======================================================
三、管理日志文件 
1.查询日志文件 
set linesize 180; 
col member for a50; 
col is_recovery_dest_file for a20; 
select * from v$logfile;
查询日志文件组号，大小及当前状态 
select group#,bytes,status,members from v$log;
2.创建新的日志文件 
增加新的日志文件组 
alter database add logfile group 4 (‘/path/redo01a.log’,'/path/redo04b.log’) size 10m;
增加新的日志文件成员 
alter database add logfile member ‘/path/redo01c.log’ to group 1,’/path/redo02c.log’ to group 2,’/path/redo03c.log’ to group 3;
3.删除日志文件 
select group#,bytes,members,status from v$log; 
alter database drop logfile group 1;
======================================================
四、管理表空间和数据文件 
1.查询表空间及包含的数据文件 
set line 156; 
set pagesize 200; 
col tablespace_name for a16; 
col file_name for a56; 
col bytes for 999,999,999,999; 
select tablespace_name,file_name,bytes from dba_data_files order by tablespace_name;
2.system表空间 
查询system表空间存放的对象及拥有者 
select distinct segment_type,owner,tablespace_name from dba_segments where tablespace_name='SYSTEM';
查询各个用户在system存放的对象数量 
select owner,count(*) from dba_segments where tablespace_name=’SYSTEM’ group by owner;
3.sysaux表空间 
查询各个用户在system存放的对象数量 
select owner,count(*) from dba_segments where tablespace_name=’SYSAUX’ group by owner;
4.建立表空间 
create tablespace hugwww_tbs datafile ‘/path/hugwww_db01.dbf’ size 100M autoextend on next 10M maxsize 5000M extent management local segment space management auto;
为应用创建用户 
create user hugwww identified by gjb888 default bablespace hugwww_tbs temporary tablespace temp;
权限设置 
grant connect,resource to hugwww; 
revoke unlimited tablespace from hugwww; 
alter user hugwww quota unlimited on hugwww_tbs; 
查看用户权限 
select * from dba_sys_privs where grantee=’hugwww’;
5.维护表空间与数据文件 
设置表空间为只读 
alter tablespace hugwww_tbs read only;
设置表空间为读写 
alter tablespace hugwww_tbs read write;
改变表空间的名称 
alter tablespace hugwww_tbs rename to hugwww_new_tbs;
6.设置默认表空间 
设置默认临时表空间为temp2 
alter database default temporary tablespace temp2;
设置用户默认表空间为users,并查询默认表空间信息 
alter database default tablespace users; 
select property_value from database_properties where property_name=’DEFAULT_PERMANENT_TABLESPACE';
7.删除表空间及其所有内容 
删除表空间后，需要手工删除数据文件 
drop tablespace hugwww_tbs including contents cascade constraints;
8.增加数据文件到表空间 
alter tablespace hugwww_tbs add datafile ‘/path/hugwww_db02.dbf’ size 300MB;
9.删除表空间中无数据的数据文件 
alter tablespace hugwww_tbs drop datafile ‘/path/hugwww_db02.dbf’;
10.数据文件的自动扩展设置 
使用autoextend on命令，有3种方法： 
alter database语句 
create tablespace语句 
alter tablespace语句 
查询数据文件是否为自动扩展： 
set linesize 160; 
col file_name for a50; 
select file_name,tablespace_name,bytes,autoextensible,maxbytes from dba_data_files order by tablesapace_name;
11.管理撤销表空间undo 
查看undo表空间参数 
show parameter undo_management; 
show parameter undo_tablespace; 
show parameter undo_retention;
创建undo表空间 
create undo tablespace undotbs02 datafile ‘/path/undotbs02.dbf’ size 5G;
切换undo表空间 
alter system set undo_tablespace=undotbs02;
删除undo表空间 
drop tablespace undotbs02;
显示数据库所有undo表空间 
select tablespace_name from dba_tablespaces where contents=’UNDO’;
12.管理临时表空间 
创建临时表空间 
create temporary tablespace temp tempfile ‘/path/temp1.dbf’ size 2G;
创建默认临时表空间 
create default temporary tablespace temp tempfile ‘/path/temp1.dbf’ size 2G;
查看临时表空间 
select file_name,bytes,tablespace_name from dba_temp_files;
显示用户相应的临时表空间 
select username,temporary_tablespace,default_tablespace from dba_users order by username;
创建临时表空间组 
create temporary tablespace temp1 tempfile ‘/path/temp1_02.dbf’ size 2G tablespace group group1; 
create temporary tablespace temp2 tempfile ‘/path/temp2_02.dbf’ size 2G tablespace group group2;
查询临时表空间组 
select * from dba_tablespace_groups;
从一个组转移到另一个组 
alter tablespace temp1 tablespace group group2;
临时表空间组指定给用户 
alter user hugwww temporary tablespace group2;
数据库默认临时表空间组 
alter database default temporary tablespace group2;
删除临时表空间组 
drop tablespace temp1 including contents adn datafiles;
======================================================
五、模式对象管理 
1.创建表 
create table hugwww.info(id number,name varchar2(32),sex int(2),address varchar2(50)) tablespace hugwww;
2.查看用户的表名，表空间及分配信息 
select owner,table_name,tablespace_name,initial_extent from dba_tables where owner='HUGWWW';
3.修改表结构 
增加新列addcol 
alter table test add(addcol varchar2(20));
修改addcol长度 
alter table test modify(addcol varchar2(80));
修改列名addcol为delcol 
alter table test rename column addcol to delcol;
删除字段delcol 
alter table test drop column delcol;
4.修改表名 
rename test to new_test;
alter table test to new_test;
5.删除表 
drop table test;
表有主键或外键，用以下命令删除 
drop table test cascade constraints;
======================================================
六、用户管理 
1.创建用户 
create user hugwww identified by gjb888 default bablespace hugwww_tbs temporary tablespace temp;
2.权限设置 
grant connect,resource to hugwww; 
revoke unlimited tablespace from hugwww; 
alter user hugwww quota unlimited on hugwww_tbs;
角色备份和导入 
exp_full_database 
imp_full_database
查看用户权限 
select * from dba_sys_privs where grantee=’hugwww’;
3.修改用户磁盘限额 
alter user hugwww quota 1G on hugwww_tbs;
4.修改用户密码 
alter user hugwww identified by ‘newpass’;
5.查看锁定的用户 
select username,account_status,lock_date from dba_users;
6.用户锁定与解锁 
show user; 
alter user hugwww account lock; 
alter user hugwww account unlock;
7.删除用户 
drop user hugwww cascade;
8.查询审计信息 
select userid,userhost,terminal,obj$name,obj$creator,ses$actions from aud$;
======================================================
七、企业管理器EM 
1.启动em 
emctl start dbconsole
2.关闭em 
emctl stop dbconsole
3.查看em状态 
emctl status dbconsole
4.登录到em 
https://ip:1158/em
完成！