Oracle配置及维护

/* 启停服务 */

关闭监听服务
[root@test ~]# su - oracle
[oracle@test ~]$ lsnrctl stop

关闭oracle数据库
$sqlplus /nolog
SQL> connect /as sysdba
SQL> shutdown immediate
SQL> exit
[oracle@test ~]$ exit
[root@test ~]# shutdown -i0 -g0 –y 或 reboot

手工启动步骤
#su - oracle
$sqlplus /nolog
SQL> connect /as sysdba
SQL> startup
SQL> exit
[oracle@test ~]$ lsnrctl start


/* 客户端配置文件 */
-- 找到 $ORACLE_HOME/版本号等中间目录/network/admin 目录下的 tnsnames.ora 文件


/* 创建表空间、用户、授权 */

-- 第1步：创建临时表空间
create temporary tablespace xxx_temp
tempfile '/u01/app/data/xxxx/xxx_temp.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;

-- 第2步：创建数据表空间
create tablespace xxx_data
logging
datafile '/u01/app/data/xxxx/xxx_data.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;

-- 第3步：创建用户并指定表空间
create user xxx identified by xxx
default tablespace xxx_data
temporary tablespace xxx_temp;

-- 第4步：给用户授予权限
grant connect,resource,dba to xxx;


/* 用户授权管理 */

-- 查看用户和角色
select * from all_users;
select * from user_users;
select * from dba_roles;

-- 查看用户或角色的权限
select * from dba_sys_privs;
select * from role_sys_privs;
select * from user_sys_privs;

-- 查看当前用户角色
select * from user_role_privs;

-- 查看当前用户权限：
select * from session_privs;

-- 查看那些用户具有sysdba或sysoper系统权限
select * from v$pwfile_users;

-- 常用的系统权限:
-- CONNECT(基本连接), RESOURCE(程序开发), DBA(数据库管理)

-- 常用的数据对象权限:
-- ALL ON 数据对象名, SELECT ON 数据对象名, UPDATE ON 数据对象名,
-- DELETE ON 数据对象名, INSERT ON 数据对象名, ALTER ON 数据对象名

GRANT CONNECT, RESOURCE TO 用户名;
GRANT SELECT ON 表名 TO 用户名;
GRANT SELECT, INSERT, DELETE ON表名 TO 用户名1, 用户名2;

REVOKE CONNECT, RESOURCE FROM 用户名;
REVOKE SELECT ON 表名 FROM 用户名;
REVOKE SELECT, INSERT, DELETE ON 表名 FROM 用户名1, 用户名2;

-- 注意回收: 当授予resource角色给user时默认有全局unlimited tablespace权限
revoke unlimited tablespace from xxx;
-- 重新赋予user只在其表空间的配额不受限制
alter user xxx quota unlimited on xxx;

-- 删除用户
drop user xxx cascade;

-- 改用户密码
alter user xxx identified by xxx;


/* 表空间管理 */

-- 查用户与表空间关系
select username,default_tablespace from dba_users;

-- 查看用户所用临时表空间
SELECT USERNAME,TEMPORARY_TABLESPACE FROM DBA_USERS;

-- 修改默认临时表空间
alter database default temporary tablespace tempXXX;

-- 查看表空间配额（MAX_BYTES为-1表示没有限制）
select * from user_ts_quotas;
select * from dba_ts_quotas;

-- 查看所有表空间
select * from dba_tablespaces;
select * from dba_data_files;
select tablespace_name, sum(bytes) / 1024 / 1024 as MB　from dba_data_files group by tablespace_name;

-- 查表空间对应文件
select t.ts#,t.name,d.ts#,d.name from v$tablespace t,v$datafile d where t.ts#=d.ts#;

-- 删除表空间文件
drop tablespace xxx including contents and datafiles;

-- 概况
select FILE_NAME,TABLESPACE_NAME,BYTES/(1024 * 1024) MB,USER_BYTES/(1024 * 1024) USER_MB,MAXBYTES/(1024 * 1024) MAX_MB from dba_data_files;
SELECT Tablespace_Name,Sum(bytes)/1024/1024 AS MB FROM Dba_Segments GROUP BY Tablespace_Name;

-- 统计各表空间详情
SELECT
    a.tablespace_name "表空间名",
    total / (1024 * 1024 * 1024) "表空间大小(G)",
    (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", round((total - free) / total, 4) * 100 "使用率 %"
FROM
    (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) a,
    (SELECT tablespace_name, SUM(bytes) total FROM dba_data_files GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;

-- 各用户具体表大小 *****
select t.owner, t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) mb
from dba_segments t
where t.segment_type='TABLE'
group by OWNER, t.segment_name, t.segment_type
order by mb desc;
select distinct segment_type from dba_segments;

-- 查临时表空间大小 *****
SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024 AS "SIZE(M)" FROM DBA_TEMP_FILES WHERE TABLESPACE_NAME = 'TEMP';
SELECT TABLESPACE_NAME, FREE_SPACE/1024/1024 AS "FREE SIZE(M)"FROM DBA_TEMP_FREE_SPACE WHERE TABLESPACE_NAME = 'TEMP';
select * from dba_temp_free_space;
select * from dba_temp_files;

-- 当前用户表大小
SELECT segment_name AS TABLENAME,BYTES B,BYTES/1024 KB,BYTES/1024/1024 MB,BYTES/1024/1024/1024 GB FROM user_segments;

-- 当前用户表空间的段大小（慎用,执行慢！）
SELECT Owner,Tablespace_name,Segment_Name,Segment_Type,Sum(bytes)/1024/1024 sums
FROM Dba_Extents
GROUP BY Owner,Tablespace_name,Segment_Name,Segment_Type
ORDER BY sums;

-- 表空间文件上线/下线
alter tablespace USERS online;
alter tablespace USERS offline;

-- 创建表空间时指定多个数据文件
create tablespace xxx_data
datafile '/u01/app/data/xxxxx/xxx_data01.dbf' size 50m autoextend on next 50m maxsize 5120M,
datafile '/u01/app/data/xxxxx/xxx_data02.dbf' size 50m autoextend on next 50m maxsize 5120M,
......;

-- 增加表空间数据文件
ALTER TABLESPACE ts_test_data
  ADD DATAFILE '/u01/app/data/xxxxx/xxx_data02.dbf'
 SIZE 1024M AUTOEXTEND ON NEXT 200M MAXSIZE 10G;

-- 更改表空间大小
alter database tempfile '/u01/app/data/xxxxx/xxx_temp.dbf' autoextend on next 50m maxsize 10240m;
alter database datafile '/u01/app/data/xxxxx/xxx_data.dbf' autoextend on next 200m maxsize 32000m;

-- 更改用户配额空间大小
alter user xxx quota 500m on xxx;


/* 清理表 */

-- 清空
truncate table xxxx;

-- 分析
ANALYZE TABLE xxxx COMPUTE STATISTICS;

-- 启用行迁移
alter table INTERMSGLOG enable row movement;

-- 收缩表
alter TABLE INTERMSGLOG shrink SPACE;

-- 关闭行迁移
alter table INTERMSGLOG DISABLE row movement;


/* 查询SGA和PGA */
-- 现场服务器是8G内存，SGA设置为4G，PGA设置为1G
-- 现场服务器是16G内存，SGA设置为10G，PGA设置为2G
-- 现场服务器是32G内存，SGA设置为20G，PGA设置为4G
SQL> show parameter sga;
SQL> show parameter pga;
SQL> alter system set sga_max_size=4G scope=spfile;
SQL> alter system set sga_target=4G scope=spfile;
SQL> alter system set pga_aggregate_target=1G scope=spfile;