数据库命令备查

***********************************************************************************************************

                                       通用

***********************************************************************************************************

-【定义变量】

DECLARE @find varchar(30)

SET @find = 'Ring%'

SELECT au_lname, au_fname, phone

FROM authors

WHERE au_lname LIKE @find

 

SET NOCOUNT ON

GO

DECLARE @pub_id char(4), @hire_date datetime

--SET @pub_id = '0877'

--SET @hire_date = '1/01/93'

SELECT @pub_id = '0877', @hire_date = '1/01/93'

SET NOCOUNT OFF

SELECT fname, lname

FROM employee

WHERE pub_id = @pub_id and hire_date >= @hire_date

***********************************************************************************************************

                                       TT

***********************************************************************************************************

-【实体存放】

select 'drop '||object_type||' '||object_name||';' from dba_objects where owner='dbusername';

select * from tables;

***********************************************************************************************************

                                       ORCL

***********************************************************************************************************

-【数据库启动】

[oracle@ngbillsuse60] /opt/oracle> sqlplus /nolog

SQL> connect sys/oracle as sysdba;  

SQL> startup;

[oracle@ngbillsuse60] /opt/oracle> lsnrctl start

-【查看表空间】

select a.tablespace_name "表空间名称",

 100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2)||'%'    "使用率(%)",

 round(nvl(b.bytes_free,0)/1024/1024/1024,2)                  "空闲(G)",

 round(a.bytes_alloc/1024/1024/1024,2)                        "容量(G)",

 round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024/1024,2)  "使用(G)",

 round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2)             "空闲率(%)" ,

 to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')                     "采样时间" 

from (select nvl(f.tablespace_name,'合计') tablespace_name, sum(f.bytes) bytes_alloc from dba_data_files f group by cube(tablespace_name)) a,

    (select nvl(f.tablespace_name,'合计') tablespace_name, sum(f.bytes) bytes_free from dba_free_space f group by cube(tablespace_name)) b

where a.tablespace_name = b.tablespace_name(+) /*AND a.tablespace_name LIKE 'D_DAT_%'*/ ORDER BY 1;

 

-【同义词信息存储】

 select*from dba_synonyms;

-【创建表】

DECLARE

existflagINT;

BEGIN

 SELECTCOUNT(1)INTO existflag FROM User_Objects vWHERELower(v.OBJECT_NAME)='tablename';

 IF existflag=1THEN

   EXECUTEIMMEDIATE'creat talbe tablename(balabala,balabalaba)';

   ENDIF;

END;

-【授权】

GRANTALLON My_TableTO dbuser;

-【忘记密码】

conn /as sydba

alter user system identified by zhangshibo

-【表空间和用户创建】

CREATEUSER newuserIDENTIFIEDBY newuser;

CREATETABLESPACE SPACE_DATADATAFILE'D:\DB\data.dbf'SIZE100M;

CREATETABLESPACE SPACE_INDDATAFILE'D:\DB\ind.dbf'SIZE100M;

ALTERUSER newuserDEFAULTTABLESPACE SPACE_DATA;

ALTERUSER newuserQUOTAUNLIMITEDON SPACE_IND;

GRANTCONNECT,RESOURCETO newuser;

DROPTABLESPACE SPACE_DATAINCLUDINGcontents;

-【日期字符串插入设置】

ORCAL数据库向日期型字段插入字符需要添加的注册表项：nls_date_format yyyymmddhh24miss

-【日期转换】

SELECT TO_DATE('2011-09-01','yyyy-mm-dd') FROM dual

SELECT to_date('20100901 20:59:59','yyyymmdd hh24:mi:ss') FROM dual

SELECT regexp_substr('aaa,bbb,ccc','[^,]+',1,2) FROM dual返回 bbb

-【用户解锁】

ALTER USER USERNAME ACCOUNT UNLOCK;

-【输入密码错误限制】

select profile from dba_users where username='dbusername'

alter profile DEFAULT limit failed_login_attempts unlimited;

 

***********************************************************************************************************

                                       SQL SERVER

***********************************************************************************************************

-【实体定义】

IF  EXISTS   (SELECT   *   FROM   sysobjects   WHERE   type   =   'P '   AND   name   =   'sp_populace_change_sel_id ')

BEGIN

DROP  Procedure   sp_populace_change_sel_id

END

-------------------------------------------------------------------------------------------------------------

-【数据库备份】

CREATEPROCEDURE [dbo].[sp_BackupDatabase]

@databaseNamesysname,@backupTypeCHAR(1)

AS BEGIN

SET NOCOUNTON;

DECLARE @sqlCommand NVARCHAR(1000)

DECLARE @dateTime NVARCHAR(20)

 

SELECT @dateTime=REPLACE(CONVERT(VARCHAR,GETDATE(),111),'/','')+

REPLACE(CONVERT(VARCHAR,GETDATE(),108),':','')

 

IF @backupType='F'

SET @sqlCommand='BACKUP DATABASE'+@databaseName '+

TO DISK =''C:\Backup\'+ @databaseName '+_Full_'+ @dateTime '.BAK'''

 

IF@backupType='D'SET@sqlCommand='BACKUP DATABASE' @databaseName 'TO DISK =''C:\Backup\' @databaseName '_Diff_' @dateTime '.BAK''WITH DIFFERENTIAL'IF@backupType='L'SET@sqlCommand='BACKUP LOG' @databaseName 'TO DISK =''C:\Backup\' @databaseName '_Log_' @dateTime '.TRN'''EXECUTEsp_executesql@sqlCommandENDgo

-------------------------------------------------------------------------------------------------------------

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

--=============================================

--Author: RyanDing

--Create date: 2010-10-10

--Description:备份数据库

--Parameter1:数据库名

--Parameter2:备份类型F=全部, D=差异, L=日志

--=============================================

ALTER PROCEDURE [dbo].[sp_BackupDatabase]

@databaseName sysname,@backupType CHAR(1)

AS BEGIN

SET NOCOUNT ON;

DECLARE @sqlCommand NVARCHAR(1000)

DECLARE @dateTime NVARCHAR(20)

 

SELECT @dateTime=REPLACE(CONVERT(VARCHAR,GETDATE(),111),'/','')+

REPLACE(CONVERT(VARCHAR,GETDATE(),108),':','')

 

 

IF @backupType='F'

SET @sqlCommand='BACKUP DATABASE'+@databaseName+

'TO DISK =''C:\Backup\'+@databaseName+'_Full_'+@dateTime+'.BAK'''

 

 

EXECUTE sp_executesql@sqlCommand

END

-------------------------------------------------------------------------------------------------------------