Oracle数据库操作

一、建立用户/表空间/分配权限/删除表空间

1）注意表空间存放目录

2）/*分为四步 */

/*第1步：创建临时表空间  */

create temporary tablespace yuhang_temp

tempfile 'D:\oracledata\yuhang_temp.dbf'

size 50m 

autoextend on 

next 50m maxsize 20480m 

extent management local; 

/*第2步：创建数据表空间  */

create tablespace yuhang_data 

logging 

datafile 'D:\oracledata\yuhang_data.dbf'

size 50m 

autoextend on 

next 50m maxsize 20480m 

extent management local; 

/*第3步：创建用户并指定表空间  */

create user yuhang identified by yuhang 

default tablespace yuhang_data 

temporary tablespace yuhang_temp; 

/*第4步：给用户授予权限  */

grant connect,resource,dba to yuhang;

对于不使用的表空间要彻底删除

drop tablespace TS_DATA_DB_ZZWK including contents and datafiles;

二：导出oracle的dmp遇到空表的情况

昨天晚上按徐大姐要求给开发导一份测试库，在导测试库的过程中遇到了一个情况是，oracle11g，在使用

export时候不支持导出空表，以下是一个简单的办法：

1、导出前查询一下有多少张空表

SELECT TABLE_NAME FROM USER_TABLES WHERE NUM_ROWS=0，如下18张

2、oracle11g不给空表分片Segment（段，自己百度看下），那么我们就给这些空表分配

Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0

将执行结果导出为tsv文件，如下：

3、将tsv中的语句复制，在sql窗口中执行，然后再导出dmp文件。如下：

再点击工具栏Tools—Export tables ,打开如下对话框：

三、exp导出dmp

如果oracle的plsql中不能使用export tables命令。那么可以打开cmd窗口，输入：

2） 导出一张表，如：

Exp bzsf/iflytek2015@BZSF  file=D:/T_RYJBXX.dmp  tables=(T_RYJBXX)

3） 导出多张表，如：

exp bzsf/iflytek2015@BZSF  file= D:/T_RYJBXX.dmp  tables=（T_RYJBXX, T_CLJBXX）

   3）导出表中某些值，如:

将数据库中的表t_uaac_organization中的字段code含有"34180"打头的数据导出

 exp gsb_test/passwd!@sgy file=E:\xuancheng.dmp tables=(t_uaac_organization) query=\" where code like '%3418%' \"

4） 导出某一个用户下所有对象，要用超级管理员

exp  workflow_test/123456@192.168.57.180:1521/sgy  file=d:/work.dmp owner=workflow_test

四、导出遇到特殊字符

比如密码中含有@符号。或者/符号

五、imp导入dmp

1）可以直接通过plsql工具导入。

2）imp workflow_test/123456@192.168.78.121:1521/zzora  file=d:/work.dmp  full=y  ignore=y

六、查看mysql表的详细信息

登陆mysql服务器，进入mysql数据库，输入：

Mysql>Show table status like 'mytable'

Name: mytable #表名

Engine: InnoDB #存储引擎伟InnoDB

 Version: 10 #mysql版本

Row_format: Compact #行格式。有Dynamic，fixed，Compact等格式。Dynamic是动态行，表字段里面宝航varchar，BloB等不定长字段。fixed是定长行。Compact是行压缩。

 Rows: 0 #表中的行数

Avg_row_length: 0 #平均每行的字节数

Data_length: 16384 #整个表的数据量（字节）

Max_data_length: 0 #表最大的容量。0表示无限

Index_length: 0 #索引数据占用磁盘空间的大小

Data_free: 10485760 #表示已分配但还未被使用的空间大小。Auto_increment: NULL #下一个AUto_increment的值

Create_time: 2011-08-0622:39:46 #创建时间

Update_time: NULL #更新时间

Check_time: NULL #使用check table等命令时的检查时间

Collation: utf8_general_ci #默认字符集和字符列排列顺序

 Checksum: NULL #如果启动，则表示整个表的校验和Create_options: max_rows=4294967295avg_row_length=32 #表创建时的选项 Comment: 1 row inset (0.00 sec)

七、oracle复制表结构和数据

Oracle:

复制表结构及其数据：create table table_name_new as select * from table_name_old

只复制表结构：create table table_name_new as select * from table_name_old where 1=2;

八、查看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;

使用情况

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;

九、登录oracle数据提示异常：表空间问题

扩大表空间：

更改system表空间的数据文件SYSTEM.dbf分配空间

alter database datafile ‘/u04/oradata/truth/system.dbf’ resize 2048M

十、linux 下启动Oralce

   a、root用户关闭防火墙  service iptables stop

   b、oracle用户开启监听 ：

 su - oracle ;

lsnrctl start;

    c、开启数据库：

     sqlplus "/as sysdba" ; 

startup ; 

exit ;

十一、查看oracle字符集、版本、修改字符集

select version from v$instance；

select userenv('language') from dual;--服务端字符集

client字符集修改：在 /home/oracle与 /root用户目录下的.bash_profile中添加或修改 export NLS_LANG="AMERICAN_AMERICA.UTF8" 语句；

修改数据库字符集为UTF-8

1.以DBA登录

2.执行转换语句：

复制代码 代码如下:

SHUTDOWN IMMEDIATE;

STARTUP MOUNT EXCLUSIVE;

ALTER SYSTEM ENABLE RESTRICTED SESSION;

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

ALTER SYSTEM SET AQ_TM_PROCESSES=0;

ALTER DATABASE OPEN;

ALTER DATABASE NATIONAL CHARACTER SET UTF8;

SHUTDOWN IMMEDIATE;

STARTUP;

十二、添加表空间大小

先查看表空间当前情况：

select tablespace_name, file_id, file_name,

round(bytes/(1024*1024),0) total_space

from dba_data_files

order by tablespace_name

重设大:1：

alter database datafile  '\oracle\oradata\anita_2008.dbf' resize 4000m

重设大小2：

alter tablespace ESPS_2008 add datafile '\oracle\oradata\anita_2010.dbf' size 1000m

十三、数据库启动 startup报错，

启动的时候提示郑：MEMORY_TARGET not supported on this system

请看：http://www.linuxidc.com/Linux/2012-12/76976.htm 进行学习

vi /etc/fstab 调整内存：

十四、oracle自启动设置

请看

http://jingyan.baidu.com/article/b2c186c8fe4306c46ef6ff16.html

十五、导入和导出dmp操作

imp workflow_test/123456@192.168.78.121:1521/zzora file=d:/work.dmp full=y

exp workflow_test/123456@192.168.57.180:1521/sgy file=d:/work.dmp owner=workflow_test

十六、表空间名字错了，修改表空间名字

Alter tablespace XX rename to YY

十七、oracle执行select提示如下

grant select on v_$statname to yztv1_test;

grant select on v_$sesstat to yztv1_test;

grant select on v_$session to yztv1_test;

grant select on v_$mystat to yztv1_test;

 十八、一个用户yztv1_test查询另一个用户workflow_test的表

grant select on workflow_test.wfworkitem to yztv1_test;

grant select on workflow_test.wfwiparticipant to yztv1_test;

十九、数据库回滚日志文件UNDOTBS01.DBF太大了

 以SYS用户登录Oracle 自带的SQL*PLUS的方法：

1-- 创建一个新的小空间的UNDO TABLESPACE

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'D:\ORACLE\ORADATA\ORCL\UNDOTBS02.DBF' SIZE 100M REUSE AUTOEXTEND ON;

 2-- 设置新的表空间为系统UNDO_TABLESPACE

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;

3—在配置文件中修改undo_tablespace的设置                                  

D:\oracle\product\10.1.0\db_1\database\initoracle.ora

将其中的undo_tablespace=UNDOTBS1 改为 UNDOTBS2

4—重启数据库                                                               

采用命令SHUTDOWN IMMEDIATE 关闭数据库，然后再采用命令STARTUP 重新开启数据库；

二十、建立表空间遇到的问题，权限不足问题

解决方法 对要创建文件的目录执行 “chown -R oracle:oinstall 目录”，

比如chown -R oracle：oinstall /u01所以这个命令的作用是：

把/u01目录下的所有文件和目录的拥有者改为oracle 。

二十一、oracle数据库日志归档满了

登陆数据库，弹出如下提示框：

办法：登陆oracle数据库服务器

Su - oracle

rman

connect target /;

crosscheck archivelog all;

delete expired archivelog all;

DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

二十二、低效率sql语句（时间长）

select *

 from (select v.sql_id,

 v.child_number,

 v.sql_text,

 v.elapsed_time,

 v.cpu_time,

 v.disk_reads,

 rank() over(order by v.elapsed_time desc) elapsed_rank

 from v$sql v) a

 where elapsed_rank <= 10;

二十三、占用CPU高的sql

select *

from (select v.sql_id,

v.child_number,

v.sql_text,

v.elapsed_time,

v.cpu_time,

v.disk_reads,

rank() over(order by v.cpu_time desc) elapsed_rank

from v$sql v) a

where elapsed_rank <= 10;

二十四、锁表查询

--锁表查询（用超级管理）

SQLSELECT object_name, machine, s.sid, s.serial#

FROM gv$locked_object l, dba_objects o, gv$session s

WHERE l.object_id　= o.object_id

AND l.session_id = s.sid;

找到被

ALTER system kill session '23, 1647'锁定的表，解锁：

二十五、导出表和表结构

导出表：export tables是一个dmp文件

导出表结构：export users object是一个sql文件

选择要导出的表名称。

对于一个sql文件再导入时候，可以点击File—New—Command Window，

弹出框中，输入@，选择导入的sql文件名称。

二十六、从开发那里导出的dmp文件导入测试库提示XX表空间不存在

1）请让开发导出dmp的时候，不要勾选表空间。