Oracle常见操作和问题总结

mtain 2016年04月04日 173次浏览

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工具

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';