者：宗杨

爱可生产品交付团队成员，主要负责公司运维平台和数据库运维故障诊断。喜爱数据库、容器等技术，爱好历史、追剧。

本文来源：原创投稿

*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

一、事件背景

我们的合作客户，驻场人员报告说一个 RDS 实例出现磁盘不足的告警，需要排查。

告警信息：

告警内容：

数据库 data 磁盘不足，磁盘占用 80% 以上

数据库 binlog 磁盘不足，磁盘占用 80% 以上

二、排查过程

登陆告警的服务器，查看磁盘空间，并寻找大容量文件后，发现端口号为 4675 的实例临时表空间 ibtmp1 的大小有 955G，导致磁盘被使用了 86%；

猜测和库里执行长 SQL 有关系，产生了很多临时数据，并写入到临时表空间。

看到有这样一条 SQL，继续分析它的执行计划；

很明显看到图中标记的这一点为使用了临时计算，说明临时表空间的快速增长和它有关系。这条 SQL 进行了三表关联，每个表都有几十万行数据，三表关联并没有在 where 条件中设置关联字段，形成了笛卡尔积，所以会产生大量临时数据；而且都是全表扫描，加载的临时数据过多;还涉及到排序产生了临时数据；这几方面导致 ibtmp1 空间快速爆满。

三、解决办法

和项目组沟通后，杀掉这个会话解决问题；

但是这个 SQL 停下来了，临时表空间中的临时数据没有释放；

最后通过重启 mysql 数据库，释放了临时表空间中的临时数据，这个只能通过重启释放。

四、分析原理

通过查看官方文档，官方是这么解释的：

翻译：

根据官网文档的解释，在正常关闭或初始化中止时，将删除临时表空间，并在每次启动服务器时重新创建。重启能够释放空间的原因在于正常关闭数据库，临时表空间就被删除了，重新启动后重新创建，也就是重启引发了临时表空间的重建，重新初始化，所以，重建后的大小为 12M。

从错误日志里可以验证上面的观点：

五、官网对于 ibtmp1 大小的说明

六、如何避免

1. 对临时表空间的大小进行限制，允许自动增长，但最大容量有上限,本例中由于 innodb_temp_data_file_path 设置的自动增长，但未设上限，所以导致 ibtmp1

有 955G。

正确方法配置参数 innodb_temp_data_file_path：

[mysqld]

innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M

参考官方文档：

设置了上限的大小，当数据文件达到最大大小时，查询将失败，并显示一条错误消息，表明表已满，查询不能往下执行，避免 ibtmp1 过大。

2. 在发送例如本例中的多表关联 SQL 时应确保有关联字段而且有索引，避免笛卡尔积式的全表扫描，对存在 group by、order by、多表关联的 SQL 要评估临时数据量，对 SQL 进行审核，没有审核不允许上线执行。

3. 在执行前通过 explain 查看执行计划，对 Using temporary 需要格外关注。

七、其他补充

1> 通过字典表查看执行的 SQL 产生临时表、使用临时表空间的情况：

查询字典表：sys.x$statements_with_temp_tables

select * from sys.x$statements_with_temp_tables where query like 'select%' and db='test' order by tmp_tables_to_disk_pct,disk_tmp_tables desc\G;

查询字典表：sys.statements_with_temp_tables

select * from sys.statements_with_temp_tables where query like 'select%' and db='test' order by tmp_tables_to_disk_pct,disk_tmp_tables desc\G;

这两个表查询的结果是一样的，各列含义如下：

query：规范化的语句字符串。

db：语句的默认数据库， NULL 如果没有。

exec_count：语句已执行的总次数。

total_latency：定时出现的语句的总等待时间。

memory_tmp_tables：由该语句的出现创建的内部内存临时表的总数。

disk_tmp_tables：由该语句的出现创建的内部磁盘临时表的总数。

avg_tmp_tables_per_query：每次出现该语句创建的内部临时表的平均数量。

tmp_tables_to_disk_pct：内部内存临时表已转换为磁盘表的百分比。

first_seen：第一次看到该声明的时间。

last_seen：最近一次发表该声明的时间。

digest：语句摘要。

参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-statements-with-temp-tables.html

通过字典表 tmp_tables_to_disk_pct 这一列结果可知，内存临时表已转换为磁盘表的比例是 100%，说明通过复现这个查询，它的临时计算结果已经都放到磁盘上了，进一步证明这个查询和临时表空间容量的快速增长有关系。

2> 对于 mysql5.7 中 kill 掉运行长 SQL 的会话，ibtmp1 容量却没有收缩问题的调研；

来源链接：http://mysql.taobao.org/monthly/2019/04/01/

从文章中的解释看，会话被杀掉后，临时表是释放的，只是在 ibtmp1 中打了删除标记，空间并没有还给操作系统，只有重启才可以释放空间。

3> 下面，进一步用 mysql8.0 同样跑一下这个查询，看是否有什么不同;

mysql 版本：8.0.18

当这个 sql 将磁盘跑满之后，发现与 5.7 不同的是这个 SQL 产生的临时数据保存到了 tmpdir，mysql5.7 是保存在 ibtmp1 中，而且由于磁盘满，SQL 执行失败，很快磁盘空间就释放了；

问题：如何使用到 8.0 版本的临时表空间？

通过查看 8.0 的官方文档得知，8.0 的临时表空间分为会话临时表空间和全局临时表空间，会话临时表空间存储用户创建的临时表和当 InnoDB 配置为磁盘内部临时表的存储引擎时由优化器创建的内部临时表，当会话断开连接时，其临时表空间将被截断并释放回池中；也就是说，在 8.0 中有一个专门的会话临时表空间，当会话被杀掉后，可以回收磁盘空间；而原来的 ibtmp1 是现在的全局临时表空间，存放的是对用户创建的临时表进行更改的回滚段，在 5.7 中 ibtmp1 存放的是用户创建的临时表和磁盘内部临时表；

也就是在 8.0 和 5.7 中 ibtmp1 的用途发生了变化，5.7 版本临时表的数据存放在 ibtmp1 中，在 8.0 版本中临时表的数据存放在会话临时表空间，如果临时表发生更改，更改的 undo 数据存放在 ibtmp1 中；

实验验证：将之前的查询结果保存成临时表，对应会话是 45 号，通过查看对应字典表，可知 45 号会话使用了 temp_8.ibt 这个表空间，通过把查询保存成临时表，可以用到会话临时表空间，如下图：

下一步杀掉 45 号会话，发现 temp_8.ibt 空间释放了，变为了初始大小，状态为非活动的，证明在 mysql8.0 中可以通过杀掉会话来释放临时表空间。

总结：在 mysql5.7 时，杀掉会话，临时表会释放，但是仅仅是在 ibtmp 文件里标记一下，空间是不会释放回操作系统的。如果要释放空间，需要重启数据库；在 mysql8.0 中可以通过杀掉会话来释放临时表空间。

八、参考文档

https://dev.mysql.com/doc/refman/5.7/en/innodb-temporary-tablespace.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-temporary-tablespace.html

http://mysql.taobao.org/monthly/2019/04/01/

者:萌新J

出处:https://www.cnblogs.com/mengxinJ/p/14387096.html

-1 Oracle 版本查询

SELECT * FROM V$VERSION;

SELECT * FROM PRODUCT_COMPONENT_VERSION;

--2 Oracle字符集查看

SELECT * FROM Nls_Database_Parameters where PARAMETER in ('NLS_LANGUAGE','NLS_CHARACTERSET') ;--服务端

SELECT Userenv('language') FROM dual;--客户端

--3 oracle用户删除

--查看用户占用了多少空间

SELECT owner, tablespace_name, ROUND (SUM (BYTES) / 1024 / 1024/ 1024, 2) "USED(G)"

FROM dba_segments

GROUP BY owner, tablespace_name

ORDER BY SUM (BYTES) DESC;

--查询当前登陆用户所占空间大小：

select sum(BYTES) / 1024 / 1024/1024 as SIZE_G

from user_segments

--查询所有dba用户所占空间大小：

select sum(BYTES) / 1024 / 1024/1024 as SIZE_G

from dba_segments

--查看当前用户下所有表占用的空间情况：

select segment_name,tablespace_name,bytes,blocks from user_segments

/*先删除用户，再删除表空间，必须保证表空间没有用户使用才能删除*/

--查询用户的相关信息：

select * from DBA_USERS;

--删除用户：

drop user username cascade;

--删除表空间和它的系统文件：

drop tablespace YSSUCO including contents and datafiles;

--如果发现不能删除用户，应该是用户还有连接，查询用户的连接：

select username,sid,serial# from v$session where username='username';

--查询出他的sid，serial#，然后杀掉：

alter system kill session ‘1505,81’;

--再次查询，可以查询它的状态：

select saddr,sid,serial#,paddr,username,status from v$session where username='username';

--发现他的状态为KILLD时，已经杀掉，再次执行删除用户即可；

--4 Oracle用户创建

/*分为四步 */

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

create tablespace YSSUCO datafile '/u01/app/oracle/oradata/FA/YSSUCO01.DBF' size 1000M autoextend on next 100M maxsize 31900M;

/*第2步：Oracle表空间不足的处理方法*/

alter tablespace YSSUCO add datafile '/u01/app/oracle/oradata/FA/YSSUCO02.DBF' size 1000M autoextend on next 100M MAXSIZE 31900M;

alter tablespace YSSUCO add datafile '/u01/app/oracle/oradata/FA/YSSUCO03.DBF' size 1000M autoextend on next 100M MAXSIZE 31900M;

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

CREATE USER username IDENTIFIED BY 1 DEFAULT TABLESPACE YSSUCO TEMPORARY TABLESPACE TEMP;

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

grant connect, resource, dba to username;

grant connect to username;

grant read,write on directory dp_dir to username;

grant exp_full_database,imp_full_database to username;

grant resource to username;

grant create database link to username;

grant create public synonym to username;

grant create synonym to username;

grant create view to username;

grant unlimited tablespace to username;

grant execute on dbms_crypto to username;

--5 Oracle创建数据库逻辑目录dp_dir

[root@CNZHAULAMC094 ~]# mkdir /dp_dir

[root@CNZHAULAMC094 ~]# chmod -R 777 /dp_dir

[root@CNZHAULAMC094 ~]# chmod -R 777 /dp_dir/expdp.dmp

[root@CNZHAULAMC094 ~]# su - oracle

[oracle@localhost ~]$ lsnrctl status;

[oracle@localhost ~]$ export ORACLE_SID=FA

[oracle@localhost ~]$ sqlplus / as sysdba;

SQL>select * from dba_directories; /*查询逻辑目录*/

SQL> create directory dp_dir as '/dp_dir'; /*创建数据库逻辑目录*/

--6 Oracle新建directory

/*1、查询有哪些directory*/

select * from dba_directories

/*2、把目录/dp_dir设置成dp_dir代表的directory*/

create or replace directory dp_dir as '/dp_dir';

/*3、赋权*/

grant read,write on directory dp_dir to username;

grant exp_full_database,imp_full_database to username;

/*4、删除*/

drop directory dp_dir

--7 Oracle数据备份

--表备份

/*1、备份表数据*/

create table user_info_bak as select * from user_info;

/*2、还原表数据*/

insert into user_info_bak select * from user_info;

--库备份，oracle 从一个oracle导数据到另外一个oracle

/*1、普通导库*/

exp INDBADMIN/INDBADMIN@10.1.252.38:1521/move owner=INDBADMIN file=D:\INDBADMIN20190622.dmp log=D:\INDBADMIN20190622.log

imp username/1@127.0.0.1:1521/orcl file=D:\app\yu\oradata\dp_dir\newgzdb.dmp log=D:\app\yu\oradata\dp_dir\username.log ignore=y FULL=y;

/*2、数据泵导库*/

1)按用户导

expdp v45test/1 schemas=v45test directory=dp_dir dumpfile=expdp.dmp ;

2)并行进程parallel

expdp v45test/1 directory=dp_dir dumpfile=expdp.dmp parallel=3 job_name=scott3

3)按表名导

expdp v45test/1 TABLES=t_s_user directory=dp_dir dumpfile=expdp.dmp;

4)按查询条件导

expdp v45test/1 directory=dp_dir dumpfile=expdp.dmp Tables=t_s_user query='WHERE c_user_code=ywy';

5)按表空间导

expdp v45test/1 directory=dp_dir dumpfile=expdp.dmp tablespaces=example;

6)导整个数据库

expdp v45test/1 directory=dp_dir dumpfile=expdp.dmp full=y;

5、还原数据

1)导到指定用户下

impdp username/1 DIRECTORY=dp_dir DUMPFILE=expdp.dmp SCHEMAS=username;

2)改变表的owner

impdp username/1 DIRECTORY=dp_dir DUMPFILE=expdp.dmp TABLES=v45test.dept REMAP_SCHEMA=v45test:username;

3)导入表空间

impdp username/1 DIRECTORY=dp_dir DUMPFILE=expdp.dmp TABLESPACES=example;

4)导入数据库

impdb username/1 DIRECTORY=dp_dir DUMPFILE=expdp.dmp FULL=y;

5)追加数据

impdp username/1 DIRECTORY=dp_dir DUMPFILE=expdp.dmp SCHEMAS=username TABLE_EXISTS_ACTION=append;

6)并行命令

从oss_scap_83(db_link名)导出scap用户,然后导入到本地数据库上scap用户上

impdp username/oracle NETWORK_LINK=oss_scap_83 directory=dp_dir SCHEMAS=scap job_name=expdmp parallel=4;

7)高版本导入底版本（12c到11g）

expdp v45test/1 directory=dp_dir dumpfile=expdp_%U.dmp parallel=6 Version=11.2.0.4.0 logfile=expdp.log schemas=v45test compression=all;

expdp v45test/1 directory=dp_dir dumpfile=expdp.dmp Version=11.2.0.4.0 logfile=expdp.log schemas=v45test ;

impdp username/1 directory=dp_dir dumpfile=expdp.dmp remap_schema=v45test:username logfile=impdp.log;

impdp username/1 directory=dp_dir parallel=6 dumpfile=expdp_01.dmp,expdp_02.dmp,expdp_03.dmp,expdp_04.dmp,expdp_05.dmp,expdp_06.dmp remap_schema=v45test:username logfile=impdp.log;

expdp username/1 directory=dp_dir dumpfile=expdp_%U.dmp parallel=6 logfile=expdp.log schemas=username compression=all;

8)不同用户不同表空间下的导入操作:

impdp 用户名/密码 directory='您创建的目录的名称' dumpfile='导出的文件名称'

remap_schema=导出的用户名称:要导入的用户名称 remap_tablespace=导出的表空间名称:要导入的表空间名称

例如：

[oracle@server36 ~]$ impdp system/oracle@XXX.XXX.XX.XXX/service_name directory=dp_dir remap_schema=EFMIS_23_YANSHI:efmis_23_20150511 dumpfile=EFMIS_23_YANSHI_201505110900.dmp

remap_schema=导出的用户:预备导入的用户

service_name：一般是orcl，可查询：select instance_name from v$instance;

9)数据文件压缩

/*压缩服务器上当前目录的内容为xxx.zip文件*/

zip -r expdp.zip ./*

/*解压zip文件到当前目录*/

unzip expdp.zip

--8 Oracle导库后操作

SQL>alter user test account unlock; --解锁用户

SQL>alter user username identified by 1; --数据库用户密码置1

SQL>update t_s_user set c_user_pwd='6B86B273FF34FCE19D6B804EFF5A3F5747ADA4EAA22F1D49C01E52DDB7875B4B' --系统用户密码置1

SQL> grant execute on DBMS_CRYPTO to test; --用户赋权

SQL> @D:\Encrypt_AES.plb;

SQL> @D:\Decrypt_AES.plb;

SQL> @D:\调整sequence-生成脚本.sql;

--9 Oracle报错解决

ORA-04031: 无法分配 4064 字节的共享内存

第一种：治标不治本。

alter system flush shared_pool;

这种方法可以立即有效果，但是不是根本的解决办法，一小时左右又开始报上面的错误了，再次执行就可以了。

第二种：治标又治本。

ALTER SYSTEM SET SHARED_POOL_SIZE='100M' SCOPE=spfile；

1

然后重启下：

sql>shutdown immediate;

sql>startup;

---------------------

ORA-12519: TNS:no appropriate service handler found 解决

有时候连得上数据库,有时候又连不上.

可能是数据库上当前的连接数目已经超过了它能够处理的最大值.

select count(*) from v$process --当前的连接数

select value from v$parameter where name='processes' --数据库允许的最大连接数

修改最大连接数:

alter system set processes=1000 scope=spfile;

重启数据库:

shutdown immediate;

startup;

--查看当前有哪些用户正在使用数据库

SELECT osuser, a.username,cpu_time/executions/1000000||'s', sql_fulltext,machine

from v$session a, v$sqlarea b

where a.sql_address=b.address order by cpu_time/executions desc;

select SEQ_ACM_CLAIM.NEXTVAL from DUAL --查询一下这个表的序列号在什么位子

alter sequence SEQ_ACM_CLAIM increment by 100 --表中SEQ_NO的值大于查询到的值，就将把索引值进行更新，设定序列步长为100（一般都是1）

select * from user_sequences; --查询所有所有索引：--

drop sequence SEQ_ACM_CLAIM; --删除索引：

CREATE UNIQUE INDEX SEQ_ACM_CLAIM ON TBL_ACM_CLAIM(SEQ_NO); ---创建索引

select * from t_s_db_up_his a where a.n_exe_status<>2; --表示执行失败的日志记录

java.sql.BatchUpdateException: ORA-00001: 违反唯一约束条件 (TEST.PK_R_FR_VAT_LOCK) --问题

t_R_FR_VAT_LOCK--表名

select max(length(c_iden)) from t_R_FR_VAT_LOCK --查询约束条件最大几位

select max(c_iden) from t_R_FR_VAT_LOCK where length(c_iden)=6 --查询约束条件最大序列

select Sequ_r_Fr_Vat_Lock.nextval from dual;--查询约束条件即将插入的序列，如果大于等于约束条件最大序列，就会报唯一性约束条件错误

alter sequence Sequ_r_Fr_Vat_Lock increment by 1000000; --不可以直接修改的，但是可以间接修改。

select Sequ_r_Fr_Vat_Lock.nextval from dual;--修改步进的值，然后查询一次，

alter sequence Sequ_r_Fr_Vat_Lock increment by 1; --然后再把步进修改回去，相当于修改了序列的当前值。

--创建序列

create sequence sequ_d_ysskmtx3_set_temp002

increment by 1 -- 每次加几个

start with 1 -- 从1开始计数

nomaxvalue -- 不设置最大值

nocycle -- 一直累加，不循环

cache 10;

--删除序列

drop sequence sequ_d_ysskmtx3_set_temp002;

--查询序列

select sequ_d_ysskmtx3_set_temp002.nextval from dual;

--表信息收集语句

begin

dbms_stats.gather_table_stats(ownname=> 'NEWGZDB',tabname=> 'T_R_FR_ASTSTAT', estimate_percent=> dbms_stats.auto_sample_size, method_opt=> 'for all indexed columns',cascade=> true, degree=> 8);

end;

--索引重建:

alter index IDX_R_FR_ASTSTAT rebuild partition PART_2019;

alter index IDX_R_FR_ASTSTAT rebuild partition PART_2020;

--全表收集

declare

begin

for cur in (select owner, table_name

from dba_tables

where READ_ONLY='NO'

and temporary='N'

and iot_name is null

and cluster_name is null

and status='VALID'

and tablespace_name not in ('USERS', 'SYSTEM', 'SYSAUX')

and owner='NEWGZDB'

order by 1, 2) loop

dbms_stats.gather_table_stats(ownname=> cur.owner,

tabname=> cur.table_name,

estimate_percent=> 25,

cascade=> true,

degree=> 4,

force=> true);

end loop;

end;

--当前执行sql语句

select a.SID,

a.SERIAL#,

a.USERNAME,

b.PARSE_CALLS,

b.PARSING_SCHEMA_NAME,

b.CPU_TIME/1000000,

b.ELAPSED_TIME/1000000,

b.DISK_READS,

b.DIRECT_WRITES,

b.BUFFER_GETS,

a.event,

b.sql_text,

b.SQL_FULLTEXT

from v$session a inner join v$sqlarea b

on a.SQL_HASH_VALUE=b. hash_value and b.PARSING_SCHEMA_NAME=upper('smsdb')

--物理读最高sql语句

select a.USERNAME,

a. USER_ID,

b.PARSE_CALLS,

b.PARSING_SCHEMA_NAME,

b.CPU_TIME/1000000,

b.ELAPSED_TIME/1000000,

b.DISK_READS,

b.DIRECT_WRITES,

b.BUFFER_GETS,

b.sql_text,

b.SQL_FULLTEXT

from dba_users a inner join v$sqlarea b

on a.USER_ID=b.PARSING_USER_ID and b.PARSING_SCHEMA_NAME=upper('smsdb') and disk_reads>1000000

--查询前10名执行最多次数SQL语句

select sql_text "SQL语句", executions "执行次数"

from (select sql_text,

executions,

rank() over

(order by executions desc) exec_rank

from v$sqlarea)

where exec_rank <=10;

--查询前10名占用CPU最高的SQL语句

select sql_text "SQL语句",

c_t "SQL执行时间（秒）",executions "执行次数",cs "每次执行时间（秒）"　from (select sql_text,

cpu_time /1000000 c_t,executions,ceil(executions/(cpu_time/1000000))cs,

rank() over(order by cpu_time desc) top_time

from v$sqlarea) where top_time <=10

--查询前10名执行时间最长SQL语句

select sql_text "SQL语句",

c_t "处理时间(秒)",executions "执行次数",cs "每次执行时间（秒）"

from (select sql_text,

ELAPSED_TIME / 1000000 c_t,executions,ceil(executions/(ELAPSED_TIME/1000000))cs,

rank() over(order by ELAPSED_TIME desc) top_time

from v$sqlarea) where top_time <=10

--查询前10名最耗资源SQL语句

select sql_text "SQL语句",

DISK_READS "物理读次数",cs "每次执行时间（秒）"

from (select sql_text,

ELAPSED_TIME / 1000000 c_t,executions,ceil(executions/(ELAPSED_TIME/1000000))cs,DISK_READS,

rank() over(order by DISK_READS desc) top_disk

from v$sqlarea) where top_disk <=10

--查询前10名最耗内存SQL语句

select sql_text "SQL语句",

BUFFER_GETS "内存读次数",cs "每次执行时间（秒）"

from (select sql_text,

ELAPSED_TIME / 1000000 c_t,executions,ceil(executions/(ELAPSED_TIME/1000000))cs,BUFFER_GETS,

rank() over(order by BUFFER_GETS desc) top_mem

from v$sqlarea) where top_mem <=10

--查看锁表语句

Select

c.sid,

c.serial#,

d.name,

b.object_name,

c.username,

c.program,

c.osuser

from gv$Locked_object a, All_objects b, gv$session c, audit_actions d

where a.object_id=b.object_id

and a.inst_id=c.inst_id(+)

and a.session_id=c.sid(+)

and c.command=d.action;

--7 Oracle数据备份

表备份

--备份表语句：

create table user_info_bak as select * from user_info;

--还原表数据：；

insert into user_info_bak select * from user_info;

库备份

oracle 从一个oracle导数据到另外一个oracle

用exp和imp导出导入数据

导出：exp OSMPPORTAL/PORTAL@10.130.24.133:1521/omsp file=/home/oracle/osmpportal.dmp

参数：owner=(system,sys)两个用户，tables=table1，table2 只要表结构不要数据：rows=n

导入：imp ccod/ccod@192.168.30.20:1521/ccpbs16 file=/home/oracle/osmpportal.dmp fromuser=OSMPPORTAL touser=osmpportal

参数：imp 登陆的（有相应权限的）用户名/密码@oracle的ip：端口/sid file=文件目录 fromuser=导出时的用户 touser=要导入的用户

ignore=y有的表已经存在会报错，忽略

expdp和impdp

一 关于expdp和impdp 使用EXPDP和IMPDP时应该注意的事项：

EXP和IMP是客户端工具程序，它们既可以在客户端使用，也可以在服务端使用。

EXPDP和IMPDP是服务端的工具程序，他们只能在ORACLE服务端使用，不能在客户端使用。

IMP只适用于EXP导出的文件，不适用于EXPDP导出文件；IMPDP只适用于EXPDP导出的文件，而不适用于EXP导出文件。

expdp或impdp命令时，可暂不指出用户名/密码@实例名 as 身份，然后根据提示再输入，如：

expdp schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1;

expdp OSMPPORTAL/PORTAL@ccpbszq DUMPFILE=osmpportaldp.dmp SCHEMAS=OSMPPORTAL

http://www.cnblogs.com/huacw/p/3888807.html

ORA-01031: insufficient privileges

原因：没有赋予相应权限

一：查看数据量

1.查询是否有用户：select * from dba_users where username='OSMPPORTAL'

2.查询当前用户总数据量：select sum(t.num_rows) from user_tables t

3.查询当前用户下各个表的数据量：select t.table_name,t.num_rows from user_tables t ORDER BY NUM_ROWS DESC

4.查询表空间对应的数据文件：select tablespace_name,file_name from dba_data_files

5.查询表空间对应的数据大小：select tablespace_name ,sum(bytes) / 1024 / 1024 as MB　from dba_data_files group by tablespace_name;

6.查询数据量：select SEGMENT_NAME,s.BYTES/1024/1024/1024 as Gb　from user_segments s where s.BYTES is not null order by s.BYTES desc

二：新建用户

1.新建用户：create user username identified by pwd

注：默认表空间：default tablespace hxzg_data;

2.修改密码：alter user username identified by newpwd

3.新建的用户也没有任何权限，必须授予权限

grant create session to zhangsan;//授予zhangsan用户创建session的权限，即登陆权限

　grant unlimited tablespace to zhangsan;//授予zhangsan用户使用表空间的权限

　grant create table to zhangsan;//授予创建表的权限

　grante drop table to zhangsan;//授予删除表的权限

　grant insert table to zhangsan;//插入表的权限

　grant update table to zhangsan;//修改表的权限

　grant all to public;//这条比较重要，授予所有权限(all)给所有用户(public)

4.drop user username; //删除用户

5.赋权限：grant resource,connect to db_hxzg;

6.给其他用户访问权限（db_hxzg以DBA权限登录）

grant select any table to sun;

几个问题

1.在机器orcl上查看用户默认表空间，以便导入时创建一样的表空间

select username,default_tablespace from dba_users where username='CMS';

2．查看用户使用的表空间

select DISTINCT owner ,tablespace_name from dba_extents where owner like 'CMS';

3.查看表空间对应的数据文件，以便在B上创建大小合适的数据文件。

select file_name,tablespace_name from dba_data_files where tablespace_name in ('WORK01');

4.检查B机器的表空间，看是否存在work01表空间

select name from v$tablespace where name=‘WORK01’;

查找不到，说明没有这个两个表空间，需要创建

5.要导入数据的server没有work01表空间,创建:

create tablespace work01

datafile '/u01/oradata/orac/work01.dbf'

size 200m

autoextend on

next 20m

maxsize unlimited

extent management local;

6. 在要导入的数据库上查找用户是否已经存在

select username from dba_users where username='CMS';

如果存在：

drop user cms cascade; --（删除用户及其拥有的所有对象）

-- 此时如果这个用户在连接，drop会出错，必须先杀掉用户的session，然后再drop

SELECT 'alter system kill session '''||SID||','||SERIAL#||''' immediate;'

FROM V$SESSION

WHERE USERNAME='CMS';

alter system kill session '93,56387' immediate;

alter system kill session '100,18899' immediate;

alter system kill session '135,24910' immediate;

alter system kill session '149,3' immediate;

alter system kill session '152,3' immediate;

alter system kill session '156,7' immediate;

alter system kill session '159,45889' immediate;

alter system kill session '160,1' immediate;

alter system kill session '161,1' immediate;

alter system kill session '162,1' immediate;

alter system kill session '163,1' immediate;

--再复制这些语句，粘贴到sqlplus中执行，来杀掉Test2的session。

如果不存在cms用户：

create user cms identified bycms default tablespace work01 temporary tablespace temp;

不管存不存在都应该给cms授权

grant connect,resource to cms;

7.最后将数据导入

下面在windows的cmd下将用户导进去

imp file=e:\cms.dmp fromuser=cms touser=cms userid=cms/cms@orac

这里要注意的是之前我是用cms用户将数据导出来的，这个cms具有dba权限，那么这里导入的时候用的userid后面的cms也必须具有这个权限不然会报错

这里我们可以临时给cms赋予dba权限，最后回收他，但是回收之后，记得再给cms赋予resource权限NFO,USER_PROJECT_INFO) file=/home/oracle/osmp2.dmp

--11oracle如何生成awr报告

[root@localhost ~]# su - oracle

--查询生成awr报告生成位置

[oracle@localhost ~]$ pwd;

/home/oracle

oracle安装目录：

--oracle_home是oracle的产品目录。

[oracle@localhost ~]$ echo $ORACLE_HOME

--oracle_base 是oracle的根目录，

[oracle@localhost ~]$ echo $ORACLE_BASE

[oracle@localhost ~]$ env |grep ORA

[oracle@localhost ~]$ lsnrctl status;

[oracle@localhost ~]$ export ORACLE_SID=FA

[oracle@localhost ~]$ sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 10 10:15:41 2019

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/awrrpt

Current Instance

~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance

----------- ------------ -------- ------------

2340707931 TEST 1 test

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Would you like an HTML report, or a plain text report?

Enter 'html' for an HTML report, or 'text' for plain text

Defaults to 'html'

Enter value for report_type: html

Type Specified: html

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host

------------ -------- ------------ ------------ ------------

* 2340707931 1 TEST test localhost.lo

caldomain

Using 2340707931 for database Id

Using 1 for instance number

Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed. Pressing <return> without

specifying a number lists all completed snapshots.

Enter value for num_days: 1

Listing the last day's Completed Snapshots

Snap

Instance DB Name Snap Id Snap Started Level

------------ ------------ --------- ------------------ -----

test TEST 319 22 Jun 2020 00:00 1

320 22 Jun 2020 01:00 1

321 22 Jun 2020 02:00 1

322 22 Jun 2020 03:00 1

323 22 Jun 2020 04:00 1

324 22 Jun 2020 05:00 1

325 22 Jun 2020 06:00 1

326 22 Jun 2020 07:00 1

327 22 Jun 2020 08:00 1

328 22 Jun 2020 09:00 1

329 22 Jun 2020 10:00 1

330 22 Jun 2020 11:00 1

331 22 Jun 2020 12:00 1

332 22 Jun 2020 13:00 1

333 22 Jun 2020 14:00 1

334 22 Jun 2020 15:00 1

335 22 Jun 2020 16:00 1

336 22 Jun 2020 17:00 1

337 22 Jun 2020 18:00 1

338 22 Jun 2020 19:00 1

339 22 Jun 2020 20:00 1

340 22 Jun 2020 20:43 1

341 22 Jun 2020 20:44 1

342 22 Jun 2020 20:58 1

343 22 Jun 2020 20:58 1

Enter value for begin_snap: 339

Begin Snapshot Id specified: 339

Enter value for end_snap: 343

End Snapshot Id specified: 343

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrrpt_1_339_343.html. To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name: awrrpt_20200622.htlm

......

</body></html>

Report written to awrrpt_20200622.htlm

awr报告生成路径：/home/oracle/awrrpt_20200622.htlm