oracle怎么建库表,Oracle 建库、建表、赋权限

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 --导出当前命名空间下表结构和数据 --