数据库参考脚本及临时表要求
目录
SQL Server参考脚本
Oracle参考脚本
DB2参考脚本
数据库临时表空间要求
SQLServer参考脚本
下面脚本是用户使用SQLServer数据库时，建立数据库的演示脚本，参考使用，注意调整参数。
----------------------------------------------------------------------------------------------------
USE master;
go
IF DB_ID (N'NCDB') IS NOT NULL
DROP DATABASE [NCDB];
go
CREATE DATABASE [NCDB]
go
ALTER DATABASE [NCDB] ADD FILEGROUP [NNC_DATA01]
go
ALTER DATABASE [NCDB] ADD FILEGROUP [NNC_DATA02]
go
ALTER DATABASE [NCDB] ADD FILEGROUP [NNC_DATA03]
go
ALTER DATABASE [NCDB] ADD FILEGROUP [NNC_INDEX01]
go
ALTER DATABASE [NCDB] ADD FILEGROUP [NNC_INDEX02]
go
ALTER DATABASE [NCDB] ADD FILEGROUP [NNC_INDEX03]
go
ALTER DATABASE [NCDB] ADD FILE(NAME = N'nnc_data01', FILENAME = N'D:\Microsoft SQL Server\MSSQL$SQL1\data\nnc_data01_Data.NDF' , SIZE = 500, FILEGROWTH = 200) TO FILEGROUP [NNC_DATA01]
go
ALTER DATABASE [NCDB] ADD FILE(NAME = N'nnc_data02', FILENAME = N'D:\Microsoft SQL Server\MSSQL$SQL1\data\nnc_data02_Data.NDF' , SIZE = 500, FILEGROWTH = 200) TO FILEGROUP [NNC_DATA02]
go
ALTER DATABASE [NCDB] ADD FILE(NAME = N'nnc_data03', FILENAME = N'D:\Microsoft SQL
Server\MSSQL$SQL1\data\nnc_data03_Data.NDF' , SIZE = 500, FILEGROWTH = 200) TO FILEGROUP [NNC_DATA03]
go
ALTER DATABASE [NCDB] ADD FILE(NAME = N'nnc_index01', FILENAME = N'D:\Microsoft SQL Server\MSSQL$SQL1\data\nnc_index01_Data.NDF' , SIZE = 200, FILEGROWTH = 100) TO FILEGROUP [NNC_INDEX01]
go
ALTER DATABASE [NCDB] ADD FILE(NAME = N'nnc_index02', FILENAME = N'D:\Microsoft SQL Server\MSSQL$SQL1\data\nnc_index02_Data.NDF' , SIZE = 200, FILEGROWTH = 100) TO FILEGROUP [NNC_INDEX02]
go
ALTER DATABASE [NCDB] ADD FILE(NAME = N'nnc_index03', FILENAME = N'D:\Microsoft SQL Server\MSSQL$SQL1\data\nnc_index03_Data.NDF' , SIZE = 200, FILEGROWTH = 100) TO FILEGROUP [NNC_INDEX03]
Go
-- 调整临时表空间
declare @name char(50)
declare @sql nvarchar(500)
select @name=rtrim(name) from tempdb.dbo.sysfiles where status & 0x40 != 0x40
set @sql='alter database tempdb modify file ( name='+@name+',size=300mb,filegrowth=10mb)' exec master.dbo.sp_executesql @sql
----------------------------------------------------------------------------------------------------
[返回页首]
Oracle参考脚本
下面脚本是用户使用ORACLE数据库时，建立数据库的演示脚本，参考使用，注意调整参数。
----------------------------------------------------------------------------------------------------
CREATE TABLESPACE NNC_DATA01 DATAFILE 'D:\ORACLE\ORADATA\ORA9I\nnc_data01.dbf' SIZE 500M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ;
CREATE TABLESPACE NNC_DATA02 DATAFILE 'D:\ORACLE\ORADATA\ORA9I\nnc_data02.dbf' SIZE 300M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ;
CREATE TABLESPACE NNC_DATA03 DATAFILE 'D:\ORACLE\ORADATA\ORA9I\nnc_data03.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K ;
CREATE TABLESPACE NNC_INDEX01 DATAFILE 'D:\ORACLE\ORADATA\ORA9I\nnc_index01.dbf' SIZE 500M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K ;
CREATE TABLESPACE NNC_INDEX02 DATAFILE 'D:\ORACLE\ORADATA\ORA9I\nnc_index02.dbf' SIZE 300M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K ;
CREATE TABLESPACE NNC_INDEX03 DATAFILE 'D:\ORACLE\ORADATA\ORA9I\nnc_index03.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ;
CREATE USER NC50 IDENTIFIED BY NC50 DEFAULT TABLESPACE NNC_DATA01 TEMPORARY TABLESPACE temp;
GRANT connect,dba to NC50;
----------------------------------------------------------------------------------------------------
[返回页首]
DB2参考脚本
下面脚本是用户使用DB2数据库时，建立数据库的演示脚本，参考使用，注意调整参数。
----------------------------------------------------------------------------------------------------
CREATE DATABASE NCTEST ON '/BigData' USING CODESET GBK TERRITORY CN COLLATE USING SYSTEM CATALOG TABLESPACE MANAGED BY DATABASE USING ( FILE '/BigData/NCT
EST/Catalogdata1' 128000 ) USER TABLESPACE MANAGED BY DATABASE USING ( FILE '/BigData/NCTEST/Userdata1' 512000 ) TEMPORARY TABLESPACE MANAGED BY DATABASE USING ( FILE '/BigData/NCTEST/Tempspace1' 768000 )
connect to NCTEST user db2inst1 using db2inst1
CREATE Bufferpool NCUSED4 SIZE 102400 PAGESIZE 4K
CREATE Bufferpool NCUSED16 SIZE 38400 PAGESIZE 16K
CREATE REGULAR TABLESPACE NNC_DATA01 PAGESIZE 16K MANAGED BY DATABASE USING ( FILE '/BigData/NCTEST/nnc_data01' 204800) EXTENTSIZE 8 OVERHEAD 24.1 PREFETCHSIZE 8 TRANSFERRATE 0.9 BUFFERPOOL NCUSED16
CREATE REGULAR TABLESPACE NNC_INDEX01 PAGESIZE 4K MANAGED BY DATABASE USING ( FILE '/BigData/NCTEST/nnc_index01' 204800) EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 32 TRANSFERRATE 0.9 BUFFERPOOL NCUSED4
CREATE REGULAR TABLESPACE NNC_DATA02 PAGESIZE 16K MANAGED BY DATABASE USING ( FILE '/BigData/NCTEST/nnc_data02' 20480) EXTENTSIZE 8 OVERHEAD 24.1 PREFETCH
SIZE 8 TRANSFERRATE 0.9 BUFFERPOOL NCUSED16
CREATE REGULAR TABLESPACE NNC_INDEX02 PAGESIZE 4K MANAGED BY DATABASE USING ( FILE '/BigData/NCTEST/nnc_index02' 40960) EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 32
TRANSFERRATE 0.9 BUFFERPOOL NCUSED4
CREATE REGULAR TABLESPACE NNC_DATA03 PAGESIZE 16K MANAGED BY DATABASE USING ( FILE '/BigData/NCTEST/nnc_data03' 204800) EXTENTSIZE 8 OVERHEAD 24.1 PREFETCHSIZE 8 TRANSFERRATE 0.9 BUFFERPOOL NCUSED16
CREATE REGULAR TABLESPACE NNC_INDEX03 PAGESIZE 4K MANAGED BY DATABASE USING ( FILE '/BigData/NCTEST/nnc_index03' 204800) EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 32 TRANSFERRATE 0.9 BUFFERPOOL NCUSED4
CREATE USER TEMPORARY TABLESPACE USERTEMP PAGESIZE 16 K MANAGED BY SYSTEM USING ( '/BigData/NCTEST/Usertemp1' ) EXTENTSIZE 32 OVERHEAD 10.67 PREFETCHSIZE 32 TRANSFERRATE 0.04 BUFFERPOOL NCTMPUSED16
db2数据库安装CREATE SYSTEM TEMPORARY TABLESPACE TEMPSPACE2 PAGESIZE 16 K MANAGED BY SYSTEM USING ( '/BigData/NCTEST/Tempspace2' ) EXTENTSIZE 32 OVERHEAD 10.67 PREFETCHSIZE 32 TRANSFERRATE 0.04 BUFFERPOOL NCTMPUSED16
GRANT
DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA, LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE TO USER db2inst1;
GRANT
DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA, LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE TO USER db2inst1;
GRANT USE OF TABLESPACE NNC_DATA01 TO USER db2inst1 WITH GRANT OPTION;
GRANT USE OF TABLESPACE NNC_DATA02 TO USER db2inst1 WITH GRANT OPTION;
GRANT USE OF TABLESPACE NNC_DATA03 TO USER db2inst1 WITH GRANT OPTION;
GRANT USE OF TABLESPACE NNC_INDEX01 TO USER db2inst1 WITH GRANT OPTION;
GRANT USE OF TABLESPACE NNC_INDEX02 TO USER db2inst1 WITH GRANT OPTION;
GRANT USE OF TABLESPACE NNC_INDEX03 TO USER db2inst1 WITH GRANT OPTION;
GRANT USE OF TABLESPACE USERTEMP TO USER db2inst1 WITH GRANT OPTION;
CONNECT RESET;
----------------------------------------------------------------------------------------------------
需要特别注意，本版支持的是DB2-97版本，在建库时，务必确保DB2_COMPATIBILITY_VECTOR变量为空，设置方式为：
db2set DB2_COMPATIBILITY_VECTOR=
db2stop force
db2start
[返回页首]
数据库临时表空间要求
对于不同数据库用户注意调整临时表空间大小。
数据库临时表空间要求
目录
----------------------------------------------------------------------------------------------------
DB2数据库
SQL Server数据库
Oracle数据库
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
● DB2数据库
在安装前对当前NC使用的数据库手工创建用户临时表空间，临时表空间名称没有约束。管理模式
为系统管理示例脚本如下：
CREATE USER TEMPORARY TABLESPACE USERTEMP01 PAGESIZE 4K MANAGED BY SYSTEM USING ('D:\db2\NODE0000\usertemp01') EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 16 TRANSFERRATE 0.9 BUFFERPOOL IBMDEFAULTBP
建立完成表空间后，需要配置连接该数据库的用户对用户临时表空间使用授权。示例脚本如下：
GRANT USE OF TABLESPACE TEMPSPACE1 TO USER TEST WITH GRANT OPTION
以上脚本均为参考脚本，在具体使用中需要根据实际情况修改表空间的名称、数据文件存储的位置、
大小及授权用户的名称等。
----------------------------------------------------------------------------------------------------
● SQL Server数据库
NC应用数据库是SQL Server数据库时，NC使用tempdb数据库作临时表数据库，不需要另建。
对于tempdb数据库，用户可以根据实际应用存储位置；对其大小要求，预调整到300M，文件增长设置为
自动增长，文件增长不受限制；用户规模更多时，建议调整更大些。下面是通过脚本调整临时表空间大小
的例子。
declare @name char(50)
declare @sql nvarchar(500)
select @name=rtrim(name) from tempdb.dbo.sysfiles where status & 0x40 != 0x40
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论