者：宗楊

愛(ài)可生產(chǎn)品交付團(tuán)隊(duì)成員，主要負(fù)責(zé)公司運(yùn)維平臺(tái)和數(shù)據(jù)庫(kù)運(yùn)維故障診斷。喜愛(ài)數(shù)據(jù)庫(kù)、容器等技術(shù)，愛(ài)好歷史、追劇。

本文來(lái)源：原創(chuàng)投稿

*愛(ài)可生開(kāi)源社區(qū)出品，原創(chuàng)內(nèi)容未經(jīng)授權(quán)不得隨意使用，轉(zhuǎn)載請(qǐng)聯(lián)系小編并注明來(lái)源。

一、事件背景

我們的合作客戶(hù)，駐場(chǎng)人員報(bào)告說(shuō)一個(gè) RDS 實(shí)例出現(xiàn)磁盤(pán)不足的告警，需要排查。

告警信息：

告警內(nèi)容：

數(shù)據(jù)庫(kù) data 磁盤(pán)不足，磁盤(pán)占用 80% 以上

數(shù)據(jù)庫(kù) binlog 磁盤(pán)不足，磁盤(pán)占用 80% 以上

二、排查過(guò)程

登陸告警的服務(wù)器，查看磁盤(pán)空間，并尋找大容量文件后，發(fā)現(xiàn)端口號(hào)為 4675 的實(shí)例臨時(shí)表空間 ibtmp1 的大小有 955G，導(dǎo)致磁盤(pán)被使用了 86%；

猜測(cè)和庫(kù)里執(zhí)行長(zhǎng) SQL 有關(guān)系，產(chǎn)生了很多臨時(shí)數(shù)據(jù)，并寫(xiě)入到臨時(shí)表空間。

看到有這樣一條 SQL，繼續(xù)分析它的執(zhí)行計(jì)劃；

很明顯看到圖中標(biāo)記的這一點(diǎn)為使用了臨時(shí)計(jì)算，說(shuō)明臨時(shí)表空間的快速增長(zhǎng)和它有關(guān)系。這條 SQL 進(jìn)行了三表關(guān)聯(lián)，每個(gè)表都有幾十萬(wàn)行數(shù)據(jù)，三表關(guān)聯(lián)并沒(méi)有在 where 條件中設(shè)置關(guān)聯(lián)字段，形成了笛卡爾積，所以會(huì)產(chǎn)生大量臨時(shí)數(shù)據(jù)；而且都是全表掃描，加載的臨時(shí)數(shù)據(jù)過(guò)多;還涉及到排序產(chǎn)生了臨時(shí)數(shù)據(jù)；這幾方面導(dǎo)致 ibtmp1 空間快速爆滿(mǎn)。

三、解決辦法

和項(xiàng)目組溝通后，殺掉這個(gè)會(huì)話(huà)解決問(wèn)題；

但是這個(gè) SQL 停下來(lái)了，臨時(shí)表空間中的臨時(shí)數(shù)據(jù)沒(méi)有釋放；

最后通過(guò)重啟 mysql 數(shù)據(jù)庫(kù)，釋放了臨時(shí)表空間中的臨時(shí)數(shù)據(jù)，這個(gè)只能通過(guò)重啟釋放。

四、分析原理

通過(guò)查看官方文檔，官方是這么解釋的：

翻譯：

根據(jù)官網(wǎng)文檔的解釋?zhuān)谡ｊP(guān)閉或初始化中止時(shí)，將刪除臨時(shí)表空間，并在每次啟動(dòng)服務(wù)器時(shí)重新創(chuàng)建。重啟能夠釋放空間的原因在于正常關(guān)閉數(shù)據(jù)庫(kù)，臨時(shí)表空間就被刪除了，重新啟動(dòng)后重新創(chuàng)建，也就是重啟引發(fā)了臨時(shí)表空間的重建，重新初始化，所以，重建后的大小為 12M。

從錯(cuò)誤日志里可以驗(yàn)證上面的觀點(diǎn)：

五、官網(wǎng)對(duì)于 ibtmp1 大小的說(shuō)明

六、如何避免

1. 對(duì)臨時(shí)表空間的大小進(jìn)行限制，允許自動(dòng)增長(zhǎng)，但最大容量有上限,本例中由于 innodb_temp_data_file_path 設(shè)置的自動(dòng)增長(zhǎng)，但未設(shè)上限，所以導(dǎo)致 ibtmp1

有 955G。

正確方法配置參數(shù) innodb_temp_data_file_path：

[mysqld]

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

參考官方文檔：

設(shè)置了上限的大小，當(dāng)數(shù)據(jù)文件達(dá)到最大大小時(shí)，查詢(xún)將失敗，并顯示一條錯(cuò)誤消息，表明表已滿(mǎn)，查詢(xún)不能往下執(zhí)行，避免 ibtmp1 過(guò)大。

2. 在發(fā)送例如本例中的多表關(guān)聯(lián) SQL 時(shí)應(yīng)確保有關(guān)聯(lián)字段而且有索引，避免笛卡爾積式的全表掃描，對(duì)存在 group by、order by、多表關(guān)聯(lián)的 SQL 要評(píng)估臨時(shí)數(shù)據(jù)量，對(duì) SQL 進(jìn)行審核，沒(méi)有審核不允許上線(xiàn)執(zhí)行。

3. 在執(zhí)行前通過(guò) explain 查看執(zhí)行計(jì)劃，對(duì) Using temporary 需要格外關(guān)注。

七、其他補(bǔ)充

1> 通過(guò)字典表查看執(zhí)行的 SQL 產(chǎn)生臨時(shí)表、使用臨時(shí)表空間的情況：

查詢(xún)字典表：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;

查詢(xún)字典表：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;

這兩個(gè)表查詢(xún)的結(jié)果是一樣的，各列含義如下：

query：規(guī)范化的語(yǔ)句字符串。

db：語(yǔ)句的默認(rèn)數(shù)據(jù)庫(kù)， NULL 如果沒(méi)有。

exec_count：語(yǔ)句已執(zhí)行的總次數(shù)。

total_latency：定時(shí)出現(xiàn)的語(yǔ)句的總等待時(shí)間。

memory_tmp_tables：由該語(yǔ)句的出現(xiàn)創(chuàng)建的內(nèi)部?jī)?nèi)存臨時(shí)表的總數(shù)。

disk_tmp_tables：由該語(yǔ)句的出現(xiàn)創(chuàng)建的內(nèi)部磁盤(pán)臨時(shí)表的總數(shù)。

avg_tmp_tables_per_query：每次出現(xiàn)該語(yǔ)句創(chuàng)建的內(nèi)部臨時(shí)表的平均數(shù)量。

tmp_tables_to_disk_pct：內(nèi)部?jī)?nèi)存臨時(shí)表已轉(zhuǎn)換為磁盤(pán)表的百分比。

first_seen：第一次看到該聲明的時(shí)間。

last_seen：最近一次發(fā)表該聲明的時(shí)間。

digest：語(yǔ)句摘要。

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

通過(guò)字典表 tmp_tables_to_disk_pct 這一列結(jié)果可知，內(nèi)存臨時(shí)表已轉(zhuǎn)換為磁盤(pán)表的比例是 100%，說(shuō)明通過(guò)復(fù)現(xiàn)這個(gè)查詢(xún)，它的臨時(shí)計(jì)算結(jié)果已經(jīng)都放到磁盤(pán)上了，進(jìn)一步證明這個(gè)查詢(xún)和臨時(shí)表空間容量的快速增長(zhǎng)有關(guān)系。

2> 對(duì)于 mysql5.7 中 kill 掉運(yùn)行長(zhǎng) SQL 的會(huì)話(huà)，ibtmp1 容量卻沒(méi)有收縮問(wèn)題的調(diào)研；

來(lái)源鏈接：http://mysql.taobao.org/monthly/2019/04/01/

從文章中的解釋看，會(huì)話(huà)被殺掉后，臨時(shí)表是釋放的，只是在 ibtmp1 中打了刪除標(biāo)記，空間并沒(méi)有還給操作系統(tǒng)，只有重啟才可以釋放空間。

3> 下面，進(jìn)一步用 mysql8.0 同樣跑一下這個(gè)查詢(xún)，看是否有什么不同;

mysql 版本：8.0.18

當(dāng)這個(gè) sql 將磁盤(pán)跑滿(mǎn)之后，發(fā)現(xiàn)與 5.7 不同的是這個(gè) SQL 產(chǎn)生的臨時(shí)數(shù)據(jù)保存到了 tmpdir，mysql5.7 是保存在 ibtmp1 中，而且由于磁盤(pán)滿(mǎn)，SQL 執(zhí)行失敗，很快磁盤(pán)空間就釋放了；

問(wèn)題：如何使用到 8.0 版本的臨時(shí)表空間？

通過(guò)查看 8.0 的官方文檔得知，8.0 的臨時(shí)表空間分為會(huì)話(huà)臨時(shí)表空間和全局臨時(shí)表空間，會(huì)話(huà)臨時(shí)表空間存儲(chǔ)用戶(hù)創(chuàng)建的臨時(shí)表和當(dāng) InnoDB 配置為磁盤(pán)內(nèi)部臨時(shí)表的存儲(chǔ)引擎時(shí)由優(yōu)化器創(chuàng)建的內(nèi)部臨時(shí)表，當(dāng)會(huì)話(huà)斷開(kāi)連接時(shí)，其臨時(shí)表空間將被截?cái)嗖⑨尫呕爻刂校灰簿褪钦f(shuō)，在 8.0 中有一個(gè)專(zhuān)門(mén)的會(huì)話(huà)臨時(shí)表空間，當(dāng)會(huì)話(huà)被殺掉后，可以回收磁盤(pán)空間；而原來(lái)的 ibtmp1 是現(xiàn)在的全局臨時(shí)表空間，存放的是對(duì)用戶(hù)創(chuàng)建的臨時(shí)表進(jìn)行更改的回滾段，在 5.7 中 ibtmp1 存放的是用戶(hù)創(chuàng)建的臨時(shí)表和磁盤(pán)內(nèi)部臨時(shí)表；

也就是在 8.0 和 5.7 中 ibtmp1 的用途發(fā)生了變化，5.7 版本臨時(shí)表的數(shù)據(jù)存放在 ibtmp1 中，在 8.0 版本中臨時(shí)表的數(shù)據(jù)存放在會(huì)話(huà)臨時(shí)表空間，如果臨時(shí)表發(fā)生更改，更改的 undo 數(shù)據(jù)存放在 ibtmp1 中；

實(shí)驗(yàn)驗(yàn)證：將之前的查詢(xún)結(jié)果保存成臨時(shí)表，對(duì)應(yīng)會(huì)話(huà)是 45 號(hào)，通過(guò)查看對(duì)應(yīng)字典表，可知 45 號(hào)會(huì)話(huà)使用了 temp_8.ibt 這個(gè)表空間，通過(guò)把查詢(xún)保存成臨時(shí)表，可以用到會(huì)話(huà)臨時(shí)表空間，如下圖：

下一步殺掉 45 號(hào)會(huì)話(huà)，發(fā)現(xiàn) temp_8.ibt 空間釋放了，變?yōu)榱顺跏即笮。瑺顟B(tài)為非活動(dòng)的，證明在 mysql8.0 中可以通過(guò)殺掉會(huì)話(huà)來(lái)釋放臨時(shí)表空間。

總結(jié)：在 mysql5.7 時(shí)，殺掉會(huì)話(huà)，臨時(shí)表會(huì)釋放，但是僅僅是在 ibtmp 文件里標(biāo)記一下，空間是不會(huì)釋放回操作系統(tǒng)的。如果要釋放空間，需要重啟數(shù)據(jù)庫(kù)；在 mysql8.0 中可以通過(guò)殺掉會(huì)話(huà)來(lái)釋放臨時(shí)表空間。

八、參考文檔

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 版本查詢(xún)

SELECT * FROM V$VERSION;

SELECT * FROM PRODUCT_COMPONENT_VERSION;

--2 Oracle字符集查看

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

SELECT Userenv('language') FROM dual;--客戶(hù)端

--3 oracle用戶(hù)刪除

--查看用戶(hù)占用了多少空間

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;

--查詢(xún)當(dāng)前登陸用戶(hù)所占空間大小：

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

from user_segments

--查詢(xún)所有dba用戶(hù)所占空間大小：

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

from dba_segments

--查看當(dāng)前用戶(hù)下所有表占用的空間情況：

select segment_name,tablespace_name,bytes,blocks from user_segments

/*先刪除用戶(hù)，再刪除表空間，必須保證表空間沒(méi)有用戶(hù)使用才能刪除*/

--查詢(xún)用戶(hù)的相關(guān)信息：

select * from DBA_USERS;

--刪除用戶(hù)：

drop user username cascade;

--刪除表空間和它的系統(tǒng)文件：

drop tablespace YSSUCO including contents and datafiles;

--如果發(fā)現(xiàn)不能刪除用戶(hù)，應(yīng)該是用戶(hù)還有連接，查詢(xún)用戶(hù)的連接：

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

--查詢(xún)出他的sid，serial#，然后殺掉：

alter system kill session ‘1505,81’;

--再次查詢(xún)，可以查詢(xún)它的狀態(tài)：

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

--發(fā)現(xiàn)他的狀態(tài)為KILLD時(shí)，已經(jīng)殺掉，再次執(zhí)行刪除用戶(hù)即可；

--4 Oracle用戶(hù)創(chuàng)建

/*分為四步 */

/*第1步：創(chuàng)建數(shù)據(jù)表空間 */

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步：創(chuàng)建用戶(hù)并指定表空間 */

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

/*第4步：給用戶(hù)授予權(quán)限 */

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創(chuàng)建數(shù)據(jù)庫(kù)邏輯目錄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; /*查詢(xún)邏輯目錄*/

SQL> create directory dp_dir as '/dp_dir'; /*創(chuàng)建數(shù)據(jù)庫(kù)邏輯目錄*/

--6 Oracle新建directory

/*1、查詢(xún)有哪些directory*/

select * from dba_directories

/*2、把目錄/dp_dir設(shè)置成dp_dir代表的directory*/

create or replace directory dp_dir as '/dp_dir';

/*3、賦權(quán)*/

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數(shù)據(jù)備份

--表備份

/*1、備份表數(shù)據(jù)*/

create table user_info_bak as select * from user_info;

/*2、還原表數(shù)據(jù)*/

insert into user_info_bak select * from user_info;

--庫(kù)備份，oracle 從一個(gè)oracle導(dǎo)數(shù)據(jù)到另外一個(gè)oracle

/*1、普通導(dǎo)庫(kù)*/

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、數(shù)據(jù)泵導(dǎo)庫(kù)*/

1)按用戶(hù)導(dǎo)

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

2)并行進(jìn)程parallel

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

3)按表名導(dǎo)

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

4)按查詢(xún)條件導(dǎo)

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

5)按表空間導(dǎo)

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

6)導(dǎo)整個(gè)數(shù)據(jù)庫(kù)

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

5、還原數(shù)據(jù)

1)導(dǎo)到指定用戶(hù)下

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)導(dǎo)入表空間

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

4)導(dǎo)入數(shù)據(jù)庫(kù)

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

5)追加數(shù)據(jù)

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

6)并行命令

從oss_scap_83(db_link名)導(dǎo)出scap用戶(hù),然后導(dǎo)入到本地?cái)?shù)據(jù)庫(kù)上scap用戶(hù)上

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

7)高版本導(dǎo)入底版本（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)不同用戶(hù)不同表空間下的導(dǎo)入操作:

impdp 用戶(hù)名/密碼 directory='您創(chuàng)建的目錄的名稱(chēng)' dumpfile='導(dǎo)出的文件名稱(chēng)'

remap_schema=導(dǎo)出的用戶(hù)名稱(chēng):要導(dǎo)入的用戶(hù)名稱(chēng) remap_tablespace=導(dǎo)出的表空間名稱(chēng):要導(dǎo)入的表空間名稱(chēng)

例如：

[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=導(dǎo)出的用戶(hù):預(yù)備導(dǎo)入的用戶(hù)

service_name：一般是orcl，可查詢(xún)：select instance_name from v$instance;

9)數(shù)據(jù)文件壓縮

/*壓縮服務(wù)器上當(dāng)前目錄的內(nèi)容為xxx.zip文件*/

zip -r expdp.zip ./*

/*解壓zip文件到當(dāng)前目錄*/

unzip expdp.zip

--8 Oracle導(dǎo)庫(kù)后操作

SQL>alter user test account unlock; --解鎖用戶(hù)

SQL>alter user username identified by 1; --數(shù)據(jù)庫(kù)用戶(hù)密碼置1

SQL>update t_s_user set c_user_pwd='6B86B273FF34FCE19D6B804EFF5A3F5747ADA4EAA22F1D49C01E52DDB7875B4B' --系統(tǒng)用戶(hù)密碼置1

SQL> grant execute on DBMS_CRYPTO to test; --用戶(hù)賦權(quán)

SQL> @D:\Encrypt_AES.plb;

SQL> @D:\Decrypt_AES.plb;

SQL> @D:\調(diào)整sequence-生成腳本.sql;

--9 Oracle報(bào)錯(cuò)解決

ORA-04031: 無(wú)法分配 4064 字節(jié)的共享內(nèi)存

第一種：治標(biāo)不治本。

alter system flush shared_pool;

這種方法可以立即有效果，但是不是根本的解決辦法，一小時(shí)左右又開(kāi)始報(bào)上面的錯(cuò)誤了，再次執(zhí)行就可以了。

第二種：治標(biāo)又治本。

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

1

然后重啟下：

sql>shutdown immediate;

sql>startup;

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

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

有時(shí)候連得上數(shù)據(jù)庫(kù),有時(shí)候又連不上.

可能是數(shù)據(jù)庫(kù)上當(dāng)前的連接數(shù)目已經(jīng)超過(guò)了它能夠處理的最大值.

select count(*) from v$process --當(dāng)前的連接數(shù)

select value from v$parameter where name='processes' --數(shù)據(jù)庫(kù)允許的最大連接數(shù)

修改最大連接數(shù):

alter system set processes=1000 scope=spfile;

重啟數(shù)據(jù)庫(kù):

shutdown immediate;

startup;

--查看當(dāng)前有哪些用戶(hù)正在使用數(shù)據(jù)庫(kù)

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 --查詢(xún)一下這個(gè)表的序列號(hào)在什么位子

alter sequence SEQ_ACM_CLAIM increment by 100 --表中SEQ_NO的值大于查詢(xún)到的值，就將把索引值進(jìn)行更新，設(shè)定序列步長(zhǎng)為100（一般都是1）

select * from user_sequences; --查詢(xún)所有所有索引：--

drop sequence SEQ_ACM_CLAIM; --刪除索引：

CREATE UNIQUE INDEX SEQ_ACM_CLAIM ON TBL_ACM_CLAIM(SEQ_NO); ---創(chuàng)建索引

select * from t_s_db_up_his a where a.n_exe_status<>2; --表示執(zhí)行失敗的日志記錄

java.sql.BatchUpdateException: ORA-00001: 違反唯一約束條件 (TEST.PK_R_FR_VAT_LOCK) --問(wèn)題

t_R_FR_VAT_LOCK--表名

select max(length(c_iden)) from t_R_FR_VAT_LOCK --查詢(xún)約束條件最大幾位

select max(c_iden) from t_R_FR_VAT_LOCK where length(c_iden)=6 --查詢(xún)約束條件最大序列

select Sequ_r_Fr_Vat_Lock.nextval from dual;--查詢(xún)約束條件即將插入的序列，如果大于等于約束條件最大序列，就會(huì)報(bào)唯一性約束條件錯(cuò)誤

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

select Sequ_r_Fr_Vat_Lock.nextval from dual;--修改步進(jìn)的值，然后查詢(xún)一次，

alter sequence Sequ_r_Fr_Vat_Lock increment by 1; --然后再把步進(jìn)修改回去，相當(dāng)于修改了序列的當(dāng)前值。

--創(chuàng)建序列

create sequence sequ_d_ysskmtx3_set_temp002

increment by 1 -- 每次加幾個(gè)

start with 1 -- 從1開(kāi)始計(jì)數(shù)

nomaxvalue -- 不設(shè)置最大值

nocycle -- 一直累加，不循環(huán)

cache 10;

--刪除序列

drop sequence sequ_d_ysskmtx3_set_temp002;

--查詢(xún)序列

select sequ_d_ysskmtx3_set_temp002.nextval from dual;

--表信息收集語(yǔ)句

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;

--當(dāng)前執(zhí)行sql語(yǔ)句

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語(yǔ)句

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

--查詢(xún)前10名執(zhí)行最多次數(shù)SQL語(yǔ)句

select sql_text "SQL語(yǔ)句", executions "執(zhí)行次數(shù)"

from (select sql_text,

executions,

rank() over

(order by executions desc) exec_rank

from v$sqlarea)

where exec_rank <=10;

--查詢(xún)前10名占用CPU最高的SQL語(yǔ)句

select sql_text "SQL語(yǔ)句",

c_t "SQL執(zhí)行時(shí)間（秒）",executions "執(zhí)行次數(shù)",cs "每次執(zhí)行時(shí)間（秒）"　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

--查詢(xún)前10名執(zhí)行時(shí)間最長(zhǎng)SQL語(yǔ)句

select sql_text "SQL語(yǔ)句",

c_t "處理時(shí)間(秒)",executions "執(zhí)行次數(shù)",cs "每次執(zhí)行時(shí)間（秒）"

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

--查詢(xún)前10名最耗資源SQL語(yǔ)句

select sql_text "SQL語(yǔ)句",

DISK_READS "物理讀次數(shù)",cs "每次執(zhí)行時(shí)間（秒）"

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

--查詢(xún)前10名最耗內(nèi)存SQL語(yǔ)句

select sql_text "SQL語(yǔ)句",

BUFFER_GETS "內(nèi)存讀次數(shù)",cs "每次執(zhí)行時(shí)間（秒）"

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

--查看鎖表語(yǔ)句

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數(shù)據(jù)備份

表備份

--備份表語(yǔ)句：

create table user_info_bak as select * from user_info;

--還原表數(shù)據(jù)：；

insert into user_info_bak select * from user_info;

庫(kù)備份

oracle 從一個(gè)oracle導(dǎo)數(shù)據(jù)到另外一個(gè)oracle

用exp和imp導(dǎo)出導(dǎo)入數(shù)據(jù)

導(dǎo)出：exp OSMPPORTAL/PORTAL@10.130.24.133:1521/omsp file=/home/oracle/osmpportal.dmp

參數(shù)：owner=(system,sys)兩個(gè)用戶(hù)，tables=table1，table2 只要表結(jié)構(gòu)不要數(shù)據(jù)：rows=n

導(dǎo)入：imp ccod/ccod@192.168.30.20:1521/ccpbs16 file=/home/oracle/osmpportal.dmp fromuser=OSMPPORTAL touser=osmpportal

參數(shù)：imp 登陸的（有相應(yīng)權(quán)限的）用戶(hù)名/密碼@oracle的ip：端口/sid file=文件目錄 fromuser=導(dǎo)出時(shí)的用戶(hù) touser=要導(dǎo)入的用戶(hù)

ignore=y有的表已經(jīng)存在會(huì)報(bào)錯(cuò)，忽略

expdp和impdp

一 關(guān)于expdp和impdp 使用EXPDP和IMPDP時(shí)應(yīng)該注意的事項(xiàng)：

EXP和IMP是客戶(hù)端工具程序，它們既可以在客戶(hù)端使用，也可以在服務(wù)端使用。

EXPDP和IMPDP是服務(wù)端的工具程序，他們只能在ORACLE服務(wù)端使用，不能在客戶(hù)端使用。

IMP只適用于EXP導(dǎo)出的文件，不適用于EXPDP導(dǎo)出文件；IMPDP只適用于EXPDP導(dǎo)出的文件，而不適用于EXP導(dǎo)出文件。

expdp或impdp命令時(shí)，可暫不指出用戶(hù)名/密碼@實(shí)例名 as 身份，然后根據(jù)提示再輸入，如：

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

原因：沒(méi)有賦予相應(yīng)權(quán)限

一：查看數(shù)據(jù)量

1.查詢(xún)是否有用戶(hù)：select * from dba_users where username='OSMPPORTAL'

2.查詢(xún)當(dāng)前用戶(hù)總數(shù)據(jù)量：select sum(t.num_rows) from user_tables t

3.查詢(xún)當(dāng)前用戶(hù)下各個(gè)表的數(shù)據(jù)量：select t.table_name,t.num_rows from user_tables t ORDER BY NUM_ROWS DESC

4.查詢(xún)表空間對(duì)應(yīng)的數(shù)據(jù)文件：select tablespace_name,file_name from dba_data_files

5.查詢(xún)表空間對(duì)應(yīng)的數(shù)據(jù)大小：select tablespace_name ,sum(bytes) / 1024 / 1024 as MB　from dba_data_files group by tablespace_name;

6.查詢(xún)數(shù)據(jù)量：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

二：新建用戶(hù)

1.新建用戶(hù)：create user username identified by pwd

注：默認(rèn)表空間：default tablespace hxzg_data;

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

3.新建的用戶(hù)也沒(méi)有任何權(quán)限，必須授予權(quán)限

grant create session to zhangsan;//授予zhangsan用戶(hù)創(chuàng)建session的權(quán)限，即登陸權(quán)限

　grant unlimited tablespace to zhangsan;//授予zhangsan用戶(hù)使用表空間的權(quán)限

　grant create table to zhangsan;//授予創(chuàng)建表的權(quán)限

　grante drop table to zhangsan;//授予刪除表的權(quán)限

　grant insert table to zhangsan;//插入表的權(quán)限

　grant update table to zhangsan;//修改表的權(quán)限

　grant all to public;//這條比較重要，授予所有權(quán)限(all)給所有用戶(hù)(public)

4.drop user username; //刪除用戶(hù)

5.賦權(quán)限：grant resource,connect to db_hxzg;

6.給其他用戶(hù)訪問(wèn)權(quán)限（db_hxzg以DBA權(quán)限登錄）

grant select any table to sun;

幾個(gè)問(wèn)題

1.在機(jī)器orcl上查看用戶(hù)默認(rèn)表空間，以便導(dǎo)入時(shí)創(chuàng)建一樣的表空間

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

2．查看用戶(hù)使用的表空間

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

3.查看表空間對(duì)應(yīng)的數(shù)據(jù)文件，以便在B上創(chuàng)建大小合適的數(shù)據(jù)文件。

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

4.檢查B機(jī)器的表空間，看是否存在work01表空間

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

查找不到，說(shuō)明沒(méi)有這個(gè)兩個(gè)表空間，需要?jiǎng)?chuàng)建

5.要導(dǎo)入數(shù)據(jù)的server沒(méi)有work01表空間,創(chuàng)建:

create tablespace work01

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

size 200m

autoextend on

next 20m

maxsize unlimited

extent management local;

6. 在要導(dǎo)入的數(shù)據(jù)庫(kù)上查找用戶(hù)是否已經(jīng)存在

select username from dba_users where username='CMS';

如果存在：

drop user cms cascade; --（刪除用戶(hù)及其擁有的所有對(duì)象）

-- 此時(shí)如果這個(gè)用戶(hù)在連接，drop會(huì)出錯(cuò)，必須先殺掉用戶(hù)的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;

--再?gòu)?fù)制這些語(yǔ)句，粘貼到sqlplus中執(zhí)行，來(lái)殺掉Test2的session。

如果不存在cms用戶(hù)：

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

不管存不存在都應(yīng)該給cms授權(quán)

grant connect,resource to cms;

7.最后將數(shù)據(jù)導(dǎo)入

下面在windows的cmd下將用戶(hù)導(dǎo)進(jìn)去

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

這里要注意的是之前我是用cms用戶(hù)將數(shù)據(jù)導(dǎo)出來(lái)的，這個(gè)cms具有dba權(quán)限，那么這里導(dǎo)入的時(shí)候用的userid后面的cms也必須具有這個(gè)權(quán)限不然會(huì)報(bào)錯(cuò)

這里我們可以臨時(shí)給cms賦予dba權(quán)限，最后回收他，但是回收之后，記得再給cms賦予resource權(quán)限NFO,USER_PROJECT_INFO) file=/home/oracle/osmp2.dmp

--11oracle如何生成awr報(bào)告

[root@localhost ~]# su - oracle

--查詢(xún)生成awr報(bào)告生成位置

[oracle@localhost ~]$ pwd;

/home/oracle

oracle安裝目錄：

--oracle_home是oracle的產(chǎn)品目錄。

[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報(bào)告生成路徑：/home/oracle/awrrpt_20200622.htlm