sys/change_on_install       　　SYSDBA 或 SYSOPER          不能以 NORMAL 登录，可作为默认的系统管理员

system/manager               　 SYSDBA 或 NORMAL            不能以 SYSOPER 登录，可作为默认的系统管理员

sysman/oem_temp                sysman                     为 oms 的用户名

scott/tiger                    NORMAL                     普通用户

aqadm /aqadm                   SYSDBA 或 NORMAL           高级队列管理员

Dbsnmp/dbsnmp                  SYSDBA 或 NORMAL           复制管理员

create tablespace zfmi

logging

datafile 'D:\oracle\oradata\zfmi\zfmi.dbf'

size 100m

autoextend on

next 32m maxsize 2048m

extent management local; 

create temporary tablespace zfmi_temp

tempfile 'D:\oracle\oradata\zfmi\zfmi_temp.dbf'

size 32m

autoextend on

next 32m maxsize 2048m

extent management local;

drop user zfmi cascade; 

cascade参数是级联删除该用户所有对象，经常遇到如用户有对象而未加此参数则用户删不了的问题，所以习惯性的加此参数

前提：删除表空间之前要确认该表空间没有被其他用户使用之后再做删除

drop tablespace zfmi including contents and datafiles cascade onstraints; 

including contents 删除表空间中的内容，如果删除表空间之前表空间中有内容，而未加此参数，表空间删不掉，所以习惯性的加此参数

including datafiles 删除表空间中的数据文件

cascade constraints 同时删除tablespace中表的外键参照

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",

       D.TOT_GROOTTE_MB "表空间大小(M)",

       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",

       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,

                     2),

               '990.99') "使用比",

       F.TOTAL_BYTES "空闲空间(M)",

       F.MAX_BYTES "最大块(M)"

  FROM (SELECT TABLESPACE_NAME,

               ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,

               ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES

          FROM SYS.DBA_FREE_SPACE

         GROUP BY TABLESPACE_NAME) F,

       (SELECT DD.TABLESPACE_NAME,

               ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB

          FROM SYS.DBA_DATA_FILES DD

         GROUP BY DD.TABLESPACE_NAME) D

 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME

 ORDER BY 4 DESC;

SELECT T.TABLESPACE_NAME,

       D.FILE_NAME,

       D.AUTOEXTENSIBLE,

       D.BYTES,

       D.MAXBYTES,

       D.STATUS

  FROM DBA_TABLESPACES T, DBA_DATA_FILES D

 WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME

 ORDER BY TABLESPACE_NAME, FILE_NAME;

ALTER TABLESPACE app_data ADD DATAFILE

'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF' SIZE 50M;

ALTER TABLESPACE app_data ADD DATAFILE

'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04.DBF' SIZE 50M

AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF'

AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP02.DBF'

RESIZE 100M;

create table rs_eregion_cust_equ_r_01 as

SELECT * FROM rs_eregion_cust_equ_r AS OF TIMESTAMP to_timestamp('2013-09-26 09:40:00','yyyy-mm-dd hh24:mi:ss');

--查询被锁的表
SELECT  sn.username, m.SID,sn.SERIAL#, m.TYPE,

           DECODE (m.lmode,

                   0, 'None',

                   1, 'Null',

                   2, 'Row Share',

                   3, 'Row Excl.',

                   4, 'Share',

                   5, 'S/Row Excl.',

                   6, 'Exclusive',

                   lmode, LTRIM (TO_CHAR (lmode, '990'))

                  ) lmode,

           DECODE (m.request,

                   0, 'None',

                   1, 'Null',

                   2, 'Row Share',

                   3, 'Row Excl.',

                   4, 'Share',

                   5, 'S/Row Excl.',

                   6, 'Exclusive',

                   request, LTRIM (TO_CHAR (m.request, '990'))

                  ) request,

           m.id1, m.id2

      FROM v$session sn, v$lock m

     WHERE (sn.SID = m.SID AND m.request != 0)           --存在锁请求，即被阻塞

        OR (      sn.SID = m.SID                           --不存在锁请求，但是锁定的对象被其他会话请求锁定

            AND m.request = 0

            AND lmode != 4

            AND (id1, id2) IN (

                          SELECT s.id1, s.id2

                            FROM v$lock s

                           WHERE request != 0 AND s.id1 = m.id1

                                 AND s.id2 = m.id2)

           )

ORDER BY id1, id2, m.request;


--执行解锁

alter system kill session 'sid,SERIAL#';

create user zfmi identified by zfmi

default tablespace zfmi temporary tablespace zfmi_temp;

grant DBA,connect,resource to zfmi;