oracle 基本操作

合集下载

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

一.启动和停止资料库控制

当重新启动机算机后须手工启动资料库和LISTENER

资料库的创建和维护方法是：netca

资料库的启动方法是：在Command Line下：

sqlplus / as sysdba

startup

关闭ORACLE命令

shutdown

shutdown immediate

LISTERNER的启动方法是：

在Command Line下：

lsnrctl start

lsnrctl stop

启动和停止Oracle Enterprise Manager资料库控制:

emctl start dbconsole

emctl stop dbconsole

启动和停止iSQL*Plus:

isqlplusctl start

isqlplusctl stop

常见一些命令

启动Enterprise Manager Console

linux or unix------oemapp console

microsoft windows------oem

创建pfile文件

create pfile='/u01/opt/app/oracle/product/92040/dbs/init.ora' FROM

spfile='/u01/opt/app/oracle/admin/orcl9i/scripts/spfileorcl9i.ora';

创建spfile文件

create spfile='/u01/opt/app/oracle/product/92040/dbs/spfileorcl9i.ora' FROM

pfile='/u01/opt/app/oracle/admin/orcl9i/scripts/init.ora';

创建密码档

/u01/opt/app/oracle/product/92040/bin/orapwd

file=/u01/opt/app/oracle/product/92040/dbs/orapworcl9i password=change_on_install

二.基本函数使用事例

select LOWER('abcdefghijklMN') as "abcd" from dual

select UPPER('abcdefghijklMN') from dual

select INITCAP('abcdefghijklMN') from dual

select CONCAT('abcdefghijklMN','ABCDEFg') from dual

select SUBSTR('abcdefghijklMN',3) from dual

select SUBSTR('abcdefghijklMN',3,8) from dual

select LENGTH('abcdefghijklMN') from dual

select INSTR('abcdefghijklMNaa','a') from dual

select INSTR('abcdefghijklMNaa','a',14) from dual

select INSTR('abcdefghijklMNaa','a',14,1) from dual

select TRIM('a' FROM 'abcdefghijklMNaa') from dual

select REPLACE('abcdefghijklMNaa','abcd','aaaa') from dual select ROUND(198.987762,2) from dual

select TRUNC(198.987762,2) from dual

select MOD(87976,2242) from dual

select sysdate-26 from dual

select TO_DATE('2005-02-21','yyyy/mm/dd') from dual

select LAST_DAY('2005/02/21') from dual

select TO_CHAR(99.987,'$009900.00') from dual

select TO_NUMBER('233233.4433','9999')-2000 from dual select NVL('',0) from dual

三.关于DDL语句的操作

切换用户SQL

connect edgar/edgar

sqlplus / as sysdba

sqlplus sys as sysdba

显示表结构SQL语句

desc table_name

创建表SQL语句

create table a as select * from b

创建TABLESPACE

1.CREATE TABLESPACE D_VSCM_DATA_02

2.conn system/manager

create tablespace test

datafile '/u03/oradata/demo1/data1_01.dbf'

size 5M

AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED

default storage (initial 128K next 1M pctincrease 0)

3.create tablespace data1

datafile '/u03/oradata/demo1/data1_01.dbf'

size 100M

extent management local

segment space management auto;

删除TABLESPACE

1.conn system/manager

2.alter tablespace test offline;

3.drop tablespace test INCLUDING CONTENTS;

创建用户