oracle常用命令

CLOB can be of 4GB. It is the limit of PL/SQL

查看db_link
select owner,db_link,host from all_db_links;
修改用户密码：
alter user cis identified by "*cis_lhhost" ;

登陆：
sqlplus /nolog
connect sys/pwd as sysdba

查看存在那些数据库：
select Name from V$database;
查看数据库存在的表
all_tables

查询结果分屏：
set pause on
删除用户：
drop user user_name cascade
查询用户集合：
select username,account_status from dba_users

用户解锁：
alter user "SYSTEM" ACCOUNT UNLOCK;

查询每个用户的权限?

SELECT * FROM DBA_SYS_PRIVS;

查询表空间信息?

SELECT * FROM DBA_DATA_FILES;

查看数据库中的表：
select tablespace_name ,table_name from dba_tables where table_name='表名';

表空间：
默认的表空间——system表空间

设定系统的默认表空间
ALTER DATABASE DEFAULT TABLESPACE ;

默认用户表空间：
create user test1 identified by test1 default tablespace ringidx;

查看默认用户表空间：
select username, default_tablespace defspace from dba_users where username=’TEST’;

查看一个表的主键有哪些：
select column_name from user_cons_columns where CONSTRAINT_NAME in (select CONSTRAINT_NAME from user_constraints where table_name =upper('&tab') and CONSTRAINT_TYPE='P');

修改列名：
alter table company_events rename column enent_date to event_date;

更改表空间
alter table <table_name> move tablespace <tablespace_name>


归档模式：
SQL> select log_mode from v$database;

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database archivelog;


rman增量备份：
SQL> SELECT FILENAME,STATUS FROM V$BLOCK_CHANGE_TRACKING
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE 'd:/rman_change.f'
REUSE;
RMAN> connect target system/orcl

连接到目标数据库: ORCL (DBID=1200287935)

RMAN> backup incremental level 0 database;

RMAN> create catalog tablespace ora_backup

查看数据库表里面的所有存储过程
select text from user_source where NAME='FTCCO_GETBRANCHNAME'

查询存着分区的表：
select table_name from user_tables where partitioned='YES';
查询分区：
select table_name,partition_name from user_tab_partitions where table_name='T_ATMMONITOR';
删除分区：
alter table T_ATMMONITOR drop partition P_MAX;
增加分区：
alter table T_ATMMONITOR add partition P_200901 values less than('200901');
alter table T_ATMMONITOR add partition P_MAX values less than(MAXVALUE);

查看表空间使用情况

select
a.a1 表空间名称,
c.c2 类型,
c.c3 区管理,
b.b2/1024/1024 表空间大小M,
(b.b2-a.a2)/1024/1024 已使用M,
substr((b.b2-a.a2)/b.b2*100,1,5) 利用率
from
(select tablespace_name a1, sum(nvl(bytes,0)) a2 from dba_free_space group by tablespace_name) a,
(select tablespace_name b1,sum(bytes) b2 from dba_data_files group by tablespace_name) b,
(select tablespace_name c1,contents c2,extent_management c3 from dba_tablespaces) c
where a.a1=b.b1 and c.c1=b.b1;


select
a.a1 tablespacename,
b.b2/1024/1024 total,
(b.b2-a.a2)/1024/1024 userd,
substr((b.b2-a.a2)/b.b2*100,1,5) usage
from
(select tablespace_name a1, sum(nvl(bytes,0)) a2 from dba_free_space group by tablespace_name) a,
(select tablespace_name b1,sum(bytes) b2 from dba_data_files group by tablespace_name) b,
(select tablespace_name c1,contents c2,extent_management c3 from dba_tablespaces) c
where a.a1=b.b1 and c.c1=b.b1;

如何查看每个用户在不同表空间中所占有的空间大小.

select distinct owner,tablespace_name,sum(bytes)
from dba_segments
group by owner,tablespace_name;


清空表，但不破坏结构
truncate table t_atm

对于多个数据库，设置默认数据库(否则会报ora-12560 TNS错误)
set oracle_sid=ty;

启动监听程序 lsnrctl start(否则会报ora-12541 TNS错误)


//
1、创建存储过程

create or replace procedure test(var_name_1 in type,var_name_2 out type) as

--声明变量(变量名 变量类型)

begin

--存储过程的执行体

end test;

打印出输入的时间信息

E.g:

create or replace procedure test(workDate in Date) is

begin

dbms_output.put_line('this is a test statement!');

end test;


SQL> set serveroutput on
SQL> begin
2 dbms_output.put_line('this is a test statement!');
3 end;
4 /
this is a test statement!
PL/SQL procedure successfully completed

查看SGA：
show parameter sga
ALTER SYSTEM SET sga_max_size = 150m SCOPE=SPFILE;
ALTER SYSTEM SET sga_target = 104857600 SCOPE=BOTH;


create pfile from spfile
查看spfile路径
show parameter spfile


alert.log
unix下在：$ORACLE_BASE/admin/sid/bdump


删除数据库连接
drop database link product.atmu.com

create database link "master.atmu.com" connect to repadmin identified by repadmin using 'ty122';
create database link "product.atmu.com" connect to repadmin identified by repadmin using 'ty123'
execute dbms_repcat.add_master_database(gname=>'REP_anymusic',master=>'product.atmu.com',use_existing_objects=>true,copy_rows=>true, propagation_mode => 'synchronous');

获取没有主键的表

select table_name from dba_tables where owner='ATOS' and table_name not in (
constraint_type='P' and owner='ATOS'
)

查看某用户的唯一索引
select * from user_indexes where table_owner='ATOS' and uniqueness='UNIQUE'
select * from user_indexes a,user_ind_columns b where a.index_name=b.index_name

删除表空间命令：
SQL>drop tablespace tablespace_name including contents and datafiles;

oracle输出函数：
DBMS_OUTPUT.PUT_LINE('TEST!');
如果屏幕没有显示执行：set serveroutput on
分屏显示：set pause on set pagesize 24

最近sql执行历史：
select sql_text,last_active_time from v$sqlarea where sql_text like '%39900006%'


oracle查看当前打开的游标数目和连接数目

oracle查看当前打开的游标数目和连接数目
在SYSDAB下查看
select count(*) from v$open_cursor;
select count(*) from v$session;

增加分区：
ALTER TABLE ATOS.T_JOURNAL
ADD
PARTITION P_201105 VALUES LESS THAN ('201105')
LOGGING
NOCOMPRESS
TABLESPACE PTS_ATOS_001;

查找失效的索引
select index_name,index_type,tablespace_name,table_type,status,table_name from user_indexes where status=upper('unusable');