数据库建表语句 导入导出 等操作

建一个和表XX类似的表
create table xm_bxqkb as select * from xm_ywjyb

exp 用户名/密码@ip/实例名 file=E:/20191122zjjgserver.dmp log=E:/20191122zjjg.log
例
exp zjjgclient/zjjgclient@192.168.10.60/shitan file=E:/20191122zjjgclient.dmp log=E:/20191122zjjg1.log

imp zc97/zc97@192.168.10.60/shitan file=E:/jiuqi.dmp full=y ignore=y log=E:/jiuqi.log
imp zjjgclient1/zjjgclient1@192.168.10.60/shitan file=E:/20191122zjjgclient.dmp full=y ignore=y log=E:/20191122zjjg1.log

exp zjjgserver/zjjgserver@192.168.10.60/shitan file=E:/20191122zjjgserver.dmp log=E:/20191122zjjg.log

exp zjjgclient/zjjgclient@192.168.10.60/shitan file=E:/20191122zjjgclient.dmp log=E:/20191122zjjg1.log

imp zc97/zc97@192.168.10.60/shitan file=E:/jiuqi.dmp full=y ignore=y log=E:/jiuqi.log
imp zjjgclient1/zjjgclient1@192.168.10.60/shitan file=E:/20191122zjjgclient.dmp full=y ignore=y log=E:/20191122zjjg1.log

--------创建表空间
create tablespace RZPT logging datafile
‘F:\Oracle\oradata\orcl\RZPT.dbf’
size 1024m autoextend on next 100m maxsize
unlimited extent management local segment space management auto;
--------创建用户
create user DC
default tablespace RZPT
identified by DC123;
--------授权
grant dba to DC
---------------导出数据
exp DC/DC123@192.168.11.250/shitan file=D:\DC.dmp

expdp zbxt/zbxt@127.0.0.1/shitan directory=zbxt dumpfile=ZBXT.DMP full=y logfile=ZBXT.log;
---------------导入数据
imp JQ/JQ@192.168.11.52:1521/shitan file=F:\JQZC19.dmp full=y ignore=y;

impdp FURONG/FURONG@192.168.11.52:1521/shitan directory=data_pump_dir REMAP_SCHEMA=scott:FURONG dumpfile=hnzc20190322.dmp logfile=hnzc20190322.log

create or replace directory PATH as ‘C:\Users\Administrator.DESKTOP-HIAGIQ3\Documents\Tencent Files\1335959960\FileRecv\dpdump’;

impdp xzzc/xzzc123@ORCL full=Y directory=PATH dumpfile=XKYY.dmp
–查看逻辑目录
select * from dba_directories
–创建逻辑目录（等效于dpdump）
create directory jyzc as ‘E:\jyzc’;
–赋权
grant read,write on directory jyzc to jyzc;
如果想导入的用户已经存在:

1. 导出用户 expdp user1/pass1 directory=dumpdir dumpfile=user1.dmp
   expdp zbxt/zbxt@shitan schemas=zbxt dumpfile=ZBXT.dmp directory=zbxt logfile=ZBXT.log
2. 导入用户 impdp user2/pass2 directory=dumpdir dumpfile=user1.dmp REMAP_SCHEMA=user1:user2 EXCLUDE=USER
   impdp zxxjyzc/zxxjyzc@shitan directory=jyzc REMAP_SCHEMA=scott:zxxjyzc dumpfile=JYZC.DMP logfile=JYZC.log
   如果想导入的用户不存在:
3. 导出用户 expdp user1/pass1 directory=dumpdir dumpfile=user1.dmp
4. 导入用户 impdp system/passsystem directory=dumpdir dumpfile=user1.dmp REMAP_SCHEMA=user1:user2
5. user2会自动建立，其权限和使用的表空间与user1相同，但此时用user2无法登录，必须修改user2的密码

2.手动增加数据文件尺寸

ALTER DATABASE DATAFILE ‘/oracle/oradata/db/GAME.dbf’

RESIZE 4000M;

3.设定数据文件自动扩展

ALTER DATABASE DATAFILE ‘F:/hengshui/ASSETS.dbf’

AUTOEXTEND ON NEXT 100M

MAXSIZE 10000M;

设定后查看表空间信息

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,

(B.BYTES100)/A.BYTES “% USED”,(C.BYTES100)/A.BYTES “% FREE”

FROM SYS.SM$T{S}_{A}VAILA,SYS.SM$TS_USED B,SYS.SM$TS_FREE C

WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE

F:\Oracle\product\11.2.0\dbhome_2\bin;

1、以system用户登录查找需要删除的用户（普通用户没有删除权限）

select * from dba_users;

2、查询需要删除用户对应的表空间

select * from dba_data_files;

1.首先将索要删除的用户锁定（这句必须执行）：

 alter user 用户名 account lock;

2.查看当前用户占用资源：

 select saddr,sid,serial#,paddr,username,status from v$session where username = '用户名';

然后可以看到status 为 INACTIVE 的记录；

3. 执行以下杀死进程的sql（下面的两个参数是status 为 INACTIVE 时的记录）：

alter system kill session ‘sid,serial#’;

4. 执行删除用户操作

drop user 用户名 cascade;

3、删除用户和表空间

drop user username cascade;

drop tablespace?tablespacename?including contents and datafiles cascade constraint;

exp DC/DC123@192.168.10.1/shitan file=D:\ZCNB.dmp INDEXES=n STATISTICS=none TRIGGERS=n

exp xzzc/xzzc@192.168.10.1/shitan file=D:\xzzc.dmp direct=y recordlength=5000 buffer=10485760

–1.创建assest表空间 路径需要自己修改，建议放在 数据库路径\app\Administrator\oradata\实例名目录下
create tablespace ASSETS logging datafile
‘D:\Oracle11g\ASSETS.dbf’ size 5000m autoextend on next 500m maxsize
unlimited extent management local segment space management auto;

alter tablespace ASSETS add datafile ‘D:\Oracle11g\ASSETS_jjkfq2.dbf’ size 5000M autoextend on next 500M;
alter tablespace ASSETS add datafile ‘D:\Oracle11g\ASSETS_jjkfq2.dbf’ size 5000M autoextend on next 500M;
alter tablespace ASSETS add datafile ‘D:\Oracle11g\ASSETS_jjkfq2.dbf’ size 5000M autoextend on next 500M;
alter tablespace ASSETS add datafile ‘D:\Oracle11g\ASSETS_jjkfq2.dbf’ size 5000M autoextend on next 500M;
alter tablespace ASSETS add datafile ‘D:\Oracle11g\ASSETS_jjkfq2.dbf’ size 5000M autoextend on next 500M;
alter tablespace ASSETS add datafile ‘D:\Oracle11g\ASSETS_jjkfq2.dbf’ size 5000M autoextend on next 500M;

查看表空间文件路径、大小、已使用、使用率

SELECT

B.FILE_NAME 物理文件名,

B.TABLESPACE_NAME 表空间名称,

B.BYTES/1024/1024 大小M,

(B.BYTES-SUM(NVL(A.BYTES,0)))/1024/1024 已使用M,

SUBSTR((B.BYTES-SUM(NVL(A.BYTES,0)))/(B.BYTES)*100,1,5) 使用率

FROM DBA_FREE_SPACE A,DBA_DATA_FILES B

WHERE A.FILE_ID=B.FILE_ID

GROUP BY B.TABLESPACE_NAME,B.FILE_NAME,B.BYTES

ORDER BY B.TABLESPACE_NAME;

查看表空间是否开启自动扩展

SELECT FILE_NAME AS 数据文件,TABLESPACE_NAME AS 表空间名称,AUTOEXTENSIBLE AS 自动扩展,STATUS AS 状态,MAXBYTES AS 可扩展最大值,USER_BYTES AS 已使用大小,INCREMENT_BY AS 自动扩展增量 FROM DBA_DATA_FILES;

创建表空间并开启自动扩展

CREATE SMALLFILE TABLESPACE TABLE_SPACE_NAME DATAFILE ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST.DBF’ SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED NOLOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT NOCOMPRESS;

修改表空间大小

ALTER DATABASE DATAFILE ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST.DBF’ RESIZE 200M;

删除表空间和数据文件

DROP TABLESPACE TABLE_SPACE_NAME INCLUDING CONTENTS AND DATAFILES;

查看临时表空间信息

SELECT * FROM DBA_TEMP_FILES

为表空间添加文件：
alter tablespace ASSETS add datafile ‘F:\Oracle11g_tablespaceFile\oradata\shitan\ASSETS9.DBF’ size 30G reuse autoextend on next 100m;

create tablespace ASSETS logging datafile
‘D:\app\Administrator\admin\shitan\ASSETS.dbf’
size 2048m autoextend on next 100m maxsize
unlimited extent management local segment space management auto;

create user zbxt
default tablespace ASSETS
identified by zbxt;

grant dba to zbxt
create directory zbxt as ‘D:\app\Administrator\admin\shitan\dpdump’;
drop directory zbxt

impdp zbxt/zbxt@shitan directory=zbxt REMAP_SCHEMA=xzzc:zbxt dumpfile=XZZC1017.DMP logfile=xzzc1017.log

ALTER DATABASE DATAFILE ‘D:/app/Administrator/admin/shitan/ASSETS.dbf’ RESIZE 2000M;

alter database datafile ‘D:\app\Administrator\admin\shitan\ASSETS.DBF’ resize 2048m;

alter database datafile ‘D:\app\Administrator\admin\shitan\ASSETS.DBF’ autoextend on;

alter tablespace ASSETS add datafile ‘D:\app\Administrator\admin\shitan\ASSETS1.DBF’ size 10G reuse autoextend on next 100m;

-修改 数据库 精度问题
create table JFBFQKB1_hcl as select * from JFBFQKB1;
–3、删除 JFBFQKB1_hcl表内的数据
delete from JFBFQKB;
–4、修改表结构
alter table JFBFQKB modify jfze NUMBER(18,4);
alter table JFBFQKB modify xx_bz NUMBER(18,4);
alter table JFBFQKB modify xx_bz_js NUMBER(18,4);
alter table JFBFQKB modify xx_bz_sb NUMBER(18,4);
alter table JFBFQKB modify xx_je NUMBER(18,4);
alter table JFBFQKB modify xx_je_js NUMBER(18,4);
alter table JFBFQKB modify xx_je_sb NUMBER(18,4);
alter table JFBFQKB modify xx_jfqk1_jf NUMBER(18,4);
alter table JFBFQKB modify xx_jfqk2_jf NUMBER(18,4);
alter table JFBFQKB modify xx_mb_bz NUMBER(18,4);
alter table JFBFQKB modify xx_mb_je NUMBER(18,4);
alter table JFBFQKB modify cz_bz NUMBER(18,4);
alter table JFBFQKB modify cz_bz_js NUMBER(18,4);
–5、还原表结构
insert into JFBFQKB select * from JFBFQKB1_hcl;

drop table JFBFQKB1_hcl;

-修改增加字段
alter 表名 add 字段名 数据类型 default ‘默认值’；

alter table XM_YSFPB add plfpgid VARCHAR2(1000) default ‘0’;
Lpad左补0 rpad
lpad(字段名，共几位，补什么)

merge into
merge into AC_SYS_JSRYB a

using (
select ‘123456789’ as saasdm,
guid as rybh,
xm as rymc,
‘中小学闲置校舍情况’ as jsmc,
‘000000’ as jdr,
‘0E029ED1DA914016B1EB73A92B9D78DC’ as jsbh,
rybh as rygh
from ryb
where length(dwbh) <= 6
AND (dwbh = ‘370102’ OR dwbh = substr(‘370102’, 0, 4))
)b

WHEN NOT MATCHED THEN

INSERT values ( b.rybh,b.jsbh,b.saasdm,b.rygh, b.rymc, b.jsmc, b.jdr,sysdate )