Oracle常用命令及优化日志（不断更新）

1.超级管理员登录 

 

2.调整BLOB存储方式

3.调整oracle日志文件的大小

查询现有日志文件文件所在组以及存储路径和状态

SQL> select a.group#,a.status,b.member,a.bytes/1024/1024 from v$log a,v$logfile b where a.group#=b.group#;

通过以下SQL语句添加日志组

alter database add logfile group 4 ('d:\oracle\oradata\test\redo04.log') size 1024M;
alter database add logfile group 5 ('d:\oracle\oradata\test\redo05.log') size 1024M;
alter database add logfile group 6 ('d:\oracle\oradata\test\redo05.log') size 1024M;

对status为INACTIVE的日志组执行删除操作

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

可以手动切换日志文件

alter system switch logfile;

4.调整数据库内存大小

备份spfileorcl.ora文件，文件位于$ORACLE_HOME/database目录中

alter system set memory_max_target=92160M scope=spfile;
alter system set sga_max_size=71680M scope=spfile;
alter system set sga_target=XXM;

重启数据库

shutdown immediate;
startup

如果startup启动后报错，将备份的spfileorcl.ora文件恢复回去，并根据启动时候提示的错误重新修改，并重新启动

 

5.查询表的定义

select dbms_metadata.get_ddl('TABLE','table_name','OWNER') from dual;
例：select dbms_metadata.get_ddl('TABLE','GDB_ITEMS','SDE') from dual;（注意大小写）

6.查询空间索引

查询某个用户下的索引

select owner,index_name from dba_indexes where index_type='DOMAIN' and owner ='OWNERNAME';

​​​​​​​例：select index_name,index_type from user_indexes where table_name='GDB_ITEMS';
    select dbms_metadata.get_ddl('INDEX','A1_IX1','SDE') from dual;
    空间索引:index_type='DOMAIN'

 查询索引状态

select status,domidx_status,domidx_opstatus from dba_indexes where owner ='DBSJ' and index_name ='A225_IX1'

 批量执行空间索引删除操作

host notepad dropindex.sql

dropindex.sql内容如下：
drop index DBSJ.A226_IX1;
drop index DBSJ.A225_IX1;
drop index DBSJ.A176_IX1;
drop index DBSJ.A175_IX1;
drop index DBSJ.A174_IX1;
drop index DBSJ.A173_IX1;
drop index DBSJ.A168_IX1;
drop index DBSJ.A164_IX1;
drop index DBSJ.A162_IX1;
drop index DBSJ.A160_IX1;

​​​​​​​spoon off
spoon dropindex.sql

select 'drop index'||owner||'.'||index_name||';' from dba_indexes where owner ='DBSJ' and index_type ='DOMAIN';
select table_name,index_name from dba_indexes where owner ='DBSJ' and index_type='DOMAIN'

set head off

 

7.表空间扩容

--查看表空间大小
SELECT FILE_NAME as 数据文件,TABLESPACE_NAME as 表空间名称,AUTOEXTENSIBLE as 自动扩展,STATUS as 状态,MAXBYTES as 可扩展最大值,USER_BYTES as 已使用大小,INCREMENT_BY as 自动扩展增量 FROM dba_data_files

--扩展空间，将数据文件扩大至5000MB
 alter database datafile 'D:\DataBase\Test.DBF' resize 5000m;

--自动增长，表空间不足时增加200MB，最大扩展5000MB
 alter database datafile 'D:\DataBase\Test.DBF' autoextend on next 200m maxsize 5000m;  

--扩展无限大空间
alter database DATAFILE 'D:\DataBase\Test.DBF'  autoextend on maxsize unlimited;  


注意表空间大小限制 
表空间数据文件容量与DB_BLOCK_SIZE有关，在初始建库时，DB_BLOCK_SIZE要根据实际需要，设置为 4K,8K、16K、32K、64K等几种大小，ORACLE的物理文件最大只允许4194304个数据块(由操作系统决定) 。即： 
4k最大表空间为：16384M 
8K最大表空间为：32768M 
16k最大表空间为：65536M 
32K最大表空间为：131072M 
64k最大表空间为：262144M

所以将maxsize设置为unlimited也并非能无限扩展，还受限于区块大小，即blocksize大小，如一般blocksize默认8k，数据文件大小最大也只能扩展到32GB。当然可以设置bigfile（大文件表空间），8k的blocksize表空间文件理论上最大可以扩展32TB，但是实际上受操作系统的文件系统限制。使用大文件表空间（bigfile tablespace）可以大幅度增强Oracle数据库的存储能力，简化数据库管理工作。与此同时，付出的代价是增加备份与恢复的时间。如果是中小型项目，数据量在TB级以内，建议使用小文件表空间。一个小文件表空间（smallfile tablespace）最多可以包含1022个数据文件（datafile）。详细可看Oracle物理文件限制大小表格Physical Database Limits 
添加表空间数据文件sql脚本如下：

--增加数据文件，TestTablespace是这里测试使用的表空间名称
alter tablespace TestTablespace add datafile 'D:\DataBase\Test2.DBF' size 1024m; 

实例：

调整表空间大小

使用户对表空间具有不限制的空间使用权！！！

8.删除用户及表空间

--查看用户和表空间的状况
select username,default_tablespace from dba_users
select tablespace_name,max_size/1024/1024 from dba_tablespaces;

--删除用户和表空间
drop user CTRLPOINT cascade;
drop tablespace TB_CTRLPOINT including contents and datafiles;

9.Oracle数据库保留字查询

select * from v$reserved_words where keyword like 'L%' order by keyword desc;

10.创建用户、表空间并授权

创建表空间

SQL> create tablespace tb_kone datafile 'D:\app\zh\oradata\orcl\tb_kone.dbf'size
 1024m autoextend on next 32m maxsize 2048m;

创建用户

SQL> create user kone identified by kone;
SQL> alter user kone default tablespace tb_kone;

给用户授予权限

GRANT CONNECT TO kone;
GRANT RESOURCE TO kone;
GRANT DBA TO kone;
GRANT UNLIMITED TABLESPACE TO kone;
GRANT create any table TO kone;
GRANT select any table TO kone;
GRANT drop any table TO kone;
GRANT delete any table TO kone;
GRANT insert any table TO kone;
GRANT update any table TO kone;

--Or全部权限
grant all privileges to kone;

提交

11.监听访问及管理

监听访问

启动、关闭

查看端口

12.Oracle连接到空闲例程

1、
 SQL>startup pfile='D:\app\Administrator\admin\agriculture\pfile\init.ora.10182016104423' (其中"init.ora.10182016104423"视具体Oracle安装位置和实例确定）。

2、创建spfile

   SQL>create spfile from 
       pfile='D:\app\Administrator\admin\agriculture\pfile\init.ora.10182016104423';

3、重新启动数据库

   SQL>shutdown immediate
   SQL>startup

13.批量复制GUID值

SQL> update SDE.TEST set guidtest = substr(sys_guid(), 1, 8)||'-'||substr(sys_gu
id(), 9, 4)||'-'||substr(sys_guid(), 13, 4)||'-'||substr(sys_guid(), 17, 4)||'-'
|| substr(sys_guid(), 20, 12);

已更新4行。

SQL> commit;

提交完成。