Oracle Database Operations

1. Create a tablespace

Create tablespace ccpbs datafile '/home/Oracle/bossdata/ora_data/ccpbs_index01.dbf' size 100 m reuse default storage (initial 5000 K next 5000 K pctincrease 20 );

2. Create user

Create user osuser identified by osuser; // The password is followed by the username

3. user authorization

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. Delete tablespace

Drop tablespace tablespacename including contents and datafiles;

5. Delete a user

Drop user user_name Cascade

6. Table deletion considerations

When deleting all data in a table, the truncate table name must be used. Because when the drop table or delete * from table name is used, the occupied space of the table in the tablespace is not released, after repeated drop and delete operations, the hundred megabytes of space on the tablespace will be exhausted.

7. Start the database

/Home/channel/> Su-Oracle // switch to the Oracle user
Password:
/Home/Oracle/> sqlplus/nolog // link

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

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

SQL> Conn/As sysdba // use the DBA identity
Connected to an idle instance.
SQL> startup // start
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 // launch 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 // start the listener

LSNRCTL for Linux: Version 9.2.0.7.0-production on 15-apr-2009 10:15:23

Copyright (c) 1991,200 2, 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/>