Oracle总结步骤

Oracle命令

导出 expdp schemas=* dumpfile=*.dmp<br />    导入 impdp schemas=* dumpfile=*.dmp    关闭监听器 lsnrctl stop    启动监听器 lsnrctl start    查看监听器状态 lsnrctl status      进入数据库 sqlplus      以管理员登录 /as sysdba    删除用户 drop user * cascade    创建用户 create user *(用户名) identified by *(密码)     关闭服务 shutdown immediate;    启动服务 startup;<br />![](https://cdn.nlark.com/yuque/0/2021/png/12842387/1624607925523-93485492-fec8-4202-8d99-874757f11c3f.png#)<br />显示表结构 desc *(表名)<br />查看所有用户 select username from dba_users<br />查看数据库名 select  name from v$database;<br />查看数据量大小 select tablespace_name ,sum(bytes) / 1024 / 1024 as MB　from dba_data_files group by tablespace_name;

查看日志记录位置

show parameter dump 最后一个

sqlplus调显示格式

set pagesize 1000; 一页条数
set line 1000; 一行长度
col QUESTION_INFO for a50; 设置字段长度

限制ip访问

打开oracle安装路径下
./dbhome_1/NETWORK/ADMIN/sqlnet.ora文件
增加以下代码
1. tcp.validnode_checking=yes —//(开启ip限制功能)
2. tcp.invited_nodes=(192.168.1.135，192.168.1.156)–(允许访问数据库的ip地址列表，多个IP地址用逗号分开)
3. tcp.excluded_nodes=(192.168.1.123)–(禁止访问数据库的ip地址列表)
然后重启监听即可

ORACLE TNS Listener远程注册投毒漏洞

1.更新OPatch最新版

cd $ORACLE_HOME/OPatch
./opatch lsinventory 显示清单
./opatch version 显示版本

备份原来的Opatch
tar -cvf Opatch_20140627.tar OPatch
关闭实例，关闭监听后
将Opatch更新放到oracle的home下
unzip p6880880_112000_Linux-x86-64.zip
cd OPatch
./opatch lsinventory 显示清单
./opatch version 显示版本

2.打补丁(64位linux)

将补丁放到$ORACLE_HOME下
unzip p12880299_112010_Linux-x86-64.zip
$ORACLE_HOME/OPatch/opatch apply

3.修改监听，重启
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.88)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC=
(GLOBAL_DBNAME = DQ)
(ORACLE_HOME =/opt/oracle/product/OraHome)
(SID_NAME = DQ)
)
)

ADR_BASE_LISTENER = /opt/oracle
SECURE_REGISTER_LISTENER = (TCP)

开启归档

查看oracle归档状态
SQL> archive log list;
关闭数据库
SQL> shutdown immediate;
启动数据库至mount状态
SQL> startup mount;
创建闪回目录
mkdir /u01/app/oracle/flashback/;
设置闪回目录
alter system set log_archive_dest_1=’location=/arp/oraarp/archive’;
启用归档模式
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list;
切换在线日志，验证归档是否正常
SQL> alter system switch logfile;
验证归档日志是否生成
在/u01/app/oracle/flashback/查看，是否生成了dbf文件

闪回

开启闪回
开启归档
设置闪回目录
SQL> alter system set db_recovery_file_dest=’/u01/app/oracle/fast_recovery_area’ scope=both;
设置闪回目录大小
SQL> alter system set db_recovery_file_dest_size=50G scope=both;
设置闪回时间 默认1440分钟(1天),实际取决于闪回区大小
SQL> alter system set db_flashback_retention_target=4320 scope=both;
开启闪回
SQL> alter database flashback on;
确认闪回开启
SQL> select flashback_on from v$database;
闪回查询
select from scott.dept as of timestamp sysdate-10/1440;
select from scott.dept as of timestamp to_timestamp(‘2017-12-14 16:20:00’,’yyyy-mm-dd hh24:mi:ss’);
select * from scott.dept as of scn 16801523;
闪回(update/insert/delete)
flashback table scott.dept to timestamp to_timestamp(‘2017-12-14 16:20:00’,’yyyy-mm-dd hh24:mi:ss’);
闪回drop
flashback table t to before drop;
闪回空间爆满
rman target /
RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;
RMAN> DELETE NOPROMPT ARCHIVELOG UNTIL TIME ‘SYSDATE-7’ ;

慢查询

select *
from (select sa.SQL_TEXT,
sa.SQL_FULLTEXT,
sa.EXECUTIONS “执行次数”,
round(sa.ELAPSED_TIME / 1000000, 2) “总执行时间”,
round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) “平均执行时间”,
sa.COMMAND_TYPE,
sa.PARSING_USER_ID “用户ID”,
u.username “用户名”,
sa.HASH_VALUE
from v$sqlarea sa
left join all_users u
on sa.PARSING_USER_ID = u.user_id
where sa.EXECUTIONS > 0 where u.username=’填写同户名’
order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
where rownum <= 50;#查询的数据数目

多查询

select *
from (select s.SQL_TEXT,
s.EXECUTIONS “执行次数”,
s.PARSING_USER_ID “用户名”,
rank() over(order by EXECUTIONS desc) EXEC_RANK
from v$sql s
left join all_users u
on u.USER_ID = s.PARSING_USER_ID) t
where exec_rank <= 100;

OPatch找不到清单

opatch lsinventory -invPtrLoc /u01/oracle/mc3yd213/db/tech_st/11.1.0/oraInst.loc
（/u01/oracle/mc3yd213/db/tech_st/11.1.0为我数据库的ORACLE_HOME）

share_pool报错

用如下命令修改SGA_MAX_SIZE与SGA_TARGET的值，将大小从1G调整为5G，数据库正常。
alter system set sga_max_size=1G scope=spfile;
alter system set sga_target=1G scope=spfile;

sql记录

select * from v$sqlarea t where t.PARSING_SCHEMA_NAME in (‘WEINTV’) order by t.LAST_ACTIVE_TIME desc;

关闭审计日志

查看审计日志
SQL> show parameter audit_trail
VALUE值为DB，表面审计功能为开启、

关闭oracle的审计功能
SQL> alter system set audit_trail=FALSE scope=spfile;
System altered.
快速删除审计日志：truncate table SYS.AUD$;

监听ORA-00119

SQL> create pfile=’/home/oracle/init.ora’ from spfile;
文件已创建。

*.local_listener=’(ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)))’
设置默认 pfile
从pfile创建spfile
create spfile from pfile=’/home/oracle/init.ora’;
SQL> startup pfile=’/home/oracle/init.ora’;

关闭监听日志

关闭监听器日志：
lsnrctl set log_status off
lsnrctl save_config

share_memory过小

删除oracle报警日志文件，解放空间
alter system flush shared_pool;

target_memory过小

SQL> create pfile=’/home/oracle/init.ora’ from spfile;
文件已创建。

修改c:\tmp\init.ora文件，在memory_target参数后面补一个零即可。

.dispatchers=’(PROTOCOL=TCP) (SERVICE=orclXDB)’
.memory_target=20971520
.nls_language=’SIMPLIFIED CHINESE’
.nls_territory=’CHINA’

设置默认 pfile
从pfile创建spfile
create spfile from pfile=’/home/oracle/init.ora’;
SQL> startup pfile=’/home/oracle/init.ora’;

ORACLE 例程已经启动。
Total System Global Area 209235968 bytes
Fixed Size 1373600 bytes
Variable Size 167774816 bytes
Database Buffers 33554432 bytes
Redo Buffers 6533120 bytes
数据库装载完毕。
数据库已经打开。

查看sharePool

select pool,sum(bytes)/1024/1024 || ‘mb’ from v$sgastat where pool is not null group by pool;

设置shared_pool_reserved_size

alter system set shared_pool_reserved_size=60m scope=spfile;

实例未知

Instance “orcl”, status UNKNOWN, has 1 handler(s) for this service…
SQL> alter system register;

Oracle清除链接

select username,sid,serial# from v$session where username=’RSARS’;

select username,sid,serial# from v$session where username=’TD_TOWN’;
alter system kill session’19,355’;

实例名/服务名

查看实例名 select instance_name from v$instance;
查看服务名 show parameter service_name

memory_target

查看
SQL> show parameter memory
df -Th
修改
umount /dev/shm/
vim /etc/fstab
mount /dev/shm/
查看共享池
sql>SHOW PARAMETERS SHARED_POOL_SIZE；
修改共享池
alter system set shared_pool_size=10M

查看表数量

select count(*) from user_tables;

查看数据库大小

select sum(bytes)/1024/1024/1024 as GB from dba_data_files;

select a.tablespace_name,total,free,total-free used from ( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files group by tablespace_name) a, ( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space group by tablespace_name) b where a.tablespace_name=b.tablespace_name

查看表空间大小

select count(blocks) * 8 /1024 SUM_M from user_tables;
或者
select a.tablespace_name, total, free, total-free as used from
(select tablespace_name, sum(bytes)/1024/1024/1024 as total from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name;

修改表空间

查看索引表空间
select index_name,status from user_indexes where table_name=’S_COMMUNITY_NEWS’;
修改索引表空间
alter index SYS_C0011477 rebuild tablespace USERS;
索引重建
alter index SYS_C0011477 rebuild;

查看clob表空间
select b.tablespace_name,a.column_name from dba_lobs a,dba_tables b
where a.table_name = b.table_name and b.TABLE_NAME=’S_COMMUNITY_NEWS’;
修改clob表空间
ALTER TABLE s_community_news MOVE TABLESPACE ECARD LOB(codebody) STORE AS(TABLESPACE USERS);

修改表表空间
alter table s_community_news move tablespace USERS;

回滚数据

插入
select * from b_cascade as of timestamp to_timestamp(‘2017-06-08 11:30:00’,’yyyy-mm-dd hh24:mi:ss’);

恢复update、delete操作后的表
alter table 表名 enable row movement;
flashback table 表名 to timestamp to_timestamp(‘2018-02-05 17:30:00’,’yyyy-mm-dd hh24:mi:ss’);

授权查询

select ‘GRANT SELECT ON weintv.’ || table_name || ‘ to vst;’ from user_tables

连接数

查看连接数 select count() from v$process
查看超时时间 select username, b. from dba_users a, dba_profiles b where a.profile = b.profile and username=’USERNAME’ ;
查看允许连接数 select value from v$parameter where name=’processes’
修改连接数 alter system set processes = 1000 scope = spfile;
设置超时时间 ALTER PROFILE DEFAULT LIMIT IDLE_TIME 10;
查看最大并发数 select from v$slicense
连接进程查询 当前用户 select sid, serial#,username from V$SESSION where USERNAME = ‘CARD’;
所有用户select saddr,sid,serial#,paddr,username,status from v$session where username=’CARD’;
关闭连接语句 alter system kill session’sid,serial#’;
创建序列 create sequence SEQ_WECHATUSER
minvalue 1
maxvalue 99999999
start with 1
increment by 1
nocycle
nocache
order;
赋权 grant connect,resource to (用户);
grant select any talbe to (用户);
grant IMP_FULL_DATABASE to (用户);
grant dba to (用户);
grant select on (表名) to (用户);
grant delete on (表名) to (用户);h
grant create on (表名) to (用户);
grant insert on (表名) to (用户);
grant update on (表名) to *(用户);
GRANT SELECT ON cfcard.SEQ_CARD_INFO TO cfacc;

查看锁表

select o.object_name,l.session_id sid,
s.serial#, l.locked_mode,l.oracle_username
from v$locked_object l, all_objects o, v$session s
where l.object_id=o.object_id AND l.session_id=s.sid
order by sid, s.serial#; kill session语句 alter system kill session ‘sid,serial#’;
查询目前连接数 show parameter processes;
更改系统连接数 alter system set processes=1000 scope=spfile;

查询用户状态

select username,account_status from dba_users where username=;

用户解锁

alter USER * account unlock

查询密码错误被锁次数

查看FAILED_LOGIN_ATTEMPTS的值
SQL> select * from dba_profiles where RESOURCE_NAME = ‘FAILED_LOGIN_ATTEMPTS’;
7，修改为30次
SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS 30;
8，修改为无限次（为安全起见，不建议使用）
SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

主键/索引

增加主键
create sequence B_COMMENT_SEQ
minvalue 1
maxvalue 99999999
start with 200
increment by 1
nocache;
alter table B_BR_HOTSPOT add constraint B_BR_HOTSPOT_PK primary key (ID);
增加唯一索引
CREATE UNIQUE INDEX we_access_token_UK ON we_access_token(appid);
删除主键/索引(表名大写)
alter table table_name drop constraint constraint_name cascade drop index;
1、查找表的主键（包括名称，构成列）：
select cu. from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = ‘P’ and au.table_name =表名
2、查找表的所有索引（包括索引名，类型，构成列，表名大写）：
select t.,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name =’S_COMMUNITY_NEWS’

修改表空间

select ‘alter table ‘|| table_name ||’ move tablespace tablespacename;’ from user_tables;

SELECT ‘ALTER INDEX ‘|| INDEX_NAME || ‘ REBUILD TABLESPACE USERS;’ FROM DBA_INDEXES d WHERE d.owner = ‘YICHUN’ AND d.tablespace_name not like ‘USERS’

alter table s_community_news move tablespace users;
alter table s_community_news move lob(codebody) store as (tablespace users);
alter table t_community_news move tablespace users;
alter table t_community_news move lob(codebody) store as (tablespace users);

exp导出空表

select ‘alter table ‘||table_name||’ allocate extent;’ from all_tables where num_rows=0
and SEGMENT_CREATED=’NO’
and owner not like ‘%SYS%’
and tablespace_name not like ‘%SYS%’;

exp导出单表数据

exp yichun/yichun file=yichun_t_community_news.dmp tables=t_community_news;

exp导出多表数据

exp yichun/yichun file=yichun_t_community_news.dmp tables=(a,b);

sqlplus默认配置

$ORACLE_HOME/sqlplus/admin/glogin.sql

块存储

确定文件和块id查询
select segment_name, file_id, block_id from dba_extents where owner=’card’ and segment_name like ‘TELLER%’

转储块
alter system dump datafile 397 block 32811;

查询转储块日志位置
select value from v$diag_info where name = ‘Default Trace File’ ;

建表空间

create tablespace “ECARD” datafile ‘/opt/oracle/datafile/ecard01.dbf’ size 500m autoextend on next 50m maxsize 1000M;
CREATE USER ecard IDENTIFIED BY ecard DEFAULT TABLESPACE ECARD;
删除表空间
DROP TABLESPACE ecard INCLUDING CONTENTS AND DATAFILES;
释放用户锁
超级管理员下
杀死数据库进程
ps -ef |grep $ORACLE_SID|grep -v grep|awk ‘{print $2}’ | xargs kill -9
连接符查看
Oracle安装目录下network/admin/tnsnames.ora

查询sql报错

select v.SQL_TEXT,v.SQL_FULLTEXT,v.SQL_ID,v.INVALIDATIONS,v.LAST_LOAD_TIME from v$sqlarea v where SQL_TEXT like ‘insert into MS_USERINFO%’ order by to_CHAR(last_load_time) desc;

查看密码有效期

SELECT * FROM dba_profiles s WHERE s.profile=’DEFAULT’ AND resource_name=’PASSWORD_LIFE_TIME’;

去除用户有效期

alter profile default limit password_life_time unlimited;
导出表结构
exp user/pass@database file=文件路径.dmp rows=n compress=n
导入表结构
imp user/pass@database file=文件路径.dmp rows=n compress=n、

expdp/impdp

查询directory路径
select from dba_directories where DIRECTORY_NAME = ‘EXP_DIR’;
删除directory路径
drop directory exp_dir;
创建directory路径
create or replace directory exp_dir as ‘(路径)’;
读写目录权限赋予用户
grant read, write on directory exp_dir to (用户);
导出数据库备份dmp文件
expdp user/password@(连接符) schemas=user dumpfile=oracle.dmp;
导入数据库备份dmp文件
impdp user/password@*(连接符) schemas=user directory=exp_dir dumpfile=oracle.dmp;
导入数据库备份dmp文件单独用户
impdp user2/pass2 directory=dumpdir dumpfile=user1.dmp REMAP_SCHEMA=user1:user2;

dblink

删除dblink
DROP PUBLIC DATABASE LINK sarslink;
创建dblink (注意用户名密码小写)
create database link sarslink connect to sars identified by sars_123 using ‘(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 47.100.5.155)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
‘;

select * from table@sarslink;

触发器

testTrigger为源表，testTrigger2为目标表

CREATE OR REPLACE TRIGGER tr_test BEFORE insert or update or delete
ON testTrigger FOR EACH ROW
declare
begin
if inserting then
insert into testTrigger2 values(:new.id,:new.name);
elsif updating then
update testTrigger2 set id=:new.id,name=:new.name where id=:old.id;
elsif deleting then
delete from testTrigger2 where id=:old.id;
end if;
end;

远程表新增表字段时，需要在目标表上执行刷新缓存
ALTER SYSTEM FLUSH SHARED_POOL;

字符分割

CREATE OR REPLACE TYPE str_split IS TABLE OF VARCHAR2 (4000)
CREATE OR REPLACE FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN str_split
PIPELINED
AS
v_length NUMBER := LENGTH(p_string);
v_start NUMBER := 1;
v_index NUMBER;
BEGIN
WHILE(v_start <= v_length)
LOOP
v_index := INSTR(p_string, p_delimiter, v_start);

    IF v_index = 0<br />        THEN<br />            PIPE ROW(SUBSTR(p_string, v_start));<br />            v_start := v_length + 1;<br />        ELSE<br />            PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));<br />            v_start := v_index + 1;<br />        END IF;<br />    END LOOP;

RETURN;<br />END splitstr;

select t.id, (select wmsys.wm_concat(t2.val)
from table(select splitstr(t.name,’,’) from test1 t1 where t1.id=t.id )ty left join test2 t2 on ty.column_value=t2.id)
cnt from test1 t

exp/imp

导出数据库备份dmp文件总
exp system/system_123@TEST file=d:\daochu.dmp full=y
导出数据库备份dmp文件单独用户
exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)
导入数据库备份dmp文件总
imp system/manager@TEST file=d:\daochu.dmp full=y
导入数据库备份dmp文件单独用户
imp card/daqian_123 fromuser=card touser=card file=/home/oracle/CARD20140422_3.DMP log=/home/oracle/imp20140422.log buffer=5400000 ;
导入导出单张表
如果用的是exp导出的话，
imp 用户名/密码 tables=<表名> ignore=y file=文件名 buffer=5400000;
查看oracle用户当前进程
select sid, serial#, status from v$session where username=(用户);
杀死oracle进程
Alter system kill session ‘(sid),*(serial#)’
查看用户
Select username from dba_users;

字符集

查看dmp文件字符集
cat ora.dmp|od -x|head -n 1|awk ‘{print $2 $3}’|cut -c 3-6
select nls_charset_name(to_number(‘0354’，’xxxx’)) from dual;
查看服务器字符集
echo $NLS_LANG
select userenv(‘language’) from dual;
select * from V$NLS_PARAMETERS where PARAMETER=’NLS_CHARACTERSET’;
默认字符集
sql> alter database character set internal_use AL32UTF8;
vi /home/oracle/.bash_profile
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
修改字符集
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK;
————————————————————————-
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK;
SHUTDOWN IMMEDIATE;
STARTUP;

修改监听端口

cd $ORACLE_HOME/network/admin/
vi listener.ora
vi tnsnames.ora

$ sqlplus / as sysdba
> show parameter local_listener
>alter system set local_listener=”(address = (protocol = tcp)(host = 192.168.100.201)(port = 9999))”;

alter system set local_listener=”(address = (protocol = tcp)(host = 156.0.27.2)(port = 1251))”;

47.103.47.70 争光
47.102.37.82
106.14.168.139
139.224.24.48
139.196.90.249
139.224.246.250

配置监听

Listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC15211))
(ADDRESS = (PROTOCOL = TCP)(HOST = iZuf679yqjjhb9zkhl09tjZ)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC=
(GLOBAL_DBNAME = DQ)
(ORACLE_HOME =/opt/oracle/product/OraHome)
(SID_NAME = DQ)
)
)

tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 106.14.77.170)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID_NAME = DQ)
)
)

查看所有的job
select from dba_jobs
查看正在运行的job
select from dba_jobs_running
查看job运行情况
SELECT what, job,last_date,next_date,sysdate,broken,failures from dba_jobs;
——————-查询JOB————————-
select job, what, next_date, next_sec, sysdate, failures, broken,interval
from user_jobs a;
——————-添加JOB————————-
variable n number;
begin
dbms_job.submit(:n, ‘MY_JOP;’, sysdate,’sysdate+2/(246060)’,true);
commit;
end;
——————-修改JOB————————-
begin
dbms_job.change(62 ,’MY_JOP;’, sysdate,’sysdate+2/(2460)’ );
commit;
end;
begin
dbms_job.change(62 ,’MY_JOP;’, to_date(‘2011-08-01 22:00:00’, ‘yyyy-mm-dd hh24:mi:ss’),’sysdate+1’);
commit;
end;
——————-删除JOB————————-
begin
dbms_job.remove(41);
end;
——————-运行Job———————————-
begin
dbms_job.run(41);
end;
分页
(select rownum as myrownum,a. from(select from userinfo ORDER BY dt_create ASC) a where rownum<=i+1)
存储过程
declare
i integer;
begin
i:=0;
for i in 1..500 loop
UPDATE userinfo SET serial_number = i+10000000 WHERE user_no = (
select user_no from((select rownum as myrownum,a. from(select from userinfo ORDER BY dt_create ASC) a where rownum<=i+1) )where myrownum>i
);
end loop;
end;
随机获取一条数据
SELECT FROM (SELECT FROM T_USER ORDER BY DBMS_RANDOM.RANDOM()) WHERE RONUM <= 10
在存有数据表上新增空字段
alter table transrule2 add accttype varchar2(2);
在空字段上增加默认值
alter table t2 modify kong default 2000;
在存有数据表上新增非空字段
alter table transrule2 add count5 number(10) default 2 not null;
在存有数据表上删除字段
ALTER TABLE test_tab2 DROP COLUMN user_number;
对空表分配空间
Select ‘alter table ‘||table_name||’ allocate extent;’ from user_tables where num_rows=0 or num_rows is null
防止用户删除表数据
Sysdba下insert into product_user_profile values (‘SQLPLUS’,’cfcard’,’delete’, ‘disabled’ ,null,null,null,null);
Oracle查看报警日志
show parameter background_dump_dest;
创建外部表
create or replace directory bdump as ‘ /opt/oracle/diag/rdbms/orcl/DQ/trace’;
create table alert_logs
(
text varchar2(2000)
)
organization external
(
type oracle_loader
default directory bdump
access parameters
(
records delimited by newline
fields
reject rows with all null fields
)
location
(
‘alert_DQ.log’
)
)
reject limit unlimited;
查看存储过程
SELECT DISTINCT NAME FROM USER_SOURCE WHERE TYPE=’PROCEDURE’;

psql调试存储过程

打开命令行，set serveroutput on，exec 存储过程
查看索引
select * from user_indexes where table_name=’TRANSJNLS’;
创建普通索引
create index Couponbatch_normal_IO ON Couponbatch(couponno);
创建唯一索引
CREATE UNIQUE INDEX Couponbatch_I0 ON Couponbatch(couponno,branchid);
添加主键
alter table student add constraint pk_student primary key(studentid);

循环

declare
i number(5) := 1;
begin
FOR L_RECORD IN (select * from teller_role t where t.role_type=’3’ and t.role_status=’1’) LOOP
insert into t_br_module values(t_br_module_seq.nextval,’HOME_A_5’ ,L_RECORD.role_no,systimestamp,systimestamp,’’);
i:= i + 1;
commit;
END LOOP;
end;

查询表空间

——查询表空间使用情况—-
使用DBA权限登陆
SELECT UPPER(F.TABLESPACE_NAME) “表空间名”,
D.TOT_GROOTTE_MB “表空间大小(M)”,
D.TOT_GROOTTE_MB - F.TOTAL_BYTES “已使用空间(M)”,
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB 100,2),’990.99’) “使用比”,
F.TOTAL_BYTES “空闲空间(M)”,
F.MAX_BYTES “最大块(M)”
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;
表空间名 表空间大小(M) 已使用空间(M) 使用比 空闲空间(M) 最大块(M)
——————————————— ——————- ——————- ———- —————- —————
…
CCEN 10 8.5 85.00 1.5 .94
…
发现表空间只有1.5M的空闲,猜测可能是表空间自动扩展失败的问题(表空间的增长量太高，ORACLE默认是50%),修改表空间文件扩展方式:
SQL>ALTER DATABASE
DATAFILE ‘/u01/oracle/oradata/orcl/ccen01.dbf’ AUTOEXTEND
ON NEXT 50M MAXSIZE UNLIMITED
问题解决.
查看表空间是否具有自动扩展的能力
SELECT T.TABLESPACE_NAME,D.FILE_NAME,
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME;

生成数据字典

mysql
SELECT
COLUMN_NAME 列名,
COLUMN_TYPE 数据类型,
DATA_TYPE 字段类型,
CHARACTER_MAXIMUM_LENGTH 长度,
IS_NULLABLE 空值,
COLUMN_DEFAULT 默认值,
COLUMN_COMMENT 备注
FROM
INFORMATION_SCHEMA. COLUMNS
WHERE
table_schema = ‘silver_mountain’
AND table_name = ‘sys_menu’;

oracle
SELECT A.table_name as “表名”,A.COLUMN_NAME as “字段名”,
case A.DATA_TYPE
when ‘NUMBER’ then ‘NUMBER’
else A.DATA_TYPE||’(‘||a.DATA_LENGTH||’)’
end as “数据类型”,
a.NULLABLE as “为空”,B.comments as “备注”
FROM all_tab_columns A,DBA_COL_COMMENTS B
WHERE A.owner=B.owner
AND A.table_name=B.table_name
AND A.COLUMN_NAME=B.COLUMN_NAME
AND A.owner=’TRAVELCARD’
ORDER BY A.table_name,A.COLUMN_ID asc;

互动式导入/导出

a服务器上：C:>exp user/pwd@sid
Export: Release 10.1.0.2.0 - Production on 水 2月 10 15:28:18 2010
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Enter array fetch buffer size: 4096 > //缓冲大小，可以选择默认
Export file: EXPDAT.DMP > D:\TEST.DMP //导出文件的位置和名称，后缀为dmp
(2)U(sers), or (3)T(ables): (2)U > t //选择导出对象，导出表，写T
Export table data (yes/no): yes > yes //导出数据
Compress extents (yes/no): yes > no //是否压缩
Export done in JA16SJIS character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path …
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > dependent //选择要导出的表， 此处写表名
. . exporting table DEPENDENT 7 rows exported
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > //可以继续导出表，直接回车 退出。
Export terminated successfully with warnings.
b服务器上：
C:>imp user/pwd
Import: Release 10.1.0.2.0 - Production on 水 2月 10 15:34:19 2010
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Import file: EXPDAT.DMP > d:\TEST.DMP
Enter insert buffer size (minimum is 8192) 30720>
Export file created by EXPORT:V10.01.00 via conventional path
import done in JA16SJIS character set and AL16UTF16 NCHAR character set
List contents of import file only (yes/no): no > NO
Ignore create error due to object existence (yes/no): no > YES
Import grants (yes/no): yes >
Import table data (yes/no): yes >
Import entire export file (yes/no): no >