- 查看oracle表空间大小
select b.file_name 文件名,b.tablespace_name 表空间名,b.bytes/1024/1024 大小M,(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已用M,substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率 from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_name,b.bytes order by b.tablespace_name;
- 查看oracle表的大小
analyze table emp compute statistics;
select num_rows * avg_row_len from user_tables where table_name = 'EMP';
- 增加表空间大小
alter database datafile '/opt/oracle/oradata/orcl/one.dbf' resize 1024m;
这个是扩展到多少的意思,并不是增长这么多!
- 向oracle插入图片
>create table image_lob(t_id varchar2(5) not null,t_image blob not null);
>create or replace directory images as 'c:oracld';f_lob
>create or replace procedure img_insert(tid varchar2,filename varchar2)as
f_lob bfile;
b_lob blob;
begin
insert into image_lob(t_id,t_image)
values(tid,empty_blob()) return t_image into b_lob;
f_lob:=bfilename('images',filename);
dbms_lob.fileopen(f_lob,dbms_lob.file_readonly);
dbms_lob.loadfromfile(b_lob,f_lob);
dbms_lob.getlength(f_lob);
dbms_lob.fileclose(f_lob);
commit;
end;
/
>exec img_insert('1','f_test.jpg');
*************************************************************************
CREATE TABLE IMAGE_LOB (T_ID VARCHAR2 (5) NOT NULL,T_IMAGE BLOB NOT NULL);
CREATE OR REPLACE DIRECTORY IMAGES AS '/home';
CREATE OR REPLACE PROCEDURE IMG_INSERT (TID VARCHAR2,FILENAME VARCHAR2) AS
F_LOB BFILE;
B_LOB BLOB;
BEGIN
INSERT INTO IMAGE_LOB (T_ID, T_IMAGE)
VALUES (TID,EMPTY_BLOB ()) RETURN T_IMAGE INTO B_LOB;
F_LOB:= BFILENAME ('IMAGES', FILENAME);
DBMS_LOB.FILEOPEN (F_LOB, DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADFROMFILE (B_LOB, F_LOB,
DBMS_LOB.GETLENGTH (F_LOB));
DBMS_LOB.FILECLOSE (F_LOB);
COMMIT;
END;
/
EXEC IMG_INSERT('1','redflag64_10g.png');
- 用pl/sql developer远程其他数据库
- oracle在win7的安装
主要遇到一个问题,系统版本问题:
正在检查系统要求
要求的结果:5.0,5.1,5.2,6.0
实际结果:6.1
C:\Users\administartor\Downloads\win32_11gR1_database_1013\database\stage\prereq\db在此位置修改refhost.xml。找到如下片段:
<!--Microsoft Windows Vista-->
<OPERATING_SYSTEM>
<VERSION VALUE="6.0"/>
修改如下:
<!--Microsoft Windows 7-->
<OPERATING_SYSTEM>
<VERSION VALUE="6.1"/> - 查询去除重复记录
select distinct tablespace_name from tabs;
扩展:http://www.myexception.cn/database/499097.html - 查询表空间名称,大小,利用率等
select b.file_name 物理文件名,b.tablespace_name 表空间,b.bytes/1024/1024 大小M,(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率 from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_name,b.bytes order by b.tablespace_name
- 查询所有表空间的名称
select tablespace_name from dba_tablespaces;
- 查看临时表空间
select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
扩展:http://www.blogjava.net/japper/archive/2012/06/28/381721.html - 查看表空间状态和数据文件的状态
表空间:select status,tablespace_name from dba_tablespaces;扩展:http://database.51cto.com/art/200910/158937.htm
数据文件:select status,name from v$datafile; - Oracle表空间和数据文件状态的关系链接
- RMAN的format 链接
- Oracle的介质恢复
链接1 链接2
- 未完待续...........
oracle的常规应用
最新推荐文章于 2024-09-19 10:39:16 发布