常用语句

假定数据库的用户名和表空间皆为为:user_name
数据库版本为:11.2.0.4.0

表空间和用户操作(一般创建新用户时一起操作)

--创建表空间
CREATE TABLESPACE user_name DATAFILE 'D:\app\Administrator\oradata\user_name\user_name01.dbf' SIZE 1024M
AUTOEXTEND  ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
ALTER TABLESPACE user_nameADD DATAFILE 'D:\app\Administrator\oradata\user_name\user_name02.dbf' SIZE 1024M;
ALTER DATABASE DATAFILE 'D:\app\Administrator\oradata\user_name\user_name01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE unlimited;
ALTER DATABASE DATAFILE 'D:\app\Administrator\oradata\user_name\user_name02.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE unlimited;
--创建用户(并给予权限)
CREATE USER user_name IDENTIFIED BY 1 DEFAULT TABLESPACE user_name  TEMPORARY TABLESPACE TEMP;
GRANT CONNECT TO user_name;
GRANT RESOURCE TO user_name;
GRANT DBA TO user_name;
GRANT UNLIMITED TABLESPACE TO user_name;
GRANT create any table TO user_name;
--彻底删除表空间操作
 alter database datafile 'D:\APP\user_name\ORADATA\user_name\user_name02.DBF' offline drop;
DROP TABLESPACE user_name INCLUDING CONTENTS AND DATAFILES;
--查询表空间状态操作
SELECT a.tablespace_name,
       filenum,
       total "TOTAL (MB)",
       f.free "FREE (MB)",
       to_char(round(free * 100 / total, 2), '990.00') "FREE%",
       to_char(round((total - free) * 100 / total, 2), '990.00') "USED%",
       round(maxsizes, 2) "MAX (MB)"
  FROM (SELECT tablespace_name,
               COUNT(file_id) filenum,
               SUM(bytes / (1024 * 1024)) total,
               SUM(maxbytes) / 1024 / 1024 maxsizes
          FROM dba_data_files
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, round(SUM(bytes / (1024 * 1024))) free
          FROM dba_free_space
         GROUP BY tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name;
SELECT ceil(max_block * block_size / 1024)
  FROM (SELECT MAX(block_id) max_block
          FROM dba_extents
         WHERE file_id IN (SELECT file_id
                             FROM dba_data_files d
                            WHERE d.tablespace_name = 'USERS')) m,
       (SELECT VALUE / 1024 block_size
          FROM v$parameter
         WHERE NAME = 'db_block_size') b;
CREATE directory dump_name AS 'E:\';
--清理表空间操作(回收站清空):
purge tablespace tablespace_name;--用于清空表空间的recycle bin
purge tablespace tablespace_name USER user_name;--清空指定表空间的recycle bin中指定用户的对象
purge recyclebin;--删除当前用户的recycle bin中的对象
purge dba_recyclebin;--删除所有用户的recycle bin中的对象，该命令要sysdba权限
drop TABLE table_name purge;--删除对象并且不放在recycle bin中，即永久的删除，不能用flashback恢复。
purge INDEX recycle_bin_object_name;-- 当想释放recycle bin的空间，又想能恢复表时，可以通过释放该对象的index所占用的空间来缓解空间压力。 因为索引是可以重建的。

数据泵操作

--创建directory并使用数据泵常用语句
create or replace directory dump_dir as 'E:\...';
grant READ, WRITE ON directory dump_dir TO user_name;
--导入数据泵
impdp user_name/ 1@user_name directory = dump_name dumpfile = user_name.dmp ;