Oracle常见操作和问题总结
1 数据库管理
1.1 用户管理
用户创建和授权
-- 删除用户
drop user 用户名 cascade;
-- 创建用户
create user 用户名 identified by passwd;
-- 授权
grant connect,resource,dba to 用户名;
-- 设置默认表空间
alter user 用户名 default tablespace 表空间名;
-- 授权表空间
alter user 用户名 quota unlimited on 表空间名;
修改用户密码alter user 用户名 identified by 密码;
1.2 表空间
1. 表空间创建
CREATE TABLESPACE TESTDATA
DATAFILE '/u01/app/oracle/oradata/orcl/test_data1.dbf' size 2000M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
2. 用户授权表空间
alter user 用户名 quota unlimited on 表空间;
或 alter user 用户名 quota *M on 表空间;
3. 表空间容量追加
alter tablespace TESTDATA add datafile '/u01/app/oracle/oradata/orcl/test_data2.dbf' size 2000M;
alter tablespace TESTDATA add datafile '/u01/app/oracle/oradata/orcl/test_data3.dbf' size 2000M;
查看表空间大小(PLSQL中执行)
SELECT a.tablespace_name "表空间名",
a.bytes / 1024 / 1024 "表空间大小(M)",
(a.bytes - b.bytes) / 1024 / 1024 "已使用空间(M)",
b.bytes / 1024 / 1024 "空闲空间(M)",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "使用比"
FROM (SELECT tablespace_name, sum(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, sum(bytes) bytes, max(bytes) largest
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
ORDER BY ((a.bytes - b.bytes) / a.bytes) DESC;
1.3 服务管理
启动LISTENER:lsnrctl start
启动oracle服务:startup;
停止oracle服务:shutdown immediate;
强制启动：
shutdown abort;
startup;
查看实例是否启动:select status from v$instance;
查看当前数据库名称:select name from v$database;
**注:**Oracle数据库启动次序：1.启动LISRENER 2.启动oracle服务
1.4 SQLPlus使用
sqlplus登陆方式
SQLPlus登录的几种不同方式
sqlplus 根据提示输入用户名、密码
sqlplus 用户名/密码 (本机登录)
sqlplus 用户名/密码@网络服务名 (远程登录)
sqlplus / as sysdba (以sysdba身份登录，用户实际为sys)
SQLPlus远程登陆
1.下载安装sqlplus客户端
2.tnsnames.ora文件配置
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.64.144)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
3.sqlplus user/password@orcl
如：sqlplus system/password@orcl
第二种无需配置文件的登陆方式
$ sqlplus /nolog
SQL> conn user/password@192.168.64.144/orcl
注：远程连接的账户只要有本地登陆的权限就可以，没有远程登陆权限这样的权限。
1.5 查看及修改Oracle编码格式
--查看Oracle数据库字符集：
select userenv(’language’) from dual;
查询结果：
SIMPLIFIED CHINESE_CHINA.AL32UTF8
--修改oracle数据库字符集：（在SQL Plus中）
sql> conn / as sysdba;
sql> shutdown immediate;
database closed.
database dismounted.
oracle instance shut down.
sql> startup mount;
oracle instance started.
total system global area 135337420 bytes
fixed size 452044 bytes
variable size 109051904 bytes
database buffers 25165824 bytes
redo buffers 667648 bytes
database mounted.
sql> alter system enable restricted session;
system altered.
sql> alter system set job_queue_processes=0;
system altered.
sql> alter system set aq_tm_processes=0;
system altered.
sql> alter database open;
database altered.
sql> alter database character set internal_use JA16SJIS;
sql> shutdown immediate;
sql> startup;
1.6 Oracle会话和连接数
查看当前会话
select terminal,saddr,sid,serial#,paddr,username,status from v$session;
杀掉会话
alter system kill session '27,33353';
拼装杀会话SQL
select 'alter system kill session '''|| sid ||''||','|| serial# ||''';' from v$session;
查看当前会话
select sid from v$session where audsid=userenv('sessionid');
查询数据库当前进程的连接数：
select count(*) from v$process;
查看数据库当前会话的连接数：
select count(*) from v$session;
查看数据库的并发连接数：
select count(*) from v$session where status='ACTIVE';
查询数据库允许的最大连接数
select value from v$parameter where name = 'processes';
或者show parameter processes;
修改数据库允许的最大连接数：
alter system set processes = 1000 scope = spfile;
重启数据库
shutdown immediate;
startup;
1.7 Oracle数据备份与恢复:RMAN工具
1.8 Oralce DB LINK 创建
drop user User1 cascade;
--创建用户
create user User1 identified by User1;
--授权
grant connect,resource,dba to User1;
grant CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK to User1;
DROP DATABASE LINK to_AIMDATA;
create database link to_AIMDATA connect to AIMDATA identified by passwd using '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.12)(PORT = 1521))(CONNECT_DATA =(SID=orcl)))';
select * from table_1@to_AIMDATA;
select 'create table '||table_name||' as select * from '||table_name||'@to_AIMDATA;' from user_tables@to_AIMDATA ;
2. 数据库操作
2.1 创建操作
-- 创建表
create table T_PERSON
(
ID varchar(40) not null comment '编号',
Name varchar(120) comment '姓名',
Sex varchar(20) comment '性别',
primary key (ID)
);
alter table T_PERSON comment '个人信息';
-- 创建Function
CREATE OR REPLACE FUNCTION getname(No varchar,pNo varchar)
return varchar
is pName varchar(200);
begin
pName:='';
select ItemName into pName
from T_Table
where CodeNo=No and ItemNo=pNo;
if pName is null then
return pNo;
else
return pName;
end if;
end;
-- 创建视图
CREATE OR REPLACE VIEW T_GROUP AS
SELECT
RoleID AS ID_, RoleName AS NAME_, '' AS TYPE_
FROM
SYS_ROLE;
-- 创建索引
create index IDX_TABLE_A_1 on TABLE_A (cNo);
2.2 修改类
-- 增加字段
alter table TABLE_A add( name VARCHAR2(32));
COMMENT ON COLUMN TABLE_A.name is '姓名';
-- 修改字段长度
alter table TABLE_A modify COMMENT varchar2(3600);
2.3 数据操作类
-- 查看数据表结构
describe t_user;
-- 复制表 只复制表结构
create table table_name_new as select * from table_name_old where 1=2;
-- 复制表 复制表结构和数据
create table table_name_new as select * from table_name_old;
-- 字符截取替换
update T_Table set NAME=(case when length(NAME)>6 then replace(NAME, substr(NAME,3,3), '文字') else replace(RPAD('NAME',6,'文字'), substr(RPAD('NAME',6,'文字'),3,3), '文字') End);
3 常见问题
3.1 解决imp导入数据库,注释乱码问题
Linux环境变量设置编码export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
3.2 Oracle 11g用exp无法导出空表的处理发布方法
Oracle 11G在用EXPORT导出时,空表不能导出.11G的新特性,当表无数据时,不分配segment,以节省空间
解决方法:
设置数据库参数
show parameter segment;
alter system set deferred_segment_creation=false;
3.3 锁表处理
select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v$locked_object lo,
dba_objects ao,
v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;
查看锁表进程SQL语句2：
select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
杀掉锁表进程：
如有记录则表示有lock，记录下SID和serial# ，将记录的ID替换下面的738,1429，即可解除LOCK
alter system kill session '109,14153';