oracle基本实用技术

1.删除表空间
drop tablespace 表空间名 including contents;
drop tablespace tbsp_ch including contents;

2.删除用户
select username from dba_users
drop user username cascade;


1.  创建表空间
create tablespace cun datafile '/oracle/oradata/orcl/cun.dbf' size 1024M reuse autoextend on next 400M maxsize unlimited default storage(initial 128k next 128k minextents 2 maxextents unlimited);

2.  创建用户
create user cun identified by "cun" default tablespace cun;

3.  用户授权
grant connect,EXP_FULL_DATABASE,IMP_FULL_DATABASE, resource, unlimited tablespace, select any table, delete any table, update any table, insert any table,create any view, create any synonym to cun;

4.  用户登录验证
conn cun/cun


删除触发器
DROP TRIGGER trigger_name;

查看表空间物理文件的名称，路径及大小
SYS>select tablespace_name, file_id,file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;

增加表空间
SYS>alter tablespace cun add datafile '/oracle/oradata/orcl/cun05.dbf' size 30G autoextend on next 100M maxsize unlimited;

SYS>SELECT tablespace_name 表空间 ,sum(blocks*8192/1000000) 剩余空间M FROM dba_free_space GROUP BY tablespace_name;

在数据库中杀死用户进程
SQL> select sid,serial# from v$session where username='TEST';
      SID    SERIAL#
---------- ----------
       150       9019
SQL> alter system kill session '150,9019';
System altered.

导入表空间
imp cun/cun  full=y  file=/home/oracle/cun_20120919.dmp  ignore=y

导出表空间:
第一种：
exp cun/cun  file=/home/oracle/table-cun.dmp
exp  cun/cun  file=/home/oracle/20130322/cun-20130322.dmp  owner=cun
exp cun/cun tables=tb_medical_level file=tb_medical_level20121102.dmp

第二种：
 SQL> spool temp.sql -- 将查询结果保存到 temp.sql 文件
  SQL> SELECT DBMS_METADATA.GET_DDL('TABLE',table_name) FROM USER_TABLES； -- 通过 dbms_metadata.get_ddl 函数获取用户表的定义
  SQL> spool off  -- 关闭保存