Oracle 常用指令 & SQL - objects 物件篇 (objects section)

取得資料庫全部的物件

set line 200
col object_name format a60
select owner, object_name, object_type from dba_objects;

取得物件DDL基本語法

使用sqlplus 先執行以下設定，不然不會完整顯示

$ sqlplus / as sysdba
SQL> set pagesize 0
SQL> set long 100000

--Basic
SQL> select dbms_metadata.get_ddl('object types','object name','schema') from dual; 

GET_DDL簡單範例

--Get TABLE DDL
SQL> select dbms_metadata.get_ddl('TABLE','TAB_NAME','SCOTT') from dual; 

--Get VIEW DDL
SQL> select dbms_metadata.get_ddl('VIEW','VIEW_NAME','SCOTT') from dual;

--Get INDEX DDL
SQL> select dbms_metadata.get_ddl('INDEX','IDX_NAME','SCOTT') from dual;

--Get MATERIALIZED VIEW DDL
SQL> select dbms_metadata.get_ddl('MATERIALIZED_VIEW','MVIEW_NAME','SCOTT') from dual;

GET_DDL 實際範例 (取得單一table ddl)

SQL> select dbms_metadata.get_ddl('TABLE','SAVE_MT','SRV') from dual; 

  CREATE TABLE "SRV"."SAVE_MT"
   (    "TRANSACTION_ID" VARCHAR2(24),
        "CHECKNUMBER" VARCHAR2(10),
        "BUYERREMARK" CHAR(1),
        "MAINREMARK" NVARCHAR2(200),
        "CUSTOMSCLEARANCEMARK" CHAR(1),
        "TAXCENTER" VARCHAR2(180),
        "PERMITDATE" DATE,
        "PERMITWORD" VARCHAR2(120),
        "PERMITNUMBER" VARCHAR2(60),
        "CATEGORY" VARCHAR2(2),
        "RELATENUMBER" VARCHAR2(20),
        "INVOICETYPE" CHAR(2),
        "GROUPMARK" CHAR(1),
        "DONATEMARK" CHAR(1),
        "CARRIERTYPE" VARCHAR2(6),
        "CARRIERID1" VARCHAR2(64),
        "CARRIERID2" VARCHAR2(64)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "DATA"

GET_DDL 實際範例 (取得schema底下所有table的ddl)

set pagesize 0
set long 100000
set feedback off
set echo off
spool schema.sql
select dbms_metadata.get_ddl('TABLE',u.table_name) from dba_tables u where owner = 'SCHEMA_NAME';
spool off;

下面列舉較常用的objects type

CONSTRAINT
DB_LINK
FUNCTION
INDEX
JAVA_SOURCE
JOB
MATERIALIZED_VIEW
PACKAGE
PACKAGE_BODY
PROCEDURE
TABLE
TABLESPACE
TRIGGER
USER
VIEW
XMLSCHEMA

Note: object types 物件類型 官網有列出來

https://docs.oracle.com/database/121/ARPLS/d_metada.htm#BGBIEDIA

隨機取樣資料筆數

--使用Sample語法，20指的是總筆數的20%，最小可以到小數點
select * from schema.table_name sample (20);

--如果出現，則只能用dbms_random.value來取樣
--KUP-04084: The ORACLE_DATAPUMP access driver does not support the ROWID column.

--隨機取樣 100000 筆
select * from (select * from schema.table_name order by dbms_random.value) where rownum <= 100000;

Database Links Objects

--Using sys
--$OWNER# 為 0:sys 1:public
select * from sys.link$ order by owner#, ctime, host;