系统常用SQL

-- shell登陆
su - oracle
lsnrctl status
sqlplus / as sysdba
sqlplus

hspipmptestopr
paic1234

-- 执行存储过程
call PKG_IPMP_SYNC_INFO_WXL.PROC_INSERT_KC22( '', '20210701','20210801');
call PKG_IPMP_SYNC_INFO.PROC_INSERT_KC24( '', '20210101','20220101');
-- 查询各表数据量
SELECT 'SELECT * FROM ' || TABLE_NAME || ';', NUM_ROWS
FROM ALL_TABLES
WHERE OWNER = 'HSPIPMPOPR';
-- oracle 查看当前用户名
show user
select user from dual
-- oracle 查看所有用户名
select * from all_users
-- 存储过程重新编译
alter package PKG_IPMP_SYNC_INFO_WXL compile reuse settings
-- 查询表所占的空间
Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Nam
-- 创建备份目录
mkdir -p /oracle/app/oracle/oradata/dump
chown -R oracle:oinstall /oracle/app/oracle/oradata/dump
sqlplus / as sysdba
create directory dump_bak as '/oracle/app/oracle/oradata/dump';
-- 查询备份目录
select * from dba_directories;
-- 删除备份目录
drop directory BAK_DIR;
-- 数据备份-导出
expdp user_local/user_local DIRECTORY=exp_dir DUMPFILE=tab.dmp LOGFILE=tab.log TABLES=USER_OFFICE  

expdp hspipmpopr/paic1234 DIRECTORY=dump_bak DUMPFILE=v_data.dmp LOGFILE=v_data.log TABLES=V_MDCS_FUND_SETL_LIST_D,V_MDCS_FUND_LIST_OPSP_TRT_D,V_MDCS_FUND_SETL_LIST_CHRGITM,V_MDCS_FUND_SETL_LIST_OPRN_D,V_MDCS_FUND_SETL_LIST_SCS_D,V_MDCS_FUND_LIST_OPSP_TRT_D;
-- 数据备份-导入
impdb hspipmptestopr/paic1234 DIRECTORY=data_pump_dir DUMPFILE=jsd20211209.dmp;
imp hspipmptestopr/paic1234 file=/oracle/app/oracle/admin/hspipmp/dpdump/jsd20211209.dmp full=y;

/oracle/app/oracle/admin/hspipmp/dpdump/jsd20211213
imp hspipmptestopr/paic1234 file=/oracle/app/oracle/admin/hspipmp/dpdump/jsd20211213/jsd20211213.dmp full=y;


-- 数据库复制-流模式
su - postgres
psql -c"create database hspipmptest2"
pg_dump hspipmp | psql hspipmptest
pg_dump hspipmptest | psql hspipmptest2
psql
alter database hspipmptest2 OWNER TO hspipmpdata;
alter database hspipmptest2 OWNER TO hspipmpopr;

--查询session and kill
SELECT pid, pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname='hspipmptest' AND pid <> pg_backend_pid();

drop database hspipmptest;