Oracle数据库操作命令导入导出数据实用分区

最新推荐文章于 2024-04-23 11:06:16 发布

窦再兴

最新推荐文章于 2024-04-23 11:06:16 发布

阅读量530

点赞数 1

分类专栏：技术贴文章标签： oracle order

原文链接：https://www.jianshu.com/p/2c61e5648512

版权

技术贴专栏收录该内容

119 篇文章 8 订阅

订阅专栏

使用spool导出：

1).新建spool.sql文件

set colsep ,
set feedback off
set heading off
set trimout on
spool D:\DBoracle\lfc.csv
select '"' || user_name || '","' || user_age || '","' || user_card || '","' || user_sex || '","' || user_addres || '","' || user_tel || '"'  from lfc_xinxi_tbl;
spool off
exit

2).sqlplus -s 用户名/密码@数据库名 @spool.sql

set colsep' '; //-域输出分隔符 
set newp none //设置查询出来的数据分多少页显示，如果需要连续的数据，中间不要出现空行就把newp设置为none，这样输出的数据行都是连续的，中间没有空行之类的
set echo off; //显示start启动的脚本中的每个sql命令，缺省为on 
set echo on //设置运行命令是是否显示语句 
set feedback on; //设置显示“已选择XX行” 
set feedback off; //回显本次sql命令处理的记录条数，缺省为on即去掉最后的 "已经选择10000行" 
set heading off; //输出域标题，缺省为on 设置为off就去掉了select结果的字段名，只显示数据 
set pagesize 0; //输出每页行数，缺省为24,为了避免分页，可设定为0。 
set linesize 80; //输出一行字符个数，缺省为80 
set numwidth 12; //输出number类型域长度，缺省为10 
set termout off; //显示脚本中的命令的执行结果，缺省为on 
set trimout on; //去除标准输出每行的拖尾空格，缺省为off 
set trimspool on; //去除重定向（spool）输出每行的拖尾空格，缺省为off 
set serveroutput on; //设置允许显示输出类似dbms_output 
set timing on; //设置显示“已用时间：XXXX” 
set autotrace on-; //设置允许对执行的sql进行分析

查看session连接数：

select count(1) from v$session;
show parameter process
select inst_id,count(*) from gv$session group by inst_id;

Sqlplus 列太窄

调整列宽 col 列名 fromat a数值

调整行宽set linesize 数值

col name format a20

set linesize 200

导入前准备

建立导入用户

CREATE USER YYBS_IMP
IDENTIFIED BY YYBS_IMP
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
GRANT RESOURCE TO YYBS_IMP;
GRANT CONNECT TO YYBS_IMP;
GRANT IMP_FULL_DATABASE TO YYBS_IMP;
ALTER USER YYBS_IMP DEFAULT ROLE ALL;
GRANT UNLIMITED TABLESPACE TO YYBS_IMP;

确认数据库

tnsping stakfdb
export ORACLE_SID=stakfdb
sqlplus / as sysdba
select name,log_mode from v$database; --确认SID
select utl_inaddr.get_host_address from dual; --确认IP地址

杀进程

select sid,serial#,username,status,osuser,machine,terminal,program from v$session;
alter system kill session '861,21309';
强杀进程：
select spid, osuser, s.program from v$session s,v$process p where s.paddr=p.addr and s.sid=144
kill -9 spid
锁用户：
select 'alter user '||USERNAME||' account lock;' from dba_users where username like 'U%' and created>to_date('20110926','yyyymmdd') order by CREATED;
DEMO:alter user UCR_CEN1 ACCOUNT LOCK;

清库

select user_id,USERNAME,ACCOUNT_STATUS,CREATED from dba_users order by CREATED;
select 'drop user '||USERNAME||' cascade;' from dba_users where username like 'U%' and created>to_date('20110926','yyyymmdd') order by CREATED;
demo:drop user UOP_UIF2 cascade;

建立Directory

sqlplus system/oracle@STAKFDB
CREATE OR REPLACE DIRECTORY imp930sta_dir AS '/app/imp930/sta';
sqlplus system/oracle@CRMKFDB
CREATE OR REPLACE DIRECTORY imp930crm_dir AS '/app/imp930/crm';
CREATE OR REPLACE DIRECTORY imp930cen_dir AS '/app/imp930/center';
CREATE OR REPLACE DIRECTORY imp930oth_dir AS '/app/imp930/other';

导入脚本

impdp system/oracle@csngstat831 dumpfile=Usta_full.dump logfile=Usta_full.log job_name=Usta_full full=y directory=imp930sta_dir TABLE_EXISTS_ACTION=replace parallel=1
impdp system/oracle@csngstat831 dumpfile=sUCR_STA4.dump logfile=sUCR_STA4.log job_name=sUCR_STA4 schemas=UCR_STA4 directory=imp930sta_dir TABLE_EXISTS_ACTION=replace parallel=1

导入过程监控

监控主机性能

nmon
vmstat
iostat

查看导入进度

select count(0) from all_objects where CREATED > sysdate-1;
select * from tab where tname like 'CRM_FULL';

查看IMPDP进度

select * from dba_datapump_jobs;
impdp system/oracle@crmkfdb attach=UCR_CRM3
help
status
start_jo
stop_job
kill_job
parallel=4

导入后工作

重置密码

select 'alter user '||USERNAME||' identified by test123456;' from dba_users where username like 'U%' and created>to_date('20110926','yyyymmdd') order by CREATED;
alter user uif_act1_sta1 identified by test123456;

解锁用户：

alter user UCR_CEN1 ACCOUNT UNLOCK;

安全策略修改

select * from dba_profiles WHERE profile = 'DEFAULT' AND resource_type = 'PASSWORD';
alter profile DEFAULT limit password_verify_function null;
alter profile DEFAULT limit FAILED_LOGIN_ATTEMPTS UNLIMITED;
alter user XXXX profile DEFAULT;

其它

重新导入同义词

table_exists_action=skip content=metadata_only
impdp system/oracle@csngcrm831 dumpfile=cUCR_CRM3.dump logfile=cUCR_CRM3.log job_name=cUCR_CRM3 schemas=UCR_CRM3 directory=imp930crm_dir TABLE_EXISTS_ACTION=skip content=metadata_only parallel=1

重建同义词：

select 'create or replace synonym UCR_CRM3.'||synonym_name||' for UCR_CEN1.'||table_name||';'
from dba_synonyms where table_owner='UCR_CEN1' and owner='UCR_CRM4';

查看更改表空间

select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
where tablespace_name like 'TBS_CRM_DUSR3'
order by tablespace_name; --查看表空间

CREATE TABLESPACE TBS_ACT_DEF
DATAFILE '/csoradata/csngcrm/TBS_ACT_DEF.dbf' SIZE 1024M
UNIFORM SIZE 128k; --建立表空间

CREATE TABLESPACE "TBS_ACT_HIACT07" DATAFILE '/oradata/ngcrm/TBS_ACT_HIACT07.dbf' SIZE 10485760 AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO; --建立表空间2

ALTER TABLESPACE "TBS_CRM_IUSR5" ADD DATAFILE '/oradata/ngbil/crm/TBS_CRM_IUSR5_2.dbf' SIZE 10485760 AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M ; --增加表空间文件

ALTER DATABASE DATAFILE '/csoradata/csngcrm/TBS_ACT_DEF.dbf'
AUTOEXTEND ON NEXT 100M
MAXSIZE 24576M; --设定自动扩展

CREATE TEMPORARY TABLESPACE temp_data
TEMPFILE '/oracle/oradata/db/TEMP_DATA.dbf' SIZE 50M --建立临时表空间

ALTER DATABASE DATAFILE '/oradata/ngcrm/TBS_CRM_DUSR3.dbf'
RESIZE 12288M; --调表空间
ALTER DATABASE TEMPFILE '/oradata/ngcrm/temp1.dbf'
RESIZE 12288M; --调临时表空间

移动表空间：
alter tablespace TBS_ACT_DEF offline;
alter tablespace TBS_ACT_DEF rename datafile '/oradata/ngbil/crm/TBS_ACT_DEF_2.dbf' to '/oradata/ngcrm/TBS_ACT_DEF_2.dbf';
alter tablespace TBS_ACT_DEF online;
select * from dba_tablespaces where tablespace_name='TBS_ACT_DEF';
select * from dba_data_files where tablespace_name='TBS_CRM_DUSR1';

查锁

select * from v$locked_object
select * from dba_objects where object_id=286655
select * from v$session where sid=822;
alter system kill session '822,94';

oracle查询当前用户名下所有表

select * from all_tables where owner='TEST'；

TEST为用户名，用户名必须是大写。

查看当前登录的用户的表:

select table_name from user_tables;

Oracle添加索引，提高效率

查询sql执行计划

1.explain plan for "你的SQL"

2.select * from table(dbms_xplan.display());

查询表中存在的索引

1. 查询一张表里面索引

select * from user_indexes where table_name=upper('User'); 

--精细化查询
select user_ind_columns.index_name,user_ind_columns.column_name,
user_ind_columns.column_position,user_indexes.uniqueness
from user_ind_columns,user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = upper('douzi');

2. 查询被索引字段

select * from user_ind_columns where index_name=('index_name');

3. 给某一字段创建索引

create index index_name on User(name);

数据库数据大，分区解决它

思路：1.先建分区表，在建索引，最后导入老数据

demo：

1.创建分区表
create table douzi
(
   ID NUMBER(20) not null,
   REMARK VARCHAR2(1000),
   create_time DATE
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'month'))
(partition part_t01 values less than(to_date('2018-11-01', 'yyyy-mm-dd')));

2.创建索引列
create index id_index on douzi(id);
--或者创建主键
alter table douzi add constraint pk_id_index primary key (ID) using INDEX;

3.插入数据
insert into douzi VALUES(1,'test partition',SYSDATE);
insert into douzi values(2, 'test partition', TO_DATE('2020-03-01 00:00:00','yyyy-MM-dd HH24:mi:ss'));
insert into douzi values(3, 'test partition', TO_DATE('2020-04-01 00:00:00','yyyy-MM-dd HH24:mi:ss'));

4.查询所有表分区 可以添加 表名where
SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME
FROM USER_TAB_PARTITIONS
where table_name = 'douzi';

5.查询多个分区的数据列表
select * from douzi partition(SYS_P22)
union 
select * from douzi partition(SYS_P21);

6.注意事项
  1>.正规使用，如有需要，可配置分区在不同表空间，不同磁盘上；自动分区可能不太适合。
  2>.自动分区是有上限:一个表或索引是1024*1024-1个分区；

7.删除数据 用于清除老数据
--删除一个表的数据是 
truncate table douzi;
--删除分区表一个分区的数据是 
alter table douzi truncate partition SYS_P21;
--删除分区
alter table douzi drop partition SYS_P21 UPDATE GLOBAL INDEXES;