Oracle 表空间和用户的维护（创建、删除、修改、导出）

1.查看表空间

select * from dba_tablespaces

2.查找物理文件

select * from dba_data_files

3.查看表空间里存放了哪些用户的数据

select distinct owner from dba_segments where tablespace_name='MY_001'

4.查看用户

select * from dba_users

5.查看用户默认的表空间

select default_tablespace from dba_users where username='LG'

6.查找用户下面所有的表

select * from dba_tables  where owner='LGL'

7.删除用户，及级联关系也删除

drop user user_name cascade

8.删除表空间，对应的表空间文件也删除

drop tablespace tablespace_name including contents and datafiles cascade constraint

9.创建表空间

（1）create tablespace ****

datafile 'd:\app\oracle12c\oradata\orcl\my_0003.dbf'

size 1024m

（2）create tablespace sfsgisj logging

datafile 'd:\app\oracle12c\oradata\orcl\my_0003.dbf'' size 50m

autoextend on

next 50m maxsize 20480m extent management local;

10.修改表空间

 ----增加物理文件

alter tablespace *** add datafile

    'd:\app\oracle12c\oradata\orcl\my_0005.dbf' size 128m

 ----修改aa表空间物理文件路径：

   --windows   

1、修改表空间为offline：
SQL> alter tablespace aa offline;
2、window下创建d:\database\a01.dbf文件(可以通过创建表空间，指定路径为d:\database\a01.dbf，再删除表空间，不删除物理文件)
3、修改表空间存储路径
SQL> alter tablespace users rename datafile 'd:\database\a.dbf' to 'd:\database\a01.dbf';
4、修改表空间为online
SQL> alter tablespace users online;

-- linux

1、修改表空间为offline：
SQL> alter tablespace aa offline;
2、拷贝
[oracle@rac1 ~]$ cp /database/a.dbf /database/a01.dbf
3、修改表空间存储路径
SQL> alter tablespace users rename datafile '/database/a.dbf' to '/database/a01.dbf';
4、修改表空间为online
SQL> alter tablespace users online;

11.创建用户及设置默认表空

---创建用户

CREATE USER GZZL_LS IDENTIFIED BY password

DEFAULT TABLESPACE "GZZL"

TEMPORARY TABLESPACE "TEMP"

QUOTA UNLIMITED ON "GZZL"; ---设置用户无限制使用这个表空间配额

12、分配用户权限

grant connect, resource,dba to plan_sz;

13.查看某个表的占用物理空间大小

select segment_name AS TABLENAME,bytes b,bytes / 1024 kb,bytes /1024 /1024 mb,bytes /1024 /1024 /1024 GB from user_segments

where segment_name = upper('D_DZ')

14.jdbc连接数据库的时候，需要使用数据库的sid_name，而不是数据库的services_name

而使用plsql连接数据库的时候，只需要数据库的services_name即可

查看数据库的sid_name语句

select INSTANCE_NAME from v$instance;

15、查询表对应的表空间

SELECT TABLE_NAME,TABLESPACE_NAME FROM USER_TABLES

16、修改表对应的表空间

alter table MAP_BOOKMARK move tablespace PLAN_DATA;

17、查询拼接的修改表对应的表空间 语句，查询出来后，复制所有数据执行

select 'alter table '|| table_name ||' move tablespace tablespacename;' from user_tables;
18、修改用户名和密码

sys用户或dba权限用户登录
1. 从Oracle用户信息表查找用户序列号
select user#,name from user$ where name = '需要修改的用户名';
2. 根据序列号直接修改用户信息表
update user$ set name = '新用户名' where user# = 之前查的那个序列号;
3、提交
commit;
4、如果不生效，尝试强制更新：
alter system checkpoint;
alter system flush shared_pool;
5、修改用户密码
ALTER USER PORTAL IDENTIFIED BY 123;

19、数据导出

exp 用户名/密码@ip/orcl file=E:\XX.dmp owner=用户名

exp afanti/"""afanti@hust"""@moe file=d:\110117.dmp owner=用户名

空表导出处理：select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;

将查出的数据运行，可以导出包含空表的所有数据（不要在Navicat中执行，Navicat中查询结果包含已经删除的表，建议在pl/sql中执行）

20、数据导入

imp 用户名/密码@ip/orcl file=E:\XX.dmp full=y

21、查询包含某个字段的所有表

select TABLE_NAME , COLUMN_NAME,DATA_TYPE from USER_TAB_COLS where COLUMN_NAME = 'XX';

22、oracle安装目录下dmp导入导出

expdp plan/"""23$%^&*("""@ip/orcl directory=DATA_PUMP_DIR dumpfile=plan_dp_20200426.dmp schemas=plan (密码有特殊字符）

impdp plan/plan@ip/orcl directory=DATA_PUMP_DIR dumpfile=plan_dp_20200426.dmp  remap_schema=plan:planhn_dev

23、用户解锁：alter user cms account unlock;