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。