SQL语句PART11

1. My test: (create and grant the sysdba to a new user by SQL*Plus)
CREATE USER FJTEST1 IDENTIFIED BY JEANJEANFANG;
GRANT SYSDBA TO FJTEST;
REVOKE SYSDBA FROM FJTEST;
CONNECT FJTEST1/JEANJEANFANG AS SYSDBA;
2. Using ORAPWD in windows:
C:/» ORAPWD;
(show help information)
3. to see the password file:
Grammer: ORACLE_BASE/ORACLE_HOME/database» attrib
F:/oracle/ora92/database/attrib
** if want to create a user that can be connect as normal and can create table, you should grant “create session”, “resource” privileges to the user or it cannot connect as normal.
e.g.:
SQL»create user fj identified by fj;
SQL»grant create session to fj;
SQL»grant resource to fj;
Then the user “fj” can be connected as “normal” and the schema belonging to this user will be created, where he/she can create tables.
SQL» select * from user_sys_privs;
Use above statements to check user’s own system privileges.

2．How to create password file using ORAPWD
F:/oracle/ora92/database/ORAPWD FILE=F:/oracle=ora92/database/PWDmydb.ora PASSWORD=oracle ENTRIES=30
F:/oracle/ora92/database/sqlplus /nolog
SQL» connect SYS/oracle AS SYSDBA
ERROR: ORA-01031: insufficient privileges
(如果发生以上错误, 说明在ORACLE中开启了多于一个的数据库,　并且处于ＳＴＡＲＴＵＰ状态．此时要连接必须用:　 connect SYS/oracle＠SID AS SYSDBA, 必须在F:/oracle/ora92/network/admin＼ＴＮＳＮＡＭＥＳ．ＯＲＡ里面定义过，　如果连接的是个别数据库，就不会报告这样的错误)
ＴＮＳＮＡＭＥＳ．ＯＲＡ　定义方式：
FJDB.SIIC.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = fangjin)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = fjdb)
)
)
** or use the following command in dos state:
C: / set oracle_sid = “jeandb”
C:/ sqlplus /nolog
SQL» connect sys/jeandb as sysdba;


3. Ｈow to create an oracle database in windows manually
Step1: setting environment variable: ORACLE_PATH=F:/oracle/ora92;

Step2: making directories: if SID=hellodb
F:/oracle/admin/hellodb/bdump/,
F:/oracle/admin/hellodb/cdump/,
F:/oracle/admin/hellodb/udump/,
F:/oracle/admin/hellodb/pfile/,
F:/oracle/admin/hellodb/create/
F:/oracle/oradata/hellodb/,
Step 3: putting following files into the concerned directories
1) F:/oracle/ora92/database/inithellodb.ora
F:/oracle/admin/hellodb/pfile/inithellodb.ora (for backup)
The contents in inithellodb.ora:
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
###########################################
# Optimizer
###########################################
hash_join_enabled=TRUE
query_rewrite_enabled=FALSE
star_transformation_enabled=FALSE
###########################################
# Instance Identification
###########################################
instance_name=hellodb
###########################################
# Miscellaneous
###########################################
compatible=9.2.0.0.0
###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=EXCLUSIVE
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=25165824
sort_area_size=524288
###########################################
# Database Identification
###########################################
db_domain="siic.com"
db_name=hellodb
###########################################
# File Configuration
###########################################
control_files=("F:/oracle/oradata/hellodb/control01.ctl", "F:/oracle/oradata/hellodb/control02.ctl", "F:/oracle/oradata/hellodb/control03.ctl")
###########################################
# Pools
###########################################
java_pool_size=0
large_pool_size=8388608
shared_pool_size=50331648
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS1
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=F:/oracle/admin/hellodb/bdump
core_dump_dest=F:/oracle/admin/hellodb/cdump
timed_statistics=TRUE
user_dump_dest=F:/oracle/admin/hellodb/udump
###########################################
# Processes and Sessions
###########################################
processes=150
###########################################
# Redo Log and Recovery
###########################################
fast_start_mttr_target=300
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_cache_size=25165824
db_file_multiblock_read_count=16

2) F:/oracle/admin/hellodb/scripts/CreateDB.sql
Contents in this sql script:
connect SYS/hellodb as SYSDBA
set echo on
spool F:/oracle/ora92/assistants/dbca/logs/CreateDB.log
startup nomount pfile="F:/oracle/ora92/database/inithellodb.ora";
CREATE DATABASE hellodb
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE 'F:/oracle/oradata/hellodb/system01.dbf' SIZE 250M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'F:/oracle/oradata/hellodb/temp01.dbf' SIZE 40M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1" DATAFILE 'F:/oracle/oradata/hellodb/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('F:/oracle/oradata/hellodb/redo01.log') SIZE 102400K,
GROUP 2 ('F:/oracle/oradata/hellodb/redo02.log') SIZE 102400K,
GROUP 3 ('F:/oracle/oradata/hellodb/redo03.log') SIZE 102400K;
spool off
exit;

Step 4: create the PWFile for hellodb
F:/oracle/ora92/database/orapwd FILE=F:/oracle/ora92/database/PWDhellodb.ora PASSWORD=hellodb ENTRIES=10

Step 5: create an Oracle database service:
F:/oracle/ora92/database/oradim –NEW –SID hellodb –STARTMODE manual –PFILE “F:/oracle/ora92/database/inithellodb.ora”
F:/oracle/ora92/database/set ORACLE_SID=hellodb
F:/oracle/ora92/database/net start (if OracleServicehellodb is not started, using the following: net start OracleServicehellodb, set Oracle_SID=hellodb)

Step 6: connect to the Oracle database service and execute the CreateDB.sql script to create a database
Connect to all database started, shutdown all instances and databases (optional)
F:/oracle/ora92/database/sqlplus /nolog
SQL»connect sys/hellodb as sysdba;
(CONNECTED)
SQL»@F:/oracle/admin/hellodb/scripts/CreateDB.sql
……
(database has been created)

Step 7: edit the registry key
Regedt32
/HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/HOME0
ORACLE_SID: REG_EXPAND_SZ: hellodb

Step 8: test and execute some sqls
F:/sqlplus /nolog
SQL»connect sys/hellodb@hellodb as sysdba;
SQL»shutdown (when database created, the database instance is startup)
SQL»startup;
SQL»@F:/oracle/admin/hellodb/scripts/CreateDBFiles.sql;
SQL»@F:/oracle/admin/hellodb/scripts/CreateDBCatalog.sql
SQL» @F:/oracle/admin/hellodb/scripts/postDBCreation.sql
…

4. Problems happened:
SQL» connect sys/hellodb as sysdba;
ERROR:
ORA-12560: TNS: 协议适配器错误
This is because the oracle service for database sid has not been started. The way to solve this problem:
F:/oracle/net start (if these is no OracleServicehellodb, )
F:/oracle/net start OracleServicehellodb
F:/oracle/sqlplus /nolog
SQL»connect sys/hellodb as sysdba;
(connected)

5. the way to create SPFile
CREATE SPFILE FROM PFILE=’/u01/oracle/dbs/init.ora’;
CREATE SPFILE=’/u01/oracle/dbs/test_spfile.ora’ FROM PFILE=’/u01/oracle/dbs/test_init.ora’;

6. create tablespace
SQL»CONNECT SYS/hellodb AS SYSDBA;
SQL»STARTUP;
1. SQL» ALTER SYSTEM SET DB_CREATE_FILE_DEST=’/u01/oradata/sample’;
SQL»CREATE TABLESPACE tbs_1;
1. in initialization parameter file:
DB_CREATE_FILES_DEST=’/u01/oradata/sample2’
SQL»CREATE TABLESPACE tbs_2 DATAFILE SIZE 400M AUTOEXTEND OFF;
1. in initialization parameter file:
DB_CREATE_FILE_DEST=’/u01/oradata/sample3’
SQL»CREATE TABLESPACE tbs_3 DATAFILE AUTOEXTEND ON MAXSIZE 800M;
4) SQL»ALTER SYSTEM SET DB_CREATE_FILE_DEST=’/u01/oradata/sample4’;
SQL»CREATE TABLESPACE tbs_4 DATAFILE SIZE 200M, SIZE 200M;

7. create undo tablespace
SQL»CREATE UNDO TABLESPACE undotbs_1;

8. alter tablespace
SQL»ALTER TABLESPACE tbs_1 ADD DATAFILE AUTOEXTEND ON MAXSIZE 800M;

9. create temporary tablespace
SQL»ALTER SYSTEM SET DB_CREATE_FILE_DEST=’/u01/oradata/sample’;
SQL»CREATE TEMPORARY TABLESPACE temptbs_1;

10. Adding New Online Redo Log Files: example
In initialization parameter file:
DB_CREATE_ONLINE_LOG_DEST_1=’/u01/oradata/sample’
DB_CREATE_ONLINE_LOG_DEST_2=’/u02/oradata/sample’
Sql statement:
SQL»ALTER DATABASE ADD LOGFILE

11. V$DATAFIEL or DBA_DATA_FILES.
SQL»ALTER DATABASE RENAME FILE ‘ora_tbs01_ziw3bopb.dbf’ TO ‘tbs0101.dbf’;
SQL»ALTER DATABASE DROP LOGFILE ‘ora_1_wo94n2xi.log’;
SQL»ALTER TABLE emp ALLOCATE EXTEND (DATAFILE ‘ora_tbs1_2ixfh90q.dbf’);

12. DROP TABLESPACE
E.g.:
SQL» drop tablespace test_2 including contents and datafiles;
** all contents and mapped data files are all deleted.

13. Automatic Database Startup at Operating System Start
See your operating system specific oracle documentation
For windows:
1. control panel
control panel -» 管理工具 -» 服务：选择OracleServicehellodb, 设置属性为：自动开始
1. oracle administration assistant for windows
开始 – 程序 – ORACLE，ORA92 – configuration and migration tools -» administration assistant for windows
选择 SID, hellodb: startup / shutdown option: oracle service: automatic in oracle service startup type. Apply and OK

14. placing a database into quiesced state
SQL»connect sys/hellodb as sysdba;
SQL»startup;
SQL»show parameter RESOURCE_MANAGER_PLAN;
Results:
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
resource_manager_plan     string      SYSTEM_PLAN
** if VALUE == null, or “”, then using the following:
SQL»alter system set resource_manager_plan=’SYSTEM_PLAN” scope=spfile sid=’hellodb’;
SQL»shutdown;
SQL»connect sys/hellodb as sysdba;
SQL»startup;
SQL»show parameter RESOURCE_MANAGER_PLAN;
** if VALUE=SYSTEM_PLAN,
SQL»ALTER SYSTEM QUIESCE RESTRICTED;
停顿(Quiescing)一个数据库是一个强大的新特征，使得DBA可以完成一些数据库处于受限模式(restricted mode)才能完成的一些操作。使用这个特征，当以sys或system帐户登陆后，DBA可以执行查询,PL/SQL，和进行其它的一些事务。而所有其它用户的会话都将处于暂停(suspended)的状态，一旦DBA把数据库置回到正常模式，用户的这些会话又将会自动继续运行了。
如果有一些未决的事务需要提交或回滚的话，先前的那条命令将会挂起而等待事务的完成。如想确定是哪些用户的会话没提交或回滚，你可以用如下的语句。
SELECT S.SID,S.SERIAL#,S.MACHINE,S.TERMINAL,S.USERNAME
FROM V$SESSION S WHERE S.SID IN
(SELECT SID FROM V$LOCK WHERE TYPE='TX')
/
查询的结果将会提供充足的信息使你能够要求那些用户提交、回滚或终止他们的事务。更坏一点的情况是你可以杀掉这些会话，会话将被被自动回滚。系统处于停顿状态后，你就可以不受其它用户的干扰进行工作了，完成工作后你可以用如下命令解除这种停顿的状态：
ALTER SYSTEM UNQUIESCE

15. suspending and resuming a database
SQL» ALTER SYSTEM SUSPEND;
System altered
SQL» SELECT DATABASE_STATUS FROM V$INSTANCE;
DATABASE_STATUS
---------
SUSPENDED

SQL» ALTER SYSTEM RESUME;
System altered
SQL» SELECT DATABASE_STATUS FROM V$INSTANCE;
DATABASE_STATUS
---------
ACTIVE

16. create a new tablespace
1. create a new tablespace
SQL» connect sys/hellodb as sysdba;
SQL» startup; (optional)
SQL» create tablespace hellojean1 datafile ‘F:/oracle/oradata/hellodb/hellojean1.dbf’ size 10M;
SQL» select tablespace_name, contents, status from user_tablespace;
SQL» select tablespace_name, file_name, bytes from dba_data_files;
1. alter tablespace datafile
SQL» shutdown;
Copy the F:/oracle/oradata/hellodb/hellojean1.dbf to F:/oracle/oradata/hellodb/hellojean2.dbf
SQL» startup;
SQL» alter tablespace hellojean1 offline;
SQL» alter tablespace hellojean1 rename datafile ‘F:/oracle/oradata/hellodb/hellojean1.dbf’ to ‘F:/oracle/oradata/hellodb/hellojean.dbf’
SQL» alter tablespace hellojean1 online;
1. open a database in mount mode
SQL» startup mount;
SQL» alter database open;
1. create table in tablespace
SQL» create table myjean1 (id number primary key, name varchar2(30)) tablespace hellojean1 storage (initial 6144 next 6144 minextents 1 maxextents 5);
SQL» create table myjean2 tablespace hellojean1 as select * from myjean1;

17. changing the database archiving mode
Exhange database “no archive mode” to “archive mode”.
SQL»SHUTDOWN;
SQL»STARTUP MOUNT;
SQL»ALTER DATABASE ARCHIVELOG;
SQL»SHUTDOWN;
SQL»STARTUP;
SQL»ARCHIVE LOG LIST;

18. enable automatic archiving at instance startup
Example:
Automatically archive the log
1. update F:/oracle/admin/hellodb/pfile/inithellodb.ora
###########################################
# Automatically Archive
###########################################
LOG_ARCHIVE_START = true
LOG_ARCHIVE_DEST_1 = 'LOCATION=F:/oracle/ora92/database/archive'
LOG_ARCHIVE_FORMAT = "%%ORACLE_SID%%%S.ARC"
1. SQL»SHUTDOWN;
2. SQL»CREATE SPFILE=’…’ FROM PFILE=’’;
3. SQL»STARTUP;

19．Change “dedicate” style database to “shared” style database
1) change init.ora
File destination: f:/oracle/admin/jeandb/pfile/init.ora
File content:
Adding following information before #Optimizer
###########################################
# MTS
###########################################
dispatchers="(protocol=TCP)"

###########################################
# Optimizer
###########################################
hash_join_enabled=TRUE
query_rewrite_enabled=FALSE
star_transformation_enabled=FALSE
…..
2) create spfile from pfile
Delete file: f:/oracle/ora92/database/SPFILEjeandb.ora
SQL» connect sys/jeandb as sysdba;
SQL»shutdown;
SQL» create spfile=’f:/oracle/ora92/database/spfilejeandb.ora’ from pfile=’f:/oracle/admin/jeandb/pfile/init.ora’;
3) startup
SQL»startup;
1. to see the dispatcher configuration
SQL» select name, network from v_$dispatcher (windows)
D000 (ADDRESS=(PROTOCAL=TC)(HOST=fangjin.siic.com)(PORT=3205))

19. config static port for shared server
1) change init.ora
File destination: f:/oracle/admin/jeandb/pfile/init.ora
File content:
Adding following information before #Optimizer
###########################################
# MTS
###########################################
dispatchers="(address=(protocol=TCP)(port=5000))"

###########################################
# Optimizer
###########################################
hash_join_enabled=TRUE
query_rewrite_enabled=FALSE
star_transformation_enabled=FALSE
…..
2) stop oracleserviceXXX
F:/net stop oracleservicejeandb
F:/oradim –delete –sid jeandb
F:/oradim –new –sid jeandb –startmode m –pfile “f:/oracle/admin/jeandb/pfile/init.ora’;
F:/set oracle_sid=jeandb;
** delete all information concerned about “jeandb” in listener.ora and tnsname.ora (destination: f:/oracle/ora92/network/admin/)
F:/net start oracleservicejeandb
3) connect oracle and create spfile
SQL» connect sys/jeandb as sysdba;
SQL»shutdown;
SQL» create spfile=’f:/oracle/ora92/database/spfilejeandb.ora’ from pfile=’f:/oracle/admin/jeandb/pfile/init.ora’;
SQL»startup;
SQL»select * from v_$dispatcher;
Network
(address=(protocol=tcp)(host=fangjin.siic.com)(port=5000))
1. put concered “jeandb” information in listener.ora and tnsname.ora
(in listener.ora)
# LISTENER.ORA Network Configuration File: F:/oracle/ora92/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = fangjin)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
……
(SID_DESC =
(GLOBAL_DBNAME = fangjin.siic.com)
(ORACLE_HOME = F:/oracle/ora92)
(SID_NAME = jeandb)
)
)
(in tnsnames.ora)
JEANDB.SIIC.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = fangjin)(PORT = 5000))
)
(CONNECT_DATA =
(SID = jeandb)
(SERVER=shared)
)
)
1. to see the results:
SQL» connect user1/user1@jeandb;
(connected)

20. enabled trace file
SQL»alter session set sql_trace true;
** then you can check trace and alert files in background_dump_dest, user_dump_dest, in here is f:/oracle/admin/jeandb/bdump/, and f:/oracle/admin/jeandb/udump/

21. terminate sessions
SQL»connect sys/jeandb@jeandb as sysdba;
SQL»startup;
SQL»select sid, serial#, username, server from v_$session;
SQL»alter system kill session ‘8,8’;

22. change directory for control files
1) shutdown the database
2) copy the control files to new directory or rename the control files
3) update init.ora, set ‘control_files’ parameter to the new path where the new control files are.
4) restart oracleservicejeandb
F:/net stop oracleservicejeandb
F:/oradim –edit –sid jeandb –startmode m –pfile ‘f:/oracle/admin/jeandb/pfile/init.ora’
F:/set oracle_sid=jeandb
F:/net start oracleservicejeandb
F:/sqlplus /nolog
1. restart the database
SQL»connect sys/jeandb as sysdba
SQL»create spfile=’f:/oracle/ora92/database/spfilejeandb.ora’ from pfile=’f:/oracle/admin/jeandb/pfile/init.ora’;
SQL» startup;
SQL»select value from v_$parameter where name=’control_files’;
SQL»select member from v_$logfile;
SQL»select name from v_$datafile;

23. how to use a LogMNR to do the undo and redo operation when system corruption happened.
1). Install the LogMNR.
SQL»connect sys/hellodb as sysdba;
SQL»@f:/oracle/ora92/rdbms/admin/dbmslm.sql;
SQL»select distinct member logfilename from v_$logfile;
…. (shows the redolog file list)
SQL»execute dbms_logmnr.add_logfile(‘f:/oracle/oradata/hellodb/redo01.log’);
SQL»execute dbms_logmnr.add_logfile(‘f:/oracle/oradata/hellodb/redo02.log’);
SQL»execute dbms_logmnr.add_logfile(‘f:/oracle/oradata/hellodb/redo03.log’);
SQL»execute dbms_logmnr.start_logmnr();
SQL»select username, operation from v_$logmnr_contents;
SQ.»execute dbms_lgmnr.build(options=»dbms_logmnr_d.store_in_redo_logs);
** extracting the source date dictionary to the redo logs. And the database must be in archive log mode.
SQL»execute dbms_lgmnr.start_logmnr(options=»dbms_logmnr.dict_from_redo_logs
** store the data dictionary into a flat file. DBA has to specified UTL_FILE_DIR initialization parameter, such as “utl_file_dir=’/tmp’”, or set the path e.g.: dbms_logmnr_d.build(dictionary_filename=»’ora_dict.txt’, dictionary_location=»’/tmp’); if so, then if DBA wants to transfer the .txt file into the database server, he has to use it as follows: dbms_logmnr.start_logmnr(dicfilename=»’/tmp/ora_dict.txt’);

24．Job queue
1. create a package and its included procedure
SQL»connect sys/hellodba as sysdba;
SQL»create or replace package menupackage as
procedure listmenus(id number);
end menupackage;
create or replace package body menupackage as
procedure listmenus(id number) is
menu fj.tbmenu.menuname%type;
begin
select menuname into menu from fj.tbmenu where menuid:=id;
dbms_output.putline(“menu name is “ || menu);
exception
what no_data_found then
dbms_output.putline(“the end”);
end listmenus;
end menupackages;
1. submit a job
SQL» dbms_job.submit(job out binary_integer, what in varchar2, next_date in date default sysdate, interval in varchar2 default ‘null’, no_parse in Boolean default false, instance in binary_integer default any_instance, force in Boolean default false);
SQL» variable jobno number;
begin
dbms_job.submit(:jobno, ‘menupackage.listmenus(1);’, SYSDATE, ‘SYSDATE+(30/(24*60*60))’);
commit;
end;/
SQL» PL/SQL ok
1. select jobs
SQL»print jobno;
SQL»select job, substr(what, 1, 35), next_date, next_second, interval from dba_jobs;
1. remove a job
SQL»dbms_job.remove(1);
1. change a job
SQL»dbms._job.change(job in binary_integer, what in varchar2, next_date in date, interval in varchar2, instance in binary_integer default null, force in Boolean default false);
1. change the definition of the job
SQL»dbms_job.what(job in binary_integer, what in varchar2);
SQL»dbms_job.what(1, ‘menupackage.listAMenu(id);’);
1. set the next execution time for the job
SQL»dbms_job.next_time(job in binary_integer, next_date in date);
SQL»execute dbms_job.next_time(1, sysdate+3);
1. set the intervals of the executed job
SQL»dbms_job.interval(job in binary_integer, interval in varchar2);
e.g.: ‘sysdate+3’ every 3 days
‘sysdate+ 1/48’ every half an hour
‘sysdate + (86400/(24*60*60))’ next day same time as last execution
‘next_day(trunk(sysdate),”Friday”) + 15/24’: every Friday at 3pm.
1. set and reset of the job
if job broken, oracle will not execute the job
SQL»dbms_job.broken(job in binary_integer, broken in Boolean, next_date in default sysdate);
1. force a job to run
SQL»dbms_job.run(job in binary_integer, force in Boolean default false);
SQL»dbms_job.run(1);

25．Create a xmldb database
1) create a database
2) connect as sysdba
3)SQL»select comp_name from dba_registry;
If there is no Oracle XML Database, then you have to install it.
1. create xmldb database (sysdba)
SQL»create tablespace xmldb_ts datafile ‘f:/oracle/oradata/dbtest2/xmldb_ts.dbf’ size 10M autoextend on maxsize unlimited extent management local uniform size 1M;
SQL»alter system set shared_pool_size=80M scope = spfile;
Restart oracle service
SQL»@f:/oracle/ora92/rdbms/admin/catqm xml_password xmldb_ts temp;
1. config the /xdbconfig.xml
declare
v_cfg XMLType;
begin
select updateXML(DBMS_XDB.cfg_get(), ‘/xdbconfig/descendant::ftp-port/text()’, ‘2121’, ‘/xdconfig/descendant::http-port/text()’, ‘8181’) into v_cfg from dual;
DBMS_XDB.cfg_update(v_cfg);
commit;
end; /

26. when you cannot open database:
1) may be the datafiles which has been changed caused the problem:
SQL»connect sys/hellodb as sysdba;
SQL»shutdown;
SQL»startup mount;
SQL»alter database datafile ‘f:/oracle/oradata/hellodb/fjtest1.dbf’ offline;
SQL»alter database datafile ‘f:/oracle/oradata/hellodb/fjtest2.dbf’ offline;
SQL»alter database open;
SQL»alter tablespace fjtes1 offline;
** get rid off all the files from database that may cause the problems.
1. it the database in archive log, and maybe you just need to do the following:
SQL» connect sys/hellodb as sysdba;
SQL» shutdown;
SQL» startup mount;
SQL» recover database;
(** if ok)
SQL» alter database open;

27. create partition table
SQL»create tablespace fjtest1 datafile ‘f:/oracle/oradata/hellodb/fjtest1.dbf’ size 50M extent management local autoallocate;
SQL»create table tb_test5(id number primary key, names varchar2(200)) tablespac
e fjtest1 partition by range(id)(partition id1 values less than (10) tablespace
fjtest1, partition id2 values less than (20) tablespace fjtest1, partition id3 v
alues less than (30) tablespace fjtest1, partition id4 values less than (40) tab
lespace fjtest1, partition id5 values less than (maxvalue) tablespace fjtest1);
SQL»begin
SQL» begin
2 insert into tb_test5 values(11, 'it is 11');
3 insert into tb_test5 values(21, 'it is 21');
4 insert into tb_test5 values(14, 'it is 14');
5 insert into tb_test5 values(17, 'it is 17');
6 insert into tb_test5 values(41, 'it is 41');
7 commit;
8 end;
9 /
SQL» select * from tb_test5 partition (id2) s;

28．Create indexed table
SQL» create table tb_test7(id number primary key, names varchar2(200)) organization index tablespace fjtest1 pctthreshold 20;
SQL» begin
2 insert into tb_test7 values(100, 'it is 100');
3 insert into tb_test7 values(78, 'it is 78');
4 insert into tb_test7 values(38, 'it is 38');
5 commit;
6 end;
7 /
SQL» select * from tb_test7
** or create indexed partitioned table
SQL» create table tb_test8(id number primary key, names varchar2(200)) organizat
ion index tablespace fjtest1 pctthreshold 20 partition by range(id) (partition i
d1 values less than(10) tablespace fjtest1, partition id2 values less than(20) t
ablespace fjtest1, partition id3 values less than(maxvalue));
SQL» begin
2 insert into tb_test8 values(15, 'it is 100');
3 insert into tb_test8 values(25, 'it is 25');
4 insert into tb_test8 values(5, 'it is 5');
5 commit;
6 end;
7 /
SQL» select * from tb_test8 partition (id1) s;

29. create cluster tables (to improve query speed and reduce i/o spaces)
SQL» create cluster emp_dept(deptno number(3)) pctused 80 pctfree 5 size 600 tablespace fjtest1 storage(initial 200k next 300k minextents 2 maxextents 20 pctincrease 33);
SQL» create table emp(id number primary key, empname varchar2(200), deptno number(3)) cluster emp_dept(deptno) tablespace fjtest1;
SQL» create table dept(deptno number(3) primary key, deptname varchar2(200)) cluster emp_dept(deptno) tablespace fjtest1;
SQL» create index ind_emp_dept on cluster emp_dept;
SQL» insert into dept values(1,’dept1’);
……
Commit;
SQL»insert into emp(1,’emp1’, 1);
…
Commit;
SQL» select a.empname, b.deptname from emp a, dept b where a.deptno=b.deptno;