青青色在线,亚洲传媒国产色情

查看當前用戶的缺省表空間

select username,default_tablespace from user_users;

查看一個表,所在的表空間

select TABLESPACE_NAME from all_tables where table_name='EMP';

navicat配置oracle連接
從高版本向低版本遷移數(shù)據(jù)

1.建立directory:CREATE OR REPLACE DIRECTORY DATA_DUMP_DIR AS '/data/db_export/dmp';
2.查詢directory創(chuàng)立:select * from dba_directories;
3.expdp userid="'username/password@orcl as sysdba'" schemas=schema_name dumpfile=dmp_name.dmp DIRECTORY=DATA_DUMP_DIR
4./data/db_export/dmp需要允許oracle用戶寫入
5.參考

在高版本主機運行
expdp swcms/swcms@orcl directory=data_pump_dir DUMPFILE=wsl_transfer_data_10g.dmp logfile=log.log schemas=swcms version=10.2.0.1.0

在低版本主機運行
impdp swcms/swcms DIRECTORY=DATA_PUMP_DIR DUMPFILE=WSL_TRANSFER_DATA_10G.DMP SCHEMAS=swcms logfile=logimpdp.log exclude=user
PL/SQL顯示中文

環(huán)境變量NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK

6.sqlplus連接oracle數(shù)據(jù)庫

sqlplus username/password@ip:port/sid

7.更改db連接數(shù)

8.csv導(dǎo)入表

9.表連接多條記錄分別取第一條

10.windows的數(shù)據(jù)庫導(dǎo)入linux報長度錯誤，可能是因為字符集的問題

11.PL/SQL

1.查看sql失敗時可以刷新后再試
2.java字符串拼接SQL可以通過Paste from Host Language轉(zhuǎn)化為正常SQL
3.導(dǎo)入txt

12.父子關(guān)系查找

1.補充1
2.補充2_剔除部分分支
3.排序

13.oracle 按自定義的序列排序結(jié)果集

14.存儲過程

1.plsql中procedure的入?yún)㈩愋停绻莕umber或varchar2的話不需要定義長度。
2.變量使用前要初始化，推薦聲明時指定默認值

1.select into形式賦值變量的不需要初始化

15.where條件中使用正則表達式

16.手動刷新物化視圖

1.在PL/SQL的Command Window中執(zhí)行EXEC dbms_mview.REFRESH('v_user_login_count');
2.查看物化視圖上次刷新時間select name, last_refresh from user_mview_refresh_times;

17、PL/SQL執(zhí)行sql腳本

1.將結(jié)果導(dǎo)出csv

set echo off
set heading off
set feedback off
set pagesize 1000
set feedback off
set term off
spool c:\test.csv
SELECT 1||','||2 FROM DUAL;
spool off 
exit

18.解除鎖表

19.rpad漢字長度問題

1.SELECT RPAD('我1', , 6, '2'),RPAD(to_nchar('我1'), 6, '2'),LENGTHB(CONVERT('我1', 'ZHS16GBK', 'UTF8')) FROM DUAL
2.參考1
3.參考2

20.小數(shù)和字符串拼接0不見的問題解決

21.獲取連續(xù)日期
SELECT TO_CHAR(TO_DATE('2018-01-01', 'yyyy-mm-dd') + (LEVEL - 1), 'yyyy-mm-dd') days, LEVEL FROM DUAL CONNECT BY TRUNC(TO_DATE('2017-01-01', 'yyyy-mm-dd')) + LEVEL - 1 <= TRUNC(TO_DATE('2017-01-04', 'yyyy-mm-dd')) order by days desc

22.shell操作oralce

1.參考1
2.參考2

sqlplus -s 用戶名/密碼@tnsnames.ora文件中配置的dababasename <<EOF
set heading off
set pagesize 0
set trimspool on
set feedback off
set termout off
set trimout on
set define on
COL yestoday  NEW_VALUE yestoday_date noprint
SELECT TO_CHAR(SYSDATE-1,'yyyymmdd') yestoday FROM DUAL;
spool ${PATH}/log_&yestoday_date..log
SQL分號結(jié)尾
spool off
exit
EOF

4.中文注意設(shè)置環(huán)境變量

1.NLS_LANG=設(shè)置為[select userenv('language') from dual;]的值
2.LANG=zh_CN.UTF-8

5.使用變量注意變量后的.要顯示需要..

23.存儲中文

24.刪除表會附帶刪除觸發(fā)器

25.游標使用概要

26.自增主鍵

27.docker安裝11.2.0.1.0
28.創(chuàng)建新的數(shù)據(jù)庫實例

1.創(chuàng)建命令:dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname orcl -sid orcl -responseFile NO_VALUE -characterSet AL32UTF8 -memoryPercentage 30 -emConfiguration LOCAL
2.查看db參數(shù)的常用sql及命令

1.select name from v$datafile;
2.SELECT * FROM NLS_DATABASE_PARAMETERS;
3.show parameter name;

29.開啟閃回

30.exp和imp字符集不一致問題

31.ORA-00257解決辦法

32.加快imp導(dǎo)入速度

1.imp swcms/swcms@orcl file=201803061230_swcms.dmp LOG=/home/oracle/app/oracle/oradata/201803061230_swcms.log feedback=10000 buffer=100000000 COMMIT=Y

33.exp導(dǎo)出時出現(xiàn)ORA-01555和ORA-22924的解決方案

1.&&lob_column改為&lob_column

34.全庫導(dǎo)入

1.注意sqlplus段在shell中需用\轉(zhuǎn)義$
2.也可使用存儲過程禁用啟用約束，但需調(diào)轉(zhuǎn)一下imp約束導(dǎo)入順序

#!/bin/bash
. /etc/profile
. ~/.bash_profile

dmpfile=`ls /data/db_back_up/dmp/*_XXX.dmp`
echo "imp "$dmpfile" start:`date`"

su - oracle -c "sqlplus /nolog" <<EOF
connect XXX/XXX@orcl as sysdba
declare
    v_sql  varchar2(1000);
begin
    for cur in (select s.sid, s.serial#
                from v\$session s
                where upper(username) in (upper('XXX'))
                )
    loop
     v_sql := 'alter system kill session ''' || cur.sid || ',' || cur.serial# || ''' immediate';
     execute immediate v_sql ;
     end loop;

     for cur in (select t.username from dba_users t where upper(t.username) in (upper('XXX')))
    loop
     v_sql := ' drop user  ' || cur.username || ' cascade ' ;
     execute immediate v_sql ;
     end loop;
end;

/

create user XXX identified by "XXX";
grant CONNECT, resource,IMP_FULL_DATABASE to XXX;
exit;
EOF
#全部導(dǎo)入
#imp XXX/XXX@orcl file="$dmpfile" commit=y ignore=y buffer=10240000 feedback=10000 full=y statistics=none 
#導(dǎo)入表結(jié)構(gòu)(不含約束)
imp XXX/XXX@orcl file="$dmpfile" commit=y ignore=y buffer=10240000 feedback=10000 full=y rows=n statistics=none CONSTRAINTS=n

tables=`
su - oracle -c "sqlplus -S /nolog" <<EOF
conn XXX/XXX@orcl
set heading off
set long 4000
set lines 4000
col tbl_names format a4000
SELECT WM_CONCAT(UT.TABLE_NAME) tbl_names FROM USER_TABLES UT WHERE UT.TABLE_NAME <> 'XXX';
exit;
EOF
`

#導(dǎo)入除XXX表的數(shù)據(jù)及約束
imp XXX/XXX@orcl file="$dmpfile" commit=y ignore=y buffer=10240000 feedback=10000 tables="$tables" statistics=none

echo "imp "$dmpfile" end:`date`"

35.跨庫dblink

37、查詢表空間使用情況

SELECT DDF.TABLESPACE_NAME "表空間名",
       ROUND((1 - NVL(FREE_SPACE, 0) / NVL(SPACE, 1)) * 100, 2) "使用率 %",
       FREE_SPACE "表空間剩余大小(M)",
       SPACE "表空間大小(M)",
       SPACE - NVL(FREE_SPACE, 0) "表空間使用大小(M)"
FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE
      FROM DBA_DATA_FILES
      GROUP BY TABLESPACE_NAME) DDF,
     (SELECT TABLESPACE_NAME,
             ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
      FROM DBA_FREE_SPACE
      GROUP BY TABLESPACE_NAME) DFS
WHERE DDF.TABLESPACE_NAME = DFS.TABLESPACE_NAME(+)
UNION ALL --臨時表空間
SELECT DTF.TABLESPACE_NAME "表空間名",
       ROUND(NVL(USED_SPACE, 0) / NVL(SPACE, 1) * 100, 2) "使用率 %",
       NVL(FREE_SPACE, 0) "表空間剩余大小(M)",
       NVL(SPACE, 0) "表空間大小(M)",
       NVL(USED_SPACE, 0) "表空間使用大小(M)"
FROM (SELECT TABLESPACE_NAME, 
             ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE
      FROM DBA_TEMP_FILES
      GROUP BY TABLESPACE_NAME) DTF,
     (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) TSH
WHERE DTF.TABLESPACE_NAME = TSH.TABLESPACE_NAME(+)
ORDER BY 4 DESC;

38.Druid加密數(shù)據(jù)庫密碼

1.java -cp D:/druid-1.0.13.jar com.alibaba.druid.filter.config.ConfigTools 密碼

39.查詢數(shù)據(jù)庫某用戶表詳細信息

SELECT  --ATC.OWNER，
        ATC.TABLE_NAME "表名"，
        ATCS.COMMENTS "表注釋"，
        ATC.COLUMN_NAME "列名"， 
        ACC.COMMENTS "列注釋",    
        ATC.DATA_TYPE "類型"，
        ATC.DATA_LENGTH "長度"，
        ATC.NULLABLE "是否為空",
        ATC.DATA_DEFAULT "默認值"
FROM
    (SELECT ATC.OWNER，
            ATC.TABLE_NAME，
            ATC.COLUMN_NAME，
            ATC.DATA_TYPE，
            ATC.DATA_LENGTH，
            ATC.NULLABLE,
            ATC.DATA_DEFAULT
     FROM ALL_TAB_COLUMNS ATC
     WHERE ATC.OWNER =&USER) ATC
     LEFT OUTER JOIN ALL_COL_COMMENTS ACC
     ON ATC.TABLE_NAME=ACC.TABLE_NAME 
     AND ATC.COLUMN_NAME=ACC.COLUMN_NAME
     AND ATC.OWNER = ACC.OWNER
     LEFT OUTER JOIN ALL_TAB_COMMENTS ATCS
     ON ATC.TABLE_NAME=ATCS.TABLE_NAME
     AND ATC.OWNER = ATCS.OWNER
ORDER BY ATC.TABLE_NAME，ATC.COLUMN_NAME

40.解決ORA-00031: session marked for kill tips

PIDs=`sqlplus -s username/password@orcl as sysdba  <<EOF
set head off
set termout off
set feedback off
set pagesize 0
set trimout on
select p.spid from v\\$process p,v\\$session s where p.addr = s.paddr  and s.status = 'KILLED';
exit
EOF`

echo $PIDs | awk -F' ' '{for(i=0;++i<=NF;)print "kill -9 "$i}' | sh

41.使用dba編譯指定schema下的所有procedures、views等(注意schema名大寫)exec dbms_utility.compile_schema('CCSTYJ');

1.查詢當前用戶下所有需要再編譯對象select 'Alter '||object_type||' '||object_name||' compile;' from user_objects where status = 'INVALID';
2.查詢所有需要再編譯對象select 'Alter '||object_type||' '||object_name||' compile;' from all_objects where status = 'INVALID';

42、REGEXP_LIKE(field, '^[[:digit:]]{11}$')中可以匹配８和8
43、行轉(zhuǎn)列

SELECT * FROM 
(
SELECT VULDC.USER_ID,T3.MONTH,VULDC.LOGIN_COUNT FROM V_USER.V_USER_LOGIN_DAILY_COUNT VULDC,
(
SELECT TO_CHAR(ADD_MONTHS(SYSDATE-1,-(LEVEL-1)),'YYYY-MM') MONTH FROM DUAL CONNECT BY LEVEL<=6
)T3a
WHERE SUBSTR(VULDC.COUNT_DATE,1,7) = T3.MONTH
)T4
PIVOT
(COUNT(LOGIN_COUNT) for MONTH in ('2019-11' m1,'2019-10' m2,'2019-09' m3,'2019-08' m4,'2019-07' m5,'2019-06' m6));

44、列轉(zhuǎn)行

SELECT WM_CONCAT(''''||TO_CHAR(ADD_MONTHS(SYSDATE-1,-(LEVEL-1)),'YYYY-MM')||''' '||'M'||LEVEL) MONTH FROM DUAL CONNECT BY LEVEL<=6

45、可以通過awr報告查看oracle中慢sql
46、docker安裝oracle 12c

1、遠程expdp

create database link link_name
connect to user
identified by "password"
using 

'(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =IP)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = name)
    )
  )'
create or replace directory DATA_DUMP_DIR as 'path'  
expdp userid="'sys/oracle@orcl as sysdba'" network_link=link_name schemas=xx dumpfile=xx.dmp DIRECTORY=DATA_DUMP_DIR logfile=xx.log parallel=4 job_name=xx

47、jar下載maven1、maven2
48、dbeaver PJ，無需安裝oracle client

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

Oracle

Oracle

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

Oracle

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av