Oracle常用sql

1、如何查询某一张表有多少个字段

select max(column_id) from user_tab_columns where table_name=upper('表名')；
select *  from user_tab_columns where table_name=upper('表名')

2、查询oracle的数据目录

select  * from dba_directories

3、查看表空间利用率 

SELECT F.TABLESPACE_NAME,
       (T.TOTAL_SPACE - F.FREE_SPACE) "USED (MB)",
       F.FREE_SPACE "FREE (MB)", 
       T.TOTAL_SPACE "TOTAL (MB)",
       (ROUND((F.FREE_SPACE / T.TOTAL_SPACE) * 100)) ||  '% ' PER_FREE
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BLOCKS * 
                         (SELECT VALUE / 1024
                            FROM V$PARAMETER
                           WHERE NAME = 'db_block_size') / 1024)) FREE_SPACE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) T
 WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME

4、查看表空间详细信息

select * from dba_data_files;

5、删除表空间以及dbf文件

DROP TABLESPACE UR_SOAR_TEMP INCLUDING CONTENTS AND DATAFILES;

6、oracle11g导出空表

select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;

7、设置表空间自动扩展：

alter database datafile '\oracle\oradata\anita_2008.dbf' autoextend on next 100m maxsize 10000m

8、数据分析索引

exec dbms_stats.gather_schema_stats(ownname => 'oracle用户',estimate_percent => '10');

9、delete 之后，闪回

select timestamp_to_scn(to_timestamp('2016-05-13 09:00:00','YYYY-MM-DD HH24:MI:SS')) from dual;
select * from 表名 AS OF SCN 20557380127

10、查询数据库中正在运行的sql

select a.program, b.spid, c.sql_text,c.SQL_ID from v$session a, v$process b, v$sqlarea c
 where a.paddr = b.addr and a.sql_hash_value = c.hash_value and a.username is not null;

11、查看索引状态

select status  from user_indexes where index_name='IDX_TAKUP_DATA_DT'

12、给一个用户分配多个表空间

    alter user username quota unlimited on tablespace_name

13、列出ororacle所有表的主外键关系 

select a.constraint_name, a.table_name, b.constraint_name from user_constraints a, user_constraints b where a.constraint_type = 'R'and b.constraint_type = 'P' and a.r_constraint_name = b.constraint_name

14、impdps数据泵导入时候注意的地方
 如果impdp使用remap_schema，且指定要导入的表时，要指定表的schema。
 如果impdp使用remap_schema，导入dmp的所有数据时则不需要指定schema。