`

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;

分享到:
评论

相关推荐

    OracleXE112安装包

    Oracle XE(Express Edition)是Oracle数据库的一个精简版本,主要针对个人用户、小型企业和开发人员,提供了一个轻量级但功能强大的数据库解决方案。Oracle XE 11.2是Oracle数据库11g第二版的Express Edition,它...

    OracleXEClient 安装包

    Oracle XE Client 安装包

    OracleXE112_Win64.zip

    oracleXE11g数据库安装包windowsX64免费下载,请笑纳。 Oracle Database Express Edition (XE) is a community supported edition of the Oracle Database family. Please go to the Oracle Database XE Community ...

    OracleXE112_Win32.zip oraclexe 2

    OracleXE112_Win32.zip oraclexe 1

    OracleXE112_Win32.zip oraclexe 3

    OracleXE112_Win32.zip oraclexe 3

    Oracle XE 允许连接的用户数不足

    Oracle XE 用户连接数限制解决方案 Oracle XE 是 Oracle 公司推出的免费版本的关系数据库管理系统,虽然免费,但是在用户连接数方面存在限制。默认情况下,Oracle XE 只允许有限数量的用户连接,这可能无法满足多...

    OracleXE112_Win32.zip oraclexe 1

    OracleXE112_Win32.zip oraclexe 1

    OracleXE112_Win32.zip oraclexe 4

    OracleXE112_Win32.zip oraclexe 4

    OracleXE112_Win64_part1

    OracleXE112_Win64 oracle11便携版。 第一部分。

    OracleXE_v11g.2【官方精简版】

    OracleXE_v11g.2【官方精简版】 Oracle使用方法  1、 在Oracle官网上下载系统对应,你所需要的Oracle Database XE程序。

    Oracle Xe 11g

    ### Oracle XE 11g:详尽解析与核心知识点 #### 核心知识点一:Oracle Database Express Edition(XE)11g概览 Oracle XE 11g,即Oracle Database Express Edition 11g Release 2 (11.2),是Oracle公司为满足小型...

    Oracle XE 64位

    Oracle XE,全称为Oracle Database Express Edition,是Oracle公司推出的一款轻量级数据库管理系统,特别适合个人用户、小型企业或开发测试环境使用。它提供了基本的数据库功能,包括SQL支持、事务处理、安全性等,...

    OracleXE112_Win32.zip

    OracleXE112_Win32.zip 是一个包含Oracle数据库11g版本11.2的Windows 32位安装程序的压缩包。Oracle 11g是Oracle公司推出的一款强大的关系型数据库管理系统,适用于中小型企业的数据存储和管理。11.2版本是该系列的...

    oracleXE字符集改成GBK

    Oracle XE十分小巧,安装简单,可供第三方软件开发商部署较小的应用。  不过Oracle XE目前的beta2缺省安装的字符集是WE8MSWIN1252,不是中文字符集,并且不能通过直接运行 alter database character set ZHS16GBK...

    OracleXEClient带sqlplus

    OracleXEClient是Oracle公司推出的一款轻量级的数据库客户端工具,专为简化SQL查询和管理而设计。这个客户端软件特别适合开发人员、DBA(数据库管理员)以及对Oracle数据库进行基本操作的用户。在本文中,我们将深入...

    oracle-xe-11.2.0-1.0.x86_64.rpm.zip

    Oracle XE 11.2.0 是一款轻量级版本的 Oracle 数据库服务器,专为开发和小型生产环境设计。这款数据库系统以其易用性、免费和对资源的低需求而闻名,使得它成为个人开发者和小型企业理想的数据库解决方案。 在描述...

    OracleXE112 + plsqldeve1106+chinese_Win64.zip

    OracleXE112 + plsqldeve1106+chinese_Win64.zip 这个压缩包文件包含了两个重要的组件,OracleXE112 和 plsqldev1106x64,它们都是Oracle数据库相关的工具。OracleXE112 是Oracle数据库的一个精简版,称为Oracle ...

    oracle xe 安装手册

    Oracle Database XE is easy to install. Oracle Database XE provides an Oracle database and tools for managing the database. Oracle Database XE supports the following development environments

Global site tag (gtag.js) - Google Analytics