oracle xe tips

1. using sqlplus with no login name
   sqlplus /nolog
  
   Should set $ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server

2. startup db

   sqlplus /nolog
   connect / as sysdba
   startup
   exit

3. shutdown db
  
   sqlplus /nolog
   connect / as sysdba
   shutdown immediate
   exit

4. enabling remote http connection

   EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);

5. change password:

   ALTER USER SYS IDENTIFIED BY newpassword;
   ALTER USER SYSTEM IDENTIFIED BY newpassword;

6. connect remote oracle server with sqlplus

   connect sys/123@10.1.1.8/XE as sysdba

7. data file type:
root@fs:/usr/lib/oracle/xe/oradata/XE# ls -ltr
total 1078224
-rw-r----- 1 oracle dba 104865792 2007-08-12 16:07 users.dbf
-rw-r----- 1 oracle dba  20979712 2007-08-12 16:45 temp.dbf
-rw-r----- 1 oracle dba 450895872 2007-08-12 22:30 sysaux.dbf
-rw-r----- 1 oracle dba 356524032 2007-08-12 22:38 system.dbf
-rw-r----- 1 oracle dba 183508992 2007-08-12 22:39 undo.dbf
-rw-r----- 1 oracle dba   7061504 2007-08-12 22:46 control.dbf
 
SYSTEM

This tablespace is automatically created when Oracle Database XE is installed. It contains the data dictionary, which is the central set of tables and views used as a read-only reference for the database. It also contains various tables and views that contain administrative information about the database. These are all contained in the SYS schema, and can be accessed only by user SYS or other administrative users with the required privilege.

SYSAUX

This is an auxiliary tablespace to the SYSTEM tablespace, and is also automatically created upon installation. Some database components and products use this tablespace. The HR sample schema is also stored in the SYSAUX tablespace.

TEMP

This tablespace stores temporary data generated when processing SQL statements. For example, this tablespace is used for sort work space. The TEMP tablespace is specified as the default temporary tablespace for every user.

UNDO

This is the tablespace used by the database to store undo information.

USERS

This tablespace is used to store permanent user objects and data. In Oracle Database XE, USERS is the assigned default tablespace for all users except the SYS user, which has the default permanent tablespace of SYSTEM.

8. create your own db;

http://fat-penguin.mocasting.com/p/111421#ManualConfig

  SQL> startup nomount

# 建立數據庫

  SQL> create database “newxe”
      1  maxinstances 1
      2  maxloghistory 1
      3  maxlogfiles 3
      4  maxlogmembers 3
      5  maxdatafiles 50
      6  datafile
      7  '/usr/lib/oracle/xe/oradata/NEWXE/system01.dbf'
      8  size 300m
      9  autoextend on
     10  extend management local  9  sysaux datafile
     11  '/usr/lib/oracle/xe/oradata/NEWXE/sysaux01.dbf'
     12  size 120m
     13  autoextend on
     14  smallfile default temporary tablespace temp
     15  tempfile
     16  '/usr/lib/oracle/xe/oradata/NEWXE/temp01.dbf'
     17  size 20m
     18  autoextend on
     19  smallfile undo tablespace undo
     20  datafile
     21  '/usr/lib/oracle/xe/oradata/NEWXE/undo01.dbf'
     22  size 200m
     23  autoextend on
     24  character set al32utf8
     25  national character set al16utf16
     26  logfile group 1 size 50m,
     27  group 2 size 50m
     28  user sys identified by [sys_password]
     29  user system identified by [system_password];

# 建立 Data Dictionary，執行以下 SQL 檔

    @?/rdbms/admin/catalog.sql
    @?/rdbms/admin/catproc.sql

9. temp file missing:

SQL> connect / as sysdba;
已连接。
SQL> select name from v$tempfile;

未选定行

SQL> alter tablespace temp add tempfile '/usr/lib/oracle/xe/oradata/XE/temp.dbf' size 10M
  2  /

表空间已更改。

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/usr/lib/oracle/xe/oradata/XE/temp.dbf
9. temp file missing:

SQL> connect / as sysdba;
已连接。
SQL> select name from v$tempfile;

未选定行

SQL> alter tablespace temp add tempfile '/usr/lib/oracle/xe/oradata/XE/temp.dbf' size 10M
  2  /

表空间已更改。

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/usr/lib/oracle/xe/oradata/XE/temp.dbf

10. ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
 
SQL> connect / as sysdba;
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
 
Total System Global Area  235999352 bytes
Fixed Size                   450680 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
shutdown immeDatabase mounted.
           
SQL> 
SQL> Recover database using backup controlfile until cancel;
ORA-00279: change 31499022 generated at 03/07/2004 12:13:55 needed for thread 1
ORA-00289: suggestion : /var/oracle9i/oradata/coredata/arch/log_1_140.arc
ORA-00280: change 31499022 for thread 1 is in sequence #140
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL <--- input
Media recovery cancelled.
SQL> alter database open resetlogs;
 
Database altered.

11. create web console;

SQL> begin
  2  dbms_xdb.sethttpport('8080');
  3  end;
  4  /

PL/SQL 过程已成功完成。

12. create user:

CREATE USER TAD IDENTIFIED BY "123" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

GRANT DBA TO TAD;