Linux5.8安装oracle11g

1、检查主机缺少的包
rpm -q gcc make binutils setarch compat-db compat-gcc compat-gcc-c++ compat-libstdc++ compat-libstdc++-devel unixODBC unixODBC-devel libaio-devel sysstat

gcc-4.1.2-52.el5
make-3.81-3.el5
binutils-2.17.50.0.6-20.el5
setarch-2.0-1.1
package compat-db is not installed
package compat-gcc is not installed
package compat-gcc-c++ is not installed
package compat-libstdc++ is not installed
package compat-libstdc++-devel is not installed
package unixODBC is not installed
package unixODBC-devel is not installed
package libaio-devel is not installed
package sysstat is not installed

2、安装所需包
插入光盘进入cd /media/RHEL_5.8\ i386\ DVD/Server/ 安装所缺少的包(也可以用配置yum的方法自动安装)
rpm -Uvh gcc-4.1.2-52.el5.i386.rpm
warning: gcc-4.1.2-52.el5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 37017186
Preparing… ########################################### [100%]
package gcc-4.1.2-52.el5.i386 is already installed
等等。。。
但有些包装上以后再次检车的时候还是会显示没装,直接忽略就可以了。
3、修改内核参数
向 /etc/sysctl.conf 文件尾部添加如下参数
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
设置完成后使用sysctl -p命令重载立即生效
4、用户和资源限制
建立oracle用户和组
建议指定GID和UID方式(尤其集群环境)
[root@dba ~]# groupadd -g 1000 oinstall
[root@dba ~]# groupadd -g 1001 dba
[root@dba ~]# useradd -u 1000 -g oinstall -G dba oracle
[root@dba ~]# passwd oracle
建立oracle软件安装文件目录
[root@dba ~]# mkdir -p /u01/oracle
[root@dba ~]# chown -R oracle:oinstall /u01/
设置oracle环境变量
vi ~oracle/.bash_profile
export ORACLE_BASE=/u01/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0
export ORACLE_SID=ora11g
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
加载配置
oracle用户注销重新登录 或 source .bash_profile
PAM限制资源
/etc/security/limits.conf

[root@ora11g ~]# grep -v ‘#’ /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240
6.运行安装命令

安装界面语言可以用locale查看 支持zh_CN.utf-8中文字体
export LANG=zh_CN.utf-8
安装界面使用英文则
export LANG=C
若当前桌面是root 用su – oracle切换用户 需要在切换前root执行 # xhost + 否则oracle用户不能连接root的桌面
7、安装界面就不贴了

发表在 Oracle | 留下评论

Ora 00980同义词转换不再有效

首先是synonym

CREATE [PUBLIC]SYNONYM synonym For schema.object

隐藏对象的名称和所有者:

select count(*) from hr.employees;
create synonym emp for hr.employees; --默认属于donny用户,是donny的私有对象private
select count(*) from emp;

为分布式数据库的远程对象提供了位置透明性:
访问其他数据库时,要首先建立数据库连结:

CREATE DATABASE LINK test_link CONNECT TO username IDENTIFIED BY pass USING 'orabase';
Select count(*) from hr.employees@test_link;
create synonym link_emp for hr.employees@test_link;
select count(*) from link_emp;

提供对象的公共访问:

create public synonym pub_emp for hr.employees;

pub_emp属于public用户,数据库所有用户都可以访问。

同义词类型
–私有 emp 实际上donny.emp
–公用 pub_emp 所有用户都可以直接访问

当公有对象和私有对象同名时(因为数据不同的用户,所以可以),以私有对象优先。(类似于局部变量)
desc dba_synonyms/ user_synonyms/ all_synonyms 数据字典,复数
tab公有同义词
建立私有的tab表,查看效果。

删除同义词:

drop synonym donny.emp;
drop public synonym pub_emp;

对于我的问题:
查询同义词表

SELECT * FROM SYS.ALL_SYNONYMS WHERE table_owner = '源表拥有者'

批量生成需要创建同义词

select 'CREATE SYNONYM '||synonym_name||' FOR 源表拥有者.'||synonym_name||' ;'FROM SYS.ALL_SYNONYMS WHERE table_owner = '源表拥有者'

批量生成卸载同义词

select 'drop synonym '||synonym_name||' ;'FROM SYS.ALL_SYNONYMS WHERE table_owner = '源表拥有者'
发表在 Oracle | 留下评论

oracle 查询表空间使用情况

–查询表空间使用情况

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;

–查询表空间的free space

select tablespace_name,
count(*) as extends,
round(sum(bytes) / 1024 / 1024, 2) as MB,
sum(blocks) as blocks
from dba_free_space
group by tablespace_name;
–查询表空间的总容量

select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name;
–查询表空间使用率

select total.tablespace_name,
round(total.MB, 2) as Total_MB,
round(total.MB - free.MB, 2) as Used_MB,
round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct
from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_free_space
group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name) total
where free.tablespace_name = total.tablespace_name;

1.查找当前表级锁的SQL如下:

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;

2.杀掉锁表进程:

alter system kill session '436,35123';

3.RAC环境中锁查找:

SELECT inst_id,DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type,block,ctime
FROM GV$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
ORDER BY id1, request;

4.监控当前数据库谁在运行什么SQL语句

select osuser, username, sql_text
from  v$session a, v$sqltext b
where  a.sql_address =b.address order by address, piece;

5.找使用CPU多的用户session

select a.sid,spid,status,substr(a.program,1,40) prog, a.terminal,osuser,value/60/100 value
from  v$session a,v$process b,v$sesstat c
where  c.statistic#=12 and
c.sid=a.sid and
a.paddr=b.addr
order by value desc;

6.查看死锁信息

SELECT (SELECT username
FROM v$session
WHERE SID = a.SID) blocker, a.SID, 'is blocking',
(SELECT username
FROM v$session
WHERE SID = b.SID) blockee, b.SID
FROM v$lock a, v$lock b
WHERE a.BLOCK = 1 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2;

7.具有最高等待的对象

SELECT   o.OWNER,o.object_name, o.object_type, a.event,
SUM (a.wait_time + a.time_waited) total_wait_time
FROM v$active_session_history a, dba_objects o
WHERE a.sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATE
AND a.current_obj# = o.object_id
GROUP BY o.OWNER,o.object_name, o.object_type, a.event
ORDER BY total_wait_time DESC;
SELECT   a.session_id, s.osuser, s.machine, s.program, o.owner, o.object_name,
o.object_type, a.event,
SUM (a.wait_time + a.time_waited) total_wait_time
FROM v$active_session_history a, dba_objects o, v$session s
WHERE a.sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATE
AND a.current_obj# = o.object_id
AND a.session_id = s.SID
GROUP BY o.owner,
o.object_name,
o.object_type,
a.event,
a.session_id,
s.program,
s.machine,
s.osuser
ORDER BY total_wait_time DESC;

8.查询当前连接会话数

select s.value,s.sid,a.username
from
v$sesstat S,v$statname N,v$session A
where
n.statistic#=s.statistic# and
name='session pga memory'
and s.sid=a.sid
order by s.value;

9.等待最多的用户

SELECT   s.SID, s.username, SUM (a.wait_time + a.time_waited) total_wait_time
FROM v$active_session_history a, v$session s
WHERE a.sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATE
GROUP BY s.SID, s.username
ORDER BY total_wait_time DESC;

10.等待最多的SQL

SELECT   a.program, a.session_id, a.user_id, d.username, s.sql_text,
SUM (a.wait_time + a.time_waited) total_wait_time
FROM v$active_session_history a, v$sqlarea s, dba_users d
WHERE a.sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATE
AND a.sql_id = s.sql_id
AND a.user_id = d.user_id
GROUP BY a.program, a.session_id, a.user_id, s.sql_text, d.username;

11.查看消耗资源最多的SQL

SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls
FROM V$SQLAREA
WHERE buffer_gets > 10000000 OR disk_reads > 1000000
ORDER BY buffer_gets + 100 * disk_reads DESC;

12.查看某条SQL语句的资源消耗

SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls
FROM V$SQLAREA
WHERE hash_Value = 228801498 AND address = hextoraw('CBD8E4B0');

13.查询会话执行的实际SQL

SELECT   a.SID, a.username, s.sql_text
FROM v$session a, v$sqltext s
WHERE a.sql_address = s.address
AND a.sql_hash_value = s.hash_value
AND a.status = 'ACTIVE'
ORDER BY a.username, a.SID, s.piece;

14.显示正在等待锁的所有会话

SELECT * FROM DBA_WAITERS;

转载自:http://www.cnblogs.com/askjacklin/archive/2012/06/04/2534571.html

发表在 Oracle | 留下评论

oracle 乱码问题

问题描述:

之前正常安装数据库客户端,pl/sql都正常显示,重装系统后,没有重装数据库客户端,只做了一些配置,能够正常监听。pl/sql出现乱码。在导出数据库时也没有在意,当再次导入数据库时,出现乱码现象。

解决办法:

如果是新安装的客户端可以到注册表中找到HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/KEY_OraClient10g_home1/添加键值对NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK

另外一种方法针对想我出现的问题,可以直接在环境变量中新建NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK环境变量。

 

 

发表在 Oracle, PL/SQL | 留下评论

ORA-01502错误

这个错误是由于索引失效造成的,重建索引后,问题就解决了。

本人失效的原因是:

当使用类似 alter table xxxxxx move tablespace xxxxxxx 命令后,索引就会失效。

检查一下索引状态

select index_name,index_type,table_type,table_name,status from dba_indexes where owner = '用户名'

当status列为UNUSABLE时证明索引已失效,这时就要重新建索引

alter index 索引名称 rebuild;
发表在 Oracle | 留下评论

创建表空间、用户

创建表空间

create tablespace 表空间名称 datafile '表空间数据文件路径(必须为真实存在路径)' size 100M autoextend on next 100M maxsize unlimited;

创建用户并制定表空间

create user 用户名 identified by 密码 default tablespace 表空间名称;

授予用户DBA权限

grant connect,resource,dba to 用户名

回收用户DBA权限

revoke connect,resource,dba from 用户名

授予用户对指定表空间的使用权限

ALTER USER 用户名 QUOTA UNLIMITED ON 指定表空间名称

删除表空间及数据文件

drop tablespace 表空间名称 including contents and datafiles ;

查询表空间数据文件信息

select * from dba_data_files t
发表在 Oracle | 留下评论

修改表空间的一些命令

查询用户对应的表空间以及段类型等信息

select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_segments where owner='用户' and tablespace_name='表空间';

批量生成修改表空间语句

select 'alter table '||segment_name||' move tablespace 新表空间名称;' from dba_segments where segment_type = 'TABLE' and owner='用户'  and tablespace_name='愿表空间名称';

批量生成修改索引表空间语句

select 'alter index '||segment_name||' rebuild nocompress noparallel tablespace 新表空间名称;' from dba_segments where owner='用户' and tablespace_name='愿表空间名称' and segment_type = 'INDEX';

批量生成修改带有lob类型的索引或者段

select 'alter table '||table_name||' move tablespace 新表空间名称 lob ('||column_name||') store as '||segment_name||' (tablespace 新表空间名称)' from dba_lobs where owner = '用户' order by table_name;

查询指定用户下所有带有lob类型的数据

select * from dba_lobs where owner = '用户' order by table_name;

 
需要注意的是:重建索引应该后执行,否则当执行alter table XXX move tablespace XXX后索引会失效。

发表在 Oracle | 留下评论

oracle导出导入schema

导出 schema
exp userid=username/password@tnsname owner=schema file=exp.dmp log=log.log direct=y
导入 schema
imp userid=username/password@tnsname file=exp.dmp fromuser=s_username touser=n_username log=log.log

需要注意的是:如果在两个表空间名称不同的情况下做迁移,那么如果源表空间中含有lob类型字段的表在导入是会出现959表空间不存在的错误。

另外:在oracle11g中 默认空表是不分配segment的,以节省空间。所以默认导出是不包含空表的。

1、设置deferred_segment_creation 参数为FALSE后,无论是空表还是非空表,都分配segment。

在sqlplus中,执行如下命令:

SQL>alter system set deferred_segment_creation=false;

查看:
SQL>show parameter deferred_segment_creation;
该值设置后只对后面新增的表产生作用,对之前建立的空表不起作用。

2、可以使用手工为空表分配Extent的方式,来解决导出之前建立的空表的问题。说明如下

使用ALLOCATE EXTENT可以为数据库对象分配Extent。其语法如下:

———–
ALLOCATE EXTENT { SIZE integer [K | M] | DATAFILE ‘filename’ | INSTANCE integer }
———–

可以针对数据表、索引、物化视图等手工分配Extent。

ALLOCATE EXTENT使用样例:

ALLOCATE EXTENT
ALLOCATE EXTENT(SIZE integer [K | M])
ALLOCATE EXTENT(DATAFILE ‘filename’)
ALLOCATE EXTENT(INSTANCE integer)
ALLOCATE EXTENT(SIZE integer [K | M]   DATAFILE ‘filename’)
ALLOCATE EXTENT(SIZE integer [K | M]   INSTANCE integer)
针对数据表操作的完整语法如下:

———–
ALTER TABLE [schema.]table_name ALLOCATE EXTENT [({ SIZE integer [K | M] | DATAFILE ‘filename’ | INSTANCE integer})]
———–
故,需要构建如下样子简单的SQL命令:

———–
alter table aTabelName allocate extent
———–

其思路就是,在导出之前给空表分配segment。当然如果当你发现的时候已经导入完成了可以使用其他方法。比如拼sql使用copy命令执行重新创建表

copy from username/password@tnsname to username/password@tnsname create table using select * from table1;

发表在 Oracle | 留下评论