建一个和表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;
如果想导入的用户已经存在:
- 导出用户 expdp user1/pass1 directory=dumpdir dumpfile=user1.dmp
expdp zbxt/zbxt@shitan schemas=zbxt dumpfile=ZBXT.dmp directory=zbxt logfile=ZBXT.log - 导入用户 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
如果想导入的用户不存在: - 导出用户 expdp user1/pass1 directory=dumpdir dumpfile=user1.dmp
- 导入用户 impdp system/passsystem directory=dumpdir dumpfile=user1.dmp REMAP_SCHEMA=user1:user2
- 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 V A I L A , S Y S . S M TS_AVAIL A,SYS.SM TSAVAILA,SYS.SMTS_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 的记录;
- 执行以下杀死进程的sql(下面的两个参数是status 为 INACTIVE 时的记录):
alter system kill session ‘sid,serial#’;
- 执行删除用户操作
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 )