oracle一些工作筆記

wadeson發表於2018-01-02
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;

 

相關文章