oracle資料庫操作

1．建資料表空間

create tablespace CCPBS datafile '/home/oracle/bossdata/ora_data/CCPBS_INDEX01.DBF' size 100m reuse default storage(initial 5000k next 5000k pctincrease 20);

2．建使用者

create user OSUSER identified by OSUSER;//identified by 後面的是密碼，前面的是使用者名稱

3．使用者授權

grant resource,connect,RECOVERY_CATALOG_OWNER to OSUSER ;

grant create table to OSUSER ;

alter user OSUSER quota unlimited ON OSDB;

alter user OSUSER default tablespace OSDB;

4．刪除資料表空間

DROP TABLESPACE TableSpaceName INCLUDING CONTENTS AND DATAFILES;

5．刪除使用者

DROP USER User_Name CASCADE

6．刪除表的注意事項

在刪除一個表中的全部資料時，須使用TRUNCATE TABLE 表名;因為用DROP TABLE，DELETE * FROM 表名時，TABLESPACE資料表空間該表的佔用空間並未釋放，反覆幾次DROP，DELETE操作後，該TABLESPACE上百兆的空間就被耗光了。

7．啟動資料庫

 

/home/channel/>su - oracle        //切換到oracle使用者下
Password:
/home/oracle/>sqlplus /nolog      //連結

SQL*Plus: Release 9.2.0.7.0 - Production on Wed Apr 15 10:11:26 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn /as sysdba         //用dba身份
Connected to an idle instance.
SQL> startup                     //啟動
ORACLE instance started.

Total System Global Area  856758052 bytes
Fixed Size       452388 bytes
Variable Size    318767104 bytes
Database Buffers   536870912 bytes
Redo Buffers       667648 bytes
Database mounted.
Database opened.

SQL> exit                              //推出oracle sqlplus
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
/home/oracle/>lsnrctl start           //啟動監聽

LSNRCTL for Linux: Version 9.2.0.7.0 - Production on 15-APR-2009 10:15:23

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Starting /opt/oracle/product/9.2.0.4/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 9.2.0.7.0 - Production
System parameter file is /opt/oracle/product/9.2.0.4/network/admin/listener.ora
Log messages written to /opt/oracle/product/9.2.0.4/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=usboss)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=usboss)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 9.2.0.7.0 - Production
Start Date                15-APR-2009 10:15:24
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/9.2.0.4/network/admin/listener.ora
Listener Log File         /opt/oracle/product/9.2.0.4/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=usboss)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "ccpbs" has 1 instance(s).
  Instance "ccpbs", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
/home/oracle/>