Oracle新增删除用户和表空间cmd的SQL代码

---

一、创建表空间

--电脑本地连接oracle的cmd语句
sqlplus SYS/SYSTEM@orcl as sysdba
--或
sqlplus /@orcl as sysdba
--或
sqlplus /nolog
conn /as sysdba

--远程
sqlplus SYS/SYSTEM@192.168.8.88/orcl as sysdba

--创建表空间(数据文件可指定地址如:D:\data\test.dbf,初始大小和自增可根据情况适当调节)
create tablespace test datafile 'test.dbf' size 500M autoextend on next 50M maxsize unlimited;

--检查是否已存在表空间
select * from all_users;
SELECT tablespace_name,file_name from dba_data_files;

--创建用户，分配表空间（最好和表空间名一致）
create user test identified by test default tablespace test temporary tablespace temp;

--给用户赋权
GRANT CONNECT, RESOURCE, DBA TO test;

二、数据库导入导出

1.导入imp

imp test/test@orcl file=E:\qms207.dmp  FULL=Y,ignore=y;

2.导入impdp

--搜索路径
select * from dba_directories

--找不到路径可以先创建一个路径
create directory mypath as 'E:\mypath'; 

--导入语句directory=DATA_PUMP_DIR这个是数据库里面的变量，它对应了一个路径
--remap_schema=老的表空间:新表空间
impdp test/test@orcl remap_schema=test:test table_exists_action=replace directory=DATA_PUMP_DIR dumpfile=TEST.DMP logfile=impdp.log

3.导出exp

--本地(加入full防止空表问题)
exp test/test@orcl file=E:\test.dmp full=y

--远程
exp test/test@192.168.8.88/orcl file=E:\test.dmp full=y

4.导出expdp

--导出文件到DATA_PUMP_DIR路径下
expdp test/test@orcl schemas=test dumpfile=TEST.DMP directory=DATA_PUMP_DIR logfile=expdp.log

三、导入导出问题处理

1.ORA-01940：无法删除当前连接的用户

select username,sid,serial# from v$session;
--找到对应的用户名的进程号
alter system kill session'***,***';

--删除此用户名下的所有表和视图
drop user qmssun cascade;
--删除用户
drop user qmssun;
--删除表空间
drop tablespace qmssun including contents and datafiles;
--改变
alter database datafile 'qms711.DBF' offline drop;

--解锁用户
alter user xm80501 account unlock;

--获取oracle表空间在电脑中的创建地址
select t1.name,t2.name from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#;

2.统一表空间

--统一表空间
SELECT
('alter table '||a.TABLE_NAME||'  move tablespace  新表空间名;')AS col
FROM user_tables a 
WHERE  tablespace_name='老的表空间名称'

--ps：在转空间的过程中会产生失效的索引，应rebuild下可在toad操作，也可执行以下查询
SELECT 'ALTER INDEX  '||t.INDEX_NAME||' REBUILD;' sql
FROM user_indexes t WHERE status='UNUSABLE' 

3.oracle11g导出少表解决办法

--（ ORACLE 11G中有个新特性，当表无数据时，不分配segment，以节省空间。）
--1）设置deferred_se1gment_creation参数--只对设置之后的表起作用
alter system set deferred_segment_creation=false;

alter system set deferred_segment_creation=false scope=both;
--2）批量处理空表
select 'alter table '||table_name||' allocate extent;' from user_tables where segment_created='NO'
--3）
----(1) 查询当前用户下的所有空表

        select table_name from user_tables where num_rows='0';

----(2) 用以下SQL语句执行查询

        select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;
        select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null;

----假设我们这里有空表TABLE_1，TABLE_2，TABLE_3，TABLE_4，则查询结果如下：

        alter table TABLE_1 allocate extent;
        alter table TABLE_2 allocate extent;
        alter table TABLE_3 allocate extent;
        alter table TABLE_4 allocate extent;

----(3) 把上面的 alter SQL语句执行就可以了
----(4) 再查询当前用户下的所有空表，这时应该就没有空表了
        select 'analyze table '||table_name||' compute statistics;' from user_tables;
        select table_name from user_tables where num_rows='0';

4.给数据库增加数据文件（原因:一个数据文件最大32G）

--1、查看数据库表空间如何查看表空间占用比
SELECT a.tablespace_name "表空间名", 
total "表空间大小", 
free "表空间剩余大小", 
(total - free) "表空间使用大小", 
total / (1024 * 1024 * 1024) "表空间大小(G)", 
free / (1024 * 1024 * 1024) "表空间剩余大小(G)", 
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", 
round((total - free) / total, 4) * 100 "使用率 %" 
FROM (SELECT tablespace_name, SUM(bytes) free 
FROM dba_free_space 
GROUP BY tablespace_name) a, 
(SELECT tablespace_name, SUM(bytes) total 
FROM dba_data_files 
GROUP BY tablespace_name) b 
WHERE a.tablespace_name = b.tablespace_name 

--2、给数据库增加数据文件
AlTER tablespace test ADD DATAFILE 'D:\data\test1.dbf' size 500M autoextend on next 50M maxsize unlimited;

5.设置密码无使用期限（默认180天）

//查看密码使用期限
select * from dba_profiles s where s.profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME'
//设置密码使用为无限制,不需要重启数据库（全部）
alter profile default limit password_life_time unlimited;
//不用更换密码（已失效的）
alter user 用户名 identified by 原来的密码 account unlock;

6.修改数据库锁定

alter user 用户名account unlock;

---