oracle创建表空间
来源:互联网 发布:数淘宝指数 编辑:程序博客网 时间:2024/06/06 14:11
1、查询ORACLE数据库名:
SQL> select name from v$database
2、查看实例名:
SQL> select instance_name from v$instance
3、创建”临时表空间“,不创建则默认临时表空间为temp:
SQL>create temporary tablespace cuico_temp
tempfile 'F:\Oracle\oradata\cuico_temp.dbf'
size 50M
autoextend on
next 50M maxsize 20480m
extent management local;
4、创建数据表空间
create tablespace cuico_data
logging
datafile 'F:\Oracle\oradata\cuico_data.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
5、创建用户表并指定表空间
create user cuico identified by cuico
default tablespace cuico_data
temporary tablespace cuico_temp;
6、给用户授予权限
grant connect,resource,dba to cuico;
测试
create tablespace cuitest_data
logging
datafile 'F:\Oracle\oradata\cuitest_data.dbf'
size 50m
autoextend on
next 50m maxsize unlimited
extent management local;
7、在cmd非sqlplus中输入命令
imp cuico/cuico@CO fromuser=lx touser=cuico file=e:\erpt.dmp
注:这三种模式是互斥的,只能写一种,你可以去掉fromuser=ERPDB
touser=ERP 或者去掉full=y
8、清数据
set pagesize 0
set echo off
spool d:\trunc.sql
select 'truncate table '||table_name from user_tables;
spool off
9、删除表
set pagesize 0
set echo off
spool d:\trunc.sql
select 'drop table '||table_name from user_tables;
spool off
SQL> select name from v$database
2、查看实例名:
SQL> select instance_name from v$instance
3、创建”临时表空间“,不创建则默认临时表空间为temp:
SQL>create temporary tablespace cuico_temp
tempfile 'F:\Oracle\oradata\cuico_temp.dbf'
size 50M
autoextend on
next 50M maxsize 20480m
extent management local;
4、创建数据表空间
create tablespace cuico_data
logging
datafile 'F:\Oracle\oradata\cuico_data.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
5、创建用户表并指定表空间
create user cuico identified by cuico
default tablespace cuico_data
temporary tablespace cuico_temp;
6、给用户授予权限
grant connect,resource,dba to cuico;
测试
create tablespace cuitest_data
logging
datafile 'F:\Oracle\oradata\cuitest_data.dbf'
size 50m
autoextend on
next 50m maxsize unlimited
extent management local;
7、在cmd非sqlplus中输入命令
imp cuico/cuico@CO fromuser=lx touser=cuico file=e:\erpt.dmp
注:这三种模式是互斥的,只能写一种,你可以去掉fromuser=ERPDB
touser=ERP 或者去掉full=y
8、清数据
set pagesize 0
set echo off
spool d:\trunc.sql
select 'truncate table '||table_name from user_tables;
spool off
9、删除表
set pagesize 0
set echo off
spool d:\trunc.sql
select 'drop table '||table_name from user_tables;
spool off
阅读全文