密码中有特殊符号，如@
sqlplus 'aml94/"cyaml94@2019!11"'@amldb
远程登陆：
Sqlplus user/passwd@ip：端口号/sid
1.查询数据库域名：
select value from v$parameter where name='db_domain';
或者 show parameter domain;
2.查询数据库服务器：
select value from v$parameter where name='service_name';
或者 show parameter service;
或者 show parameter names;
3.查看数据库日志所在路径
show parameter dump_dest;
4.字符集:
1）查看所有字符集：
select value from V$NLS_VALID_VALUES where parameter='CHARACTERSET' order by value;
2）查看当前字符集：
select * from nls_database_parameters;
select userenv('language') from dual;
SELECT parameter, value FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET';
5.查询数据量大小:
1）查询用户大小：
select sum(bytes)/1024/1024 from dba_segments
where owner=用户
2）查询用户下各个表的大小：
select segment_name,sum(bytes)/1024/1024
from dba_segments
where owner ='用户'
and segment_type like'TAB%'
group by owner,segment_name
order by 2 desc;
3）查询用户表大小以及带lob大小：
select segment_name,sum(bytes) / 1024 / 1024
from dba_segments
where segment_name in
(select table_name
from dba_tables
where owner='CMIS' and table_name in('LC_APPL','LC_APPT_INDIV','LPB_APPL_DN','LC_APPL_LIMIT','LC_APPT_EXT','LC_APPL_CONT','LC_APPL_TMP','S_COM_CDE','P_LOAN_TYP')
union all
select segment_name
from dba_lobs
where table_name in
(select table_name from dba_tables where owner='CMIS' and table_name in('LC_APPL','LC_APPT_INDIV','LPB_APPL_DN','LC_APPL_LIMIT','LC_APPT_EXT','LC_APPL_CONT','LC_APPL_TMP','S_COM_CDE','P_LOAN_TYP')))
group by segment_name;
6.文件路径：
1）查询数据文件路径：
select file_name from dba_data_files;
2）临时表空间路径：
SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024 AS "SPACE(M)"
FROM DBA_TEMP_FILES WHERE TABLESPACE_NAME= 'TEMP';
7.表空间：
1）查询创建表空间的ddl：
select dbms_metadata.get_ddl('TABLESPACE',TABLESPACE_NAME)||';'
from dba_tablespaces
where tablespace_name in
例子：
select distinct 'create tablespace '||tablespace_name||' datafile '||'''/u01/app/oracle/oradata/orcl'||tablespace_name||'.dbf'' size 100m autoextend on maxsize 4g;' from dba_tables where owner in
select distinct 'create temporary tablespace '||tablespace_name||' tempfile ''/data/datafile/'||tablespace_name ||'.dbf'' size 1g autoextend on;' from dba_temp_files;
2）创建表空间：
create tablespace TS_NCQS_INDEX
datafile '/oradata/ccis/ts_ncqs_index'
size 30g
autoextend on;
3）添加表空间：
alter tablespace 表空间名 add datafile 路径 size 4G;
alter tablespace add datafile '' size 1g autoextend on;
alter tablespace TRADE33 add datafile '/local/oracle/trade33/trade33_21.dbf' size 1g autoextend on;
alter tablespace TB_P add datafile size 30G autoextend off;
alter tablespace undotbs1 add datafile '/oradata/ccis/ccisetl/undotbs02.dbf' size 30G autoextend on next 128M;
临时表空间：
alter tablespace TEMP add tempfile '/export/grid/12/DB/ora11g/app/oradata/orcldb/temp02.dbf' size 30G autoextend on;
4）删除表空间：
drop tablespace NCELL including contents and datafiles cascade constraint;
5）表空间创建时间
select a.file_name,a.tablespace_name,to_char(b.creation_time,'yyyy-mm-dd') creation_time from dba_data_files a,v$datafile b where a.file_id=b.file# order by tablespace_name;
select a.tablespace_name,a.file_name,b.CREATION_TIME
from dba_data_files a,v$datafile b
where a.file_id=b.FILE# and a.tablespace_name='TB_P'
order by b.CREATION_TIME;
6）表空间使用率：
set linesize 120
set pagesize 5000
set long 5000
SELECT a.tablespace_name,
ROUND (a.total_size) "total_size(MB)",
ROUND (a.total_size) - ROUND (b.free_size, 3) "used_size(MB)",
ROUND (b.free_size, 3) "free_size(MB)",
ROUND (b.free_size / total_size * 100, 2) || '%' free_rate
FROM ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 total_size
FROM dba_data_files
GROUP BY tablespace_name) a,
( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 free_size
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+) order by ROUND (b.free_size / total_size * 100, 2);
查询临时表空间使用率
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);
SELECT TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024,FREE_SPACE/1024/1024 AS "FREE SPACE(M)" FROM DBA_TEMP_FREE_SPACE;
查询指定用户下的表空间使用率：
SELECT a.tablespace_name,
ROUND (a.total_size) "total_size(MB)",
ROUND (a.total_size) - ROUND (b.free_size, 3) "used_size(MB)",
ROUND (b.free_size, 3) "free_size(MB)",
ROUND (b.free_size / total_size * 100, 2) || '%' free_rate
FROM ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 total_size
FROM dba_data_files
GROUP BY tablespace_name) a,
( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 free_size
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+) and a.tablespace_name in(select distinct tablespace_name from dba_tables where owner in('DSG'))
order by ROUND (b.free_size / total_size * 100, 2);
8.用户：
1）查询创建用户的ddl：
select dbms_metadata.get_ddl('USER',username)||';'
from dba_users where username in
2）创建用户：
create user global identified by global
default tablespace ODPS
temporary tablespace temp profile default;
3）权限(dba_role_privs dba_sys_privs)：
select 'grant '||GRANTED_ROLE||' to '||GRANTEE||';'
from dba_role_privs where grantee in
select 'grant '||PRIVILEGE||' to '||GRANTEE||';'
from dba_sys_privs where grantee in
4）删除用户：
drop user NCELL cascade;
9.表
1）创表的ddl：
select dbms_metadata.get_ddl('TABLE',TABLE_NAME,OWNER)
from dba_tables
where owner='APEX_030200' and table_name='WWV_FLOW_CALS'
2）查询某个表有多少字段：
Select count(*) from dba_tab_columns where owner=用户 and table_name=表名;
3）检查无效数据库对象：
select owner,object_name,object_type,status from dba_objects where status='INVALID';
4）删除主键和唯一索引：
alter table tablename drop CONSTRAINT 主键名字 cascade；
alter table tablename drop CONSTRAINT 主键名字 cascade drop index；
select 'alter table '||owner||'.'||table_name||' drop constraint'|| CONSTRAINT_NAME||' cascade drop index;' from dba_constraints where owner=
alter table "FIN"."T_ACCOUNT_INFO" disable constraint PK_ACCOUNT_INFO;
drop index FIN.PK_ACCOUNT_INFO;
5）删除唯一索引语句：
select 'drop index '||owner||'.'||table_name||' '||index_name||';' from dba_indexes where owner= and uniqueness='UNIQUE' and index_name not like 'SYS%';
6）查找主键事哪个字段的：
select column_name from dba_cons_columns where owner='XRISK' and constraint_name in ('SYS_C0012393','SYS_C0012394','SYS_C0013637');
10.手动添加监听
alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=10.100.14.196)(PORT=1521))' scope=both;