一、用户
查询当前用户
SQL> show user
USER is "TEST"
查询当前用户下所有表
SQL> select table_name from tabs;
SQL> select table_name from user_tables;
SQL> select * from user_tables;
//以上任意一种皆可
查询当前用户的默认表空间
select username,default_tablespace from user_users;
授予test用户resource权限
grant connect,resource to test;
查询当前用户的角色权限
select * from user_role_privs;
查询当前用户的系统权限和表级权限
select * from user_sys_privs;
select * from user_tab_privs;
查询指定用户所具有的系统权限
select * from dba_sys_privs where grantee='TEST1';
// TEST1为指定用户名
删除当前用户下所有表空间
先登录到该用户下
drop user user_name cascade;
//加了cascade就可以把用户连带的数据全部删掉。
查看名称包含test字符的表
select object_name,object_id from user_objects where instr(object_name,'LOG')>0;
————————————————
二、表
创建表空间
create tablespace BKJ datafile '/u01/app/oracle/oradata/orcl/bkj.dbf' size 1024M;
删除表空间
drop tablespace BKJ including contents and datafiles cascade constraint;
//BKJ为表空间名
查看指定表创建时间
select object_name,created from user_objects where object_name=upper('table_name');
查看指定表大小
select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('table_name');
创建用户并设置临时表空间
create user 用户名 identified by 密码 default tablespace space_data(表空间名称) temporary tablespace space_temp(临时表空间名称);
三、导出
指定导出绝对路径
登录sysdba;
create directory exp as '/home/oracle';
//exp为导出名称
grant read,write on directory webexp to test;
//赋予用户test导出目录的读写权限
expdp test/test123 directory=webexp dumpfile=test.dmp SCHEMAS=test
//test/test123 导出表的账号密码,directory=webexp,导出目录名称,dumpfile=test.dmp,自定义数据名称
三、导入
登录sysdb;
select f.tablespace_name, round(sum(f.bytes)/1024/1024,2) "tblspc size" from dba_data_files f group by f.tablespace_name;
select f.tablespace_name, round(sum(f.bytes)/1024/1024,2) "free space" from dba_free_space f group by f.tablespace_name;
drop tablespace TEST including contents and datafiles cascade constraint;
// 删除TEST表空间
drop user TEST cascade;
//删除GLJCY用户和用户所关联的数据全部删除
create tablespace test01 datafile '/u01/app/oracle/oradata/orcl/test01.dbf' size 1024M;
//建立test01表空间,注意确定你的orcl路径位置
create user test01 identified by test2009 default tablespace test temporary tablespace temp;
//创建用户并设置默认表空间
grant connect,resource to test01; //授予dba权限给resource
create directory webexp as '/home/oracle';
//指定导入绝对路径
grant read,write on directory directory_name to test01;
//赋予用户导入导出权限
impdp system/Passw0rd directory=directory_name dumpfile=test01.dmp schemas=test01
//导入库