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

傷城~ 2023-06-08 10:53 5阅读 0赞

# 使用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查询当前用户名下所有表][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;

[oracle]: https://www.cnblogs.com/qlqwjy/p/8405478.html