Oracle 常用的简单语句

一. 表空间
    1.创建数据表空间几种SQL
      create tablespace XXXX
      datafile 'E:\oracle\product\10.2.0\oradata\orcl\XXXXX.dbf'   （表空间的放置位置）
      SIZE 200M REUSE AUTOEXTEND ON NEXT 100M;
      或者：
      create tablespace XXXX
      datafile 'E:\oracle\product\10.2.0\oradata\orcl\XXXXX.dbf'   
      size 200M 
      autoextend on next 100M maxsize unlimited extend managemnet;
      或者：
      create tablespaceXXXX
      logging  
      datafile 'E:\oracle\product\10.2.0\oradata\orcl\XXXXX.dbf'
      size 200m  
      autoextend on  next 100m  maxsize 20480m  extent management local;

    2.创建临时表空间  
      create temporary tablespace user_temp  
      tempfile 'F:\oracle\product\10.2.0\oradata\orcl\TS_TEMP.dbf' 
      size 200m  
      autoextend on  next 100m  

      maxsize 20480m  

    extent management local; 

    3. 删除表空间
      前提：删除表空间之前确认该表空间没有被其他用户使用之后在删除
      drop tablespace TS_STD including contents and datafiles cascade onstraints;
      //including contents 删除表空间的内容（如果删除表空间之前表空间中有内容，而未加此参数，表空间删不掉）
      //including datafiles 删除表空间的数据文件
      //cascade onstraints 删除tablespace中表的外键参照。

    4.如果删除表空间之前删除了表空间的文件，解决办法：
      如果在清除表空间之前，先删除了表空间对应的数据文件，会造成数据库无法正常启动和关闭。
      可用如下的方法恢复
      其中，filename是已经被删除的数据文件，如果有多个，则需要多次执行，tablespace_name是相应表空间的名称。
      $ sqlplus /nolog
      SQL> coon /as sysdba;
      如果数据库已经启动，则需要先执行下面这行;
      SQL> shutdown abort;
      SQL> startup mount;
      SQL> alter database datafile 'filename' offline drop;
      SQL> alter database open;
      SQL> drop database tablespace_name including contents;

    5.查看Oracle表空间使用情况
       select * from dba_free_space;
       select * from dba_data_files;
       select b.file_id, --文件ID号
            b.tablespace_name, --空间名
            b.bytes, --字节数
            (b.bytes - sum(nvl(a.bytes, 0))), --已使用,
            sum(nvl(a.bytes, 0)),--剩余空间
            sum(nvl(a.bytes, 0)) / (b.bytes) * 100  --剩余百分比
       from dba_free_space a, dba_data_files b
       where a.file_id = b.file_id
       group by b.tablespace_name, b.file_id, b.bytes

       order by b.file_id

二. 创建用户
      1.创建用户;

  create user 用户名

  identified by 密码

           default tablespace 表空间

           temporary tablespace 临时表空间;

       2．给用户授予权限
           grant connect,resource,dba to test;
           注意：创建完成后，必须分配权限，否则连不上数据库和sqlplus

       3.删除用户
         drop user test cascade;
          注意：cascade参数是级联删除该用户所有对象，经常遇到如用户有对象而未加此参数则用户删不了的问题，所以习惯加此参数。
   
三.导出表的命令：
       1.导入导出表的ＳＱＬ语句(导出的表0行数据全部导出)
           exp 用户名/密码@实例名 file=d:\test.dmp owner=test log=d:\test.log rows=y

       2.只导出表结构，不导表数据
         exp test/test@orcl file=d:\test.dmp owner=test log=d:\test.log rows=n;

       3.将数据库中的system和sys用户的表导出
          exp system/manager@orcl file=d:\daochu.dmp owner=(system,sys) log=d:\daochu.log
 
       4.将数据库中的表table1,table2,table3导出
          exp test/test@orcl file=d:\test.dmp tables=(table1,table2,table3) log=d:\test.log;

       5.将数据库中的表table1中的字段filed1以‘00’打头的数据导出
         exp test/test@orcl file=d:\test.dmp tables=(table1) query="where filed1 like '00%'" log=d:\test.log;
         注：上面是常用的导出，对于压缩，即用winzip把dmp文件可以很好的压缩，也可以在上面的命令后面加上compress=y命令。

四. 常用的SQL语言
      1. 复制表结构及其数据  备份表的SQL：
           create table table_name_new as select * from table_name_old

      2. 只复制表结构：
           create table table_name_new as select * from table_name_old where 1=2;

      3. 只复制表数据：
         如果两个表结构一样：
         insert into table_name_new select * from table_name_old
         如果两个表结构不一样：
          insert into table_name_new(column1,column2...) select column1,column2... from table_name_old

     4.查询排序后的第一条记录
        select * from ( select tb.*, rownum from table_name tb where column_name like '20080311%' order by id) where rownum=1

     5.查看有哪些表
        select table_name,tablespace_name from user_tables
        select * from user_tables

     6.查看表的最大ID
        select max(t.id) maxid from test t

      7.求一个月有多少天
        select rownum as varchar01
        from dual c
        connect by rownum <=
        to_char(trunc(add_months(to_date('2012-06', 'YYYY-MM'), 1), 'MM') - 1,'DD');

        select to_char(last_day(to_date('2011-11-01', 'yyyy-mm-dd')), 'dd') from dual;

      8.创建database link
         -- Drop existing database link 
            drop database link XXXX;
         -- Create database link 
          create database link XXXX
          connect to XXXX 
         identified by XXXX

          using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))'; 

      9.查询某个表中的所有的列名的sql语句
         SELECT WMSYS.WM_CONCAT(U.COLUMN_NAME) FROM USER_TAB_COLUMNS U WHERE U.TABLE_NAME=upper('表名');

五. 查看ORACLE的一些资源(可以根据这个查看SQL语句的消耗) 
       select * from dba_tables a where a.TABLE_NAME like '%PROFILER%';
       SELECT * FROM PLSQL_PROFILER_DATA;
       SELECT * FROM PLSQL_PROFILER_RUNS;
       SELECT * FROM PLSQL_PROFILER_UNITS;
       SELECT * FROM ALL_SOURCE A