Oracle10G常用维护语句

创建表空间

CREATE TABLESPACE CPORTAL LOGGING DATAFILE /dev/raw/raw SIZE M REUSE EXTENT MANAGEMENT LOCAL

创建用户

CREATE USER CPORTAL PROFILE DEFAULT IDENTIFIED BY CPORTAL DEFAULT

TABLESPACE CPORTAL TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;

GRANT CONNECT TO CPORTAL;

GRANT RESOURCE TO CPORTAL;

参数修改

ALTER SYSTEM SET sga_target = M SCOPE=SPFILE;

ALTER SYSTEM SET pga_aggregate_target = M SCOPE=SPFILE;

ALTER SYSTEM SET OPEN_CURSORS= SCOPE=SPFILE;

ALTER SYSTEM SET processes= SCOPE=SPFILE;

ALTER SYSTEM SET remote_login_passwordfile = NONE

SCOPE=SPFILE;

create pfile from spfile;

==============================================================

Modify the other oracle parameter!

==============================================================

ALTER DATABASE TEMPFILE /opt/oracle/oradata/cportal/tempdbf RESIZE M;

ALTER DATABASE DATAFILE /opt/oracle/oradata/cportal/undotbsdbf RESIZE M;

ALTER DATABASE DATAFILE /opt/oracle/oradata/cportal/undotbsdbf AUTOEXTEND ON MAXSIZE M

查看表空间名称和大小

select ttablespace_nameround(sum(bytes/(*))) ts_size from dba_tablespaces t dba_data_files d

where ttablespace_name = dtablespace_name

group by ttablespace_name;

查看表空间的使用情况

select sum(bytes)/(*) as free_spacetablespace_name

from dba_free_space

group by tablespace_name;

查看表空间物理文件的名称及大小

select tablespace_name file_id file_name

round(bytes/(*)) total_space

from dba_data_files

order by tablespace_name;

查看数据库库对象

select owner object_type status count(*) count# from all_objects group by owner object_type status;

查看控制文件

select name from v$controlfile

查看日志文件

select member from v$logfile

结束用户会话

alter system kill session SIDSERIAL#;

select sidserial#usernameprogrammachinestatus from v$session;