学步园

查看和修改sga
show parameter sga;
alter system set sga_target=’300M’ scope=both;

Linux下查看二进制文件spfile
strings spfile.ora

动态查看alert日志
tail -f alert.log
tail -10 alert.log

 

数据库四种状态
查看bdump下的alert_mydb.log
shutdown
nomount状态：只需要初始化参数文件pfile或spfile
mount状态：只需要控制文件，控制文件记录数据文件的路径和信息
open状态： 需要日志文件和数据文件

 

pfile和spfile都丢失的时候启动数据库

通过alert日志文件找出初始化参数，新建初始化参数文件，修改即可。
数据库开归档
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  369098752 bytes
Fixed Size                  1219448 bytes
Variable Size             150996104 bytes
Database Buffers          209715200 bytes
Redo Buffers                7168000 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

 

查看归档日志
archive log list;

show parameter fast

show parameter sga

alter system set fast_start_mttr_target=600 scope=both;

检查监听
tnsping mydb

密码文件作用：监听方式连接的时候验证sys as sysdba 用户，没有密码文件sys用户无法登录
创建密码文件 orapwd file=oramydb password=oracle

查看数据库默认数据块大小
show parameter db_block_size;

查看表，索引等的大小
desc dba_segment;
select owner,segment_name table_name,sum(bytes)/1024 kb from dba_segments where owner=’SCOTT’ and table_name=’emp’;

 

静默安装

记录
$ ./runInstaller -record -destinationFile /home/oracle/install_database.rsp

使用
$ ./runInstaller -silent -responseFile /home/oracle/install_database.rsp
跑脚本
#. $ORACLE_BASE/oraInventory/orainstRoot.sh
#. $ORACLE_HOME/Root.sh

 

进程相关

查看Oracle进程
ps -ef |grep oracle
ps -ef |grep ora_

查看客户端进程
ps -ef |grep LOCAL=NO

[oracle@redhat admin]$ ps -ef |grep LOCAL=NO
oracle   15845     1  0 17:57 ?        00:00:04 oraclemaindb (LOCAL=NO)
oracle   19699     1  0 18:47 ?        00:00:07 oraclemaindb (LOCAL=NO)
oracle   19703     1  0 18:47 ?        00:00:06 oraclemaindb (LOCAL=NO)
oracle   19707     1  0 18:47 ?        00:00:01 oraclemaindb (LOCAL=NO)
oracle   19803     1  0 18:47 ?        00:00:08 oraclemaindb (LOCAL=NO)
oracle   23592     1  0 19:34 ?        00:00:01 oraclemaindb (LOCAL=NO)
oracle   24871     1  0 19:52 ?        00:00:00 oraclemaindb (LOCAL=NO)
oracle   25213     1  0 19:57 ?        00:00:01 oraclemaindb (LOCAL=NO)
oracle   28119 26046  0 20:48 pts/4    00:00:00 grep LOCAL=NO
[oracle@redhat admin]$ kill -9 23592
[oracle@redhat admin]$ ps -ef |grep LOCAL=NO
oracle   15845     1  0 17:57 ?        00:00:04 oraclemaindb (LOCAL=NO)
oracle   19699     1  0 18:47 ?        00:00:07 oraclemaindb (LOCAL=NO)
oracle   19703     1  0 18:47 ?        00:00:06 oraclemaindb (LOCAL=NO)
oracle   19707     1  0 18:47 ?        00:00:01 oraclemaindb (LOCAL=NO)
oracle   19803     1  0 18:47 ?        00:00:08 oraclemaindb (LOCAL=NO)
oracle   24871     1  0 19:52 ?        00:00:00 oraclemaindb (LOCAL=NO)
oracle   25213     1  0 19:57 ?        00:00:01 oraclemaindb (LOCAL=NO)
oracle   28171 26046  0 20:49 pts/4    00:00:00 grep LOCAL=NO

 

创建索引
create index t_id_x on t(id) tablespace usersx;

dba_tables->pct_free,pct_used;

 

看block块大小
show parameter db_block_size;

 

查看当前实例和状态
select instance_name,status from v$instance;
查看数据库库对象

select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;

查看数据库的版本
Select version FROM Product_component_version  Where SUBSTR(PRODUCT,1,6)=’Oracle’;

查看数据库的创建日期和归档方式
Select Created, Log_Mode, Log_Mode From V$Database;

 

手动建库
cd $ORACLE_HOME/dbs
cat init.ora |grep -v ^# > initmydb.ora

注释
db_block_buffers = 100
shares_pool_size = 3500000
log_buffer = 32768
修改
control_files = (‘/u02/oradata/mydb/control01.ctl’, ‘/u02/oradata/mydb/control02.crl’)
sga_target=300m
background_dump_dest=/u02/admin/mydb/bdump
core_dump_dest=/u02/admin/mydb/cdump
user_dump_dest=/u02/admin/mydb/udump
undo_management=auto
undo_tablespace=mydbtbs1
创建数据库密码文件
orapwd file=orapwmydb password=oracle

 

用户和权限相关
相关数据字典 user_users  dba_users

显示当前连接用户：show user;

查询拥有sysdba权限的用户
select * from v$pwfile_users;

查看所有用户
SELECT USERNAME FROM DBA_USERS;

查询某用户状态
select username,account_status from dba_users where lower(username)=’adminru’;

查询当前会话或用户可以使用的系统权限
select * from session_privs;
select * from user_sys_privs;
select * from user_tab_privs;

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

查看数据库的角色
select * from dba_role_privs where grantee=’DBA’;

查看角色包含的权限
select * from dba_sys_privs where grantee=’RESOURCE’;
select * from dba_tab_privs where grantee=’ADMINRU’;

 

创建用户
create user wang identified by wang
default tablespace users
temporary tablespace temp
quota 0 on system;

create user test identified by test
default tablespace users
temporary tablespace temp
quota unlimited on users;

修改用户

修改用户的默认和临时表空间
alter user scott default tablespace users;
alter user scott temporary tablespace temp;

修改用户磁盘限额：
alter user wbtest quota 100M on users;

修改用户口令：
alter user scott identified by tigerabc;

SQL> select username,password from dba_users where username=’USER001′;

USERNAME                       PASSWORD
—————————— ——————————
USER001                        DD0643D7826752ED

SQL> alter user user001 identified by values ‘DD0643D7826752ED’;
User altered.

查询锁定用户：select username,account_status,lock_date from dba_users;
解锁用户：alter user mdsys account unlock;

删除用户
drop user wbtest cascade;

用户授权

grant connect,resource to wang;
grant drop and table to scott with admin option;
grant select,update,insert,delete on hr.jobs to rjb;
grant global query rewite to scott;
grant dba to scott;

撤销权限
revoke alter tablespace from wang;
revoke create any index from scott;

 

查看当前用户的缺省表空间
select username,default_tablespace from user_users;

select username,default_tablespace from dba_users where username=’adminru’;

查看所有用户的缺省表空间
select username,default_tablespace from dba_users;
select owner ,table_name,tablespace_name from dba_tables where owner=’adminru’ and table_name=’table_a’;

查看当前用户的系统权限和表级权限
select * from user_sys_privs;
select * from user_tab_privs;

查看当前用户拥有的表
select * from tab;

查看角色
set pagesize 100
select * from dba_roles;

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

权限查询
select * from role_sys_prive;

创建角色
create role teller identified by teller;

角色授权
grant create session,create database link to teller;
grant teller to wang;

角色生效
set role role1;
set role all;
sys用户可以创建数据库,可以关闭数据库  system不可以
查看数据字典：
desc dict;
select table_name from dict where table_name like ‘DBA_TAB%’;

desc dba_tab_privs;

 

连接相关

用系统管理员，查看当前数据库有几个用户连接：
select username,sid,serial# from v$session;

如果要停某个连接用
alter system kill session ‘sid,serial#’;

如果这命令不行,找它UNIX的进程数

select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.paddr=pro.addr;

说明：21是某个连接的sid数

然后用 kill 命令杀此进程号。

查看客户端连接
SQL> set line 100;
SQL> select SID,SERIAL#,MACHINE from v$session;
杀死客户端进程
SQL> alter system kill session ’50,11′;

 

表和视图相关

查看用户下所有的表
select * from user_tables;

查看名称包含log字符的表
select object_name,object_id from user_objects  where instr(object_name,’LOG’)>0;

查看某表的创建时间
select object_name,created from user_objects where object_name=upper(‘&table_name’);

查看某表的大小
select sum(bytes)/(1024*1024) as “size(M)” from user_segments where segment_name=upper(‘&table_name’);

查看放在ORACLE的内存区里的表
select table_name,cache from user_tables where instr(cache,’Y')>0;

 

数据库审计

相关视图
select table_name from dict where table_name like ‘DBA_AUDIT%’;

DBA_AUDIT_TRAIL
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_OBJECT
DBA_AUDIT_EXISTS
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS

查看审计参数
SHOW PARAMETERS AUDIT

修改参数使生效
alter system set audit_file_dest=’/u02/maindb_audit’ scope=spfile
alter system set audit_trail=db,extended scope=spfile

设置登录审计

审计每一个登录用户：audit  session;
连接成功的活动均和审计：audit session whenever successful
连接不成功的活动均和审计：audit session whenever not successful
取消登录审计：noaudit session

SQL记录审计
会话记录，只记录当前会话的第一条
audit select on adminru.table_a;
audit update on adminru.temp_table;
访问时记录，记录所有
audit update on adminru.table_a by access;
audit all on adminru.table_b by access;

取消审计
noaudit update on adminru.table_a;
noaudit all on adminru.table_b;

查询审计
select username,sql_text from dba_audit_trail;

set linesize 128;
set pagesize 100;
col os_username format a10;
col username for a10;
col userhost for a20;
col timestamp for a22;
col owner for a10;
col obj_name for a10;
col sql_text for a30;
select os_username,username,userhost,timestamp,owner,obj_name,sql_text from dba_audit_trail;

查看对象的审计
select owner,obj_name,username,action_name,sql_text from dba_audit_object;

 

系统时间格式
select sysdate from dual;
show parameter nls_date_format;
alter session set nls_date_format=’yyyy/mm/dd hh24:mi:ss’  临时

alter system set nls_date_format=’yyyy/mm/dd hh24:mi:ss’ scope=spfile; 永久

 

查询错误
oerr ora 00600

 

数据库闪回
从回收站还原表
flashback table temp_table to before drop;

绕过回收站删除
drop table table_name purge;

drop tablespace ts_name including contents;

drop user user_name cascade;
查询回收站
show recyclebin;

select owner,original_name,object_name,type,ts_name,droptime,related,space from dba_recyclebin where can_undrop=’YES’;

select original_name,object_name,type,ts_name,droptime,related,space from user_recyclebin  where can_undrop=’YES’;

查询是否启用闪回数据库
select flashback_on from v$database;

启用闪回数据库，mount状态下
alter database flashback on;

闪回数据库
RMAN> flashback database to time = “to_date(’2011-04-22 18:00:00′,’yyyy-mm-dd hh24:mi:ss’)”;

 

数据库日志文件Logfile
数据字典  v$logfile

日志文件分类：联机重做日志文件(redo log file)和归档日志文件。

查询日志文件
set linesize 180;
col member for a50;
con is_recovery_dest_file for a20;
select * from v$logfile;

查询日志文件组号、大小、状态
select group#,bytes,status,members from v$log;

创建新的日志文件
alter database add logfile member ‘/u01/app/oracle/oradata/maindb/redo02b.log’ to group 2;

删除日志文件组
alter database  drop logfile group 1;

删除不活动的组成员
alter database drop logfile member ‘/u01/app/oracle/oradata/maindb/redo01b.log’;

切换日志当前组
alter system switch logfile;

日志由活动改变成不活动
alter system checkpoint;

 

数据库控制文件Conrolfile
相关数据字典 v$controlfile

查询控制文件
select * from v$controlfile;

备份控制文件
1.备份到二进制文件 alter database backup controlfile to ‘/oracle/backup/control.bkp’;
2.备份到脚本文件 alter database backup controlfile to trace;

 

恢复控制文件
1.关闭数据库
2.覆盖掉坏的控制文件
3.启动数据库

 

多路复用是指将同一个控制文件或其他文件的复本保持在多个磁盘上。分spfile和init.ora两种多路复用

init.ora
control_files=’/u01/app/oracle/oradata/mydb/control01.ctl’,'/u01/app/oracle/oradata/mydb/control02.ctl’,

‘/u01/app/oracle/oradata/mydb/control03.ctl’,'/home/oracle/oracle_bak/control01.ctl’,

‘/home/oracle/oracle_bak/control02.ctl’,'/home/oracle/oracle_bak/control02.ctl’

spfile
alter system set control_files=’/u01/app/oracle/oradata/mydb/control01.ctl’,'/u01/app/oracle/oradata/mydb/control02.ctl’,

‘/u01/app/oracle/oradata/mydb/control03.ctl’,'/home/oracle/oracle_bak/control01.ctl’,'/home/oracle/oracle_bak/control02.ctl’,

‘/home/oracle/oracle_bak/control02.ctl’ scope=spfile;

 
相关数据字典  dba_data_files

查询表空间和对应数据文件
set line 156;
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;
查看表空间的名称及大小

select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;

查看表空间物理文件的名称及大小

select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;

查看表空间的使用情况

select sum(bytes)/(1024*1024) as free_space,tablespace_name
from dba_free_space
group by tablespace_name;

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES “% USED”,(C.BYTES*100)/A.BYTES “% FREE”
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

查看回滚段名称及大小

select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
max_extents, v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name ;

查看用户对应的临时表空间和默认表空间
select username,temporary_tablespace,default_tablespace from dba_users order by username;

创建表空间，扩展大小128K
create tablespace tbs_1 datafile ‘/u01/app/oracle/oradata/mydb/tbs_1.dbf’ size 100M
extent management local uniform size 128k

创建表空间，表空间扩展大小自动管理
create tablespace tbs_2 datafile ‘/u01/app/oracle/oradata/mydb/tbs_02.dbf’ size 100M
extent management local autoallocate;
创建大文件表空间
create bigfile tablespace bigtbs1 datafile ‘….\bigtbs01.dbf’ size 25G;

更改表空间名
alter tablespace tbs_2 rename to tbs_02;