1、Oracle建库
--创建数据表空间--
create tablespace TEST
logging
datafile 'F:\app\zt\oradata\orcl\TEST.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
--删除创建表空间及其文件--
drop tablespace SDE_TEST including contents and datafiles cascade constraint;
--创建多个数据库文件--
create tablespace TEST
logging
datafile 'F:\app\zt\oradata\orcl\TEST1.dbf'
size 50m
autoextend on
next 50m maxsize 20480m,
'F:\app\zt\oradata\orcl\TEST2.dbf'
size 50m
autoextend on
next 50m maxsize 20480m,
'F:\app\zt\oradata\orcl\TEST3.dbf'
size 50m
autoextend on
next 50m maxsize 20480m,
'F:\app\zt\oradata\orcl\TEST4.dbf'
size 50m
autoextend on
next 50m maxsize 20480m,
'F:\app\zt\oradata\orcl\TEST5.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
--增加一个数据库文件--
ALTER TABLESPACE "TEST" ADD DATAFILE 'F:\app\zt\oradata\orcl\TEST2.DBF' SIZE 2G AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;
2、Oracle建用户
--创建用户--
create user SDE_TEST identified by SDE_TEST
default tablespace TEST
temporary tablespace temp;
--修改密码--
alter user SDE_TEST identified by tf;
--删除用户--
select username,sid,serial# from v$session; --查看用户的连接状态
alter system kill session '74,91'; --找到要删除用户的sid和serial并杀死
drop user SDE_TEST cascade; --删除用户，及级联关系也删除掉
--删除用户异常--
Oracle ORA-01940 无法删除当前已连接用户
3、Oracle赋权限
grant connect,resource,dba to SDE_TEST;
4、cmd 执行 Sql语句
4.1 进入命令行
sqlplus / as sysdba
4.2 SDE_TEST /SDE_TEST 登录
conn SDE_TEST/SDE_TEST;
4.3导入SQL语句,导入创建好的表
@G:\20190321.sql
5、导出dmp文件，导入dmp文件数据
5.1 导出指定表
exp SDE_TEST/SDE_TEST@localhost/orcl file=c:\temp\exp20190625.dmp tables=(tb_sys_log,tb_build)
5.2导入所有表
imp SDE_TEST/SDE_TEST@localhost/orcl file=E:\20190321.dmp full=y
5.2只导入表数据
imp SDE_TEST/SDE_TEST@localhost/orcl file=E:\20190321.dmp data_only=y
6、快速清除当前用户下的所有表数据
SELECT 'DELETE FROM '|| table_name || ';' FROM USER_TABLES ORDER BY TABLE_NAME;
7、导入批量数据
imp SDE_TEST/SDE_TEST@localhost/orcl file=E:\20190321.dmp full=y data_only=y commit=y feedback=10000 buffer=10240000
8、expdp / impdp 命令批量导入导出
expdp命令：expdp SDE_TEST/SDE_TEST@localhost/orcl schemas=SDE_TEST DIRECTORY=tmpdir DUMPFILE=SDE_TEST_expdp_20190402.dmp logfile=SDE_TEST_expdp_20190402.log --导出当前命名空间下表结构和数据 --
文章评论