oracle日常-新建数据库

新建数据库

---创建表空间
CREATE TABLESPACE 表空间
      DATAFILE 'f:/oracle/product/10.2.0/oradata/orcl/表空间.dbf' SIZE 600M REUSE
      AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL; 
--创建用户     
 CREATE USER 用户 IDENTIFIED BY 用户
    PROFILE DEFAULT
    DEFAULT TABLESPACE 表空间
    TEMPORARY TABLESPACE TEMP
    ACCOUNT UNLOCK;
---设置密码,添加权限
-- Modify the user 
alter user 用户
  identified by 密码;
-- Grant/Revoke role privileges 
grant dba to 用户;

查找数据库文件位置

select tablespace_name, file_id, bytes / 1024 / 1024, file_name 　　
  from dba_data_files
 order by file_id;

删除表空间

DROP TABLESPACE 表空间 INCLUDING CONTENTS  AND DATAFILES CASCADE CONSTRAINTS;

数据库锁表  解锁

select object_name, machine, s.SID, s.SERIAL#
  from v$locked_object i, dba_objects o, v$session s
 where i.OBJECT_ID = o.OBJECT_ID
   and i.SESSION_ID = s.SID;

 alter system kill session '214,7689';