- 浏览: 98582 次
- 性别:
- 来自: 北京
文章分类
最新评论
第一章:日志管理
1.forcing log switches
sql> alter system switch logfile;
2.forcing checkpoints
sql> alter system checkpoint;
3.adding online redo log groups
sql> alter database add logfile [group 4]
sql> (''/disk3/log4a.rdo'',''/disk4/log4b.rdo'') size 1m;
4.adding online redo log members
sql> alter database add logfile member
sql> ''/disk3/log1b.rdo'' to group 1,
sql> ''/disk4/log2b.rdo'' to group 2;
5.changes the name of the online redo logfile
sql> alter database rename file ''c:/oracle/oradata/oradb/redo01.log''
sql> to ''c:/oracle/oradata/redo01.log'';
6.drop online redo log groups
sql> alter database drop logfile group 3;
7.drop online redo log members
sql> alter database drop logfile member ''c:/oracle/oradata/redo01.log'';
8.clearing online redo log files
sql> alter database clear [unarchived] logfile ''c:/oracle/log2a.rdo'';
9.using logminer analyzing redo logfiles
a. in the init.ora specify utl_file_dir = '' ''
b. sql> execute dbms_logmnr_d.build(''oradb.ora'',''c:\oracle\oradb\log'');
c. sql> execute dbms_logmnr_add_logfile(''c:\oracle\oradata\oradb\redo01.log'',
sql> dbms_logmnr.new);
d. sql> execute dbms_logmnr.add_logfile(''c:\oracle\oradata\oradb\redo02.log'',
sql> dbms_logmnr.addfile);
e. sql> execute dbms_logmnr.start_logmnr(dictfilename=>''c:\oracle\oradb\log\oradb.ora'');
f. sql> select * from v$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameters
sql> v$logmnr_logs);
g. sql> execute dbms_logmnr.end_logmnr;
第二章:表空间管理
1.create tablespaces
sql> create tablespace tablespace_name datafile ''c:\oracle\oradata\file1.dbf'' size 100m,
sql> ''c:\oracle\oradata\file2.dbf'' size 100m minimum extent 550k [logging/nologging]
sql> default storage (initial 500k next 500k maxextents 500 pctinccease 0)
sql> [online/offline] [permanent/temporary] [extent_management_clause]
2.locally managed tablespace
sql> create tablespace user_data datafile ''c:\oracle\oradata\user_data01.dbf''
sql> size 500m extent management local uniform size 10m;
3.temporary tablespace
sql> create temporary tablespace temp tempfile ''c:\oracle\oradata\temp01.dbf''
sql> size 500m extent management local uniform size 10m;
4.change the storage setting
sql> alter tablespace app_data minimum extent 2m;
sql> alter tablespace app_data default storage(initial 2m next 2m maxextents 999);
5.taking tablespace offline or online
sql> alter tablespace app_data offline;
sql> alter tablespace app_data online;
6.read_only tablespace
sql> alter tablespace app_data read only|write;
7.droping tablespace
sql> drop tablespace app_data including contents;
8.enableing automatic extension of data files
sql> alter tablespace app_data add datafile ''c:\oracle\oradata\app_data01.dbf''size 200m
sql> autoextend on next 10m maxsize 500m;
9.change the size fo data files manually
sql> alter database datafile ''c:\oracle\oradata\app_data.dbf''resize 200m;
10.moving data files: alter tablespace
sql> alter tablespace app_data rename datafile ''c:\oracle\oradata\app_data.dbf''
sql> to ''c:\oracle\app_data.dbf'';
11.moving data files:alter database
sql> alter database rename file ''c:\oracle\oradata\app_data.dbf''
sql> to ''c:\oracle\app_data.dbf'';
第三章:表
1.create a table
sql> create table table_name (column datatype,column datatype]....)
sql> tablespace tablespace_name [pctfree integer] [pctused integer]
sql> [initrans integer] [maxtrans integer]
sql> storage(initial 200k next 200k pctincrease 0 maxextents 50)
sql> [logging|nologging] [cache|nocache]
2.copy an existing table
sql> create table table_name [logging|nologging] as subquery
3.create temporary table
sql> create global temporary table xay_temp as select * from xay;
on commit preserve rows/on commit delete rows
4.pctfree = (average row size - initial row size) *100 /average row size
pctused = 100-pctfree- (average row size*100/available data space)
5.change storage and block utilization parameter
sql> alter table table_name pctfree=30 pctused=50 storage(next 500k
sql> minextents 2 maxextents 100);
6.manually allocating extents
sql> alter table table_name allocate extent(size 500k datafile ''c:/oracle/data.dbf'');
7.move tablespace
sql> alter table employee move tablespace users;
8.deallocate of unused space
sql> alter table table_name deallocate unused [keep integer]
9.truncate a table
sql> truncate table table_name;
10.drop a table
sql> drop table table_name [cascade constraints];
11.drop a column
sql> alter table table_name drop column comments cascade constraints checkpoint 1000;
alter table table_name drop columns continue;
12.mark a column as unused
sql> alter table table_name set unused column comments cascade constraints;
alter table table_name drop unused columns checkpoint 1000;
alter table orders drop columns continue checkpoint 1000
data_dictionary : dba_unused_col_tabs
1.forcing log switches
sql> alter system switch logfile;
2.forcing checkpoints
sql> alter system checkpoint;
3.adding online redo log groups
sql> alter database add logfile [group 4]
sql> (''/disk3/log4a.rdo'',''/disk4/log4b.rdo'') size 1m;
4.adding online redo log members
sql> alter database add logfile member
sql> ''/disk3/log1b.rdo'' to group 1,
sql> ''/disk4/log2b.rdo'' to group 2;
5.changes the name of the online redo logfile
sql> alter database rename file ''c:/oracle/oradata/oradb/redo01.log''
sql> to ''c:/oracle/oradata/redo01.log'';
6.drop online redo log groups
sql> alter database drop logfile group 3;
7.drop online redo log members
sql> alter database drop logfile member ''c:/oracle/oradata/redo01.log'';
8.clearing online redo log files
sql> alter database clear [unarchived] logfile ''c:/oracle/log2a.rdo'';
9.using logminer analyzing redo logfiles
a. in the init.ora specify utl_file_dir = '' ''
b. sql> execute dbms_logmnr_d.build(''oradb.ora'',''c:\oracle\oradb\log'');
c. sql> execute dbms_logmnr_add_logfile(''c:\oracle\oradata\oradb\redo01.log'',
sql> dbms_logmnr.new);
d. sql> execute dbms_logmnr.add_logfile(''c:\oracle\oradata\oradb\redo02.log'',
sql> dbms_logmnr.addfile);
e. sql> execute dbms_logmnr.start_logmnr(dictfilename=>''c:\oracle\oradb\log\oradb.ora'');
f. sql> select * from v$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameters
sql> v$logmnr_logs);
g. sql> execute dbms_logmnr.end_logmnr;
第二章:表空间管理
1.create tablespaces
sql> create tablespace tablespace_name datafile ''c:\oracle\oradata\file1.dbf'' size 100m,
sql> ''c:\oracle\oradata\file2.dbf'' size 100m minimum extent 550k [logging/nologging]
sql> default storage (initial 500k next 500k maxextents 500 pctinccease 0)
sql> [online/offline] [permanent/temporary] [extent_management_clause]
2.locally managed tablespace
sql> create tablespace user_data datafile ''c:\oracle\oradata\user_data01.dbf''
sql> size 500m extent management local uniform size 10m;
3.temporary tablespace
sql> create temporary tablespace temp tempfile ''c:\oracle\oradata\temp01.dbf''
sql> size 500m extent management local uniform size 10m;
4.change the storage setting
sql> alter tablespace app_data minimum extent 2m;
sql> alter tablespace app_data default storage(initial 2m next 2m maxextents 999);
5.taking tablespace offline or online
sql> alter tablespace app_data offline;
sql> alter tablespace app_data online;
6.read_only tablespace
sql> alter tablespace app_data read only|write;
7.droping tablespace
sql> drop tablespace app_data including contents;
8.enableing automatic extension of data files
sql> alter tablespace app_data add datafile ''c:\oracle\oradata\app_data01.dbf''size 200m
sql> autoextend on next 10m maxsize 500m;
9.change the size fo data files manually
sql> alter database datafile ''c:\oracle\oradata\app_data.dbf''resize 200m;
10.moving data files: alter tablespace
sql> alter tablespace app_data rename datafile ''c:\oracle\oradata\app_data.dbf''
sql> to ''c:\oracle\app_data.dbf'';
11.moving data files:alter database
sql> alter database rename file ''c:\oracle\oradata\app_data.dbf''
sql> to ''c:\oracle\app_data.dbf'';
第三章:表
1.create a table
sql> create table table_name (column datatype,column datatype]....)
sql> tablespace tablespace_name [pctfree integer] [pctused integer]
sql> [initrans integer] [maxtrans integer]
sql> storage(initial 200k next 200k pctincrease 0 maxextents 50)
sql> [logging|nologging] [cache|nocache]
2.copy an existing table
sql> create table table_name [logging|nologging] as subquery
3.create temporary table
sql> create global temporary table xay_temp as select * from xay;
on commit preserve rows/on commit delete rows
4.pctfree = (average row size - initial row size) *100 /average row size
pctused = 100-pctfree- (average row size*100/available data space)
5.change storage and block utilization parameter
sql> alter table table_name pctfree=30 pctused=50 storage(next 500k
sql> minextents 2 maxextents 100);
6.manually allocating extents
sql> alter table table_name allocate extent(size 500k datafile ''c:/oracle/data.dbf'');
7.move tablespace
sql> alter table employee move tablespace users;
8.deallocate of unused space
sql> alter table table_name deallocate unused [keep integer]
9.truncate a table
sql> truncate table table_name;
10.drop a table
sql> drop table table_name [cascade constraints];
11.drop a column
sql> alter table table_name drop column comments cascade constraints checkpoint 1000;
alter table table_name drop columns continue;
12.mark a column as unused
sql> alter table table_name set unused column comments cascade constraints;
alter table table_name drop unused columns checkpoint 1000;
alter table orders drop columns continue checkpoint 1000
data_dictionary : dba_unused_col_tabs
发表评论
-
Oracle体系结构之-连接配置结构
2010-10-29 14:57 666Oracle的连接可以分成两种:一、专用服务器连接结构(De ... -
Oracle体系结构之-Oracle后台进程
2010-10-29 14:56 610... -
Oracle体系结构之-内存结构
2010-10-29 14:55 651一、系统全局区(SGA) 系统全局区的数据被多个用户共享。当 ... -
Oracle体系结构之-逻辑结构
2010-10-29 14:55 657一、Oracle 逻辑结构简介 ... -
Oracle体系结构之-物理结构
2010-10-29 14:53 647一、物理文件的分类 Oracle数据库的物理文件可以分成 ... -
Oracle体系结构之-Oracle中各种名称
2010-10-29 14:49 596一、数据库名数据库名是数据库的“身份证号码”,用于标示一个数 ... -
Oracle RMAN快速入门指南
2010-10-29 14:29 599前言: 这篇文章主要介绍RMAN的常用方法,其中包 ... -
Oracle归档模式的命令及参数说明
2010-10-29 14:28 709Oracle数据库可以运行 ... -
Oracle表的恢复
2010-10-29 14:27 612flashback table与9i的flashbac ... -
Oracle数据库lsner的注册
2010-10-29 14:26 686数据库注册分两种一种是动态注册一种是静态注册。 动态注册 ... -
Oracle锁表、解表
2010-10-29 14:26 837查看被锁的表: select p.spid ... -
Oracle体系结构之-数据库、表空间、实例简介
2010-10-29 14:24 593一、数据库 数据库顾 ... -
oracle 问题精解[常用]
2010-10-29 14:23 669Q1.怎样创建表? Q2.怎样删除表? Q3.怎样创建视图? ... -
oracle常用命令大汇总(五)
2010-10-29 14:21 585第十二章: backup and recovery ... -
oracle常用命令大汇总(四)
2010-10-29 14:20 594第十章:managing privileges 1. ... -
oracle常用命令大汇总(三)
2010-10-29 14:19 593第七章:reorganizing data 1.us ... -
oracle常用命令大汇总(二)
2010-10-29 14:18 556第四章:索引 1.creating function ... -
ORACLE学习笔记-ORACLE(基本命令)
2010-10-29 14:16 568--查看VGA信息: show sga; select ... -
Oracle服务器参数文件-----spfile
2010-10-26 18:32 8721.spfile简介 spfile是oracle ... -
Inside the Oracle SGA Regions
2010-10-26 18:31 656Oracle Tips by Burleson Consult ...
相关推荐
Oracle常用命令大汇总,里面有所有的Oracle常用命令,供所有人下载!
oracle常用命令汇总,一些ORACLE管理工作中经常需要采用到的命令行。可供平常复习或查询之用。
Oracle常用命令大汇总.doc
典藏之作 oracle 常用命令大汇总,oracle中常用命令的集合,便于程序员开发
数据库oracle常用命令,汇总了丰富的sql集合,包括系统管理,资源管理,空间管理,业务管理,数据库优化,日常巡检运维等脚本
最近在学习数据库,从网上找了很多有关的数据库命令总结了一下。相信对初学者一定有帮助的。有oracle 和mysql两种,大家可以结合着学习。
Linux常用命令汇总,用于初学者学习Linux知识 “Hello everybody out there using minix ---- I’m doing a free operating system” 1991.8,网络上出现了一篇以该句开头的一个帖子,发帖人就是一个芬兰的Linus ...
以下是对Oracle中的数据库基本常用命令进行了总结介绍,需要的朋友可以过来参考下
oralce常用的set命令汇总,需要的可以下载看看。
oracle常用的的导入导出命令 包含导出/入表、表结构、数据、方案、整个数据库
汇总所有常用oracle命令,并以简单的例子说明,适合做参考手册
入侵Oracle数据库常用操作命令 82 Oracle数据库优化及其应用程序研究 83 Instance实例和数据库 85 Oracle数据缓冲区内部机制 85 Oracle 9i数据库密码重用规则分析 87 Oracle数据库空间管理方法 91 用SQL*Loader将...
想统一整理Oracle中常用的命令语句,以后遇到,会不断更新此博客中。为了以后方便查询,因此整理此博客中
权限相同的两个oracle用户,其中一个用户导入的数据,另一个用户登录后看不到导入的数据,为什么? 怎么更改mysql的root密码(至少使用两种方法) mysql有哪些引擎,列出并说明之间的差异 mysql如何实现mysql多机...