oracle基础笔记

1、plsql中文乱码

--查看语言,plsql中文乱码
select * from V$NLS_PARAMETERS;
select userenv('language') from dual;
新建环境变量: NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
SIMPLIFIED CHINESE_CHINA.ZHS16GBK

 

2、控制台管理员登录

sqlplus /nolog
connect /as sysdba

 

3、创建表空间,用户,目录

create tablespace mytbs datafile 'D:\databases\tablespaces\mytbs.dbf' size 50m autoextend on next 32m maxsize unlimited extent management local logging online blocksize 8k autoallocate segment space management auto;
create user mytbs identified by mytbs default tablespace mytbs; --创建角色
grant connect,resource,dba to mytbs; --设置权限
create or replace directory dbdir as 'D:\databases\backups'; --创建目录
grant read, write on directory dbdir to mytbs;

 

4、删除用户和表空间

drop user mytbs cascade;--删除用户，及级联关系也删除掉
--alter tablespace mytbs offline;--删除表空间，及对应的表空间文件也删除掉
drop tablespace mytbs including contents and datafiles cascade constraint;

 

5、imp/exp

--imp导入
imp mytbs/mytbs@localhost:1521/orcl file=D:\databases\backups\mytbs.dmp log=D:\databases\backups\mytbs.log fromuser=mytbs touser=mytbs tablespaces=mytbs ignore=y;
--exp导出
exp mytbs/mytbs@localhost:1521/orcl file=D:\databases\backups\mytbs.dmp log=D:\databases\backups\mytbs.log owner=mytbs grants=y;

 

6、expdp/impdp

--expdp导出[语句末尾不能带分号]
expdp amarabs/amarabs@localhost:1521/orcl directory=DBDIR dumpfile=abscj_20181215expdp.dmp logfile=abscj_20181215expdp.log schemas=AMARABS
--impdp导出[相同用户]
impdp amarabs/amarabs@localhost:1521/orcl directory=DBDIR dumpfile=abscj_20181215expdp.dmp schemas=AMARABS;
--impdp导出[不同用户:旧/新]
impdp amarabs/amarabs@localhost:1521/orcl directory=DBDIR dumpfile=abscj_20181215expdp.dmp remap_schema=amarabs:abscj remap_tablespace=amarabs:abscj;

 

7、修改表的表空间、修改索引的表空间

--修改用户表的表空间
select 'alter table AMARABS.'||table_name||' move tablespace AMARABS;' from user_tables;
--alter table aa move tablespace amarabs;--修改表空间

--修改用户索引的表空间
select 'alter index ALS757.'||index_name||' rebuild tablespace ALS757;' from dba_indexes where table_owner='ALS757' and index_type <> 'LOB' and tablespace_name <>'ALS757' order by table_name;
-- alter table tb_name move tablespace tbs_name;
-- alter index index_name rebuild tablespace tbs_name;

select 'alter table ALS757.'||table_name||' move tablespace ALS757;' from dba_tables where OWNER='ALS757' and TABLESPACE_NAME <> 'ALS757';
select distinct table_name,'select * from ALS757.'||table_name||';' from dba_tables where OWNER='ALS757' and TABLESPACE_NAME <> 'ALS757';
select 'alter index ALS757.'||index_name||' rebuild tablespace ALS757;' from dba_indexes where table_owner='ALS757' and index_type <> 'LOB' and tablespace_name <>'ALS757' order by table_name;
select distinct table_name,'select * from '||table_owner||'.'||table_name||';' from dba_indexes where owner='ALS757' and TABLESPACE_NAME <> 'ALS757';

 

8、查看数据库的信息

--表空间的名称及大小
SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;

--表空间使用
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;

--查看数据库库对象(table,index,view,function,sequence等)
SELECT owner, object_type, status, COUNT(*) count#
FROM all_objects
GROUP BY owner, object_type, status;

--查看数据库的版本　
SELECT version FROM product_component_version WHERE substr(product, 1, 6) = 'Oracle';

8、局域网访问设置

主要在两个文件中配置oracle所在服务器的ip为0.0.0.0
tnsnames.ora 针对某个数据库实例，其中ORCL为当前数据中的实例

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
)

　　listener.ora一般针对整个数据库

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = d:\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:d:\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (SID_NAME = ORCL)
    )
  )
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))

　　改完保存，重启oracle的两个服务，局域网就可以连接了。

OracleOraDb11g_home1TNSListener：监听器服务
OracleServiceORCL：数据库服务(数据库实例)