oracle一些工作笔记

表空间：

oracle表空间对应的数据文件：

SELECT t1.name, t2.name FROM v$tablespace t1, v$datafile t2 WHERE t1.ts#=t2.ts# order by t1.name;

创建表空间：

SQL> show user;
USER is "SYS"
SQL> create tablespace taiping_hk_uat1 datafile '/u01/app/oradata/oracle/uat1/uat1_01.dbf' size 4000M autoextend on maxsize 8000M;

删除表空间，同时删除数据文件：

drop tablespace test_data including contents and datafiles;

查看临时表空间和系统表空间状态，表空间名称（使用sysdba）：

select * from (  
Select a.tablespace_name,  
to_char(a.bytes/1024/1024,'99,999.999') total_bytes,  
to_char(b.bytes/1024/1024,'99,999.999') free_bytes,  
to_char(a.bytes/1024/1024 - b.bytes/1024/1024,'99,999.999') use_bytes,  
to_char((1 - b.bytes/a.bytes)*100,'99.99') || '%' use  
from (select tablespace_name,  
sum(bytes) bytes  
from dba_data_files  
group by tablespace_name) a,  
(select tablespace_name,  
sum(bytes) bytes  
from dba_free_space  
group by tablespace_name) b  
where a.tablespace_name = b.tablespace_name  
union all  
select c.tablespace_name,  
to_char(c.bytes/1024/1024,'99,999.999') total_bytes,  
to_char( (c.bytes-d.bytes_used)/1024/1024,'99,999.999') free_bytes,  
to_char(d.bytes_used/1024/1024,'99,999.999') use_bytes,  
to_char(d.bytes_used*100/c.bytes,'99.99') || '%' use  
from  
(select tablespace_name,sum(bytes) bytes  
from dba_temp_files group by tablespace_name) c,  
(select tablespace_name,sum(bytes_cached) bytes_used  
from v$temp_extent_pool group by tablespace_name) d  
where c.tablespace_name = d.tablespace_name  
) 

查看表空间信息：

select * from v$tablespace;

查看当前用户使用的表空间：

SQL>select username,default_tablespace from user_users;

扩展表空间：

alter tablespace tablespace_name add datafile 'datafile_path' size 4000M autoextend on next 15000M maxsize 20000M;

查看表空间总大小：

SQL> select * from dba_data_files;

查看表空间剩余大小：

SQL> select * from dba_free_space d where d.TABLESPACE_NAME='SYSTEM';

directory：

查看directory：

select * from dba_directories;

删数据脚本：

select 'drop ' || object_type || ' ' || object_name || ';'  From user_objects 
Where object_type In ('FUNCTION','PACKAGE','PROCEDURE','SEQUENCE','TYPE') and object_name!='P_PART_STATISTIC'
Union
Select 'drop package body ' || object_name || ';' From user_objects 
Where object_type='PACKAGE BODY' And object_name Not In(Select object_name From user_objects Where object_type='PACKAGE')
union
select 'DROP SYNONYM ' || synonym_name || ' force;' from user_synonyms
union
select 'DROP TABLE ' || table_name || ' cascade constraints purge;' from user_tables
union
select 'DROP VIEW ' || view_name || ' cascade constraints;' from user_views
union 
select 'DROP CLUSTER ' || cluster_name || 'including tables cascade constraints;' drop_sql From user_clusters;

删数据步骤：

sql>spool /home/oracle/drop.sql
sql>执行删除数据脚本@/home/oracle/check.sql
sql>spool off
sql>@/home/oracle/drop.sql

导入导出数据：

sql>create directory dumpfile as '/home/oracle/data_backup';
sql>grant read,write on directory dumpfile to tphk_main_test;
$expdp username/password directory=dumpfile dumpfile=username_`date +%Y%m%d`_exclude.dmp logfile=username_`date +%Y%m%d`.log compression=all exclude=grant,statistics

impdp username/password directory=dumpfile dumpfile=username_version10-20_`date +%Y%m%d`_exclude.dmp logfile=username_version10-20_`date +%Y%m%d`_exclude.log TRANSFORM=oid:n remap_schema=username:user_schema remap_tablespace=tablespace1:tablespace2

时间转换：

SQL> select to_char(trunc(sysdate),'yyyymmdd hh24:mi:ss') from dual;
TO_CHAR(TRUNC(SYSDATE),'YYYYMM
------------------------------
20161109 00:00:00

查询正在执行的sql：

select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT from v$session a, v$sqlarea b where a.sql_address = b.address;

查询前十条性能差的sql. 

SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS, COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea order BY disk_reads DESC )where ROWNUM<10 ; 

查看索引个数和类别:

SQL> select * from user_indexes where table_name='表名' ;
SELECT * FROM ALL_INDEXES WHERE TABLE_NAME='表名';

查看表中的索引名称和对应的字段：

select user_ind_columns.index_name,user_ind_columns.column_name,
user_ind_columns.column_position,user_indexes.uniqueness
from user_ind_columns,user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = ‘你想要查询的表名字’;

查看占io较大的正在运行的session:

SELECT se.sid,se.serial#,pr.SPID,se.username,se.status, 
se.terminal,se.program,se.MODULE,、se.sql_address,st.event,st. 
p1text,si.physical_reads, 
si.block_changes FROM v$session se,v$session_wait st, 
v$sess_io si,v$process pr WHERE st.sid=se.sid AND st. 
sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st. 
wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC

查看用户的系统权限以及拥有的角色:

SQL> select * from dba_role_privs d where d.GRANTEE='SCOTT';

SQL> select * from dba_sys_privs d where d.GRANTEE='SCOTT';

SQL> select * from dba_tab_privs d where d.grantee='CHINA_CLP';

SQL> SELECT * FROM DBA_SYS_PRIVS D WHERE D.GRANTEE='CONNECT';

oracle连接方式：

tnsname：
test=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.101.20)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =oracle.test)
     )
  )

那么连接方式：

1、sqlplus username/password@test

2、sqlplus username/password@192.168.101.20:1521/oracle.test

oracle交互：

[oracle@oracle1 ~]$ sqlplus scott/tiger << EOF
> show user;
> quit
> EOF

查看表锁情况，是否有锁表：

SELECT l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, s.user#,
l.os_user_name,s.machine, s.terminal,a.sql_text, a.action
FROM v$sqlarea a,v$session s, v$locked_object l
WHERE l.session_id = s.sid
AND s.prev_sql_addr = a.address
ORDER BY sid, s.serial#;

select distinct a.sid,b.SERIAL#,b.PROCESS,b.STATUS from v$session_wait a,v$session b
where a.SID=b.SID;