整合營銷服務商
電腦端+手機端+微信端=數據同步管理
免費咨詢熱線：
電腦端+手機端+微信端=數據同步管理
免費咨詢熱線：
者：宗楊
愛可生產品交付團隊成員，主要負責公司運維平臺和數據庫運維故障診斷。喜愛數據庫、容器等技術，愛好歷史、追劇。
本文來源：原創投稿
*愛可生開源社區出品，原創內容未經授權不得隨意使用，轉載請聯系小編并注明來源。
我們的合作客戶，駐場人員報告說一個 RDS 實例出現磁盤不足的告警，需要排查。
告警信息：
告警內容：
數據庫 data 磁盤不足，磁盤占用 80% 以上
數據庫 binlog 磁盤不足，磁盤占用 80% 以上
登陸告警的服務器，查看磁盤空間，并尋找大容量文件后，發現端口號為 4675 的實例臨時表空間 ibtmp1 的大小有 955G，導致磁盤被使用了 86%；
猜測和庫里執行長 SQL 有關系，產生了很多臨時數據，并寫入到臨時表空間。
看到有這樣一條 SQL，繼續分析它的執行計劃；
很明顯看到圖中標記的這一點為使用了臨時計算，說明臨時表空間的快速增長和它有關系。這條 SQL 進行了三表關聯，每個表都有幾十萬行數據，三表關聯并沒有在 where 條件中設置關聯字段，形成了笛卡爾積，所以會產生大量臨時數據；而且都是全表掃描，加載的臨時數據過多;還涉及到排序產生了臨時數據；這幾方面導致 ibtmp1 空間快速爆滿。
和項目組溝通后，殺掉這個會話解決問題；
但是這個 SQL 停下來了，臨時表空間中的臨時數據沒有釋放；
最后通過重啟 mysql 數據庫，釋放了臨時表空間中的臨時數據，這個只能通過重啟釋放。
通過查看官方文檔，官方是這么解釋的：
翻譯：
根據官網文檔的解釋，在正常關閉或初始化中止時，將刪除臨時表空間，并在每次啟動服務器時重新創建。重啟能夠釋放空間的原因在于正常關閉數據庫，臨時表空間就被刪除了，重新啟動后重新創建，也就是重啟引發了臨時表空間的重建，重新初始化，所以，重建后的大小為 12M。
從錯誤日志里可以驗證上面的觀點：
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
*請認真填寫需求信息，我們會在24小時內與您取得聯系。
