PL/SQL 常用脚本(持续更新)

----------------------------------------------------------------------
--连接字符串
----------------------------------------------------------------------

(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.21)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = testdb)))

 

----------------------------------------------------------------------
--更改数据库字符集(只能更改为超集)
----------------------------------------------------------------------

STARTUP nomount;
ALTER database mount EXCLUSIVE;
ALTER system enable/disable restricted SESSION;
ALTER system SET job_queue_process=0;
ALTER database OPEN;
ALTER database CHARACTER SET zhs16gbk;

 

----------------------------------------------------------------------
--创建表空间
----------------------------------------------------------------------
create temporary tablespace wtpbi_temp
tempfile 'c:\oracle\oradata\Oracle10g\test_temp.dbf'
size 50m
autoextend on
next 50m maxsize 10240m
extent management local;

create tablespace wtp_data
nologging
datafile 'c:\oracle\oradata\Oracle10g\wtp_data.dbf'
size 50m
autoextend on
next 50m maxsize 30720m
extent management local;

create user wtpusr identified by wtp 
default tablespace wtp_data
temporary tablespace wtp_temp;

grant connect,resource,dba to wtpusr;

----------------------------------------------------------------------
--为表空间增加文件
----------------------------------------------------------------------

ALTER TABLESPACE wtp_data ADD DATAFILE 'c:\oracle\oradata\Oracle10g\wtp_data_append01.dbf' SIZE 10240m;

 

----------------------------------------------------------------------
--为表空间与数据文件视图
----------------------------------------------------------------------

select s.name, d.name from v$datafile d, v$tablespace s where d.ts# = s.ts#;

 

----------------------------------------------------------------------
--创建数据库链接
----------------------------------------------------------------------

CREATE public DATABASE LINK zsdb CONNECT TO dbuser IDENTIFIED BY "password" USING 'testdb';

 

----------------------------------------------------------------------
--排查表锁
----------------------------------------------------------------------

select session_id from v$locked_object;
select * from v$locked_object;
SELECT sid, serial#, username, osuser FROM v$session where sid = 53;
ALTER SYSTEM KILL SESSION '53,16201';

 

----------------------------------------------------------------------
--剩余表空间查询
----------------------------------------------------------------------

select tablespace_name,sum(bytes)/1024/1024 from dba_free_space   group by tablespace_name;

 

----------------------------------------------------------------------
--导入导出命令
----------------------------------------------------------------------

exp wtpusr/wtp file=e:\dwbackup\wtp-metadata.dmp rows=N owner=(wtpusr)
imp wtpusr/wtp file=d:\backup\wtp-data-20121031.dmp fromuser=wtpusr touser=wtpusr ignore=y

exp system/wtp file=d:\backup\wtp-full-20121031.dmp full=y
imp system/wtp full=y file=d:\backup\wtp-data-20121031.dmp ignore=y

 

----------------------------------------------------------------------
--修改表名称
----------------------------------------------------------------------

ALTER TABLE tablea RENAME TO tableb;

 

----------------------------------------------------------------------
--快速加载
----------------------------------------------------------------------

insert /*+append*/ into t select * from tableA nologging; 

 

----------------------------------------------------------------------
--常用函数
----------------------------------------------------------------------

to_char(datefield,'yyyy-mm-dd hh24:mi:ss')

 

The following is a list of valid parameters when the to_char function is used to convert a date to a string. These parameters can be used in many combinations.

 

 

Parameter	Explanation
YEAR	Year, spelled out
YYYY	4-digit year
YYY         YY         Y	Last 3, 2, or 1 digit(s) of year.
IYY         IY         I	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year ｂａｓｅd on the ISO standard
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
RM	Roman numeral month (I-XII; JAN = I).
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year (1-52 or 1-53) ｂａｓｅd on the ISO standard.
D	Day of week (1-7).
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DY	Abbreviated name of day.
J	Julian day; the number of days since January 1, 4712 BC.
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
FF	Fractional seconds.

 

The following are date examples for the to_char function.

 

to_char(sysdate, 'yyyy/mm/dd');	would return '2003/07/09'
to_char(sysdate, 'Month DD, YYYY');	would return 'July 09, 2003'
to_char(sysdate, 'FMMonth DD, YYYY');	would return 'July 9, 2003'
to_char(sysdate, 'MON DDth, YYYY');	would return 'JUL 09TH, 2003'
to_char(sysdate, 'FMMON DDth, YYYY');	would return 'JUL 9TH, 2003'
to_char(sysdate, 'FMMon ddth, YYYY');	would return 'Jul 9th, 2003'