oracle使用技巧

最新推荐文章于 2023-02-25 11:44:17 发布

iteye_17779

最新推荐文章于 2023-02-25 11:44:17 发布

阅读量92

点赞数

文章标签：数据库

select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null

查询表数量

SELECT    table_name   FROM    all_tables     WHERE     owner='BDCBDC1'  order by table_name;

创建临时表空间

create temporary tablespace user_temp
tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;

创建表空间

create tablespace user_data
logging
datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;

误删恢复

insertintoemp
select*--6分钟前删除的记录
from(select*--6分钟前删除的记录
fromempasoftimestampsysdate-6/1440--6分钟前的原始数据
minus--减去
select*fromemp--现在表中的记录
)

-- USER SQL
CREATE USER test IDENTIFIED BY test
DEFAULT TABLESPACE "BDC"
TEMPORARY TABLESPACE "TEMP";
-- QUOTAS
-- ROLES
GRANT "IMP_FULL_DATABASE" TO test ;
GRANT "CONNECT" TO test ;
GRANT "RESOURCE" TO test ;
GRANT "EXP_FULL_DATABASE" TO test ;
ALTER USER test DEFAULT ROLE "IMP_FULL_DATABASE","CONNECT","RESOURCE","EXP_FULL_DATABASE";
-- SYSTEM PRIVILEGES

show parameter db_block_size ;

SELECT (SELECT MAX(block_id) FROM dba_extents WHERE tablespace_name = 'BDC')*8192/1024/1024 FROM dual;

ALTER DATABASE DATAFILE 'D:\soft\oracle\oradata\orcl\SYSTEM01.DBF' RESIZE 700M;

SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)
FROM DBA_TABLESPACES TS where TS.tablespace_name = 'BDC';