Create/Drop Database

    版本為 13:15, 30 Nov 2024

    到這個版本。

    返回到 版本存檔.

    查閱目前版本

    Create a Database:

    建立 createDB.sql:

    • 修改 SYS 密碼
    • 修改檔案路徑
    • 修改 redolog size
    CREATE DATABASE win
       USER SYS IDENTIFIED BY mypass
       USER SYSTEM IDENTIFIED BY mypass
       LOGFILE GROUP 1 ('/opt/oracle/oradata/win/redo01.log') SIZE 100M,
               GROUP 2 ('/opt/oracle/oradata/win/redo02.log') SIZE 100M,
               GROUP 3 ('/opt/oracle/oradata/win/redo03.log') SIZE 100M
       MAXLOGFILES 5
       MAXLOGMEMBERS 5
       MAXLOGHISTORY 1
       MAXDATAFILES 100
       MAXINSTANCES 1
       CHARACTER SET AL32UTF8
       NATIONAL CHARACTER SET AL16UTF16
       DATAFILE '/opt/oracle/oradata/win/system01.dbf' SIZE 325M REUSE
       EXTENT MANAGEMENT LOCAL
       SYSAUX DATAFILE '/opt/oracle/oradata/win/sysaux01.dbf' SIZE 325M REUSE
       DEFAULT TEMPORARY TABLESPACE temp
          TEMPFILE '/opt/oracle/oradata/win/temp01.dbf' 
          SIZE 500M REUSE AUTOEXTEND ON MAXSIZE 2048M
       UNDO TABLESPACE undotbs1 
          DATAFILE '/opt/oracle/oradata/win/undotbs01.dbf'
          SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
    

    開始建立

    確認環境變數
    $ echo $ORACLE_SID
    win
    $ echo $ORACLE_HOME
    /opt/oracle/product/10.2.0/db_1
    
    $ sqlplus /nolog
    
    SQL> connect / as sysdba
    SQL> startup nomount
    SQL> @createDB.sql
    

    DB 建立完後,還需要執行幾個 SQL

    SQL> @?/rdbms/admin/catalog.sql
    SQL> @?/rdbms/admin/catproc.sql
    
    SQL> conn system/pass
    SQL> @?/sqlplus/admin/pupbld.sql  
    

    PUPBLD.SQL       Create user profiles,必須用 SYSTEM 執行,執行過程中會出現一些錯誤是正常的;如果沒有執行,一般用戶會無法連線 DB。

    更多其他的 SQL 可參閱 http://docs.oracle.com/cd/B14117_01/...s.htm#REFRN005

     

    Drop a Database:

    $ export ORACLE_SID=<my_SID>
    $ sqlplus / as sysdba
    
    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    
    SQL> startup mount exclusive restrict
    ORACLE instance started.
    
    Total System Global Area  281018368 bytes
    Fixed Size                  2095672 bytes
    Variable Size             109053384 bytes
    Database Buffers          163577856 bytes
    Redo Buffers                6291456 bytes
    Database mounted.
    
    SQL> drop database;
    
    Database dropped.
    
    Powered by MindTouch Core