My Oracle CheetSheet – 1

–flush buffer cache and shared pool
alter system flush buffer_cache;
alter system flush SHARED_POOL;
=======================================================
–increase a column’s type value
CREATE TABLE a (aa VARCHAR(10));
INSERT INTO a VALUES (‘sdfsdf’);
INSERT INTO a VALUES (‘sdxxxf’);
INSERT INTO a VALUES (‘sddsdf’);
ALTER TABLE a modify aa VARCHAR2(50);
=======================================================
–create a sequence
CREATE SEQUENCE my_table_seq
            START WITH 1
            INCREMENT BY 1
            NOMAXVALUE
            NOCYCLE;
=======================================================
–create a table
CREATE TABLE T_E_LOC_UPD_LOGS(
        NLOG_ID          NUMBER(7, 0)     NOT NULL,
        NSTART_LOGID     NUMBER(10, 0)    NOT NULL,
        NFINISH_LOGID    NUMBER(10, 0),
        BSUCCESS         NUMBER(1, 0)     NOT NULL,
        DLOG_DATE        DATE             NOT NULL,
        NERROR_CODE      NUMBER(6, 0),
        VERROR_DESC      VARCHAR2(500),
        CONSTRAINT T_E_LOC_UPD_LOGS_PK PRIMARY KEY (NLOG_ID)
    );
=======================================================
–create a directory
CREATE DIRECTORY oraload AS ‘c:\oraload\’;
GRANT READ,WRITE ON DIRECTORY oraload TO hr;                                
SELECT * FROM all_directories; 
=======================================================
–grant access to any table to a schema
GRANT SELECT, INSERT, UPDATE, DELETE ON my_table TO hr;
=======================================================
–add primary key
ALTER TABLE my_table ADD ( CONSTRAINT my_table_PK PRIMARY KEY (LOG_ID));
=======================================================
–crate database link
— requires “xxxxxxxxx.xxxx” entry in TNSNAMES.ORA
CREATE DATABASE LINK my_link
CONNECT TO <<username>> IDENTIFIED BY <<password>>
USING ‘xxxxxxxxx.xxxx’;

SELECT * FROM all_db_links; 
DROP DATABASE LINK my_link;  
SELECT * FROM my_table@my_link;
=======================================================
–some tablespace options
SELECT * FROM User_Tablespaces;
ALTER TABLESPACE akdora AUTOEXTEND ON NEXT 30M MAXSIZE UNLIMITED;
Alter table FR_TBL_loc_upd_data storage (maxextents unlimited);
CREATE TABLESPACE
  akdora
  DATAFILE
  ‘/akdora.dbf’
  size
  300M
  AUTOEXTEND ON
  next 30M 
  MAXSIZE UNLIMITED;
ALTER TABLESPACE akdora NOLOGGING;
=======================================================
–ANALYZE Statement
http://www.ss64.com/ora/analyze.html
=======================================================

Advertisement