****************
TABLESPACE
****************
#查看表空间创建过程
set long 9999
select dbms_metadata.get_ddl('TABLESPACE','EXAMPLE') from dual;
#查询单个表空间的使用率
SELECT sysdate,a.tablespace_name "表空间名",
round(total / 1024 / 1024 / 1024, 3) "表空间大小(GB)",
round(free / 1024 / 1024 / 1024, 3) "表空间剩余大小(GB)",
round((total - free) / 1024 / 1024 / 1024, 3) "表空间使用大小(GB)",
ROUND((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM DBA_DATA_FILES
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
order by 4 DESC;
*****
ASM
*****
BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
SOURCE_DIRECTORY_OBJECT=>'$S_DIR',
SOURCE_FILE_NAME=>'$1',
DESTINATION_DIRECTORY_OBJECT=>'$D_DIR',
DESTINATION_FILE_NAME=>'$1');
END;
ASM文件拷贝到本地
现在使用ASM的用户越来越多了,而ASM最不方便的就是所有的文件都在oracle自己管理的系统里。我们碰到需要将某个文件拷贝出来的时候就比较麻烦,可能要依赖于RMAN,发现有个方法可以用:
登录ORACLE数据库服务器
1 create or replace directory SOURCE_DIR as '+DATADG/sfoss/onlinelog/';
2 create or replace directory ORACLE_DEST as '/tmp/oralog/dest';
3 BEGIN
dbms_file_transfer.copy_file(source_directory_object =>'SOURCE_DIR', source_file_name => 'group_1.257.695065683',destination_directory_object => 'ORACLE_DEST',destination_file_name => 'redo_1.log');
END;
/
这样今后就不怕从ASM里拷贝任何文件出来了
**************************
NLS_CHARACTERCET
**************************
#查看数据库字符集
SQL> select property_name,property_value from database_properties where property_name='NLS_CHARACTERSET';
#查用户语言环境变量和数据库字符集
SQL> select userenv('language') from dual;
Gbk中一个中文占用2个字符,而utf8中一个中文占用3个字符,所以从gbk导入utf8的表字段可能超长,导致出错。
SQLPLUS环境变量也要设置成UTF8
$export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
impdp加“TABBLE_EXISTS_ACTION=TRUNCATE”
***********************
NLS_DATE_FORMAT
***********************
#修改系统日期时间格式
1.在.bash_profile中增加一句:export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
(注意:要生效必须在前面再加一句:export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK或export NLS_LANG=AMERICAN)
2.在SQLPLUS的glogin.sql中增加一句:SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
3.修改当前会话的日期格式:SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
4.修改数据库参数,需要重启生效:SQL> alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;
***********
v$session
***********
SQL> select sid, serial#, username,status
from v$session
where sid in (select blocking_session from v$session);
***********************
v$recovery_file_dest
***********************
SQL> select name,floor(space_limit/1024/1024) "size MB",
seil(space_used/1024/1024) "used MB"
from v$recovery_file_dest order by name;
**********************
v$session_longops
**********************
SQL> select sid,serial#,context,sofar,totalwork,round(sofar/totalwork*100,2) "%_complete" from v$session_longops where opname like 'RMAN%';
-------------------------------------------------------------------------------------------------------
By aaron8219 Chinaunix Blog: http://blog.chinaunix.net/uid/24612962.html
原创内容,转载请注明链接,谢谢!
TABLESPACE
****************
#查看表空间创建过程
set long 9999
select dbms_metadata.get_ddl('TABLESPACE','EXAMPLE') from dual;
#查询单个表空间的使用率
SELECT sysdate,a.tablespace_name "表空间名",
round(total / 1024 / 1024 / 1024, 3) "表空间大小(GB)",
round(free / 1024 / 1024 / 1024, 3) "表空间剩余大小(GB)",
round((total - free) / 1024 / 1024 / 1024, 3) "表空间使用大小(GB)",
ROUND((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM DBA_DATA_FILES
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
order by 4 DESC;
*****
ASM
*****
BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
SOURCE_DIRECTORY_OBJECT=>'$S_DIR',
SOURCE_FILE_NAME=>'$1',
DESTINATION_DIRECTORY_OBJECT=>'$D_DIR',
DESTINATION_FILE_NAME=>'$1');
END;
ASM文件拷贝到本地
现在使用ASM的用户越来越多了,而ASM最不方便的就是所有的文件都在oracle自己管理的系统里。我们碰到需要将某个文件拷贝出来的时候就比较麻烦,可能要依赖于RMAN,发现有个方法可以用:
登录ORACLE数据库服务器
1 create or replace directory SOURCE_DIR as '+DATADG/sfoss/onlinelog/';
2 create or replace directory ORACLE_DEST as '/tmp/oralog/dest';
3 BEGIN
dbms_file_transfer.copy_file(source_directory_object =>'SOURCE_DIR', source_file_name => 'group_1.257.695065683',destination_directory_object => 'ORACLE_DEST',destination_file_name => 'redo_1.log');
END;
/
这样今后就不怕从ASM里拷贝任何文件出来了
**************************
NLS_CHARACTERCET
**************************
#查看数据库字符集
SQL> select property_name,property_value from database_properties where property_name='NLS_CHARACTERSET';
#查用户语言环境变量和数据库字符集
SQL> select userenv('language') from dual;
Gbk中一个中文占用2个字符,而utf8中一个中文占用3个字符,所以从gbk导入utf8的表字段可能超长,导致出错。
SQLPLUS环境变量也要设置成UTF8
$export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
impdp加“TABBLE_EXISTS_ACTION=TRUNCATE”
***********************
NLS_DATE_FORMAT
***********************
#修改系统日期时间格式
1.在.bash_profile中增加一句:export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
(注意:要生效必须在前面再加一句:export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK或export NLS_LANG=AMERICAN)
2.在SQLPLUS的glogin.sql中增加一句:SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
3.修改当前会话的日期格式:SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
4.修改数据库参数,需要重启生效:SQL> alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;
***********
v$session
***********
SQL> select sid, serial#, username,status
from v$session
where sid in (select blocking_session from v$session);
***********************
v$recovery_file_dest
***********************
SQL> select name,floor(space_limit/1024/1024) "size MB",
seil(space_used/1024/1024) "used MB"
from v$recovery_file_dest order by name;
**********************
v$session_longops
**********************
SQL> select sid,serial#,context,sofar,totalwork,round(sofar/totalwork*100,2) "%_complete" from v$session_longops where opname like 'RMAN%';
-------------------------------------------------------------------------------------------------------
By aaron8219 Chinaunix Blog: http://blog.chinaunix.net/uid/24612962.html
原创内容,转载请注明链接,谢谢!
http://blog.csdn.net/aaron8219/article/details/10056055