sql script create db

oracle sqlplus脚本建库 [转贴 2007-10-31 15:28:46]    

字号：大 中 小

/******************************************************************/
--查询表空间参数
select tablespace_name,min_extents,max_extents,pct_increase,status from dba_tablespaces;

--查询数据文件信息
--autoextensible数据库已满后是否自动扩展
select tablespace_name,bytes,autoextensible,file_name from dba_data_files;

/******************************************************************/
--创建表空间

--    一般信息
--        DATAFILE：数据文件目录
--            存储
--                AUTOEXTEND：数据文件满后自动扩展
--                    ON NEXT：增量
--                    MAXSIZE  UNLIMITED：最大容量无限制
--        SIZE：文件大小
--    存储
--        启用事件记录：LOGGING为生成从做日志并可恢复，NOLOGGING为快速更新不生成日志且不可恢复
--        MANAGEMENT LOCAL：本地管理
--            缺省：自动分配
--            UNIFORM SIZE：统一分配
--        MANAGEMENT DICTIONARY：在字典中管理
--            DEFAULT STORAGE：覆盖默认区值
--                INITIAL：初始大小
--                NEXT ：下一个大小
--                MINEXTENTS：最小数量
--                MAXEXTENTS UNLIMITED ：最大数量不限制
--                PCTINCREASE：增量，单位"%"
--                MINIMUM EXTENT：最小区大小
CREATE TABLESPACE "TEST"
    NOLOGGING
    DATAFILE 'G:\ORACLE\ORADATA\MYORACLE\TEST.ora' SIZE 5M REUSE
    AUTOEXTEND ON NEXT  1M MAXSIZE  UNLIMITED EXTENT
    MANAGEMENT LOCAL UNIFORM SIZE 12K;
--最好写成相对路径，免得出错
CREATE TABLESPACE "TEST"
    NOLOGGING
    DATAFILE '../DATABASE/TEST.ora' SIZE 5M REUSE --建议用'../oradata/TEST.ora'
    AUTOEXTEND ON NEXT  1M MAXSIZE  UNLIMITED EXTENT
    MANAGEMENT LOCAL UNIFORM SIZE 12K;
CREATE TABLESPACE "TEST"
    LOGGING
    DATAFILE 'G:\ORACLE\ORADATA\MYORACLE\TEST.ora' SIZE 5M EXTENT
    MANAGEMENT DICTIONARY DEFAULT STORAGE ( INITIAL 1K NEXT 2K
    MINEXTENTS 5 MAXEXTENTS 67 PCTINCREASE 4 ) MINIMUM EXTENT 3K;

/******************************************************************/
--增加表空间, 注意这里test.ora1不能与原表空间文件名称相同
--添加一个新的大小为5M数据库文件test.ora1
alter tablespace mytesttablespace add datafile 'c:\test\test.ora1' size 5M;

/******************************************************************/
--修改表空间数据库文件属性，myoracle为sid
--将test.ora1数据库文件改为3M，其中resize可以是ONLINE, OFFLINE, RESIZE, AUTOEXTEND 或 END/DROP
alter database myoracle datafile 'c:\test\test.ora1' resize 3M;
alter database myoracle datafile '$ORACLE_HOME/oradata/undo102.dbf' autoextend on next 10m maxsize 500M;

/******************************************************************/
/*删除表空间
语法：drop tablespace tablespace_name including contents and datafiles;
删除表空间时要系统不会删除表空间数据库文件，要彻底删除要手动删除
*/
drop tablespace mytesttablespace including contents and datafiles;

/******************************************************************/
--创建用户
--命令：
--CREATE USER 名称 IDENTIFIED BY 口令 DEFAULT TABLESPACE "默认表空间名" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK
--    一般属性
--    DEFAULT TABLESPACE ：默认表空间名
--    TEMPORARY TABLESPACE ：临时表空间名，默认TEMP
--    ACCOUNT ：用户状态，默认UNLOCK 未锁定；LOCK 锁定
CREATE USER "TEST"  PROFILE "DEFAULT"
    IDENTIFIED BY "test" DEFAULT TABLESPACE "TEST"
    TEMPORARY TABLESPACE "TEMP"
    ACCOUNT UNLOCK;
--可以写成
CREATE USER "TEST"  PROFILE "DEFAULT"
    IDENTIFIED BY "test" DEFAULT TABLESPACE "TEST" ;

/******************************************************************/
--用户授权
--grant "connect,resource,dba" to "someuser" with admin option;
--    WITH ADMIN OPTION ：管理选项
--授予usertest DBA权限
grant dba to "usertest" with admin option;
--取消授权
--REVOKE "RESOURCE" FROM "SCOTT";

/******************************************************************/
--建表
--在usertest方案下建表,注意表名不能用关键字，否则报错ORA-00903: 表名无效
--语法：
--      create table [schema.]<table_name>(
--      <column_name><data type>[default <expression>]<constraint>[,]
--      .......
--      )TABLESPACE TABLESPACE_NAME;
--schema:表示所属的用户名称或模式名称亦即方案
--table_name:表示新建表的名称
--column_name：表示字段名称
--data type：表示字段的数据类型
--default <expression>：表示字段的默认值
--constraint：表示约束条件
--TABLESPACE_NAME：所用表空间
CREATE TABLE "TEST"."TEST" (
    "ID" NUMBER(10) DEFAULT 0 NOT NULL,
    "NAME" VARCHAR2(20) NOT NULL,
    "INFO" VARCHAR2(1000),
    PRIMARY KEY("ID")
    )TABLESPACE "TEST" ;
--也可以写成
CREATE TABLE "TEST"."TEST" (
    "ID" NUMBER(10) DEFAULT 0 PRIMARY KEY,
    "NAME" VARCHAR2(20) NOT NULL,
    "INFO" VARCHAR2(1000)
    )TABLESPACE "TEST" ;

/******************************************************************/
--插入数据
--语法：
--     INSERT INTO tablename (column1,column2,…) VALUES (expression1,expression2,…);
INSERT INTO "TEST"."TEST" ("ID" ,"NAME" ,"INFO" ) VALUES (1 ,'testname' ,''  );
--也可以写成
INSERT INTO "TEST"."TEST" VALUES (1 ,'testname' ,''  );

/******************************************************************/

 

Oracle Basic DBA

http://thomas.eibner.dk/oracle/basicdba/

Basic DBA

• Introduction

• Starting on a newly installed system

• Creation of a tablespace

• Creation of a user

• Granting rights

• Altering quota on tablespaces

• Deleting a user

• Resources

  ---

  Introduction

  This document is intended to be a help for those new to Oracle. The primary intention with this little document is to help people getting started on creating users and tablespaces in their newly installed Oracle database. It's not intended to be a document on how to install the database, since there's already a few of those out there.
  
  Any use of this material is on own risk. There's no guarantee that any of the things described in this document works. The experiences this document describes where made on an Oracle 8.0.5.0.0 database installed on a Debian GNU/Linux System.
  
  

• Starting on a newly installed system

  When you start of on a newly installed system you will need to create a user that will be accessing the database. For security reasons you should not be using the system user nor the system tablespace. In the next sections you will learn how to create users and tablespaces for them to use.
  
  

• Creation of tablespace

  What is a tablespace and why do I need one? A tablespace is where Oracle allocates space for your database. Without this you cannot create tables or store data.
  To see the Oracle documentation for this operation type 'help create tablespace' on the sqlplus prompt 'SQL>'.
  What you basically need to know to get going is how to create a simple tablespace.

  CREATE TABLESPACE tablespace
  DATAFILE datafile
  SIZE size
    [ AUTOEXTEND ON NEXT size [M] 
      [MAXSIZE size [M]]
    ]
  

  With this simplified structure of the command we can build a small statment for creating a small test-tablespace.

  SQL> CREATE TABLESPACE test 
         DATAFILE '/path/to/oracle/oradata/SID/test.dbf' 
         SIZE 10M;
  

  Our first tablespace will be able to hold roughly 10 MB of data. You might ask yourself what will happen if you try to store more data than the tablespace will hold? Then Oracle will give you an error and not be able to store the data you're trying insert. You can either add more datafiles or alter the tablespace if you have created the tablespace already. But it's better to be prepared and make the tablespace more extensible. The next example will show just how to do that.
  

  SQL> CREATE TABLESPACE test 
         DATAFILE '/path/to/oracle/oradata/SID/test01.dbf' 
         SIZE 10M 
         AUTOEXTEND ON NEXT 10M
         MAXSIZE 100M;
  

  The datafile I used in this example is different from the one in the earlier example in the way that it names the datafile as tablespace followed by a number followed by the fileextension. This practice makes it a lot easier figuring out how you created the tablespace and a better scheme for adding new datafiles. The example set a maximum size of 100 MB since we don't want the database being able to consume all available space on the disksystem.
  
  

• Creation of a user

  To be able to connect to an Oracle database you need to create a user. A user can have different rights depending on what the user should be privilegded to do. It's generally a very good idea to create a user to not screw anything up on the database. To see the Oracle documentation for creating a user type 'help create user' on the sqlplus prompt.
  From the Oracle documentation we can derive the next example.
  

  SQL> CREATE USER test 
         IDENTIFIED BY passwd 
         DEFAULT TABLESPACE test 
         TEMPORARY TABLESPAC temp;
  

  This will create a user test which has the password passwd and with test as the default tablespace, temp is an Oracle temporary tablespace.
  
  

• Granting rights

  Without any rights, the newly created user won't even be able to log on to the Oracle database. Among others creating tables is also a very important privilegde to have. Typing 'help grant' will only refer you to the Oracle server reference so I'll give a quick rundown of the important privilegies.
  

  SQL> GRANT CREATE SESSION TO test;
  

  The session priviledge will allow the user to connect to the database.
  

  SQL> GRANT CREATE TABLE TO test;
  

  The table priviledge will allow the user to create tables in the database.
  

  SQL> GRANT CREATE VIEW TO test;
  

  The view priviledge will allow the user to create views of tables in the database.
  

  SQL> GRANT CREATE SEQUENCE TO test;
  

  The sequence priviledge will alow the user to create sequences for making unique ids for his tables.
  
  

• Altering quota on tablespaces

  This must be all then, right? No, for the user to be able to create tables you should set the quota for the user on the default tablespace. This could have been done in the process of creating the user, but to avoid being to complicated I put this off until now.
  

  SQL> ALTER USER test QUOTA unlimited ON test;
  

  In this example the user would be able to fill up the whole tablespace, which sometimes isn't what you want. Instead you can put a quota in bytes, kilobytes, or megabytes on the user.
  
  

• Deleting a user

  Now that you have messed around with your new user you will probably want to start over fresh and you're asking yourself how to delete what you just created. Again the built-in help of sqlplus is very usefull. So if you try a 'help drop' on the sqlplus prompt you will get a listing of the different options you have.
  

  SQL> DROP USER test CASCADE;
  

  Cascade is the keyword to effectively wipe out everything belonging to the user including, but not limited to, tables, views, and sequences.
  
  

• Resources

  Like with everything else there is tons of help to get on the Internet. The documentation that comes with your Oracle Database server is refenced a lot from the built-in help in sqlplus.
  I haven't found any specific introductions to this subject, but I probably just didn't look hard enough.
  Oracle.com