oracle 数据库
启动
通过DBA权限登录到oracle数据库
startup nomount (nomount模式)启动实例不加载数据库。
startup mount (mount模式)启动实例加载数据库但不打开数据库。
startup (open 模式)启动实例加载并打开数据库,就是我们上面所用的命令
停止
shutdown immediate 立即关闭数据库。
shutdown transactional 关闭数据库事物。
shutdown abort 强制关闭。
创建表空间
create tablespace 表空间名 datafile '绝对路径' size *M (表空间大小) tempfile '绝对路径(临时表空间)' autoextend on (自动增长) next *M maxsize *M;
示例:
create tablespace test datafile 'E:\app\oracle\product\11.2.0\dbhome_1\oradata\test_001.dbf' size 500M;
删除表空间并删除文件
drop tablespace test including contents and datafiles;
创建用户并指定表空间
create user 用户名 identified by 密码 default tablespace 表空间名;
示例:
create user testdb identified by testdb default tablespace test;
删除用户未删除表空间
drop user test cascade;
授权
grant connect to testdb;
grant resource to testdb;
grant create any sequence to testdb;
grant create any table to testdb;
grant delete any table to testdb;
grant insert any table to testdb;
grant select any table to testdb;
grant unlimited tablespace to testdb;
grant execute any procedure to testdb;
grant update any table to testdb;
grant create user to testdb;
grant drop user to testdb;
grant alter user to testdb;
grant create any view to testdb;
grant drop any view to testdb
grant exp_full_database to testdb;
grant imp_full_database to testdb;
grant create session to testdb;
撤销权限只需要将 grant 改成revoke
导入需要有导入权限
表存在的导入,如果表不存在可将ignore=y去掉
imp testdb/testdb@testdb full=y file=E:\app\dump\sql.dump ignore=y
指定表导入
imp testdb/testdb@testdb file=E:\app\dump\sql.dump tables=表名1,表名2
导出
完全导出
exp testdb/testdb@testdb file=E:\app\dump\sql.dump full=y
指定表导出表名用逗号隔开
exp testdb/testdb@testdb file=E:\app\dump\sql.dump tables=表名1,表名2