普通的情况下
先关闭监听
lsnrctl
stop
然后
sqlplus /nolog
conn / as sysdba
shutdown immediate
如果此过程长时间hung,可继续如下工作:
alter system checkpoint;
shutdown abort;
startup restrict;
shutdown immediate;

oracle中插入时间

oracle中插入时间2007-08-10, by crrok2002在oracle中建有date类型的字段,插入可以采取如下方法:
如果是小时为:1-12 采取如下格式:yyyy-mm-dd HH:MI:SS
insert into test values(to_date(‘2007-04-15 11:22:40’,’yyyy-mm-dd HH:MI:SS’));
如果是小时为:1-24 采取如下格式:yyyy-mm-dd HH24:MI:SS
insert into test values(to_date(‘2007-04-15 13:22:40’,’yyyy-mm-dd HH24:MI:SS’));

insert into accesslog ( time ) values (to_date(‘[15/feb/2008:12:00:02’,’[dd/Mon/yyyy:HH24:MI:SS’,’NLS_DATE_LANGUAGE = American’))

“ora-01460”的错误,
“ora-01460”的错误,可能是字符集设置跟服务器不一致。可以先在plsqldev中打开sql窗口,执行select userenv(‘language’) from dual,然后就可以得到你要连接的数据库的字符集设置,然后在本地环境变量里设置NLS_LANG=<服务器的字符集>,问题就解决了。

————2008-4-4 16:56———————-

统计表空间

SELECT Total.name “Tablespace Name”,
Free_space, (total_space-Free_space) Used_space, total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name = Total.name ;

–2008-4-4 16:57———————————

找出该表空间对应的数据文件及路径

select * from dba_data_files t
where t.tablespace_name = ‘ARD’

–2008-4-4 16:58———————————

增大数据文件

alter database datafile ‘全路径的数据文件名称’ resize ***M

–2008-4-4 16:59——————————–

增加数据文件

alter tablespace 表空间名称

add datafile ‘全路径的数据文件名称’ ***M

注解:表空间尽量让free百分比保持在10%以上,如果低于10%就增加datafile或者resizedatafile,一般数据文件不要超过2G


查看系统会话状态

select s.sid, s.serial#, s.username,s.status,
p.SPID, s.machine, s.PROGRAM,
sw.EVENT, sql.SQL_TEXT, sql.PLAN_HASH_VALUE
from v$session s,
v$process p,
v$sql sql,
v$session_wait sw
where s.PADDR = p.ADDR
and p.BACKGROUND is null
and s.sid = sw.sid(+)
and s.SQL_ADDRESS = sql.ADDRESS(+)


在Oracle中查看所有的表:

select * from tab/dba_tables/dba_objects/cat

看用户建立的表 :

select table_name from user_tables;
select table_nam from user_tables where user=’’
区别
select table_name from user_tables; //当前用户的表

select table_name from all_tables; //所有用户的表

select table_name from dba_tables; //包括系统表

标题: 修改NLS_LANGUAGE 和 NLS_TERRITORY 的值?
如果用spfile:
ALTER SYSTEM SET nls_language = “SIMPLIFIED CHINESE” SCOPE=SPFILE
ALTER SYSTEM SET nls_territory = “CHINA” SCOPE=SPFILE

否则手动修改init{SID}.ora.文件,重启数据库


建立表空间
create tablespace logdata datafile ‘/data/oracle/oradata/logdata.dbf’ size 500M ;

默认用户名密码system/manager登陆创建新用户:

create user test1 identified by test1;
grant connect,resource to test1;

用test1登陆:

sqlplus test1/test1

—–查看数据文件———————————-
select file#,name from v$datafile ;

    FILE#   NAME

1 1 /data/oracle/oradata/ken/system01.dbf
2 2 /data/oracle/oradata/ken/undotbs01.dbf
3 3 /data/oracle/oradata/ken/sysaux01.dbf
4 4 /data/oracle/oradata/ken/users01.dbf
5 5 /data/oracle/oradata/logdata.dbf

——确认表空间所在数据文件的名称

select d.name from
v$datafile d join v$tablespace t using(ts#)
where t.name = ‘LOGDATA’ ;

SQL> select d.name from
2 v$datafile d join v$tablespace t using(ts#)
3 where t.name = ‘LOGDATA’ ;

NAME

/data/oracle/oradata/logdata.dbf

! mv /data/oracle/oradata/logdata.dbf /data/oracle/oradata/ken/logdata.dbf


//快速更新不可恢复
ALTER TABLESPACE “LOGDATA” NOLOGGING
//生成重做日志可恢复
ALTER TABLESPACE “LOGDATA”LOGGING

———-2009-3-30 14:58——————————-
Oracle数据库里查看表空间使用状况

Oracle表空间的事情状况要经常查看,一般空闲比例过低的时候就应该考虑增大表看空间了。查看方法如下SQL:

方法一:

select dbf.tablespace_name,
dbf.totalspace “总量(M)”,
dbf.totalblocks as 总块数,
dfs.freespace “剩余总量(M)”,
dfs.freeblocks “剩余块数”,
(dfs.freespace / dbf.totalspace) * 100 “空闲比例”
from (select t.tablespace_name,
sum(t.bytes) / 1024 / 1024 totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)

方法二:

SELECT Total.name “Tablespace Name”,
Free_space, (total_space-Free_space) Used_space, total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name = Total.name

文档更新时间: 2019-12-16 06:38   作者:月影鹏鹏