--1.CHECK
• Check bảng
SQL> SELECT table_name, tablespace_name, num_rows FROM DBA_TABLES WHERE tablespace_name in ('USERS', 'MY_SPACE');
• Check cột
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, LAST_ANALYZED
FROM DBA_TAB_COLUMNS
WHERE OWNER = 'HR'
ORDER BY TABLE_NAME;
--2.CREATE TABLE
• CREATE TABLE departments_demo
( department_id NUMBER(4)
, department_name VARCHAR2(30)
CONSTRAINT dept_name_nn NOT NULL
, manager_id NUMBER(6)
, location_id NUMBER(4)
, dn VARCHAR2(300)
) ;
• Create table as select
CREATE TABLE tmp AS SELECT MyColOld MyColNew, col2, col3 FROM MY_TABLE;
DROP TABLE MY_TABLE;
RENAME tmp TO MY_TABLE;
CREATE TABLE dept_80
PARALLEL
AS SELECT * FROM employees
WHERE department_id = 80;
--3.ALTER TABLE
• Đổi tên bảng:
alter table USER2.XX_TEMP33 rename to XX_TEMP3;
// alter table USER2.XX_TEMP33 rename to USER2.XX_TEMP3 -- error
• Renaming a Column:
ALTER TABLE cus
RENAME COLUMN credit_limit TO credit_amount;
Hoặc: Dùng TOAD -> Alter Table -> Thay đổi tên column
• Dropping a Column:
ALTER TABLE BINHTV.EMP DROP COLUMN HIREDATE;
• Adding Column:
ALTER TABLE countries
ADD (duty_pct NUMBER(2,2) CHECK (duty_pct < 10.5),
visa_needed VARCHAR2(3));
ALTER TABLE BINHTV.EMP
ADD (NewField1 VARCHAR2(1));
ALTER TABLE BINHTV.TABLE1
ADD (GROUP_ID_MSC NUMBER)
alter table table1 PARALLEL (DEGREE 4);
• Modify column
ALTER TABLE BINHTV.PAYMENT
MODIFY(AGENT_CODE VARCHAR2(50 BYTE))
/
• Data Encryption:
ALTER TABLE employees MODIFY (salary ENCRYPT USING '3DES168');
The following statement adds a new encrypted column online_acct_pw to the oe.customers table.
ALTER TABLE cus
ADD (online_acct_pw VARCHAR2(8) ENCRYPT);
The following example decrypts the customer.online_acct_pw column:
ALTER TABLE cus
MODIFY (online_acct_pw DECRYPT);
• Allocating Extents:
ALTER TABLE employees
ALLOCATE EXTENT (SIZE 5K INSTANCE 4);
• Specifying Default Column Value:
ALTER TABLE product_information
MODIFY (min_price DEFAULT 10);
• Compress table
ALTER TABLE <table_name> compress;
--4.DROP
DROP TABLE [Owner.]TableName [[PURGE]
DROP TABLE CUS_OWNER_TEST.EMP;
--5.LOCK
LOCK TABLE employees
IN EXCLUSIVE MODE
NOWAIT;
LOCK TABLE employees@remote
IN SHARE MODE;
--6.TRUNCATE
TRUNCATE TABLE BINHTV.TEST01;
