Oracle数据库一些实用操作记录

1. 查询表空间利用率
   select
   　　b.file_name 物理文件名,
   　　b.tablespace_name 表空间,
   　　b.bytes/1024/1024 大小M,
   　　(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,
   　　substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率
   from dba_free_space a,dba_data_files b
   where a.file_id=b.file_id
   group by b.tablespace_name,b.file_name,b.bytes
   order by b.tablespace_name;
2. 创建表空间
   create tablespace tjploan_dbs datafile ‘/home/oracle/tablespacesfile/tjploan01.dbf’ size 20G autoextend on next 1G maxsize 30G extent management local;
3. 删除用户
   drop user tjploan cascade;
4. 创建用户
   create user tjploan identified by tjploan default tablespace tjploan_dbs;
5. 授权
   grant dba to tjploan;
6. 废除表空间占用限制
   revoke unlimited tablespace from tjploan;
7. 限制用户占用USER表空间为0
   alter user tjploan quota 0 on users;
8. 自建表空间无限制
   alter user tjploan quota unlimited on tjploan_dbs;
9. 导入dmp文件
   imp tjploan/tjploan@orcl file=ploan20170501.dmp fromuser=ploan touser=tjploan full=y ignore=y tablespaces=tjploan_dbs log=20170601.log;
10. 删除表空间（如果有数据需先删除用户）
    drop tablespace ysxd_dbs INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
    drop user YSXDICR CASCADE;
11. 查询用户名
    select username from dba_users;
12. 查看表空间下有多少用户
    select distinct s.owner from dba_segments s where s.tablespace_name =‘USERS’
13. 查询锁表记录

select sess.sid, 
    sess.serial#, 
    lo.oracle_username, 
    lo.os_user_name, 
    ao.object_name, 
    lo.locked_mode,
	sys_context('userenv', 'ip_address') as ip  
    from v$locked_object lo, 
    dba_objects ao, 
    v$session sess 
where ao.object_id = lo.object_id and lo.session_id = sess.sid;

根据查询出来的记录执行alter system kill session 'sid,serial#'; 杀掉进程解除锁表占用。

另一些操作

1、监听程序找不到符合协议堆栈要求的可用处理程序

   select count(*) from v$process;                         取得数据库目前的进程数。
   select value from v$parameter where name = 'processes';	 取得进程数的上限。
   conn / as sysdba   
   show parameter processes
   show parameter sessions
   alter system set processes=300 scope=spfile;
   alter system set sessions=335 scope=spfile;
   shutdown
   startup
   show parameter processes
   show parameter sessions

如果连接数较多shutdown没反应则可以：1、shutdown abort;2、startup restrict;3、shutdown;4、startup;
2、存储过程中如果用到dba_data_files、v$session这类系统表会提示表或视图不存在，需要授权：grant select any dictionary to system;即可
3、实用数据泵导库时需要先创建directory

Create directory TEST as '/home/oracle/dumptemp';
Grant read,write on directory TEST to jnals2;
impdp jnals2/jnals2 directory=TEST dumpfile=jnbank_20171109_0005.dmp ignore=y remap_schema=jnuat20171109:jnals2

4、忘记以前创建过的directory可以进行查询

SELECT * FROM  ALL_DIRECTORIES;
SELECT * FROM  dba_DIRECTORIES;

5、删除directory

drop directory TEST;

6、oracle11g导出时不导出空表
11g新增参数deferred_segment_creation默认为true，默认状态下为了节省空间新建空表不分配表空间，在插入数据后才动态分配表空间，目的是节约内存，如果为false则会导入。查询该参数使用show parameter deferred_segment_creation;修改该参数使用alter system set deferred_segment_creation=false;

PLSQL登陆时出现NLS_LANG is not defined on the client

在后续查询中还会查询结果出现乱码。
解决方案： 进入注册表HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient11g_homexx查看有无NLS_LANG的变量值为SIMPLIFIED CHINESE_CHINA.ZHS16GBK如果没有则新建一个
如果是64位系统装32位客户端则在HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\KEY_OraClient11g_home1重复上面操作
如果都有进入数据库查询select * from V$NLS_PARAMETERS查看结果，结果如下：

如果都是AMERICA和$字样说明环境变量没有配置正确，则在系统环境变量中添加两个变量LANG=zh_CN.GBK
NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK然后重新进入plsql应该就可以了。

Oracle导出带版本号的问题

exp/imp不能使用带版本号导出，因此无法实现高版本dmp导入低版本
expdp/impdp可以带版本导入，导入导出步骤

create directory dump_test as /home/oracle/dumpfile
grant read,write on dump_test to username
expdp username/passwd diectory=dump_test file=testexpdp.dmp schemas=test version=11.1.0.6.0 TRANSFORM=segment_attributes:n logfile=expdp.log
impdp username/passwd diectory=dump_test file=testexpdp.dmp remap_tablespace=test:tptest TRANSFORM=segment_attributes:n REMAP_SCHEMA=scott:system

注意：如果oracle是10g的，要加参数 EXCLUDE=TABLE_STATISTICS 选项将 table_statistics 对象过滤。 否则会出现数据泵导入中table_statistics长时间等待、用impdp 导入，检查 table_statistics 时等待了N长时间

DBLINK的使用

1、查询当前用户是否有创建DB Link权限(dba权限用户默认有权限)

select * from user_sys_privs where privilege like upper('%DATABASE LINK%'); 

2、使用sys/system用户授权

GRANT CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK TO <username>

3、创建dblink，dblink有公共和私有之分。即关键字中的PUBLIC，如果不写PUBLIC就是私有dblink，私有dblink只能创建用户使用，其他用户不能使用。因此一般公共dblink使用较多

CREATE PUBLIC DATABASE LINK <dblink_name>
CONNECT TO <username>IDENTIFIED BY <password>
USING '(
DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =(PROTOCOL =TCP)
(HOST = <remote_ip>)(PORT = <remote_port>))
(CONNECT_DATA=
(SERVICE_NAME=<remote_sid>)))
)';

连接说明可以在TNSNAMES.ORA文件中提前定义，然后再using后使用别名也是可以的
4、查询是否创建成功

select * from dba_objects where object_type='DATABASE LINK';

创建的dblink可以使用同义词替换，创建语句CREATE SYNONYM 同义词名 FOR 表名@数据库链接名;这样创建后再查该表就可以直接使用select * from 同义词名进行查询了
5、删除dblink

DROP PUBLIC DATABASE LINK <dblink_name>;

注：数据库参数GLOBAL_NAMES如果为true时创建的dblink时的link_name要与数据库sid相同，否则会报ORA-2085错；如果为false则无所谓。