【迁移】单实例环境使用数据泵（Data Pump）数据迁移

--查看监听状态
lsnrctl status
--关闭监听程序
lsnrctl stop
--检查监听状态
lsnrctl status

select username from dba_users where username not in('SYS','SYSTEM','SCOTT','OUTLN','MGMT_VIEW','FLOWS_FILES','MDSYS','ORDSYS','EXFSYS','DBSNMP','WMSYS','APPQOSSYS','APEX_030200','OWBSYS_AUDIT','ORDDATA','CTXSYS','ANONYMOUS','SYSMAN','XDB','ORDPLUGINS','OWBSYS','SI_INFORMTN_SCHEMA','OLAPSYS','ORACLE_OCM','XS$NULL','MDDATA','DIP','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR');

--查询status 为 INACTIVE的会话
select SID,SERIAL#,username,status from v$session where username in ('用户名1'，'用户名2')；
--杀死进程
alter system kill session 'sid,serial#';

--查询avtive状态日志组
select * from v$log;
--切换日志组，直达avtive变为inactive，可多切换几次
alter system switch logfile;
--注：此处无需做手工CheckPoint

select userenv('language') from dual;

select * from dba_directories;

--创建目录
create or replace directory mydump as '/u01/temp';
--授权
grant read, write on directory mydump to 用户名;

--方式一：通过block大小去估算，默认
expdp \'/ as sysdba\' ESTIMATE_ONLY=y schemas=用户名 ESTIMATE=blocks;
--方式二：通过统计信息去估算
expdp \'/ as sysdba\' ESTIMATE_ONLY=y schemas=用户名 ESTIMATE=statistics;

--注意parallel参数需要考虑CPU核心数，另可使用filesize参数设置导出单个文件大小
expdp \"/ as sysdba\" directory=mydump dumpfile=YKT-FULL_%U.DMP parallel=4 logfile=YKT-FULL.log full=y;

--CASCADE参数会删除用户所有关联对象
DROP USER 用户名 CASCADE;

--在源库查询，在目标库create tablespace
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size 
FROM dba_tablespaces t, dba_data_files d 
WHERE t.tablespace_name = d.tablespace_name 
GROUP BY t.tablespace_name; 

impdp \"/ as sysdba\" directory=ENMO_DUMPDIR dumpfile=YKT-FULL_%U.DMP parallel=4 logfile=impdp_xk.log schemas=用户名;

select count(1) from dba_objects;

--查询数据库所有的表
select t.table_name,t.num_rows from all_tables t；
--查询当前用户表
select t.table_name,t.num_rows from user_tables t；

alter database datafile 'XXXXXXX' resize 10G;

ORA-39082: Object type PACKAGE_BODY:"XXXX"."PXG_TS_LEAING" created with compilation warnings

select owner,object_name,object_type,status from dba_objects where object_name='PXG_TS_LEAING';

select * from dba_jobs
select * from dba_jobs_running

exec DBMS_JOB.broken(49,TRUE);

show parameter job
alter system set job_queue_processes=0;