Removing Your Oracle Database
The task of removing an Oracle database differs based on what OS you are running on. For example, if you are running on UNIX, generally you must remove the related database files (database data files, online redo logs, and control files), files in the admin directories related to the database (database parameter files, trace files, etc.), and entries in ancillary database files such as the oratab, tnsnames.ora, and listener.ora files. If you are running on Windows, you need to make the same changes required on UNIX systems, but you also need to remove the database services for the database that you are removing. This will prevent errors from occurring when you start your Oracle database.
You can also use the DBCA to remove a database. The DBCA can be used to drop a database that you created with the DBCA or one that you manually created.
Oracle Database 10g offers a new command, drop database, that takes care of dropping your database for you. You still need to perform ancillary tasks, such as removing the database service and changing database files such as oratab, tnsnames.ora, and listener.ora.
Removing an Oracle database is quite different than removing the Oracle database software. If removing the software is your quest, you should first remove all Oracle databases on your system, and then use the Oracle Universal Installer to remove the Oracle RDBMS software. To completely remove the Oracle RDBMS software, you will probably have to manually remove files from the old ORACLE_HOME, as the installer is not always perfect in its removal process.
Tuesday, February 23, 2010
ORA-01079 ORACLE database was not properly created, operation aborted
ORA-01079 ORACLE database was not properly created, operation aborted
Possible Causes and Solutions
An error occurred during the creation of the Oracle database. Generally, this is followed by other error messages. Reasons for this error message might include
• Errors in the database initialization file. • Errors in the create database command. • Oracle RDBMS bugs.
Possible Causes and Solutions
An error occurred during the creation of the Oracle database. Generally, this is followed by other error messages. Reasons for this error message might include
• Errors in the database initialization file. • Errors in the create database command. • Oracle RDBMS bugs.
ORA-01078 Failure in processing system parameters
ORA-01078 Failure in processing system parameters
Possible Causes and Solutions
Several possible causes (generally followed by another error message):
• The parameter file could not be opened (followed by an LRM-00109 error message). Check that the parameter file exists, and that it can be opened and read by the database executable. By default, the database expects the parameter file to be in ORACLE_HOME/dbs (this is port-specific, however; for example, Windows expects it to be in $ORACLE_HOME/database by default).
• An unknown parameter may be included in the parameter file (error LRM-00101 also appears). Correct the unknown parameter and restart the database.
Possible Causes and Solutions
Several possible causes (generally followed by another error message):
• The parameter file could not be opened (followed by an LRM-00109 error message). Check that the parameter file exists, and that it can be opened and read by the database executable. By default, the database expects the parameter file to be in ORACLE_HOME/dbs (this is port-specific, however; for example, Windows expects it to be in $ORACLE_HOME/database by default).
• An unknown parameter may be included in the parameter file (error LRM-00101 also appears). Correct the unknown parameter and restart the database.
ORA-01031 Insufficient privileges
ORA-01031 Insufficient privileges
Possible Causes and Solutions
Generally, some security violation has occurred. Check the following: If the remote_login_passwordfile parameter is not set, or is set to none: 1. Make sure you are logged in as the Oracle user (or equivalent) if you are running in UNIX. 2. Make sure you are logged in to a Windows account that is a member of the ORA_DBA group. If the remote_login_passwordfile parameter is set, then make sure you are logging in with the correct password. If you cannot remember the password for your database, you can 1. Use the orapwd utility to create an Oracle database password file. 2. Add the Windows user you are signed in as to the ORA_DBA group.
Possible Causes and Solutions
Generally, some security violation has occurred. Check the following: If the remote_login_passwordfile parameter is not set, or is set to none: 1. Make sure you are logged in as the Oracle user (or equivalent) if you are running in UNIX. 2. Make sure you are logged in to a Windows account that is a member of the ORA_DBA group. If the remote_login_passwordfile parameter is set, then make sure you are logging in with the correct password. If you cannot remember the password for your database, you can 1. Use the orapwd utility to create an Oracle database password file. 2. Add the Windows user you are signed in as to the ORA_DBA group.
ORA-00221 Error on write to controlfile
ORA-00221 Error on write to controlfile
Possible Causes and Solutions
An error has occurred when trying to create the control file, or on subsequent writes to the control file. You should make sure that the file system can be written to by the Oracle database login account. Also make sure that a control file does not already exist.
Possible Causes and Solutions
An error has occurred when trying to create the control file, or on subsequent writes to the control file. You should make sure that the file system can be written to by the Oracle database login account. Also make sure that a control file does not already exist.
ORA-00215 Must be at least one controlfile
ORA-00215 Must be at least one controlfile
Possible Causes and Solutions
You do not have at least one control file listed in your database parameter file, which is required. Correct the parameter file and re-run.
Possible Causes and Solutions
You do not have at least one control file listed in your database parameter file, which is required. Correct the parameter file and re-run.
ORA-00213 Cannot reuse controlfile
ORA-00213 Cannot reuse controlfile
Possible Causes and Solutions
Generally, you are re-creating a database that previously existed, or a previous database creation failed. Remove the control files, and try to create the database again. Exercise caution, and make sure you remove the correct control file. This error may indicate that you are trying to overwrite the control files of another database.
Possible Causes and Solutions
Generally, you are re-creating a database that previously existed, or a previous database creation failed. Remove the control files, and try to create the database again. Exercise caution, and make sure you remove the correct control file. This error may indicate that you are trying to overwrite the control files of another database.
ORA-00200 Controlfile could not be created
ORA-00200 Controlfile could not be created
Possible Causes and Solutions
The RDBMS was unable to create the control file. This may mean that the control file already exists, or it might be that the directory does not exist.
Possible Causes and Solutions
The RDBMS was unable to create the control file. This may mean that the control file already exists, or it might be that the directory does not exist.
LRM-00109
LRM-00109 Could not open parameter file ‘xyz’
Possible Causes and Solutions
You have used the startup pfile command and the pfile or ifile specified in the command could not be found. Accompanied with the ORA-01078 error message.
Possible Causes and Solutions
You have used the startup pfile command and the pfile or ifile specified in the command could not be found. Accompanied with the ORA-01078 error message.
Oracle DBA Cheat Sheet
Oracle DBA Cheat Sheet
One note of caution: if you don’t know what a specific keyword of a command does, don’t use it without checking out its purpose. This is a reference for those who understand what something like cascade constraints means when associated with a drop table command. So, without further delay, let’s get on with the examples!
alter cluster
ALTER CLUSTER pub_cluster SIZE 4K;
ALTER CLUSTER pub_cluster DEALLOCATE UNUSED KEEP 1M;
alter database: Alter a Data File
ALTER DATABASE DATAFILE 4 OFFLINE;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf' OFFLINE;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf' RESIZE 100m;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 1000M;
ALTER DATABASE DATAFILE 4 END BACKUP;
alter database: Alter a Tempfile
ALTER DATABASE TEMPFILE 4 RESIZE 100M;
ALTER DATABASE TEMPFILE 4
AUTOEXTEND ON NEXT 100M MAXSIZE 1000M;
ALTER DATABASE TEMPFILE 4 DROP INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE 4 OFFLINE;
alter database: ARCHIVELOG Mode Commands
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE NOARCHIVELOG;
ALTER DATABASE FORCE LOGGING;
ALTER DATABASE CLEAR LOGFILE '/opt/oracle/logfiles/redo01.rdo';
ALTER DATABASE CLEAR UNARCHIVED LOGFILE
'/opt/oracle/logfiles/redo01.rdo';
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE);
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
alter database: Control File Operations
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
ALTER DATABASE BACKUP CONTROLFILE TO TRACE
AS '/opt/oracle/logfile_backup/backup_logfile.trc'
REUSE RESETLOGS;
ALTER DATABASE BACKUP CONTROLFILE TO
'/opt/oracle/logfile_backup/backup_logfile.ctl';
alter database: Create a Data File
ALTER DATABASE CREATE DATAFILE
'/opt/oracle/datafile/users01.dbf' AS '/opt/oracle/datafile/users01.dbf';
ALTER DATABASE CREATE DATAFILE 4
AS '/opt/oracle/datafile/users01.dbf';
ALTER DATABASE CREATE DATAFILE
'/opt/oracle/datafile/users01.dbf' AS NEW;
alter database: Datafile Offline/Online
See alter database: Alter a Data File
alter database: Logfile Commands
ALTER DATABASE ADD LOGFILE GROUP 2
('/opt/oracle/logfiles/redo02a.rdo', '/opt/oracle/logfiles/redo02b.rdo')
SIZE 300M REUSE;
ALTER DATABASE ADD LOGFILE MEMBER
'/opt/oracle/logfiles/redo02c.rdo'
to GROUP 2;
ALTER DATABASE ADD LOGFILE thread 3 GROUP 2
('/opt/oracle/logfiles/redo02a.rdo', '/opt/oracle/logfiles/redo02b.rdo')
SIZE 300M REUSE;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE MEMBER '/opt/oracle/logfiles/redo02b.rdo';
alter database: Mount and Open the Database
ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;
alter database: Move or Rename a Database File or Online Redo Log
Note Note The database must be mounted to rename or move online redo logs. The database must be mounted or the data files taken offline to move database data files.
ALTER DATABASE RENAME FILE '/ora/datafile/oldfile.dbf' TO '/ora/datafile/newfile.dbf';
alter database: Open the Database Read-Only
ALTER DATABASE OPEN READ ONLY;
alter database: Open the Database with resetlogs
ALTER DATABASE OPEN RESETLOGS;
alter database: Recover the Database
For database recovery, I recommend the use of the recover command instead. See the “recover” section, later in the chapter.
alter function: Recompile a Function
ALTER FUNCTION my_function COMPILE;
alter index: Allocate and Deallocate Extents
ALTER INDEX ix_my_tab ALLOCATE EXTENT;
ALTER INDEX ix_my_tab ALLOCATE EXTENT
DATAFILE '/ora/datafile/newidx.dbf';
ALTER INDEX ix_my_tab DEALLOCATE UNUSED;
ALTER INDEX ix_my_tab DEALLOCATE UNUSED KEEP 100M;
alter index: Miscellaneous Maintenance
ALTER INDEX ix_my_tab PARALLEL 3;
ALTER INDEX ix_my_tab NOPARALLEL;
ALTER INDEX ix_my_tab NOCOMPRESS;
ALTER INDEX ix_my_tab COMPRESS;
alter index: Modify Logging Attributes
ALTER INDEX ix_my_tab LOGGING;
ALTER INDEX ix_my_tab NOLOGGING;
alter index: Modify Storage and Physical Attributes
ALTER INDEX ix_my_tab PCTFREE 10 PCTUSED 40 INITRANS 5
STORAGE (NEXT 100k MAXEXTENTS UNLIMITED FREELISTS 10
BUFFER_POOL KEEP);
alter index: Partition – Add Hash Index Partition
ALTER INDEX ix_my_tab ADD PARTITION
TABLESPACE NEWIDXTBS;
alter index: Partition – Coalesce Partition
ALTER INDEX ix_my_tab COALESCE PARTITION;
alter index: Partition – Drop Partition
ALTER INDEX ix_my_tab DROP PARTITION ix_my_tab_jan_04;
alter index: Partition – Modify Default Attributes
ALTER INDEX ix_my_tab MODIFY DEFAULT ATTRIBUTES
FOR PARTITION ix_my_tab_jan_04
PCTFREE 10 PCTUSED 40 TABLESPACE newidxtbs
NOLOGGING COMPRESS;
alter index: Partition – Modify Partition
ALTER INDEX ix_my_tab MODIFY PARTITION ix_my_tab_jan_04
DEALLOCATE UNUSED KEEP 100M;
ALTER INDEX ix_my_tab MODIFY PARTITION ix_my_tab_jan_04
ALLOCATE EXTENT SIZE 100m;
ALTER INDEX ix_my_tab MODIFY PARTITION ix_my_tab_jan_04
PCTUSED 40 STORAGE(NEXT 50m) NOLOGGING;
alter index: Partition – Modify Subpartition
ALTER INDEX ix_my_tab MODIFY SUBPARTITION ix_my_tab_jan_04
DEALLOCATE UNUSED KEEP 100M;
ALTER INDEX ix_my_tab MODIFY SUBPARTITION ix_my_tab_jan_04
ALLOCATE EXTENT SIZE 100m;
ALTER INDEX ix_my_tab MODIFY SUBPARTITION ix_my_tab_jan_04
PCTUSED 40 STORAGE(NEXT 50m) NOLOGGING;
alter index: Partition – Rename
ALTER INDEX ix_my_tab RENAME
PARTITION ix_my_tab_jan_04 TO ix_my_tab_jan_05;
ALTER INDEX ix_my_tab RENAME
SUBPARTITION ix_my_tab_jan_04 TO ix_my_tab_jan_05;
alter index: Partition – Split
ALTER INDEX ix_my_tab SPLIT PARTITION ix_my_tab_jan_05
AT ('15-JAN-05') INTO PARTITION ix_my_tab_jan_05a
TABLESPACE myidxtbs
STORAGE (INITIAL 100m NEXT 50M FREELISTS 5);
alter index: Rebuild Nonpartitioned Indexes
ALTER INDEX ix_my_tab REBUILD ONLINE;
ALTER INDEX ix_my_tab REBUILD ONLINE
TABLESPACE idx_tbs_new PCTFREE 1
STORAGE (INITIAL 50M NEXT 50m FREELISTS 5)
COMPUTE STATISTICS PARALLEL 0;
alter index: Rebuild Partitions
ALTER INDEX ix_my_tab
REBUILD PARTITION ix_my_tab_jan_04 ONLINE;
ALTER INDEX ix_my_tab
REBUILD SUBPARTITION ix_my_tab_jan_04 ONLINE
PCTFREE 1 STORAGE (INITIAL 50M NEXT 50m FREELISTS 5)
COMPUTE STATISTICS PARALLEL 0;
alter index: Rename
ALTER INDEX ix_my_tab RENAME TO 'ix_my_tab_01';
alter index: Shrink
ALTER INDEX ix_my_tab SHRINK SPACE;
ALTER INDEX ix_my_tab SHRINK SPACE COMPACT CASCADE;
alter materialized view: Allocate and Deallocate Extents
ALTER MATERIALIZED VIEW mv_my_tab ALLOCATE EXTENT;
ALTER MATERIALIZED VIEW mv_my_tab DEALLOCATE UNUSED;
alter materialized view: Miscellaneous
ALTER MATERIALIZED VIEW mv_my_tab COMPRESS;
ALTER MATERIALIZED VIEW mv_my_tab PARALLEL 3;
ALTER MATERIALIZED VIEW mv_my_tab NOLOGGING;
ALTER MATERIALIZED VIEW mv_my_tab LOGGING;
ALTER MATERIALIZED VIEW mv_my_tab CONSIDER FRESH;
ALTER MATERIALIZED VIEW mv_my_tab ENABLE QUERY REWRITE;
alter materialized view: Physical Attributes and Storage
ALTER MATERIALIZED VIEW mv_my_tab
PCTFREE 5 PCTUSED 60
STORAGE (NEXT 100m FREELISTS 5);
alter materialized view: Refresh
ALTER MATERIALIZED VIEW mv_my_tab REFRESH FAST;
ALTER MATERIALIZED VIEW mv_my_tab REFRESH COMPLETE;
ALTER MATERIALIZED VIEW mv_my_tab REFRESH FAST ON DEMAND;
ALTER MATERIALIZED VIEW mv_my_tab REFRESH FAST ON COMMIT;
ALTER MATERIALIZED VIEW mv_my_tab REFRESH COMPLETE
START WITH sysdate;
ALTER MATERIALIZED VIEW mv_my_tab REFRESH COMPLETE
START WITH sysdate NEXT sysdate+1/24;
alter materialized view: Shrink Space
ALTER MATERIALIZED VIEW mv_my_tab SHRINK SPACE;
ALTER MATERIALIZED VIEW mv_my_tab
SHRINK SPACE COMPACT CASCADE;
alter materialized view log: Add Components
ALTER MATERIALIZED VIEW LOG ON my_tab ADD PRIMARY KEY;
ALTER MATERIALIZED VIEW LOG ON my_tab ADD (col1, col2)
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG ON my_tab ADD (col1, col2),
ROWID, SEQUENCE INCLUDING NEW VALUES;
alter materialized view log: Allocate and Deallocate Extents
ALTER MATERIALIZED VIEW LOG ON my_tab ALLOCATE EXTENT;
ALTER MATERIALIZED VIEW LOG ON my_tab DEALLOCATE UNUSED;
alter materialized view log: Miscellaneous
ALTER MATERIALIZED VIEW LOG ON my_tab PARALLEL 3;
ALTER MATERIALIZED VIEW LOG ON my_tab NOLOGGING;
ALTER MATERIALIZED VIEW LOG ON my_tab SHRINK SPACE;
alter materialized view log: Physical Attributes and Storage
ALTER MATERIALIZED VIEW LOG ON my_tab
PCTFREE 5 PCTUSED 60
STORAGE (NEXT 100m FREELISTS 5);
alter package: Compile
ALTER PACKAGE pk_my_package COMPILE;
ALTER PACKAGE pk_my_package COMPILE SPECIFICATION;
ALTER PACKAGE pk_my_package COMPILE BODY;
alter procedure: Compile
ALTER PROCEDURE pk_my_package COMPILE;
alter profile: Miscellaneous
ALTER ROLE my_role IDENTIFIED BY password;
ALTER ROLE my_role NOT IDENTIFIED;
alter profile: Modify Limits (Password)
ALTER PROFILE my_profile LIMIT FAILED_LOGIN_ATTEMPTS=3;
ALTER PROFILE my_profile LIMIT PASSWORD_LOCK_TIME=2/24;
ALTER PROFILE my_profile LIMIT PASSWORD_GRACE_TIME=5;
ALTER PROFILE my_profile LIMIT PASSWORD_LIFETIME=60;
ALTER PROFILE my_profile LIMIT PASSWORD_REUSE_TIME=365 PASSWORD_REUSE_MAX=3;
alter profile: Modify Limits (Resource)
ALTER PROFILE my_profile LIMIT SESSIONS_PER_CPU=10;
ALTER PROFILE my_profile LIMIT CONNECT_TIME=1000;
ALTER PROFILE my_profile LIMIT IDLE_TIME=60;
ALTER PROFILE my_profile LIMIT PRIVATE_SGA=1000000;
alter rollback segment: Online/Offline
ALTER ROLLBACK SEGMENT rbs01 OFFLINE;
ALTER ROLLBACK SEGMENT rbs01 ONLINE;
alter rollback segment: Shrink
ALTER ROLLBACK SEGMENT rbs01 SHRINK;
ALTER ROLLBACK SEGMENT rbs01 SHRINK TO 100M;
alter rollback segment: storage Clause
ALTER ROLLBACK SEGMENT rbs01 STORAGE(NEXT 50M OPTIMAL 100M);
alter sequence: Miscellaneous
ALTER SEQUENCE my_seq INCREMENT BY –5;
ALTER SEQUENCE my_seq INCREMENT BY 1 MAXVALUE 50000 CYCLE;
ALTER SEQUENCE my_seq NOMAXVALUE;
ALTER SEQUENCE my_seq CACHE ORDER;
ALTER SEQUENCE my_seq INCREMENT BY 1
MINVALUE 1 MAXVALUE 500 CYCLE;
alter session: Enable and Disable Parallel Operations
ALTER SESSION ENABLE PARALLEL DML PARALLEL 3;
ALTER SESSION ENABLE PARALLEL DDL;
ALTER SESSION DISABLE PARALLEL QUERY;
alter session: Resumable Space Management
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;
ALTER SESSION DISABLE RESUMABLE;
alter session: Set Session Parameters
ALTER SESSION SET nls_date_format='MM/DD/YYYY HH24:MI:SS';
ALTER SESSION SET sort_area_size=10000000;
ALTER SESSION SET query_rewrite_enabled=TRUE;
ALTER SESSION SET resumable_timeout=3600;
ALTER SESSION SET skip_unusable_indexes=TRUE;
ALTER SESSION SET SQL_TRACE=TRUE;
alter system: Logfile and Archive Logfile Management
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM ARCHIVE LOG START;
ALTER SYSTEM ARCHIVE LOG STOP;
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER SYSTEM ARCHIVE LOG THREAD 1 ALL;
ALTER SYSTEM ARCHIVE LOG ALL TO 'C:\oracle\allarch';
alter system: Set System Parameters
ALTER SYSTEM SET db_cache_size=325M
COMMENT='This change is to add more memory to the system'
SCOPE=BOTH;
ALTER SYSTEM SET COMPATIBLE=10.0.0
COMMENT='GOING TO 10G!' SCOPE=SPFILE;
alter system: System Management
ALTER SYSTEM CHECKPOINT GLOBAL;
ALTER SYSTEM KILL SESSION '145,334';
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM DISABLE RESTRICTED SESSION;
ALTER SYSTEM SUSPEND;
ALTER SYSTEM QUIESCE RESTRICTED;
ALTER SYSTEM UNQUIESCE;
ALTER SYSTEM RESUME;
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
alter table: External Table Operations
ALTER TABLE ext_parts REJECT LIMIT 500;
ALTER TABLE ext_parts DEFUALT DIRECTORY ext_employee_dir;
ALTER TABLE ext_parts ACCESS PARAMETERS
(FIELDS TERMINATED BY ’,’);
ALTER TABLE ext_parts LOCATION (’PARTS01.TXT’,’PARTS02.TXT’);
ALTER TABLE ext_parts ADD COLUMN (SSN NUMBER);
alter table: Move Table
ALTER TABLE parts move TABLESPACE parts_new_tbs PCTFREE 10 PCTUSED 60;
alter table: Table Column – Add
ALTER TABLE PARTS ADD (part_location VARCHAR2(20) );
ALTER TABLE PARTS ADD (part_location VARCHAR2(20), part_bin VARCHAR2(30) );
ALTER TABLE parts ADD (photo BLOB)
LOB (photo) STORE AS lob_parts_photo
(TABLESPACE parts_lob_tbs);
alter table: Table Column – Modify
ALTER TABLE PARTS MODIFY (part_location VARCHAR2(30) );
ALTER TABLE PARTS MODIFY
part_location VARCHAR2(30), part_bin VARCHAR2(20) );
ALTER TABLE parts modify (name NOT NULL);
ALTER TABLE parts modify (name NULL);
ALTER TABLE parts MODIFY LOB (photo) (STORAGE(FREELISTS 2));
ALTER TABLE parts MODIFY LOB (photo) (PCTVERSION 50);
alter table: Table Column – Remove
ALTER TABLE parts DROP (part_location);
ALTER TABLE parts DROP (part_location, part_bin);
alter table: Table Column – Rename
ALTER TABLE parts RENAME COLUMN part_location TO part_loc;
alter table: Table Constraints – Add Check Constraint
ALTER TABLE parts ADD (CONSTRAINT ck_parts_01 CHECK (id > 0) );
alter table: Table Constraints – Add Default Value
ALTER TABLE PARTS MODIFY (name DEFAULT 'Not Available');
ALTER TABLE PARTS ADD (vendor_code NUMBER DEFAULT 0);
ALTER TABLE PARTS MODIFY (part_description DEFAULT NULL);
alter table: Table Constraints – Add Foreign Key
ALTER TABLE parts ADD CONSTRAINT fk_part_bin
FOREIGN KEY (bin_code) REFERENCES part_bin;
alter table: Table Constraints – Add Primary and Unique Key
ALTER TABLE parts ADD CONSTRAINT pk_parts_part_id
PRIMARY KEY (id) USING INDEX TABLESPACE parts_index
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);
ALTER TABLE parts ADD CONSTRAINT uk_parts_part_bin
UNIQUE (part_bin)USING INDEX TABLESPACE parts_index
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);
alter table: Table Constraints – Modify
ALTER TABLE parts DISABLE UNIQUE (part_bin);
ALTER TABLE parts DISABLE CONSTRAINT uk_parts_part_bin;
ALTER TABLE parts DISABLE CONSTRAINT uk_parts_part_bin KEEP INDEX;
ALTER TABLE parts DISABLE CONSTRAINT fk_part_bin;
ALTER TABLE parts DISABLE CONSTRAINT fk_part_bin
DISABLE PRIMARY KEY KEEP INDEX;
ALTER TABLE parts ENABLE CONSTRAINT fk_part_bin;
ALTER TABLE parts ENABLE PRIMARY KEY;
ALTER TABLE parts ENABLE UNIQUE (part_bin);
ALTER TABLE parts ENABLE NOVALIDATE CONSTRAINT fk_part_bin;
ALTER TABLE parts ENABLE NOVALIDATE PRIMARY KEY;
ALTER TABLE parts ENABLE NOVALIDATE UNIQUE (part_bin);
ALTER TABLE parts ENABLE NOVALIDATE PRIMARY KEY
ENABLE NOVALIDATE CONSTRAINT fk_part_bin;
alter table: Table Constraints – Remove
ALTER TABLE parts DROP CONSTRAINT fk_part_bin;
ALTER TABLE parts DROP PRIMARY KEY;
ALTER TABLE parts DROP PRIMARY KEY CASCADE;
ALTER TABLE parts DROP UNIQUE (uk_parts_part_bin);
alter table: Table Partition – Add
ALTER TABLE store_sales ADD PARTITION sales_q1_04
VALUES LESS THAN (TO_DATE('01-APR-2004','DD-MON-YYYY'))
TABLESPACE data_0104_tbs UPDATE GLOBAL INDEXES;
ALTER TABLE daily_transactions ADD PARTITION;
ALTER TABLE daily_transactions
ADD PARTITION Alaska VALUES ('AK');
ALTER TABLE daily_transactions
add PARTITION SALES_2004_Q1 VALUES LESS THAN
(TO_DATE('01-APR-2004','DD-MON-YYYY')) SUBPARTITIONS 4;
alter table: Table Partition – Merge
ALTER TABLE store_sales
MERGE PARTITIONS Oklahoma, texas
INTO PARTITION oktx;
alter table: Table Partition – Move
ALTER TABLE store_sales MOVE PARTITION sales_overflow TABLESPACE
new_sales_overflow STORAGE (INITIAL 100m NEXT 100m PCTINCREASE 0)
UPDATE GLOBAL INDEXES;
alter table: Table Partition – Remove
ALTER TABLE store_sales DROP PARTITION sales_q1_04 UPDATE GLOBAL INDEXES;
alter table: Table Partition – Rename
ALTER TABLE store_sales RENAME PARTITION sales_q1 TO sales_first_quarter;
alter table: Table Partition – Split
ALTER TABLE store_sales
SPLIT PARTITION sales_overflow AT
(TO_DATE('01-FEB-2004','DD-MON-YYYY') )
INTO (PARTITION sales_q4_2003,
PARTITION sales_overflow)
UPDATE GLOBAL INDEXES;
ALTER TABLE composite_sales SPLIT PARTITION sales_q1
AT (TO_DATE('15-FEB-2003','DD-MON-YYYY'))
INTO (PARTITION sales_q1_01 SUBPARTITIONS 4
STORE IN (q1_01_tab1, q1_01_tab2, q1_01_tab3, q1_01_tab4),
PARTITION sales_q1_02 SUBPARTITIONS 4
STORE IN (q1_02_tab1, q1_02_tab2, q1_02_tab3, q1_02_tab4) )
UPDATE GLOBAL INDEXES;
alter table: Table Partition – Truncate
ALTER TABLE store_sales TRUNCATE PARTITION sales_overflow
UPDATE GLOBAL INDEXES;
alter table: Table Properties
ALTER TABLE parts PCTFREE 10 PCTUSED 60;
ALTER TABLE parts STORAGE (NEXT 1M);
ALTER TABLE parts PARALLEL 4;
alter table: Triggers – Modify Status
ALTER TABLE parts DISABLE ALL TRIGGERS;
ALTER TABLE parts ENABLE ALL TRIGGERS;
alter tablespace: Backups
ALTER TABLESPACE my_data_tbs BEGIN BACKUP;
ALTER TABLESPACE my_data_tbs END BACKUP;
alter tablespace: Data Files and Tempfiles
ALTER TABLESPACE mytbs
ADD DATAFILE '/ora100/oracle/mydb/mydb_mytbs_01.dbf' SIZE 100M;
ALTER TABLESPACE mytemp
ADD TEMPFILE '/ora100/oracle/mydb/mydb_mytemp_01.dbf'
SIZE 100M;
ALTER TABLESPACE mytemp AUTOEXTEND OFF;
ALTER TABLESPACE mytemp AUTOEXTEND ON NEXT 100m MAXSIZE 1G;
alter tablespace: Rename
ALTER TABLESPACE my_data_tbs RENAME TO my_newdata_tbs;
alter tablespace: Tablespace Management
ALTER TABLESPACE my_data_tbs DEFAULT
STORAGE (INITIAL 100m NEXT 100m FREELISTS 3);
ALTER TABLESPACE my_data_tbs MINIMUM EXTENT 500k;
ALTER TABLESPACE my_data_tbs RESIZE 100m;
ALTER TABLESPACE my_data_tbs COALESCE;
ALTER TABLESPACE my_data_tbs OFFLINE;
ALTER TABLESPACE my_data_tbs ONLINE;
ALTER TABLESPACE mytbs READ ONLY;
ALTER TABLESPACE mytbs READ WRITE;
ALTER TABLESPACE mytbs FORCE LOGGING;
ALTER TABLESPACE mytbs NOLOGGING;
ALTER TABLESPACE mytbs FLASHBACK ON;
ALTER TABLESPACE mytbs FLASHBACK OFF;
ALTER TABLESPACE mytbs RETENTION GUARANTEE;
ALTER TABLESPACE mytbs RETENTION NOGUARANTEE;
alter trigger
ALTER TRIGGER tr_my_trigger DISABLE;
ALTER TRIGGER tr_my_trigger ENABLE;
ALTER TRIGGER tr_my_trigger RENAME TO tr_new_my_trigger;
ALTER TRIGGER tr_my_trigger COMPILE;
alter user: Change Password
ALTER USER olduser IDENTIFIED BY newpassword;
ALTER USER olduser IDENTIFIED EXTERNALLY;
alter user: Password and Account Management
ALTER USER olduser PASSWORD EXPIRE;
ALTER USER olduser ACCOUNT LOCK;
ALTER USER olduser ACCOUNT UNLOCK;
alter user: Profile
ALTER USER olduser PROFILE admin_profile;
alter user: Quotas
ALTER USER olduser QUOTA UNLIMITED ON users;
ALTER USER olduser QUOTA 10000M ON USERS;
alter user: Roles
ALTER USER olduser DEFAULT ROLE admin_role;
ALTER USER olduser DEFAULT ROLE NONE;
ALTER USER olduser DEFAULT ROLE ALL EXCEPT admin_role;
alter user: Tablespace Assignments
ALTER USER olduser DEFAULT TABLESPACE users;
ALTER USER olduser TEMPORARY TABLESPACE temp;
alter view: Constraints
ALTER VIEW my_view
ADD CONSTRAINT u_my_view_01 UNIQUE (empno)
RELY DISABLE NOVALIDATE;
ALTER VIEW my_view DROP CONSTRAINT u_my_view_01;
ALTER VIEW my_view DROP PRIMARY KEY;
ALTER VIEW my_view MODIFY CONSTRAINT u_my_view_01 NORELY;
ALTER VIEW my_view MODIFY CONSTRAINT u_my_view_01 RELY;
alter view: Recompile
ALTER VIEW my_view RECOMPILE;
analyze: Analyze Cluster
ANALYZE CLUSTER my_cluster_tab COMPUTE STATISTICS FOR ALL ROWS;
ANALYZE CLUSTER my_cluster_tab
ESTIMATE STATISTICS SAMPLE 10000 ROWS FOR ALL ROWS;
analyze: Analyze Index
ANALYZE INDEX ix_tab_01 COMPUTE STATISTICS FOR ALL ROWS;
ANALYZE INDEX ix_tab_01
ESTIMATE STATISTICS SAMPLE 10000 ROWS FOR ALL ROWS;
analyze: Analyze Table
ANALYZE TABLE mytab COMPUTE STATISTICS
FOR ALL INDEXED COLUMNS SIZE 100;
ANALYZE TABLE mytab COMPUTE STATISTICS
FOR ALL INDEXES;
audit
AUDIT ALL ON scott.emp;
AUDIT UPDATE, DELETE ON scott.emp;
AUDIT SELECT on scott.emp WHENEVER NOT SUCCESSFUL;
AUDIT INSERT, UPDATE, DELETE ON DEFAULT;
comment
COMMENT ON TABLE scott.mytab IS
'This is a comment on the mytab table';
COMMENT ON COLUMN scott.mytab.col1 IS
'This is a comment on the col1 column';
COMMENT ON MATERIALIZED VIEW scott.mview IS
'This is a comment on the materialized view mview';
create cluster
CREATE CLUSTER pub_cluster (pubnum NUMBER)
SIZE 8K PCTFREE 10 PCTUSED 60 TABLESPACE user_data;
CREATE CLUSTER pub_cluster (pubnum NUMBER)
SIZE 8K HASHKEYS 1000 PCTFREE 10 PCTUSED 60
TABLESPACE user_data;
create control file
CREATE CONTROLFILE REUSE DATABASE "mydb"
NORESETLOGS NOARCHIVELOG
MAXLOGFILES 32 MAXLOGMEMBERS 3
MAXDATAFILES 200 MAXINSTANCES 1
MAXLOGHISTORY 1000
LOGFILE
GROUP 1 ('/ora01/oracle/mydb/mydb_redo1a.rdo',
'/ora02/oracle/mydb/mydb_redo1b.rdo') SIZE 500K,
GROUP 2 ('/ora01/oracle/mydb/mydb_redo2a.rdo',
'/ora01/oracle/mydb/mydb_redo2b.rdo') SIZE 500K
DATAFILE
'/ora01/oracle/mydb/mydb_system_01.dbf ',
'/ora01/oracle/mydb/mydb_users_01.dbf ',
'/ora01/oracle/mydb/mydb_undo_01.dbf ',
'/ora01/oracle/mydb/mydb_sysaux_01.dbf ',
'/ora01/oracle/mydb/mydb_alldata_01.dbf ';
create database
CREATE DATABASE prodb
MAXINSTANCES 1 MAXLOGHISTORY 1
MAXLOGFILES 5 MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE 'C:\oracle\ora92010\prodb\system01.dbf'
SIZE 250M REUSE AUTOEXTEND ON NEXT 10240K
MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL DEFAULT
TEMPORARY TABLESPACE TEMP
TEMPFILE 'C:\oracle\ora92010\prodb\temp01.dbf'
SIZE 40M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SYSAUX TABLESPACE
DATAFILE 'C:\oracle\ora92010\prodb\sysauxtbs01.dbf'
SIZE 300M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1"
DATAFILE 'C:\oracle\ora92010\prodb\undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
LOGFILE
GROUP 1 ('C:\oracle\ora92010\prodb\redo01.log') SIZE 102400K,
GROUP 2 ('C:\oracle\ora92010\prodb\redo02.log') SIZE 102400K,
GROUP 3 ('C:\oracle\ora92010\prodb\redo03.log') SIZE 102400K;
create database link
CREATE DATABASE LINK my_db_link
CONNECT TO current_user
USING 'my_db';
CREATE PUBLIC DATABASE LINK my_db_link
CONNECT TO remote_user IDENTIFIED BY psicorp
USING 'my_db';
create directory
CREATE OR REPLACE DIRECTORY mydir AS
'/opt/oracle/admin/directories/mydir';
create function
CREATE OR REPLACE FUNCTION find_value_in_table
(p_value IN NUMBER, p_table IN VARCHAR2,
p_column IN VARCHAR2)
RETURN NUMBER IS
v_found NUMBER;
v_sql VARCHAR2(2000);
BEGIN
v_sql:='SELECT 1 FROM '||p_table||' WHERE '||p_column||
' = '||p_value;
execute immediate v_sql into v_found;
return v_found;
END;
/
create index: Function-Based Index
CREATE INDEX fb_upper_last_name_emp ON emp_info (UPPER(last_name) );
create index: Global Partitioned Indexes
CREATE INDEX ix_part_my_tab_01 ON store_sales (invoice_number)
GLOBAL PARTITION BY RANGE (invoice_number)
(PARTITION part_001 VALUES LESS THAN (1000),
PARTITION part_002 VALUES LESS THAN (10000),
PARTITION part_003 VALUES LESS THAN (MAXVALUE) );
CREATE INDEX ix_part_my_tab_02 ON store_sales
(store_id, time_id)
GLOBAL PARTITION BY RANGE (store_id, time_id)
(PARTITION PART_001 VALUES LESS THAN
(1000, TO_DATE('04-01-2003','MM-DD-YYYY') )
TABLESPACE partition_001
STORAGE (INITIAL 100M NEXT 200M PCTINCREASE 0),
PARTITION part_002 VALUES LESS THAN
(1000, TO_DATE('07-01-2003','MM-DD-YYYY') )
TABLESPACE partition_002
STORAGE (INITIAL 200M NEXT 400M PCTINCREASE 0),
PARTITION part_003 VALUES LESS THAN (maxvalue, maxvalue)
TABLESPACE partition_003 );
create index: Local Partitioned Indexes
CREATE INDEX ix_part_my_tab_01 ON my_tab
(col_one, col_two, col_three)
LOCAL (PARTITION tbs_part_01 TABLESPACE part_tbs_01,
PARTITION tbs_part_02 TABLESPACE part_tbs_02,
PARTITION tbs_part_03 TABLESPACE part_tbs_03,
PARTITION tbs_part_04 TABLESPACE part_tbs_04);
CREATE INDEX ix_part_my_tab_01 ON my_tab (col_one, col_two, col_three)
LOCAL STORE IN (part_tbs_01, part_tbs_02, part_tbs_03, part_tbs_04);
CREATE INDEX ix_part_my_tab_01 ON my_tab (col_one, col_two, col_three)
LOCAL STORE IN (
part_tbs_01 STORAGE (INITIAL 10M NEXT 10M MAXEXTENTS 200),
part_tbs_02,
part_tbs_03 STORAGE (INITIAL 100M NEXT 100M MAXEXTENTS 200),
part_tbs_04 STORAGE (INITIAL 1000M NEXT 1000M MAXEXTENTS 200));
create index: Local Subpartitioned Indexes
CREATE INDEX sales_ix ON store_sales(time_id, store_id)
STORAGE (INITIAL 1M MAXEXTENTS UNLIMITED) LOCAL
(PARTITION q1_2003,
PARTITION q2_2003,
PARTITION q3_2003
(SUBPARTITION pq3200301, SUBPARTITION pq3200302,
SUBPARTITION pq3200303, SUBPARTITION pq3200304,
SUBPARTITION pq3200305),
PARTITION q4_2003
(SUBPARTITION pq4200301 TABLESPACE tbs_1,
SUBPARTITION pq4200302 TABLESPACE tbs_1,
SUBPARTITION pq4200303 TABLESPACE tbs_1,
SUBPARTITION pq4200304 TABLESPACE tbs_1,
SUBPARTITION pq4200305 TABLESPACE tbs_1,
SUBPARTITION pq4200306 TABLESPACE tbs_1,
SUBPARTITION pq4200307 TABLESPACE tbs_1,
SUBPARTITION pq4200308 TABLESPACE tbs_1),
PARTITION sales_overflow
(SUBPARTITION pqoflw01 TABLESPACE tbs_2,
SUBPARTITION pqoflw02 TABLESPACE tbs_2,
SUBPARTITION pqoflw03 TABLESPACE tbs_2,
SUBPARTITION pqoflw04 TABLESPACE tbs_2));
create index: Nonpartitioned Indexes
CREATE INDEX ix_mytab_01 ON mytab(column_1);
CREATE UNIQUE INDEX ix_mytab_01 ON mytab(column_1, column_2, column_3);
CREATE INDEX ix_mytab_01 ON mytab(column_1, column_2, column_3)
TABLESPACE my_indexes COMPRESS
STORAGE (INITIAL 10K NEXT 10K PCTFREE 10) COMPUTE STATISTICS;
CREATE BITMAP INDEX bit_mytab_01 ON my_tab(col_two)
TABLESPACE my_tbs;
create materialized view
CREATE MATERIALIZED VIEW emp_dept_mv1
TABLESPACE users BUILD IMMEDIATE
REFRESH FAST ON COMMIT WITH ROWID
ENABLE QUERY REWRITE AS
SELECT d.rowid deptrowid, e.rowid emprowid,
e.empno, e.ename, e.job, d.loc
FROM dept d, emp e
WHERE d.deptno = e.deptno;
CREATE MATERIALIZED VIEW emp_dept_mv3
TABLESPACE users BUILD IMMEDIATE
REFRESH FAST ON COMMIT WITH ROWID
DISABLE QUERY REWRITE AS
SELECT d.rowid deptrowid, e.rowid emprowid,
d.dname, d.loc, e.ename, e.job
FROM dept d, emp e
WHERE d.deptno (+) = e.deptno;
create materialized view: Partitioned Materialized View
CREATE MATERIALIZED VIEW part_emp_mv1
PARTITION BY RANGE (hiredate)
(PARTITION month1
VALUES LESS THAN (TO_DATE('01-APR-1981', 'DD-MON-YYYY'))
PCTFREE 0 PCTUSED 99
STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
TABLESPACE users,
PARTITION month2
VALUES LESS THAN (TO_DATE('01-DEC-1981', 'DD-MON-YYYY'))
PCTFREE 0 PCTUSED 99
STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
TABLESPACE users,
PARTITION month3
VALUES LESS THAN (TO_DATE('01-APR-1988', 'DD-MON-YYYY'))
PCTFREE 0 PCTUSED 99
STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
TABLESPACE users)
BUILD IMMEDIATE REFRESH FAST ENABLE QUERY REWRITE AS
SELECT hiredate, count(*) as hires
FROM emp
GROUP BY hiredate;
create materialized view log
CREATE MATERIALIZED VIEW LOG ON emp
TABLESPACE users
WITH PRIMARY KEY, SEQUENCE,
(ename, job, mgr, hiredate, sal, comm, deptno)
INCLUDING NEW VALUES;
create package/create package body
CREATE OR REPLACE PACKAGE get_Tomdate_pkg IS
FUNCTION GetTomdate RETURN DATE;
PRAGMA RESTRICT_REFERENCES (GetTomdate, WNDS);
PROCEDURE ResetSysDate;
END get_Tomdate_pkg;
/
CREATE OR REPLACE PACKAGE BODY get_Tomdate_pkg IS
v_Sysdate DATE := TRUNC(SYSDATE);
FUNCTION GetTomdate RETURN DATE IS
BEGIN
RETURN v_sysdate+1;
END GetTomdate;
PROCEDURE ResetSysdate IS
BEGIN
v_Sysdate := SYSDATE;
END ResetSysdate;
END get_Tomdate_pkg;
/
create pfile
CREATE PFILE FROM SPFILE;
CREATE PFILE='/opt/oracle/admin/mydb/pfile/initmybd.ora'
FROM SPFILE='/opt/oracle/admin/mydb/pfile/spfilemybd.ora';
create procedure
CREATE OR REPLACE PROCEDURE new_emp_salary
(p_empid IN NUMBER, p_increase IN NUMBER)
AS
BEGIN
UPDATE emp SET salary=salary*p_increase
WHERE empid=p_empid;
END;
/
create profile
CREATE PROFILE development_profile
LIMIT
SESSIONS_PER_USER 2 CONNECT_TIME 100000 IDLE_TIME 100000
LOGICAL_READS_PER_SESSION 1000000
PRIVATE_SGA 10m
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 3
PASSWORD_LOCK_TIME 30
PASSWORD_GRACE_TIME 5;
create role
CREATE ROLE developer_role IDENTIFIED USING develop;
create rollback segment
CREATE ROLLBACK SEGMENT r01 TABLESPACE RBS
STORAGE (INITIAL 100m NEXT 100M MINEXTENTS 5 OPTIMAL 500M);
create sequence
CREATE SEQUENCE my_seq
START WITH 1 INCREMENT BY 1 MAXVALUE 1000000 CYCLE CACHE;
create spfile
CREATE SPFILE FROM PFILE;
CREATE SPFILE='/opt/oracle/admin/mydb/pfile/spfilemybd.ora'
FROM PFILE='/opt/oracle/admin/mydb/pfile/initmybd.ora';
create synonym
CREATE SYNONYM scott_user.emp FOR scott.EMP;
CREATE PUBLIC SYNONYM emp FOR scott.EMP;
create table
CREATE TABLE my_tab
(id NUMBER, current_value VARCHAR2(2000) ) COMPRESS;
CREATE TABLE parts (id NUMBER, version NUMBER, name VARCHAR2(30),
Bin_code NUMBER, upc NUMBER, active_code VARCHAR2(1) NOT NULL
CONSTRAINT ck_parts_active_code_01
CHECK (UPPER(active_code)= 'Y' or UPPER(active_code)='N'),
CONSTRAINT pk_parts PRIMARY KEY (id, version)
USING INDEX TABLESPACE parts_index
STORAGE (INITIAL 1m NEXT 1m) )
TABLESPACE parts_tablespace
PCTFREE 20 PCTUSED 60 STORAGE ( INITIAL 10m NEXT 10m PCTINCREASE 0);
create tablespace: Permanent Tablespace
CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m;
CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m FORCE LOGGING BLOCKSIZE 8k;
CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m NOLOGGING
DEFAULT COMPRESS EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m NOLOGGING
DEFAULT COMPRESS EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 10G;
create tablespace: Temporary Tablespace
CREATE TABLESPACE temp_tbs
TEMPFILE '/opt/oracle/mydbs/data/mydbs_temp_tbs_01.tmp'
SIZE 100m;
create tablespace: Undo Tablespace
CREATE TABLESPACE undo_tbs
TEMPFILE '/opt/oracle/mydbs/data/mydbs_undo_tbs_01.tmp'
SIZE 1g RETENTION GUARANTEE;
create trigger
CREATE OR REPLACE TRIGGER emp_comm_after_insert
BEFORE INSERT ON emp FOR EACH ROW
DECLARE
v_sal number;
v_comm number;
BEGIN
-- Find username of person performing the INSERT into the table
v_sal:=:new.salary;
:new.comm:=v_sal*.10;
END;
/
create user
CREATE USER Robert IDENTIFIED BY Freeman
DEFAULT TABLESPACE users_tbs
TEMPORARY TABLESPACE temp
QUOTA 100M ON users_tbs
QUOTA UNLIMITED ON data_tbs;
create view
CREATE OR REPLACE VIEW vw_emp_dept_10 AS
SELECT * FROM EMP WHERE dept=10;
CREATE OR REPLACE VIEW vw_public_email AS
SELECT ename_first, ename_last, email_address
FROM EMP WHERE public='Y'
delete
DELETE FROM emp WHERE empid=100;
DELETE FROM emp e WHERE e.rowid >
(SELECT MIN (esub.ROWID) FROM emp esub
WHERE e.empid=esub.empid);
drop cluster
DROP CLUSTER scott.emp_cluster
INCLUDING TABLES CASCADE CONSTRAINTS;
drop database
DROP DATABASE;
drop database link
DROP DATABASE LINK my_db_link;
DROP PUBLIC DATABASE LINK my_db_link;
drop directory
DROP DIRECTORY mydir;
drop function
DROP FUNCTION find_value_in_table;
drop index
DROP INDEX ix_my_tab;
drop materialized view
DROP MATERIALIZED VIEW my_mview;
DROP MATERIALIZED VIEW my_mview PRESERVE TABLE;
drop materialized view log
DROP MATERIALIZED VIEW LOG ON mytab;
drop package/drop package body
DROP PACKAGE scott.my_package
DROP PACKAGE BODY scott.my_package;
drop procedure
DROP PROCEDURE my_proc;
drop profile
DROP PROFILE my_profile CASCADE;
drop role
DROP ROLE my_role;
drop rollback segment
DROP ROLLBACK SEGMENT rbs01;
drop sequence
DROP SEQUENCE my_seq;
drop synonym
DROP SYNONYM my_synonym;
DROP PUBLIC SYNONYM my_synonym;
drop table
DROP TABLE my_tab;
DROP TABLE my_tab CASCADE CONSTRAINTS;
DROP TABLE my_tab CASCADE CONSTRAINTS PURGE;
drop tablespace
DROP TABLESPACE my_tbs;
DROP TABLESPACE my_tbs INCLUDING CONTENTS;
DROP TABLESPACE my_tbs INCLUDING CONTENTS
AND DATAFILES CASCADE CONSTRAINTS;
drop trigger
DROP TRIGGER my_trigger;
drop user
DROP USER my_user CASCADE;
drop view
DROP VIEW my_view CASCADE CONSTRAINTS;
explain plan
EXPLAIN PLAN SET STATEMENT_ID='TEST' FOR
SELECT * FROM emp WHERE EMPID=100;
flashback database
FLASHBACK DATABASE TO SCN 10000;
FLASHBACK DATABASE TO TIMESTAMP SYSDATE – 1/24;
FLASHBACK DATABASE TO BEFORE TIMESTAMP SYSDATE – 1/24;
flashback table
FLASHBACK TABLE my_tab TO SCN 10000;
FLASHBACK TABLE my_tab TO TIMESTAMP SYSDATE – 1/24
ENABLE TRIGGERS;
FLASHBACK TABLE my_tab TO BEFORE DROP;
FLASHBACK TABLE my_tab TO BEFORE DROP RENAME TO rec_tab;
grants: Object Grants
GRANT SELECT ON scott.my_tab TO my_user;
GRANT INSERT, UPDATE, SELECT ON scott.my_tab TO my_user;
GRANT SELECT ON scott.my_tab TO my_user WITH GRANT OPTION;
GRANT SELECT ON scott.my_tab TO PUBLIC WITH GRANT OPTION;
grants: System Grants
GRANT CREATE TABLE to my_user;
GRANT CREATE ANY TABLE to my_user WITH ADMIN OPTION;
GRANT ALL PRIVILEGES to my_user WITH ADMIN OPTION;
insert
INSERT INTO dept VALUES (100, 'Marketing', 'Y');
INSERT INTO dept (deptid, dept_name, active)
VALUES (100, 'Marketing', 'Y');
INSERT INTO emp_history SELECT * FROM emp a
WHERE a.empid NOT IN (SELECT empid FROM emp_history);
INSERT INTO emp_pay_summary
SELECT empid, sum(gross_pay) FROM emp_pay_history
GROUP BY empid;
INSERT ALL
INTO store_sales (store_id, sales_date, deptid, sales_amt)
VALUES (store_id, start_date, deptid, mon_sales)
INTO store_sales (store_id, sales_date, deptid, sales_amt)
VALUES (store_id, start_date+1, deptid, tue_sales)
INTO store_sales (store_id, sales_date, deptid, sales_amt)
VALUES (store_id, start_date+2, deptid, wed_sales)
INTO store_sales (store_id, sales_date, deptid, sales_amt)
VALUES (store_id, start_date+3, deptid, thur_sales)
INTO store_sales (store_id, sales_date, deptid, sales_amt)
VALUES (store_id, start_date+4, deptid, fri_sales)
INTO store_sales (store_id, sales_date, deptid, sales_amt)
VALUES (store_id, start_date+5, deptid, sat_sales)
INTO store_sales (store_id, sales_date, deptid, sales_amt)
VALUES (store_id, start_date+6, deptid, sun_sales)
SELECT store_id, start_date, deptid, mon_sales, tue_sales,
wed_sales, thur_sales, fri_sales, sat_sales, sun_sales
FROM store_sales_load;
INSERT ALL
WHEN store_id < 100 THEN INTO east_stores
WHEN store_id >= 100 THEN INTO west_stores
ELSE INTO misc_stores
SELECT * FROM store_sales_load;
INSERT /*+ APPEND */ INTO emp VALUES (100,
'Jacob','Freeman',1000,20, null, 10, sysdate, 100,
sysdate+365);
lock table
LOCK TABLE my_table IN EXCLUSIVE MODE NOWAIT;
LOCK TABLE my_table IN ROW EXCLUSIVE MODE;
merge
MERGE INTO emp_retire A
USING (SELECT empno, ename_last, ename_first, salary
FROM emp WHERE retire_cd='Y') B
ON (a.empid=b.empid)
WHEN MATCHED THEN UPDATE SET
a.ename_last=b.ename_last,
a.ename_first=b.ename_first,
a.salary=b.salary
DELETE WHERE (b.retire_cd='D')
WHEN NOT MATCHED THEN INSERT
(a.empid, a.ename_last, a.ename_first, a.salary)
VALUES (b.empid, b.ename_last, b.ename_first, b.salary)
WHERE (b.retire_cd!='D');
noaudit
NOAUDIT ALL ON scott.emp;
NOAUDIT UPDATE, DELETE ON scott.emp;
NOAUDIT SELECT on scott.emp WHENEVER NOT SUCCESSFUL;
NOAUDIT INSERT, UPDATE, DELETE ON DEFAULT;
purge
PURGE TABLE my_tab;
PURGE INDEX ix_my_tab;
PURGE RECYCLEBIN;
PURGE DBA_RECYCLEBIN;
PURGE TABLESPACE data_tbs USER scott;
recover
RECOVER DATABASE;
RECOVER TABLESPACE user_data, user_index;
RECOVER DATAFILE
'/opt/oracle/admin/mydb/datafile/mydb_users_01.dbf';
RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
RECOVER DATABASE UNTIL CHANGE 94044;
RECOVER DATABASE UNTIL TIME '2004-08-01:22:00:04';
rename
RENAME my_table to my_tab;
revoke: Object Grants
REVOKE SELECT ON scott.my_tab FROM my_user;
REVOKE INSERT, UPDATE, SELECT ON scott.my_tab FROM my_user;
REVOKE SELECT ON scott.my_tab FROM my_user;
REVOKE SELECT ON scott.my_tab FROM PUBLIC;
revoke: System Grants
REVOKE CREATE TABLE FROM my_user;
REVOKE CREATE ANY TABLE FROM my_user;
REVOKE ALL PRIVILEGES FROM my_user;
rollback
ROLLBACK;
savepoint
SAVEPOINT alpha;
select
SELECT ename_last, dname
FROM emp a, dept b
WHERE a.deptid=b.deptid;
SELECT a.empid, b.dept_name
FROM emp a, dept b
WHERE a.deptid=b.deptid (+);
SELECT a.empid, b.dept_name
FROM emp a LEFT OUTER JOIN dept b
ON a.deptid=b.deptid;
SELECT * FROM dept WHERE EXISTS
(SELECT * FROM emp
WHERE emp.deptid=dept.deptid
AND emp.salary > 100);
SELECT ename_first, ename_last,
CASE deptid
WHEN 10 THEN 'Acounting' WHEN 20 THEN 'Sales'
ELSE 'None' END FROM emp;
SELECT empid, ename_last, salary, comm
FROM emp a
WHERE salary*.10 > (SELECT AVG(comm) FROM emp z
WHERE a.deptid=z.deptid);
WITH avg_dept_sales AS (
SELECT a.deptid, avg(b.sales_amt) avg_sales
FROM emp a, dept_sales b
WHERE a.deptid=b.deptid
GROUP BY a.deptid),
emp_salaries AS
(SELECT empid, AVG(salary) avg_salary FROM emp
GROUP BY empid)
SELECT * FROM emp_salaries b WHERE avg_salary*.05 >
(SELECT avg_sales FROM avg_dept_sales);
SELECT /*+ INDEX (a, emp_last_name_ix) */ empid
FROM emp a WHERE ename_last='Freeman'
SELECT empid, TO_CHAR(retire_date, 'MM/DD/YYYY')
FROM emp
WHERE retire_date IS NOT NULL
ORDER BY retire_date
SELECT empid, COUNT(*)
FROM emp
GROUP BY empid
HAVING COUNT(*) > 1;
SELECT empid, salary FROM emp
AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '1' DAY)
WHERE empid=20;
SELECT empid, salary FROM emp
VERSIONS BETWEEN
TIMESTAMP SYSTIMESTAMP - INTERVAL '1' DAY AND
SYSTIMESTAMP - INTERVAL '1' HOUR
WHERE empid=20;
set constraints
SET CONSTRAINTS ALL IMMEDIATE;
SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINT fk_my_tab DEFERRED;
set transaction
SET TRANSACTION USE ROLLBACK SEGMENT rbs01;
SET TRANSACTION READ ONLY;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
truncate
TRUNCATE TABLE my_tab;
TRUNCATE TABLE my_tab PRESERVE MATERIALIZED VIEW LOG;
TRUNCATE TABLE my_tab REUSE STORAGE;
TRUNCATE TABLE my_tab DROP STORAGE;
update
UPDATE emp SET salary=100 WHERE empid=100;
UPDATE emp SET salary=NULL, retire_date=SYSDATE
WHERE empid=100;
UPDATE emp SET salary=salary*1.10
WHERE deptid IN
(SELECT deptid FROM dept WHERE dept_name = 'Sales');
UPDATE emp a SET (salary, comm)=
(SELECT salary*1.10, comm*1.10
FROM emp b WHERE a.empid=b.empid);
INSERT INTO store_sales
PARTITION (store_sales_jan_2004) sa
SET sa.sales_amt=1.10 where store_id=100;
One note of caution: if you don’t know what a specific keyword of a command does, don’t use it without checking out its purpose. This is a reference for those who understand what something like cascade constraints means when associated with a drop table command. So, without further delay, let’s get on with the examples!
alter cluster
ALTER CLUSTER pub_cluster SIZE 4K;
ALTER CLUSTER pub_cluster DEALLOCATE UNUSED KEEP 1M;
alter database: Alter a Data File
ALTER DATABASE DATAFILE 4 OFFLINE;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf' OFFLINE;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf' RESIZE 100m;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 1000M;
ALTER DATABASE DATAFILE 4 END BACKUP;
alter database: Alter a Tempfile
ALTER DATABASE TEMPFILE 4 RESIZE 100M;
ALTER DATABASE TEMPFILE 4
AUTOEXTEND ON NEXT 100M MAXSIZE 1000M;
ALTER DATABASE TEMPFILE 4 DROP INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE 4 OFFLINE;
alter database: ARCHIVELOG Mode Commands
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE NOARCHIVELOG;
ALTER DATABASE FORCE LOGGING;
ALTER DATABASE CLEAR LOGFILE '/opt/oracle/logfiles/redo01.rdo';
ALTER DATABASE CLEAR UNARCHIVED LOGFILE
'/opt/oracle/logfiles/redo01.rdo';
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE);
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
alter database: Control File Operations
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
ALTER DATABASE BACKUP CONTROLFILE TO TRACE
AS '/opt/oracle/logfile_backup/backup_logfile.trc'
REUSE RESETLOGS;
ALTER DATABASE BACKUP CONTROLFILE TO
'/opt/oracle/logfile_backup/backup_logfile.ctl';
alter database: Create a Data File
ALTER DATABASE CREATE DATAFILE
'/opt/oracle/datafile/users01.dbf' AS '/opt/oracle/datafile/users01.dbf';
ALTER DATABASE CREATE DATAFILE 4
AS '/opt/oracle/datafile/users01.dbf';
ALTER DATABASE CREATE DATAFILE
'/opt/oracle/datafile/users01.dbf' AS NEW;
alter database: Datafile Offline/Online
See alter database: Alter a Data File
alter database: Logfile Commands
ALTER DATABASE ADD LOGFILE GROUP 2
('/opt/oracle/logfiles/redo02a.rdo', '/opt/oracle/logfiles/redo02b.rdo')
SIZE 300M REUSE;
ALTER DATABASE ADD LOGFILE MEMBER
'/opt/oracle/logfiles/redo02c.rdo'
to GROUP 2;
ALTER DATABASE ADD LOGFILE thread 3 GROUP 2
('/opt/oracle/logfiles/redo02a.rdo', '/opt/oracle/logfiles/redo02b.rdo')
SIZE 300M REUSE;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE MEMBER '/opt/oracle/logfiles/redo02b.rdo';
alter database: Mount and Open the Database
ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;
alter database: Move or Rename a Database File or Online Redo Log
Note Note The database must be mounted to rename or move online redo logs. The database must be mounted or the data files taken offline to move database data files.
ALTER DATABASE RENAME FILE '/ora/datafile/oldfile.dbf' TO '/ora/datafile/newfile.dbf';
alter database: Open the Database Read-Only
ALTER DATABASE OPEN READ ONLY;
alter database: Open the Database with resetlogs
ALTER DATABASE OPEN RESETLOGS;
alter database: Recover the Database
For database recovery, I recommend the use of the recover command instead. See the “recover” section, later in the chapter.
alter function: Recompile a Function
ALTER FUNCTION my_function COMPILE;
alter index: Allocate and Deallocate Extents
ALTER INDEX ix_my_tab ALLOCATE EXTENT;
ALTER INDEX ix_my_tab ALLOCATE EXTENT
DATAFILE '/ora/datafile/newidx.dbf';
ALTER INDEX ix_my_tab DEALLOCATE UNUSED;
ALTER INDEX ix_my_tab DEALLOCATE UNUSED KEEP 100M;
alter index: Miscellaneous Maintenance
ALTER INDEX ix_my_tab PARALLEL 3;
ALTER INDEX ix_my_tab NOPARALLEL;
ALTER INDEX ix_my_tab NOCOMPRESS;
ALTER INDEX ix_my_tab COMPRESS;
alter index: Modify Logging Attributes
ALTER INDEX ix_my_tab LOGGING;
ALTER INDEX ix_my_tab NOLOGGING;
alter index: Modify Storage and Physical Attributes
ALTER INDEX ix_my_tab PCTFREE 10 PCTUSED 40 INITRANS 5
STORAGE (NEXT 100k MAXEXTENTS UNLIMITED FREELISTS 10
BUFFER_POOL KEEP);
alter index: Partition – Add Hash Index Partition
ALTER INDEX ix_my_tab ADD PARTITION
TABLESPACE NEWIDXTBS;
alter index: Partition – Coalesce Partition
ALTER INDEX ix_my_tab COALESCE PARTITION;
alter index: Partition – Drop Partition
ALTER INDEX ix_my_tab DROP PARTITION ix_my_tab_jan_04;
alter index: Partition – Modify Default Attributes
ALTER INDEX ix_my_tab MODIFY DEFAULT ATTRIBUTES
FOR PARTITION ix_my_tab_jan_04
PCTFREE 10 PCTUSED 40 TABLESPACE newidxtbs
NOLOGGING COMPRESS;
alter index: Partition – Modify Partition
ALTER INDEX ix_my_tab MODIFY PARTITION ix_my_tab_jan_04
DEALLOCATE UNUSED KEEP 100M;
ALTER INDEX ix_my_tab MODIFY PARTITION ix_my_tab_jan_04
ALLOCATE EXTENT SIZE 100m;
ALTER INDEX ix_my_tab MODIFY PARTITION ix_my_tab_jan_04
PCTUSED 40 STORAGE(NEXT 50m) NOLOGGING;
alter index: Partition – Modify Subpartition
ALTER INDEX ix_my_tab MODIFY SUBPARTITION ix_my_tab_jan_04
DEALLOCATE UNUSED KEEP 100M;
ALTER INDEX ix_my_tab MODIFY SUBPARTITION ix_my_tab_jan_04
ALLOCATE EXTENT SIZE 100m;
ALTER INDEX ix_my_tab MODIFY SUBPARTITION ix_my_tab_jan_04
PCTUSED 40 STORAGE(NEXT 50m) NOLOGGING;
alter index: Partition – Rename
ALTER INDEX ix_my_tab RENAME
PARTITION ix_my_tab_jan_04 TO ix_my_tab_jan_05;
ALTER INDEX ix_my_tab RENAME
SUBPARTITION ix_my_tab_jan_04 TO ix_my_tab_jan_05;
alter index: Partition – Split
ALTER INDEX ix_my_tab SPLIT PARTITION ix_my_tab_jan_05
AT ('15-JAN-05') INTO PARTITION ix_my_tab_jan_05a
TABLESPACE myidxtbs
STORAGE (INITIAL 100m NEXT 50M FREELISTS 5);
alter index: Rebuild Nonpartitioned Indexes
ALTER INDEX ix_my_tab REBUILD ONLINE;
ALTER INDEX ix_my_tab REBUILD ONLINE
TABLESPACE idx_tbs_new PCTFREE 1
STORAGE (INITIAL 50M NEXT 50m FREELISTS 5)
COMPUTE STATISTICS PARALLEL 0;
alter index: Rebuild Partitions
ALTER INDEX ix_my_tab
REBUILD PARTITION ix_my_tab_jan_04 ONLINE;
ALTER INDEX ix_my_tab
REBUILD SUBPARTITION ix_my_tab_jan_04 ONLINE
PCTFREE 1 STORAGE (INITIAL 50M NEXT 50m FREELISTS 5)
COMPUTE STATISTICS PARALLEL 0;
alter index: Rename
ALTER INDEX ix_my_tab RENAME TO 'ix_my_tab_01';
alter index: Shrink
ALTER INDEX ix_my_tab SHRINK SPACE;
ALTER INDEX ix_my_tab SHRINK SPACE COMPACT CASCADE;
alter materialized view: Allocate and Deallocate Extents
ALTER MATERIALIZED VIEW mv_my_tab ALLOCATE EXTENT;
ALTER MATERIALIZED VIEW mv_my_tab DEALLOCATE UNUSED;
alter materialized view: Miscellaneous
ALTER MATERIALIZED VIEW mv_my_tab COMPRESS;
ALTER MATERIALIZED VIEW mv_my_tab PARALLEL 3;
ALTER MATERIALIZED VIEW mv_my_tab NOLOGGING;
ALTER MATERIALIZED VIEW mv_my_tab LOGGING;
ALTER MATERIALIZED VIEW mv_my_tab CONSIDER FRESH;
ALTER MATERIALIZED VIEW mv_my_tab ENABLE QUERY REWRITE;
alter materialized view: Physical Attributes and Storage
ALTER MATERIALIZED VIEW mv_my_tab
PCTFREE 5 PCTUSED 60
STORAGE (NEXT 100m FREELISTS 5);
alter materialized view: Refresh
ALTER MATERIALIZED VIEW mv_my_tab REFRESH FAST;
ALTER MATERIALIZED VIEW mv_my_tab REFRESH COMPLETE;
ALTER MATERIALIZED VIEW mv_my_tab REFRESH FAST ON DEMAND;
ALTER MATERIALIZED VIEW mv_my_tab REFRESH FAST ON COMMIT;
ALTER MATERIALIZED VIEW mv_my_tab REFRESH COMPLETE
START WITH sysdate;
ALTER MATERIALIZED VIEW mv_my_tab REFRESH COMPLETE
START WITH sysdate NEXT sysdate+1/24;
alter materialized view: Shrink Space
ALTER MATERIALIZED VIEW mv_my_tab SHRINK SPACE;
ALTER MATERIALIZED VIEW mv_my_tab
SHRINK SPACE COMPACT CASCADE;
alter materialized view log: Add Components
ALTER MATERIALIZED VIEW LOG ON my_tab ADD PRIMARY KEY;
ALTER MATERIALIZED VIEW LOG ON my_tab ADD (col1, col2)
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG ON my_tab ADD (col1, col2),
ROWID, SEQUENCE INCLUDING NEW VALUES;
alter materialized view log: Allocate and Deallocate Extents
ALTER MATERIALIZED VIEW LOG ON my_tab ALLOCATE EXTENT;
ALTER MATERIALIZED VIEW LOG ON my_tab DEALLOCATE UNUSED;
alter materialized view log: Miscellaneous
ALTER MATERIALIZED VIEW LOG ON my_tab PARALLEL 3;
ALTER MATERIALIZED VIEW LOG ON my_tab NOLOGGING;
ALTER MATERIALIZED VIEW LOG ON my_tab SHRINK SPACE;
alter materialized view log: Physical Attributes and Storage
ALTER MATERIALIZED VIEW LOG ON my_tab
PCTFREE 5 PCTUSED 60
STORAGE (NEXT 100m FREELISTS 5);
alter package: Compile
ALTER PACKAGE pk_my_package COMPILE;
ALTER PACKAGE pk_my_package COMPILE SPECIFICATION;
ALTER PACKAGE pk_my_package COMPILE BODY;
alter procedure: Compile
ALTER PROCEDURE pk_my_package COMPILE;
alter profile: Miscellaneous
ALTER ROLE my_role IDENTIFIED BY password;
ALTER ROLE my_role NOT IDENTIFIED;
alter profile: Modify Limits (Password)
ALTER PROFILE my_profile LIMIT FAILED_LOGIN_ATTEMPTS=3;
ALTER PROFILE my_profile LIMIT PASSWORD_LOCK_TIME=2/24;
ALTER PROFILE my_profile LIMIT PASSWORD_GRACE_TIME=5;
ALTER PROFILE my_profile LIMIT PASSWORD_LIFETIME=60;
ALTER PROFILE my_profile LIMIT PASSWORD_REUSE_TIME=365 PASSWORD_REUSE_MAX=3;
alter profile: Modify Limits (Resource)
ALTER PROFILE my_profile LIMIT SESSIONS_PER_CPU=10;
ALTER PROFILE my_profile LIMIT CONNECT_TIME=1000;
ALTER PROFILE my_profile LIMIT IDLE_TIME=60;
ALTER PROFILE my_profile LIMIT PRIVATE_SGA=1000000;
alter rollback segment: Online/Offline
ALTER ROLLBACK SEGMENT rbs01 OFFLINE;
ALTER ROLLBACK SEGMENT rbs01 ONLINE;
alter rollback segment: Shrink
ALTER ROLLBACK SEGMENT rbs01 SHRINK;
ALTER ROLLBACK SEGMENT rbs01 SHRINK TO 100M;
alter rollback segment: storage Clause
ALTER ROLLBACK SEGMENT rbs01 STORAGE(NEXT 50M OPTIMAL 100M);
alter sequence: Miscellaneous
ALTER SEQUENCE my_seq INCREMENT BY –5;
ALTER SEQUENCE my_seq INCREMENT BY 1 MAXVALUE 50000 CYCLE;
ALTER SEQUENCE my_seq NOMAXVALUE;
ALTER SEQUENCE my_seq CACHE ORDER;
ALTER SEQUENCE my_seq INCREMENT BY 1
MINVALUE 1 MAXVALUE 500 CYCLE;
alter session: Enable and Disable Parallel Operations
ALTER SESSION ENABLE PARALLEL DML PARALLEL 3;
ALTER SESSION ENABLE PARALLEL DDL;
ALTER SESSION DISABLE PARALLEL QUERY;
alter session: Resumable Space Management
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;
ALTER SESSION DISABLE RESUMABLE;
alter session: Set Session Parameters
ALTER SESSION SET nls_date_format='MM/DD/YYYY HH24:MI:SS';
ALTER SESSION SET sort_area_size=10000000;
ALTER SESSION SET query_rewrite_enabled=TRUE;
ALTER SESSION SET resumable_timeout=3600;
ALTER SESSION SET skip_unusable_indexes=TRUE;
ALTER SESSION SET SQL_TRACE=TRUE;
alter system: Logfile and Archive Logfile Management
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM ARCHIVE LOG START;
ALTER SYSTEM ARCHIVE LOG STOP;
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER SYSTEM ARCHIVE LOG THREAD 1 ALL;
ALTER SYSTEM ARCHIVE LOG ALL TO 'C:\oracle\allarch';
alter system: Set System Parameters
ALTER SYSTEM SET db_cache_size=325M
COMMENT='This change is to add more memory to the system'
SCOPE=BOTH;
ALTER SYSTEM SET COMPATIBLE=10.0.0
COMMENT='GOING TO 10G!' SCOPE=SPFILE;
alter system: System Management
ALTER SYSTEM CHECKPOINT GLOBAL;
ALTER SYSTEM KILL SESSION '145,334';
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM DISABLE RESTRICTED SESSION;
ALTER SYSTEM SUSPEND;
ALTER SYSTEM QUIESCE RESTRICTED;
ALTER SYSTEM UNQUIESCE;
ALTER SYSTEM RESUME;
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
alter table: External Table Operations
ALTER TABLE ext_parts REJECT LIMIT 500;
ALTER TABLE ext_parts DEFUALT DIRECTORY ext_employee_dir;
ALTER TABLE ext_parts ACCESS PARAMETERS
(FIELDS TERMINATED BY ’,’);
ALTER TABLE ext_parts LOCATION (’PARTS01.TXT’,’PARTS02.TXT’);
ALTER TABLE ext_parts ADD COLUMN (SSN NUMBER);
alter table: Move Table
ALTER TABLE parts move TABLESPACE parts_new_tbs PCTFREE 10 PCTUSED 60;
alter table: Table Column – Add
ALTER TABLE PARTS ADD (part_location VARCHAR2(20) );
ALTER TABLE PARTS ADD (part_location VARCHAR2(20), part_bin VARCHAR2(30) );
ALTER TABLE parts ADD (photo BLOB)
LOB (photo) STORE AS lob_parts_photo
(TABLESPACE parts_lob_tbs);
alter table: Table Column – Modify
ALTER TABLE PARTS MODIFY (part_location VARCHAR2(30) );
ALTER TABLE PARTS MODIFY
part_location VARCHAR2(30), part_bin VARCHAR2(20) );
ALTER TABLE parts modify (name NOT NULL);
ALTER TABLE parts modify (name NULL);
ALTER TABLE parts MODIFY LOB (photo) (STORAGE(FREELISTS 2));
ALTER TABLE parts MODIFY LOB (photo) (PCTVERSION 50);
alter table: Table Column – Remove
ALTER TABLE parts DROP (part_location);
ALTER TABLE parts DROP (part_location, part_bin);
alter table: Table Column – Rename
ALTER TABLE parts RENAME COLUMN part_location TO part_loc;
alter table: Table Constraints – Add Check Constraint
ALTER TABLE parts ADD (CONSTRAINT ck_parts_01 CHECK (id > 0) );
alter table: Table Constraints – Add Default Value
ALTER TABLE PARTS MODIFY (name DEFAULT 'Not Available');
ALTER TABLE PARTS ADD (vendor_code NUMBER DEFAULT 0);
ALTER TABLE PARTS MODIFY (part_description DEFAULT NULL);
alter table: Table Constraints – Add Foreign Key
ALTER TABLE parts ADD CONSTRAINT fk_part_bin
FOREIGN KEY (bin_code) REFERENCES part_bin;
alter table: Table Constraints – Add Primary and Unique Key
ALTER TABLE parts ADD CONSTRAINT pk_parts_part_id
PRIMARY KEY (id) USING INDEX TABLESPACE parts_index
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);
ALTER TABLE parts ADD CONSTRAINT uk_parts_part_bin
UNIQUE (part_bin)USING INDEX TABLESPACE parts_index
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);
alter table: Table Constraints – Modify
ALTER TABLE parts DISABLE UNIQUE (part_bin);
ALTER TABLE parts DISABLE CONSTRAINT uk_parts_part_bin;
ALTER TABLE parts DISABLE CONSTRAINT uk_parts_part_bin KEEP INDEX;
ALTER TABLE parts DISABLE CONSTRAINT fk_part_bin;
ALTER TABLE parts DISABLE CONSTRAINT fk_part_bin
DISABLE PRIMARY KEY KEEP INDEX;
ALTER TABLE parts ENABLE CONSTRAINT fk_part_bin;
ALTER TABLE parts ENABLE PRIMARY KEY;
ALTER TABLE parts ENABLE UNIQUE (part_bin);
ALTER TABLE parts ENABLE NOVALIDATE CONSTRAINT fk_part_bin;
ALTER TABLE parts ENABLE NOVALIDATE PRIMARY KEY;
ALTER TABLE parts ENABLE NOVALIDATE UNIQUE (part_bin);
ALTER TABLE parts ENABLE NOVALIDATE PRIMARY KEY
ENABLE NOVALIDATE CONSTRAINT fk_part_bin;
alter table: Table Constraints – Remove
ALTER TABLE parts DROP CONSTRAINT fk_part_bin;
ALTER TABLE parts DROP PRIMARY KEY;
ALTER TABLE parts DROP PRIMARY KEY CASCADE;
ALTER TABLE parts DROP UNIQUE (uk_parts_part_bin);
alter table: Table Partition – Add
ALTER TABLE store_sales ADD PARTITION sales_q1_04
VALUES LESS THAN (TO_DATE('01-APR-2004','DD-MON-YYYY'))
TABLESPACE data_0104_tbs UPDATE GLOBAL INDEXES;
ALTER TABLE daily_transactions ADD PARTITION;
ALTER TABLE daily_transactions
ADD PARTITION Alaska VALUES ('AK');
ALTER TABLE daily_transactions
add PARTITION SALES_2004_Q1 VALUES LESS THAN
(TO_DATE('01-APR-2004','DD-MON-YYYY')) SUBPARTITIONS 4;
alter table: Table Partition – Merge
ALTER TABLE store_sales
MERGE PARTITIONS Oklahoma, texas
INTO PARTITION oktx;
alter table: Table Partition – Move
ALTER TABLE store_sales MOVE PARTITION sales_overflow TABLESPACE
new_sales_overflow STORAGE (INITIAL 100m NEXT 100m PCTINCREASE 0)
UPDATE GLOBAL INDEXES;
alter table: Table Partition – Remove
ALTER TABLE store_sales DROP PARTITION sales_q1_04 UPDATE GLOBAL INDEXES;
alter table: Table Partition – Rename
ALTER TABLE store_sales RENAME PARTITION sales_q1 TO sales_first_quarter;
alter table: Table Partition – Split
ALTER TABLE store_sales
SPLIT PARTITION sales_overflow AT
(TO_DATE('01-FEB-2004','DD-MON-YYYY') )
INTO (PARTITION sales_q4_2003,
PARTITION sales_overflow)
UPDATE GLOBAL INDEXES;
ALTER TABLE composite_sales SPLIT PARTITION sales_q1
AT (TO_DATE('15-FEB-2003','DD-MON-YYYY'))
INTO (PARTITION sales_q1_01 SUBPARTITIONS 4
STORE IN (q1_01_tab1, q1_01_tab2, q1_01_tab3, q1_01_tab4),
PARTITION sales_q1_02 SUBPARTITIONS 4
STORE IN (q1_02_tab1, q1_02_tab2, q1_02_tab3, q1_02_tab4) )
UPDATE GLOBAL INDEXES;
alter table: Table Partition – Truncate
ALTER TABLE store_sales TRUNCATE PARTITION sales_overflow
UPDATE GLOBAL INDEXES;
alter table: Table Properties
ALTER TABLE parts PCTFREE 10 PCTUSED 60;
ALTER TABLE parts STORAGE (NEXT 1M);
ALTER TABLE parts PARALLEL 4;
alter table: Triggers – Modify Status
ALTER TABLE parts DISABLE ALL TRIGGERS;
ALTER TABLE parts ENABLE ALL TRIGGERS;
alter tablespace: Backups
ALTER TABLESPACE my_data_tbs BEGIN BACKUP;
ALTER TABLESPACE my_data_tbs END BACKUP;
alter tablespace: Data Files and Tempfiles
ALTER TABLESPACE mytbs
ADD DATAFILE '/ora100/oracle/mydb/mydb_mytbs_01.dbf' SIZE 100M;
ALTER TABLESPACE mytemp
ADD TEMPFILE '/ora100/oracle/mydb/mydb_mytemp_01.dbf'
SIZE 100M;
ALTER TABLESPACE mytemp AUTOEXTEND OFF;
ALTER TABLESPACE mytemp AUTOEXTEND ON NEXT 100m MAXSIZE 1G;
alter tablespace: Rename
ALTER TABLESPACE my_data_tbs RENAME TO my_newdata_tbs;
alter tablespace: Tablespace Management
ALTER TABLESPACE my_data_tbs DEFAULT
STORAGE (INITIAL 100m NEXT 100m FREELISTS 3);
ALTER TABLESPACE my_data_tbs MINIMUM EXTENT 500k;
ALTER TABLESPACE my_data_tbs RESIZE 100m;
ALTER TABLESPACE my_data_tbs COALESCE;
ALTER TABLESPACE my_data_tbs OFFLINE;
ALTER TABLESPACE my_data_tbs ONLINE;
ALTER TABLESPACE mytbs READ ONLY;
ALTER TABLESPACE mytbs READ WRITE;
ALTER TABLESPACE mytbs FORCE LOGGING;
ALTER TABLESPACE mytbs NOLOGGING;
ALTER TABLESPACE mytbs FLASHBACK ON;
ALTER TABLESPACE mytbs FLASHBACK OFF;
ALTER TABLESPACE mytbs RETENTION GUARANTEE;
ALTER TABLESPACE mytbs RETENTION NOGUARANTEE;
alter trigger
ALTER TRIGGER tr_my_trigger DISABLE;
ALTER TRIGGER tr_my_trigger ENABLE;
ALTER TRIGGER tr_my_trigger RENAME TO tr_new_my_trigger;
ALTER TRIGGER tr_my_trigger COMPILE;
alter user: Change Password
ALTER USER olduser IDENTIFIED BY newpassword;
ALTER USER olduser IDENTIFIED EXTERNALLY;
alter user: Password and Account Management
ALTER USER olduser PASSWORD EXPIRE;
ALTER USER olduser ACCOUNT LOCK;
ALTER USER olduser ACCOUNT UNLOCK;
alter user: Profile
ALTER USER olduser PROFILE admin_profile;
alter user: Quotas
ALTER USER olduser QUOTA UNLIMITED ON users;
ALTER USER olduser QUOTA 10000M ON USERS;
alter user: Roles
ALTER USER olduser DEFAULT ROLE admin_role;
ALTER USER olduser DEFAULT ROLE NONE;
ALTER USER olduser DEFAULT ROLE ALL EXCEPT admin_role;
alter user: Tablespace Assignments
ALTER USER olduser DEFAULT TABLESPACE users;
ALTER USER olduser TEMPORARY TABLESPACE temp;
alter view: Constraints
ALTER VIEW my_view
ADD CONSTRAINT u_my_view_01 UNIQUE (empno)
RELY DISABLE NOVALIDATE;
ALTER VIEW my_view DROP CONSTRAINT u_my_view_01;
ALTER VIEW my_view DROP PRIMARY KEY;
ALTER VIEW my_view MODIFY CONSTRAINT u_my_view_01 NORELY;
ALTER VIEW my_view MODIFY CONSTRAINT u_my_view_01 RELY;
alter view: Recompile
ALTER VIEW my_view RECOMPILE;
analyze: Analyze Cluster
ANALYZE CLUSTER my_cluster_tab COMPUTE STATISTICS FOR ALL ROWS;
ANALYZE CLUSTER my_cluster_tab
ESTIMATE STATISTICS SAMPLE 10000 ROWS FOR ALL ROWS;
analyze: Analyze Index
ANALYZE INDEX ix_tab_01 COMPUTE STATISTICS FOR ALL ROWS;
ANALYZE INDEX ix_tab_01
ESTIMATE STATISTICS SAMPLE 10000 ROWS FOR ALL ROWS;
analyze: Analyze Table
ANALYZE TABLE mytab COMPUTE STATISTICS
FOR ALL INDEXED COLUMNS SIZE 100;
ANALYZE TABLE mytab COMPUTE STATISTICS
FOR ALL INDEXES;
audit
AUDIT ALL ON scott.emp;
AUDIT UPDATE, DELETE ON scott.emp;
AUDIT SELECT on scott.emp WHENEVER NOT SUCCESSFUL;
AUDIT INSERT, UPDATE, DELETE ON DEFAULT;
comment
COMMENT ON TABLE scott.mytab IS
'This is a comment on the mytab table';
COMMENT ON COLUMN scott.mytab.col1 IS
'This is a comment on the col1 column';
COMMENT ON MATERIALIZED VIEW scott.mview IS
'This is a comment on the materialized view mview';
create cluster
CREATE CLUSTER pub_cluster (pubnum NUMBER)
SIZE 8K PCTFREE 10 PCTUSED 60 TABLESPACE user_data;
CREATE CLUSTER pub_cluster (pubnum NUMBER)
SIZE 8K HASHKEYS 1000 PCTFREE 10 PCTUSED 60
TABLESPACE user_data;
create control file
CREATE CONTROLFILE REUSE DATABASE "mydb"
NORESETLOGS NOARCHIVELOG
MAXLOGFILES 32 MAXLOGMEMBERS 3
MAXDATAFILES 200 MAXINSTANCES 1
MAXLOGHISTORY 1000
LOGFILE
GROUP 1 ('/ora01/oracle/mydb/mydb_redo1a.rdo',
'/ora02/oracle/mydb/mydb_redo1b.rdo') SIZE 500K,
GROUP 2 ('/ora01/oracle/mydb/mydb_redo2a.rdo',
'/ora01/oracle/mydb/mydb_redo2b.rdo') SIZE 500K
DATAFILE
'/ora01/oracle/mydb/mydb_system_01.dbf ',
'/ora01/oracle/mydb/mydb_users_01.dbf ',
'/ora01/oracle/mydb/mydb_undo_01.dbf ',
'/ora01/oracle/mydb/mydb_sysaux_01.dbf ',
'/ora01/oracle/mydb/mydb_alldata_01.dbf ';
create database
CREATE DATABASE prodb
MAXINSTANCES 1 MAXLOGHISTORY 1
MAXLOGFILES 5 MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE 'C:\oracle\ora92010\prodb\system01.dbf'
SIZE 250M REUSE AUTOEXTEND ON NEXT 10240K
MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL DEFAULT
TEMPORARY TABLESPACE TEMP
TEMPFILE 'C:\oracle\ora92010\prodb\temp01.dbf'
SIZE 40M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SYSAUX TABLESPACE
DATAFILE 'C:\oracle\ora92010\prodb\sysauxtbs01.dbf'
SIZE 300M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1"
DATAFILE 'C:\oracle\ora92010\prodb\undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
LOGFILE
GROUP 1 ('C:\oracle\ora92010\prodb\redo01.log') SIZE 102400K,
GROUP 2 ('C:\oracle\ora92010\prodb\redo02.log') SIZE 102400K,
GROUP 3 ('C:\oracle\ora92010\prodb\redo03.log') SIZE 102400K;
create database link
CREATE DATABASE LINK my_db_link
CONNECT TO current_user
USING 'my_db';
CREATE PUBLIC DATABASE LINK my_db_link
CONNECT TO remote_user IDENTIFIED BY psicorp
USING 'my_db';
create directory
CREATE OR REPLACE DIRECTORY mydir AS
'/opt/oracle/admin/directories/mydir';
create function
CREATE OR REPLACE FUNCTION find_value_in_table
(p_value IN NUMBER, p_table IN VARCHAR2,
p_column IN VARCHAR2)
RETURN NUMBER IS
v_found NUMBER;
v_sql VARCHAR2(2000);
BEGIN
v_sql:='SELECT 1 FROM '||p_table||' WHERE '||p_column||
' = '||p_value;
execute immediate v_sql into v_found;
return v_found;
END;
/
create index: Function-Based Index
CREATE INDEX fb_upper_last_name_emp ON emp_info (UPPER(last_name) );
create index: Global Partitioned Indexes
CREATE INDEX ix_part_my_tab_01 ON store_sales (invoice_number)
GLOBAL PARTITION BY RANGE (invoice_number)
(PARTITION part_001 VALUES LESS THAN (1000),
PARTITION part_002 VALUES LESS THAN (10000),
PARTITION part_003 VALUES LESS THAN (MAXVALUE) );
CREATE INDEX ix_part_my_tab_02 ON store_sales
(store_id, time_id)
GLOBAL PARTITION BY RANGE (store_id, time_id)
(PARTITION PART_001 VALUES LESS THAN
(1000, TO_DATE('04-01-2003','MM-DD-YYYY') )
TABLESPACE partition_001
STORAGE (INITIAL 100M NEXT 200M PCTINCREASE 0),
PARTITION part_002 VALUES LESS THAN
(1000, TO_DATE('07-01-2003','MM-DD-YYYY') )
TABLESPACE partition_002
STORAGE (INITIAL 200M NEXT 400M PCTINCREASE 0),
PARTITION part_003 VALUES LESS THAN (maxvalue, maxvalue)
TABLESPACE partition_003 );
create index: Local Partitioned Indexes
CREATE INDEX ix_part_my_tab_01 ON my_tab
(col_one, col_two, col_three)
LOCAL (PARTITION tbs_part_01 TABLESPACE part_tbs_01,
PARTITION tbs_part_02 TABLESPACE part_tbs_02,
PARTITION tbs_part_03 TABLESPACE part_tbs_03,
PARTITION tbs_part_04 TABLESPACE part_tbs_04);
CREATE INDEX ix_part_my_tab_01 ON my_tab (col_one, col_two, col_three)
LOCAL STORE IN (part_tbs_01, part_tbs_02, part_tbs_03, part_tbs_04);
CREATE INDEX ix_part_my_tab_01 ON my_tab (col_one, col_two, col_three)
LOCAL STORE IN (
part_tbs_01 STORAGE (INITIAL 10M NEXT 10M MAXEXTENTS 200),
part_tbs_02,
part_tbs_03 STORAGE (INITIAL 100M NEXT 100M MAXEXTENTS 200),
part_tbs_04 STORAGE (INITIAL 1000M NEXT 1000M MAXEXTENTS 200));
create index: Local Subpartitioned Indexes
CREATE INDEX sales_ix ON store_sales(time_id, store_id)
STORAGE (INITIAL 1M MAXEXTENTS UNLIMITED) LOCAL
(PARTITION q1_2003,
PARTITION q2_2003,
PARTITION q3_2003
(SUBPARTITION pq3200301, SUBPARTITION pq3200302,
SUBPARTITION pq3200303, SUBPARTITION pq3200304,
SUBPARTITION pq3200305),
PARTITION q4_2003
(SUBPARTITION pq4200301 TABLESPACE tbs_1,
SUBPARTITION pq4200302 TABLESPACE tbs_1,
SUBPARTITION pq4200303 TABLESPACE tbs_1,
SUBPARTITION pq4200304 TABLESPACE tbs_1,
SUBPARTITION pq4200305 TABLESPACE tbs_1,
SUBPARTITION pq4200306 TABLESPACE tbs_1,
SUBPARTITION pq4200307 TABLESPACE tbs_1,
SUBPARTITION pq4200308 TABLESPACE tbs_1),
PARTITION sales_overflow
(SUBPARTITION pqoflw01 TABLESPACE tbs_2,
SUBPARTITION pqoflw02 TABLESPACE tbs_2,
SUBPARTITION pqoflw03 TABLESPACE tbs_2,
SUBPARTITION pqoflw04 TABLESPACE tbs_2));
create index: Nonpartitioned Indexes
CREATE INDEX ix_mytab_01 ON mytab(column_1);
CREATE UNIQUE INDEX ix_mytab_01 ON mytab(column_1, column_2, column_3);
CREATE INDEX ix_mytab_01 ON mytab(column_1, column_2, column_3)
TABLESPACE my_indexes COMPRESS
STORAGE (INITIAL 10K NEXT 10K PCTFREE 10) COMPUTE STATISTICS;
CREATE BITMAP INDEX bit_mytab_01 ON my_tab(col_two)
TABLESPACE my_tbs;
create materialized view
CREATE MATERIALIZED VIEW emp_dept_mv1
TABLESPACE users BUILD IMMEDIATE
REFRESH FAST ON COMMIT WITH ROWID
ENABLE QUERY REWRITE AS
SELECT d.rowid deptrowid, e.rowid emprowid,
e.empno, e.ename, e.job, d.loc
FROM dept d, emp e
WHERE d.deptno = e.deptno;
CREATE MATERIALIZED VIEW emp_dept_mv3
TABLESPACE users BUILD IMMEDIATE
REFRESH FAST ON COMMIT WITH ROWID
DISABLE QUERY REWRITE AS
SELECT d.rowid deptrowid, e.rowid emprowid,
d.dname, d.loc, e.ename, e.job
FROM dept d, emp e
WHERE d.deptno (+) = e.deptno;
create materialized view: Partitioned Materialized View
CREATE MATERIALIZED VIEW part_emp_mv1
PARTITION BY RANGE (hiredate)
(PARTITION month1
VALUES LESS THAN (TO_DATE('01-APR-1981', 'DD-MON-YYYY'))
PCTFREE 0 PCTUSED 99
STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
TABLESPACE users,
PARTITION month2
VALUES LESS THAN (TO_DATE('01-DEC-1981', 'DD-MON-YYYY'))
PCTFREE 0 PCTUSED 99
STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
TABLESPACE users,
PARTITION month3
VALUES LESS THAN (TO_DATE('01-APR-1988', 'DD-MON-YYYY'))
PCTFREE 0 PCTUSED 99
STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
TABLESPACE users)
BUILD IMMEDIATE REFRESH FAST ENABLE QUERY REWRITE AS
SELECT hiredate, count(*) as hires
FROM emp
GROUP BY hiredate;
create materialized view log
CREATE MATERIALIZED VIEW LOG ON emp
TABLESPACE users
WITH PRIMARY KEY, SEQUENCE,
(ename, job, mgr, hiredate, sal, comm, deptno)
INCLUDING NEW VALUES;
create package/create package body
CREATE OR REPLACE PACKAGE get_Tomdate_pkg IS
FUNCTION GetTomdate RETURN DATE;
PRAGMA RESTRICT_REFERENCES (GetTomdate, WNDS);
PROCEDURE ResetSysDate;
END get_Tomdate_pkg;
/
CREATE OR REPLACE PACKAGE BODY get_Tomdate_pkg IS
v_Sysdate DATE := TRUNC(SYSDATE);
FUNCTION GetTomdate RETURN DATE IS
BEGIN
RETURN v_sysdate+1;
END GetTomdate;
PROCEDURE ResetSysdate IS
BEGIN
v_Sysdate := SYSDATE;
END ResetSysdate;
END get_Tomdate_pkg;
/
create pfile
CREATE PFILE FROM SPFILE;
CREATE PFILE='/opt/oracle/admin/mydb/pfile/initmybd.ora'
FROM SPFILE='/opt/oracle/admin/mydb/pfile/spfilemybd.ora';
create procedure
CREATE OR REPLACE PROCEDURE new_emp_salary
(p_empid IN NUMBER, p_increase IN NUMBER)
AS
BEGIN
UPDATE emp SET salary=salary*p_increase
WHERE empid=p_empid;
END;
/
create profile
CREATE PROFILE development_profile
LIMIT
SESSIONS_PER_USER 2 CONNECT_TIME 100000 IDLE_TIME 100000
LOGICAL_READS_PER_SESSION 1000000
PRIVATE_SGA 10m
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 3
PASSWORD_LOCK_TIME 30
PASSWORD_GRACE_TIME 5;
create role
CREATE ROLE developer_role IDENTIFIED USING develop;
create rollback segment
CREATE ROLLBACK SEGMENT r01 TABLESPACE RBS
STORAGE (INITIAL 100m NEXT 100M MINEXTENTS 5 OPTIMAL 500M);
create sequence
CREATE SEQUENCE my_seq
START WITH 1 INCREMENT BY 1 MAXVALUE 1000000 CYCLE CACHE;
create spfile
CREATE SPFILE FROM PFILE;
CREATE SPFILE='/opt/oracle/admin/mydb/pfile/spfilemybd.ora'
FROM PFILE='/opt/oracle/admin/mydb/pfile/initmybd.ora';
create synonym
CREATE SYNONYM scott_user.emp FOR scott.EMP;
CREATE PUBLIC SYNONYM emp FOR scott.EMP;
create table
CREATE TABLE my_tab
(id NUMBER, current_value VARCHAR2(2000) ) COMPRESS;
CREATE TABLE parts (id NUMBER, version NUMBER, name VARCHAR2(30),
Bin_code NUMBER, upc NUMBER, active_code VARCHAR2(1) NOT NULL
CONSTRAINT ck_parts_active_code_01
CHECK (UPPER(active_code)= 'Y' or UPPER(active_code)='N'),
CONSTRAINT pk_parts PRIMARY KEY (id, version)
USING INDEX TABLESPACE parts_index
STORAGE (INITIAL 1m NEXT 1m) )
TABLESPACE parts_tablespace
PCTFREE 20 PCTUSED 60 STORAGE ( INITIAL 10m NEXT 10m PCTINCREASE 0);
create tablespace: Permanent Tablespace
CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m;
CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m FORCE LOGGING BLOCKSIZE 8k;
CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m NOLOGGING
DEFAULT COMPRESS EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m NOLOGGING
DEFAULT COMPRESS EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 10G;
create tablespace: Temporary Tablespace
CREATE TABLESPACE temp_tbs
TEMPFILE '/opt/oracle/mydbs/data/mydbs_temp_tbs_01.tmp'
SIZE 100m;
create tablespace: Undo Tablespace
CREATE TABLESPACE undo_tbs
TEMPFILE '/opt/oracle/mydbs/data/mydbs_undo_tbs_01.tmp'
SIZE 1g RETENTION GUARANTEE;
create trigger
CREATE OR REPLACE TRIGGER emp_comm_after_insert
BEFORE INSERT ON emp FOR EACH ROW
DECLARE
v_sal number;
v_comm number;
BEGIN
-- Find username of person performing the INSERT into the table
v_sal:=:new.salary;
:new.comm:=v_sal*.10;
END;
/
create user
CREATE USER Robert IDENTIFIED BY Freeman
DEFAULT TABLESPACE users_tbs
TEMPORARY TABLESPACE temp
QUOTA 100M ON users_tbs
QUOTA UNLIMITED ON data_tbs;
create view
CREATE OR REPLACE VIEW vw_emp_dept_10 AS
SELECT * FROM EMP WHERE dept=10;
CREATE OR REPLACE VIEW vw_public_email AS
SELECT ename_first, ename_last, email_address
FROM EMP WHERE public='Y'
delete
DELETE FROM emp WHERE empid=100;
DELETE FROM emp e WHERE e.rowid >
(SELECT MIN (esub.ROWID) FROM emp esub
WHERE e.empid=esub.empid);
drop cluster
DROP CLUSTER scott.emp_cluster
INCLUDING TABLES CASCADE CONSTRAINTS;
drop database
DROP DATABASE;
drop database link
DROP DATABASE LINK my_db_link;
DROP PUBLIC DATABASE LINK my_db_link;
drop directory
DROP DIRECTORY mydir;
drop function
DROP FUNCTION find_value_in_table;
drop index
DROP INDEX ix_my_tab;
drop materialized view
DROP MATERIALIZED VIEW my_mview;
DROP MATERIALIZED VIEW my_mview PRESERVE TABLE;
drop materialized view log
DROP MATERIALIZED VIEW LOG ON mytab;
drop package/drop package body
DROP PACKAGE scott.my_package
DROP PACKAGE BODY scott.my_package;
drop procedure
DROP PROCEDURE my_proc;
drop profile
DROP PROFILE my_profile CASCADE;
drop role
DROP ROLE my_role;
drop rollback segment
DROP ROLLBACK SEGMENT rbs01;
drop sequence
DROP SEQUENCE my_seq;
drop synonym
DROP SYNONYM my_synonym;
DROP PUBLIC SYNONYM my_synonym;
drop table
DROP TABLE my_tab;
DROP TABLE my_tab CASCADE CONSTRAINTS;
DROP TABLE my_tab CASCADE CONSTRAINTS PURGE;
drop tablespace
DROP TABLESPACE my_tbs;
DROP TABLESPACE my_tbs INCLUDING CONTENTS;
DROP TABLESPACE my_tbs INCLUDING CONTENTS
AND DATAFILES CASCADE CONSTRAINTS;
drop trigger
DROP TRIGGER my_trigger;
drop user
DROP USER my_user CASCADE;
drop view
DROP VIEW my_view CASCADE CONSTRAINTS;
explain plan
EXPLAIN PLAN SET STATEMENT_ID='TEST' FOR
SELECT * FROM emp WHERE EMPID=100;
flashback database
FLASHBACK DATABASE TO SCN 10000;
FLASHBACK DATABASE TO TIMESTAMP SYSDATE – 1/24;
FLASHBACK DATABASE TO BEFORE TIMESTAMP SYSDATE – 1/24;
flashback table
FLASHBACK TABLE my_tab TO SCN 10000;
FLASHBACK TABLE my_tab TO TIMESTAMP SYSDATE – 1/24
ENABLE TRIGGERS;
FLASHBACK TABLE my_tab TO BEFORE DROP;
FLASHBACK TABLE my_tab TO BEFORE DROP RENAME TO rec_tab;
grants: Object Grants
GRANT SELECT ON scott.my_tab TO my_user;
GRANT INSERT, UPDATE, SELECT ON scott.my_tab TO my_user;
GRANT SELECT ON scott.my_tab TO my_user WITH GRANT OPTION;
GRANT SELECT ON scott.my_tab TO PUBLIC WITH GRANT OPTION;
grants: System Grants
GRANT CREATE TABLE to my_user;
GRANT CREATE ANY TABLE to my_user WITH ADMIN OPTION;
GRANT ALL PRIVILEGES to my_user WITH ADMIN OPTION;
insert
INSERT INTO dept VALUES (100, 'Marketing', 'Y');
INSERT INTO dept (deptid, dept_name, active)
VALUES (100, 'Marketing', 'Y');
INSERT INTO emp_history SELECT * FROM emp a
WHERE a.empid NOT IN (SELECT empid FROM emp_history);
INSERT INTO emp_pay_summary
SELECT empid, sum(gross_pay) FROM emp_pay_history
GROUP BY empid;
INSERT ALL
INTO store_sales (store_id, sales_date, deptid, sales_amt)
VALUES (store_id, start_date, deptid, mon_sales)
INTO store_sales (store_id, sales_date, deptid, sales_amt)
VALUES (store_id, start_date+1, deptid, tue_sales)
INTO store_sales (store_id, sales_date, deptid, sales_amt)
VALUES (store_id, start_date+2, deptid, wed_sales)
INTO store_sales (store_id, sales_date, deptid, sales_amt)
VALUES (store_id, start_date+3, deptid, thur_sales)
INTO store_sales (store_id, sales_date, deptid, sales_amt)
VALUES (store_id, start_date+4, deptid, fri_sales)
INTO store_sales (store_id, sales_date, deptid, sales_amt)
VALUES (store_id, start_date+5, deptid, sat_sales)
INTO store_sales (store_id, sales_date, deptid, sales_amt)
VALUES (store_id, start_date+6, deptid, sun_sales)
SELECT store_id, start_date, deptid, mon_sales, tue_sales,
wed_sales, thur_sales, fri_sales, sat_sales, sun_sales
FROM store_sales_load;
INSERT ALL
WHEN store_id < 100 THEN INTO east_stores
WHEN store_id >= 100 THEN INTO west_stores
ELSE INTO misc_stores
SELECT * FROM store_sales_load;
INSERT /*+ APPEND */ INTO emp VALUES (100,
'Jacob','Freeman',1000,20, null, 10, sysdate, 100,
sysdate+365);
lock table
LOCK TABLE my_table IN EXCLUSIVE MODE NOWAIT;
LOCK TABLE my_table IN ROW EXCLUSIVE MODE;
merge
MERGE INTO emp_retire A
USING (SELECT empno, ename_last, ename_first, salary
FROM emp WHERE retire_cd='Y') B
ON (a.empid=b.empid)
WHEN MATCHED THEN UPDATE SET
a.ename_last=b.ename_last,
a.ename_first=b.ename_first,
a.salary=b.salary
DELETE WHERE (b.retire_cd='D')
WHEN NOT MATCHED THEN INSERT
(a.empid, a.ename_last, a.ename_first, a.salary)
VALUES (b.empid, b.ename_last, b.ename_first, b.salary)
WHERE (b.retire_cd!='D');
noaudit
NOAUDIT ALL ON scott.emp;
NOAUDIT UPDATE, DELETE ON scott.emp;
NOAUDIT SELECT on scott.emp WHENEVER NOT SUCCESSFUL;
NOAUDIT INSERT, UPDATE, DELETE ON DEFAULT;
purge
PURGE TABLE my_tab;
PURGE INDEX ix_my_tab;
PURGE RECYCLEBIN;
PURGE DBA_RECYCLEBIN;
PURGE TABLESPACE data_tbs USER scott;
recover
RECOVER DATABASE;
RECOVER TABLESPACE user_data, user_index;
RECOVER DATAFILE
'/opt/oracle/admin/mydb/datafile/mydb_users_01.dbf';
RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
RECOVER DATABASE UNTIL CHANGE 94044;
RECOVER DATABASE UNTIL TIME '2004-08-01:22:00:04';
rename
RENAME my_table to my_tab;
revoke: Object Grants
REVOKE SELECT ON scott.my_tab FROM my_user;
REVOKE INSERT, UPDATE, SELECT ON scott.my_tab FROM my_user;
REVOKE SELECT ON scott.my_tab FROM my_user;
REVOKE SELECT ON scott.my_tab FROM PUBLIC;
revoke: System Grants
REVOKE CREATE TABLE FROM my_user;
REVOKE CREATE ANY TABLE FROM my_user;
REVOKE ALL PRIVILEGES FROM my_user;
rollback
ROLLBACK;
savepoint
SAVEPOINT alpha;
select
SELECT ename_last, dname
FROM emp a, dept b
WHERE a.deptid=b.deptid;
SELECT a.empid, b.dept_name
FROM emp a, dept b
WHERE a.deptid=b.deptid (+);
SELECT a.empid, b.dept_name
FROM emp a LEFT OUTER JOIN dept b
ON a.deptid=b.deptid;
SELECT * FROM dept WHERE EXISTS
(SELECT * FROM emp
WHERE emp.deptid=dept.deptid
AND emp.salary > 100);
SELECT ename_first, ename_last,
CASE deptid
WHEN 10 THEN 'Acounting' WHEN 20 THEN 'Sales'
ELSE 'None' END FROM emp;
SELECT empid, ename_last, salary, comm
FROM emp a
WHERE salary*.10 > (SELECT AVG(comm) FROM emp z
WHERE a.deptid=z.deptid);
WITH avg_dept_sales AS (
SELECT a.deptid, avg(b.sales_amt) avg_sales
FROM emp a, dept_sales b
WHERE a.deptid=b.deptid
GROUP BY a.deptid),
emp_salaries AS
(SELECT empid, AVG(salary) avg_salary FROM emp
GROUP BY empid)
SELECT * FROM emp_salaries b WHERE avg_salary*.05 >
(SELECT avg_sales FROM avg_dept_sales);
SELECT /*+ INDEX (a, emp_last_name_ix) */ empid
FROM emp a WHERE ename_last='Freeman'
SELECT empid, TO_CHAR(retire_date, 'MM/DD/YYYY')
FROM emp
WHERE retire_date IS NOT NULL
ORDER BY retire_date
SELECT empid, COUNT(*)
FROM emp
GROUP BY empid
HAVING COUNT(*) > 1;
SELECT empid, salary FROM emp
AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '1' DAY)
WHERE empid=20;
SELECT empid, salary FROM emp
VERSIONS BETWEEN
TIMESTAMP SYSTIMESTAMP - INTERVAL '1' DAY AND
SYSTIMESTAMP - INTERVAL '1' HOUR
WHERE empid=20;
set constraints
SET CONSTRAINTS ALL IMMEDIATE;
SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINT fk_my_tab DEFERRED;
set transaction
SET TRANSACTION USE ROLLBACK SEGMENT rbs01;
SET TRANSACTION READ ONLY;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
truncate
TRUNCATE TABLE my_tab;
TRUNCATE TABLE my_tab PRESERVE MATERIALIZED VIEW LOG;
TRUNCATE TABLE my_tab REUSE STORAGE;
TRUNCATE TABLE my_tab DROP STORAGE;
update
UPDATE emp SET salary=100 WHERE empid=100;
UPDATE emp SET salary=NULL, retire_date=SYSDATE
WHERE empid=100;
UPDATE emp SET salary=salary*1.10
WHERE deptid IN
(SELECT deptid FROM dept WHERE dept_name = 'Sales');
UPDATE emp a SET (salary, comm)=
(SELECT salary*1.10, comm*1.10
FROM emp b WHERE a.empid=b.empid);
INSERT INTO store_sales
PARTITION (store_sales_jan_2004) sa
SET sa.sales_amt=1.10 where store_id=100;
Tuesday, February 16, 2010
List of index in a oracle table
SQL> select index_name, substr(column_name,1,20) column_name,
2 column_position
3 from dba_ind_columns
4 where upper(table_name) like upper('%&T%')
5 order by 1,3
6 ;
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ---------------
KNM_ANREQ_DTL_N1 REQ_LINE_ID 1
KNM_ANREQ_DTL_N1 REQ_NO 2
KNM_ANREQ_DTL_U1 REQ_LINE_ID 1
KNM_ANREQ_HDR_N1 DEPT_VALUE 1
KNM_ANREQ_HDR_N1 REQ_NO 2
KNM_ANREQ_HDR_U1 REQ_NO 1
KNM_CHQ_DTL_N1 HDR_ID 1
KNM_CHQ_DTL_N1 CHK_HANDED_OVER_DATE 2
KNM_CHQ_DTL_N1 PAYMENT_ID 3
KNM_EMD_INFO_N1 EMD_ID 1
KNM_EMD_INFO_N1 PO_HEADER_ID 2
KNM_EMD_INFO_N1 ORGANIZATION_ID 3
KNM_EMD_INFO_N1 RECEIPT_NUM 4
KNM_EMD_INFO_U1 EMD_ID 1
KNM_INSP_N1 HDR_ID 1
KNM_INSP_N1 IRR_NO 2
KNM_INSP_N1 INVENTORY_ITEM_ID 3
KNM_INSP_N1 ORGANIZATION_ID 4
KNM_INSP_N2 ORGANIZATION_ID 1
KNM_INSP_N2 INVENTORY_ITEM_ID 2
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ---------------
KNM_INSP_U1 HDR_ID 1
KNM_INV_REF_DTL_N1 INVREF_ID 1
KNM_INV_REF_DTL_N1 INVREF_LINE_ID 2
KNM_INV_REF_DTL_N1 DED_NAME 3
KNM_INV_REF_DTL_N1 SUBMIT_FLAG 4
KNM_INV_REF_DTL_U1 INVREF_ID 1
KNM_INV_REF_DTL_U1 INVREF_LINE_ID 2
KNM_INV_REF_HDR_N1 INVREF_ID 1
KNM_INV_REF_HDR_N1 PO_HEADER_ID 2
KNM_INV_REF_HDR_N1 VENDOR_ID 3
KNM_INV_REF_HDR_N1 SUBMIT_FLAG 4
KNM_INV_REF_HDR_U1 INVREF_ID 1
KNM_ISSUE_U1 TRANSACTION_NO 1
KNM_ISSUE_U1 ISSUE_ID 2
KNM_ITEMWISE_ONHAND_THR_ID_PK THR_ID 1
KNM_MAT_ASSG_LINE_N1 TRANSACTION_NO 1
KNM_MAT_ASSG_LINE_N1 SL_NO 2
KNM_MAT_ASSG_N1 TRANSACTION_NO 1
KNM_MAT_ASSG_N1 REQUEST_NUM 2
KNM_MAT_CONS_U1 LINE_ID 1
KNM_MAT_ISSUE_DTL_BATCH_N1 MAT_REQUEST_NUM 1
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ---------------
KNM_MAT_ISSUE_DTL_BATCH_N1 ISSUE_ID 2
KNM_MAT_ISSUE_DTL_BATCH_N1 INVENTORY_ITEM_ID 3
KNM_MAT_ISSUE_DTL_BATCH_N1 ORGANIZATION_ID 4
KNM_MAT_ISSUE_DTL_BATCH_N1 LOT_NO 5
KNM_MAT_REQ_LINE_N1 HEADER_ID 1
KNM_MAT_REQ_LINE_N1 ITEM_ID 2
KNM_MAT_REQ_LINE_N1 LINE_ID 3
KNM_MAT_REQ_LINE_U1 LINE_ID 1
KNM_MAT_REQ_N1 REQUEST_NUM 1
KNM_MAT_REQ_N1 INDENTING_DEPT 2
KNM_MAT_REQ_N1 TRANSACTION_TYPE 3
KNM_MAT_REQ_N1 ORGANIZATION_ID 4
KNM_MAT_REQ_U1 HEADER_ID 1
KNM_MAT_REQ_U1 REQUEST_NUM 2
KNM_MAT_REQ_U1 ORGANIZATION_ID 3
KNM_OLDADV_DTL_N1 BILL_NO 1
KNM_OLDADV_DTL_N1 HDR_ID 2
KNM_OLDADV_DTL_N1 LINE_ID 3
KNM_OLD_ADV_N1 HDR_ID 1
KNM_OLD_ADV_N1 VENDOR_ID 2
KNM_OLD_ADV_STG_N1 SL_NO 1
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ---------------
KNM_OLD_ADV_STG_N1 LC 2
KNM_OLD_ADV_STG_N1 CHQNO 3
KNM_OLD_ADV_STG_N1 JDATE 4
KNM_OLD_ADV_STG_N1 VCHNO 5
KNM_OLD_ADV_STG_N1 DED_NAME 6
KNM_OLD_ADV_STG_N2 LC 1
KNM_OLD_ADV_STG_N2 SL_NO 2
KNM_OLD_ADV_STG_N2 DED_NAME 3
KNM_OLD_ADV_STG_N2 NAME 4
KNM_OLD_ADV_STG_N2 BILL_NO 5
KNM_PREPAY_DTL_N1 INVOICE_ID 1
KNM_PREPAY_DTL_N1 PREPAY_INVOICE_ID 2
KNM_PREPAY_DTL_N1 ORG_ID 3
KNM_SUPP_OLD_STG_N1 SUPP_BILL_NO 1
KNM_SUPP_OLD_STG_N1 BILL_NO 2
KNM_SUPP_OLD_STG_N1 CHQNO 3
KNM_SUPP_OLD_STG_N1 CHQ_DATE 4
KNM_SUPP_OLD_STG_N1 VOUCHER_NO 5
KNM_TAX_DED_DTL_N1 LINE_ID 1
KNM_TAX_DED_DTL_N1 PO_HEADER_ID 2
KNM_TAX_DED_DTL_N1 INVOICE_ID 3
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ---------------
KNM_TAX_DED_DTL_N1 DEDUCTION_NAME 4
KNM_TAX_DED_N1 PO_HEADER_ID 1
KNM_TAX_DED_N1 INVOICE_ID 2
KNM_TAX_DED_N1 SUBMITTED_FLAG 3
KNM_TP_SALE_DTL_N1 PO_HEADER_ID 1
KNM_TP_SALE_DTL_N1 LINE_ID 2
KNM_TP_SALE_DTL_N1 HDR_ID 3
KNM_TP_SALE_DTL_N1 VENDOR_ID 4
KNM_TP_SALE_DTL_N1 VENDOR_SITE_ID 5
KNM_TP_SALE_HDR_N1 HDR_ID 1
KNM_TP_SALE_HDR_N1 PO_HEADER_ID 2
KNM_TP_SUP_N1 PO_HEADER_ID 1
KNM_TP_SUP_N1 VENDOR_ID 2
KNM_TP_SUP_N1 VENDOR_SITE_ID 3
KNM_VCH_UQ VOUCHER_NO 1
KNM_VCH_UQ FIN_YEAR 2
KNM_VCH_UQ VENDOR_ID 3
KNM_VOUCHER_UQ VOUCHER_NO 1
KNM_VOUCHER_UQ INVOICE_NUM 2
KNM_VOUCHER_UQ CHQ_NO 3
KNM_WORKS_HDR_N1 WORK_NO 1
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ---------------
KNM_WORKS_HDR_N1 EST_HEADER_ID 2
KNM_WORKS_HDR_N1 ORG_ID 3
KNM_WORKS_HDR_N1 SUBMIT_FLAG 4
KNM_WORKS_HDR_N1 EST_APPROVAL_STATUS 5
KNM_WORKS_HDR_N1 DEPARTMENT 6
KNM_WORKS_HDR_N2 WORK_NO 1
KNM_WORKS_HDR_N2 EST_HEADER_ID 2
KNM_WORKS_HDR_N3 SUBMIT_FLAG 1
KNM_WORKS_HDR_N3 WORK_NO 2
KNM_WORKS_HDR_N3 EST_HEADER_ID 3
KNM_WORKS_HDR_N4 EST_APPROVAL_STATUS 1
KNM_WORKS_HDR_N4 WORK_NAME 2
KNM_WORKS_HDR_N4 TYPE 3
KNM_WORKS_HDR_N5 WORK_NO 1
KNM_WORKS_HDR_N5 DEPARTMENT 2
KNM_WORKS_HDR_N5 BOROUGH 3
KNM_WORKS_HDR_N5 WARD 4
KNM_WORKS_HDR_N5 EST_APPROVAL_STATUS 5
KNM_WORKS_HDR_N6 DEPARTMENT 1
KNM_WORKS_HDR_N6 SUBMIT_FLAG 2
KNM_WORKS_HDR_U1 EST_HEADER_ID 1
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ---------------
KNM_WORKS_LINES_N1 EST_HEADER_ID 1
KNM_WORKS_LINES_N1 EST_LINE_ID 2
KNM_WORKS_LINES_N1 LINE_NUM 3
KNM_WORKS_LINES_N1 SOURCE_TYPE 4
KNM_WORKS_LINES_N1 EST_ITEM_ID 5
KNM_WORKS_LINES_N1 ORGANIZATION_ID 6
KNM_WORKS_LINES_U1 EST_LINE_ID 1
KNM_WORKS_LINES_U1 SOURCE_TYPE 2
133 rows selected
SQL>
2 column_position
3 from dba_ind_columns
4 where upper(table_name) like upper('%&T%')
5 order by 1,3
6 ;
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ---------------
KNM_ANREQ_DTL_N1 REQ_LINE_ID 1
KNM_ANREQ_DTL_N1 REQ_NO 2
KNM_ANREQ_DTL_U1 REQ_LINE_ID 1
KNM_ANREQ_HDR_N1 DEPT_VALUE 1
KNM_ANREQ_HDR_N1 REQ_NO 2
KNM_ANREQ_HDR_U1 REQ_NO 1
KNM_CHQ_DTL_N1 HDR_ID 1
KNM_CHQ_DTL_N1 CHK_HANDED_OVER_DATE 2
KNM_CHQ_DTL_N1 PAYMENT_ID 3
KNM_EMD_INFO_N1 EMD_ID 1
KNM_EMD_INFO_N1 PO_HEADER_ID 2
KNM_EMD_INFO_N1 ORGANIZATION_ID 3
KNM_EMD_INFO_N1 RECEIPT_NUM 4
KNM_EMD_INFO_U1 EMD_ID 1
KNM_INSP_N1 HDR_ID 1
KNM_INSP_N1 IRR_NO 2
KNM_INSP_N1 INVENTORY_ITEM_ID 3
KNM_INSP_N1 ORGANIZATION_ID 4
KNM_INSP_N2 ORGANIZATION_ID 1
KNM_INSP_N2 INVENTORY_ITEM_ID 2
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ---------------
KNM_INSP_U1 HDR_ID 1
KNM_INV_REF_DTL_N1 INVREF_ID 1
KNM_INV_REF_DTL_N1 INVREF_LINE_ID 2
KNM_INV_REF_DTL_N1 DED_NAME 3
KNM_INV_REF_DTL_N1 SUBMIT_FLAG 4
KNM_INV_REF_DTL_U1 INVREF_ID 1
KNM_INV_REF_DTL_U1 INVREF_LINE_ID 2
KNM_INV_REF_HDR_N1 INVREF_ID 1
KNM_INV_REF_HDR_N1 PO_HEADER_ID 2
KNM_INV_REF_HDR_N1 VENDOR_ID 3
KNM_INV_REF_HDR_N1 SUBMIT_FLAG 4
KNM_INV_REF_HDR_U1 INVREF_ID 1
KNM_ISSUE_U1 TRANSACTION_NO 1
KNM_ISSUE_U1 ISSUE_ID 2
KNM_ITEMWISE_ONHAND_THR_ID_PK THR_ID 1
KNM_MAT_ASSG_LINE_N1 TRANSACTION_NO 1
KNM_MAT_ASSG_LINE_N1 SL_NO 2
KNM_MAT_ASSG_N1 TRANSACTION_NO 1
KNM_MAT_ASSG_N1 REQUEST_NUM 2
KNM_MAT_CONS_U1 LINE_ID 1
KNM_MAT_ISSUE_DTL_BATCH_N1 MAT_REQUEST_NUM 1
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ---------------
KNM_MAT_ISSUE_DTL_BATCH_N1 ISSUE_ID 2
KNM_MAT_ISSUE_DTL_BATCH_N1 INVENTORY_ITEM_ID 3
KNM_MAT_ISSUE_DTL_BATCH_N1 ORGANIZATION_ID 4
KNM_MAT_ISSUE_DTL_BATCH_N1 LOT_NO 5
KNM_MAT_REQ_LINE_N1 HEADER_ID 1
KNM_MAT_REQ_LINE_N1 ITEM_ID 2
KNM_MAT_REQ_LINE_N1 LINE_ID 3
KNM_MAT_REQ_LINE_U1 LINE_ID 1
KNM_MAT_REQ_N1 REQUEST_NUM 1
KNM_MAT_REQ_N1 INDENTING_DEPT 2
KNM_MAT_REQ_N1 TRANSACTION_TYPE 3
KNM_MAT_REQ_N1 ORGANIZATION_ID 4
KNM_MAT_REQ_U1 HEADER_ID 1
KNM_MAT_REQ_U1 REQUEST_NUM 2
KNM_MAT_REQ_U1 ORGANIZATION_ID 3
KNM_OLDADV_DTL_N1 BILL_NO 1
KNM_OLDADV_DTL_N1 HDR_ID 2
KNM_OLDADV_DTL_N1 LINE_ID 3
KNM_OLD_ADV_N1 HDR_ID 1
KNM_OLD_ADV_N1 VENDOR_ID 2
KNM_OLD_ADV_STG_N1 SL_NO 1
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ---------------
KNM_OLD_ADV_STG_N1 LC 2
KNM_OLD_ADV_STG_N1 CHQNO 3
KNM_OLD_ADV_STG_N1 JDATE 4
KNM_OLD_ADV_STG_N1 VCHNO 5
KNM_OLD_ADV_STG_N1 DED_NAME 6
KNM_OLD_ADV_STG_N2 LC 1
KNM_OLD_ADV_STG_N2 SL_NO 2
KNM_OLD_ADV_STG_N2 DED_NAME 3
KNM_OLD_ADV_STG_N2 NAME 4
KNM_OLD_ADV_STG_N2 BILL_NO 5
KNM_PREPAY_DTL_N1 INVOICE_ID 1
KNM_PREPAY_DTL_N1 PREPAY_INVOICE_ID 2
KNM_PREPAY_DTL_N1 ORG_ID 3
KNM_SUPP_OLD_STG_N1 SUPP_BILL_NO 1
KNM_SUPP_OLD_STG_N1 BILL_NO 2
KNM_SUPP_OLD_STG_N1 CHQNO 3
KNM_SUPP_OLD_STG_N1 CHQ_DATE 4
KNM_SUPP_OLD_STG_N1 VOUCHER_NO 5
KNM_TAX_DED_DTL_N1 LINE_ID 1
KNM_TAX_DED_DTL_N1 PO_HEADER_ID 2
KNM_TAX_DED_DTL_N1 INVOICE_ID 3
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ---------------
KNM_TAX_DED_DTL_N1 DEDUCTION_NAME 4
KNM_TAX_DED_N1 PO_HEADER_ID 1
KNM_TAX_DED_N1 INVOICE_ID 2
KNM_TAX_DED_N1 SUBMITTED_FLAG 3
KNM_TP_SALE_DTL_N1 PO_HEADER_ID 1
KNM_TP_SALE_DTL_N1 LINE_ID 2
KNM_TP_SALE_DTL_N1 HDR_ID 3
KNM_TP_SALE_DTL_N1 VENDOR_ID 4
KNM_TP_SALE_DTL_N1 VENDOR_SITE_ID 5
KNM_TP_SALE_HDR_N1 HDR_ID 1
KNM_TP_SALE_HDR_N1 PO_HEADER_ID 2
KNM_TP_SUP_N1 PO_HEADER_ID 1
KNM_TP_SUP_N1 VENDOR_ID 2
KNM_TP_SUP_N1 VENDOR_SITE_ID 3
KNM_VCH_UQ VOUCHER_NO 1
KNM_VCH_UQ FIN_YEAR 2
KNM_VCH_UQ VENDOR_ID 3
KNM_VOUCHER_UQ VOUCHER_NO 1
KNM_VOUCHER_UQ INVOICE_NUM 2
KNM_VOUCHER_UQ CHQ_NO 3
KNM_WORKS_HDR_N1 WORK_NO 1
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ---------------
KNM_WORKS_HDR_N1 EST_HEADER_ID 2
KNM_WORKS_HDR_N1 ORG_ID 3
KNM_WORKS_HDR_N1 SUBMIT_FLAG 4
KNM_WORKS_HDR_N1 EST_APPROVAL_STATUS 5
KNM_WORKS_HDR_N1 DEPARTMENT 6
KNM_WORKS_HDR_N2 WORK_NO 1
KNM_WORKS_HDR_N2 EST_HEADER_ID 2
KNM_WORKS_HDR_N3 SUBMIT_FLAG 1
KNM_WORKS_HDR_N3 WORK_NO 2
KNM_WORKS_HDR_N3 EST_HEADER_ID 3
KNM_WORKS_HDR_N4 EST_APPROVAL_STATUS 1
KNM_WORKS_HDR_N4 WORK_NAME 2
KNM_WORKS_HDR_N4 TYPE 3
KNM_WORKS_HDR_N5 WORK_NO 1
KNM_WORKS_HDR_N5 DEPARTMENT 2
KNM_WORKS_HDR_N5 BOROUGH 3
KNM_WORKS_HDR_N5 WARD 4
KNM_WORKS_HDR_N5 EST_APPROVAL_STATUS 5
KNM_WORKS_HDR_N6 DEPARTMENT 1
KNM_WORKS_HDR_N6 SUBMIT_FLAG 2
KNM_WORKS_HDR_U1 EST_HEADER_ID 1
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ---------------
KNM_WORKS_LINES_N1 EST_HEADER_ID 1
KNM_WORKS_LINES_N1 EST_LINE_ID 2
KNM_WORKS_LINES_N1 LINE_NUM 3
KNM_WORKS_LINES_N1 SOURCE_TYPE 4
KNM_WORKS_LINES_N1 EST_ITEM_ID 5
KNM_WORKS_LINES_N1 ORGANIZATION_ID 6
KNM_WORKS_LINES_U1 EST_LINE_ID 1
KNM_WORKS_LINES_U1 SOURCE_TYPE 2
133 rows selected
SQL>
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 91
ORA-06512: at "SYS.DBMS_OUTPUT", line 58
ORA-06512: at line 6
Sol : set serveroutput on size 1000000 ( give some extra size)
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 91
ORA-06512: at "SYS.DBMS_OUTPUT", line 58
ORA-06512: at line 6
Sol : set serveroutput on size 1000000 ( give some extra size)
ORA-01476: divisor is equal to zero
ORA-01476: divisor is equal to zero
Error: function FA_GAINLOSS_UPD_PKG.farboe returned failure
Error: function FA_GAINLOSS_UPD_PKG.fagpdp returned failure
User-Defined Exception
Error: function FA_GAINLOSS_UPD_PKG.fagpdp returned failure
SOLUTION:
Check the setup and also the code.
Error: function FA_GAINLOSS_UPD_PKG.farboe returned failure
Error: function FA_GAINLOSS_UPD_PKG.fagpdp returned failure
User-Defined Exception
Error: function FA_GAINLOSS_UPD_PKG.fagpdp returned failure
SOLUTION:
Check the setup and also the code.
IO error writing block to file %s (block # %s)
"IO error writing block to file %s (block # %s)"
// *Cause: Device on which the file resides is probably offline
// *Action: Restore access to the device
Solution : -
Disk is full
so remove log and out files from $APPLCSF/
// *Cause: Device on which the file resides is probably offline
// *Action: Restore access to the device
Solution : -
Disk is full
so remove log and out files from $APPLCSF/
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode
SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode
SQL> !
$ oerr ora 265
00265, 00000, "instance recovery required, cannot set ARCHIVELOG mode"
// *Cause: The database either crashed or was shutdown with the ABORT
// option. Media recovery cannot be enabled because the online
// logs may not be sufficient to recover the current datafiles.
// *Action: Open the database and then enter the SHUTDOWN command with the
// NORMAL or IMMEDIATE option.
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode
SQL> !
$ oerr ora 265
00265, 00000, "instance recovery required, cannot set ARCHIVELOG mode"
// *Cause: The database either crashed or was shutdown with the ABORT
// option. Media recovery cannot be enabled because the online
// logs may not be sufficient to recover the current datafiles.
// *Action: Open the database and then enter the SHUTDOWN command with the
// NORMAL or IMMEDIATE option.
ora 00031 session marked for kill
oracle error ora 00031
00031, 00000, "session marked for kill"
// *Cause: The session specified in an ALTER SYSTEM KILL SESSION command
// cannot be killed immediately (because it is rolling back or blocked
// on a network operation), but it has been marked for kill. This
// means it will be killed as soon as possible after its current
// uninterruptable operation is done.
// *Action: No action is required for the session to be killed, but further
// executions of the ALTER SYSTEM KILL SESSION command on this session
// may cause the session to be killed sooner.
00031, 00000, "session marked for kill"
// *Cause: The session specified in an ALTER SYSTEM KILL SESSION command
// cannot be killed immediately (because it is rolling back or blocked
// on a network operation), but it has been marked for kill. This
// means it will be killed as soon as possible after its current
// uninterruptable operation is done.
// *Action: No action is required for the session to be killed, but further
// executions of the ALTER SYSTEM KILL SESSION command on this session
// may cause the session to be killed sooner.
ORA-19504: failed to create file
ARC2: Beginning to archive log 2 thread 1 sequence 68308
Creating archive destination LOG_ARCHIVE_DEST_2: '/oracleerpdb/archive2/KOUSHIKERP_1_68308.arc'
ARC2: Error 19504 Creating archive log file to '/oracleerpdb/archive2/KOUSHIKERP_1_68308.arc'
Mon Apr 30 14:00:27 2007
Errors in file /oradb/oracleerp/oracleerpdb/9.2.0/admin/KOUSHIKERP_krishna/bdump/oracleerp_arc2_7695.trc:
ORA-19504: failed to create file "/oracleerpdb/archive2/KOUSHIKERP_1_68308.arc"
ORA-19504: failed to create file "/oracleerpdb/archive2/KOUSHIKERP_1_68308.arc"
ORA-27044: unable to write the header block of file.
Dump file /oradb/oracleerp/oracleerpdb/9.2.0/admin/KOUSHIKERP_krishna/bdump/oracleerp_arc2_7695.trc
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
ORACLE_HOME = /oradb/oracleerp/oracleerpdb/9.2.0
System name: SunOS
Node name: n1
Release: 5.9
Version: Generic_118558-09
Machine: sun4u
Instance name: KOUSHIKERP
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 7695, image: oracle@n1 (ARC2)
*** SESSION ID:(20.1) 2007-04-30 14:00:27.897
Failed to create file '/oracleerpdb/archive2/KOUSHIKERP_1_68308.arc' (file not accessible?)
*** 2007-04-30 14:00:27.898
ARC2: Error 19504 Creating archive log file to '/oracleerpdb/archive2/KOUSHIKERP_1_68308.arc'
*** 2007-04-30 14:00:27.898
kcrrfail: dest:2 err:19504 force:0
ORA-19504: failed to create file "/oracleerpdb/archive2/KOUSHIKERP_1_68308.arc"
ORA-19504: failed to create file "/oracleerpdb/archive2/KOUSHIKERP_1_68308.arc"
ORA-27044: unable to write the header block of file
SVR4 Error: 5: I/O error
Additional information: 1.
Creating archive destination LOG_ARCHIVE_DEST_2: '/oracleerpdb/archive2/KOUSHIKERP_1_68308.arc'
ARC2: Error 19504 Creating archive log file to '/oracleerpdb/archive2/KOUSHIKERP_1_68308.arc'
Mon Apr 30 14:00:27 2007
Errors in file /oradb/oracleerp/oracleerpdb/9.2.0/admin/KOUSHIKERP_krishna/bdump/oracleerp_arc2_7695.trc:
ORA-19504: failed to create file "/oracleerpdb/archive2/KOUSHIKERP_1_68308.arc"
ORA-19504: failed to create file "/oracleerpdb/archive2/KOUSHIKERP_1_68308.arc"
ORA-27044: unable to write the header block of file.
Dump file /oradb/oracleerp/oracleerpdb/9.2.0/admin/KOUSHIKERP_krishna/bdump/oracleerp_arc2_7695.trc
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
ORACLE_HOME = /oradb/oracleerp/oracleerpdb/9.2.0
System name: SunOS
Node name: n1
Release: 5.9
Version: Generic_118558-09
Machine: sun4u
Instance name: KOUSHIKERP
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 7695, image: oracle@n1 (ARC2)
*** SESSION ID:(20.1) 2007-04-30 14:00:27.897
Failed to create file '/oracleerpdb/archive2/KOUSHIKERP_1_68308.arc' (file not accessible?)
*** 2007-04-30 14:00:27.898
ARC2: Error 19504 Creating archive log file to '/oracleerpdb/archive2/KOUSHIKERP_1_68308.arc'
*** 2007-04-30 14:00:27.898
kcrrfail: dest:2 err:19504 force:0
ORA-19504: failed to create file "/oracleerpdb/archive2/KOUSHIKERP_1_68308.arc"
ORA-19504: failed to create file "/oracleerpdb/archive2/KOUSHIKERP_1_68308.arc"
ORA-27044: unable to write the header block of file
SVR4 Error: 5: I/O error
Additional information: 1.
Wednesday, February 3, 2010
Unable to create anonymous session.
Unable to create anonymous session. Your session is no longer valid. Oracle error 20001: java.sql.SQLException: ORA-20001: Oracle error -20001: ORA-20001: Oracle error -1653: ORA-01653: unable to extend table APPLSYS.FND_LOGINS by 16 in tablespace APPS_TS_ARCHIVE has been detected in FND_SIGNON.AUDIT_USER. has been detected in FND_SIGNON.NEW_PROXY_ICX_SESSION. ORA-06512: at "APPS.APP_EXCEPTION", line 72 ORA-06512: at "APPS.FND_SIGNON", line 18 ORA-06512: at "APPS.FND_SIGNON", line 530 ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 390 ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 538 ORA-06512: at "APPS.FND_AOLJ_UTIL", line 217 ORA-06512: at line 1 has been detected in createSession(int, String)(userid=6','716498702FC0487EE044001A4B0621D724654810232605405413161982081592').
Solution:
Check the available space in the server.
Solution:
Check the available space in the server.
Forms wont launch with R12 when using MS Internet Explorer v6.0.29
Symptoms
On 12.0.3 in Production: Only when using when using Internet Explorer 6.0.29, when launching any core forms function the forms launcher screen comes up but the form is not launched. Pressing F5 to refresh or relaunching the page will allow the form to launch
The problem is not apparent with IE 6.0.28
EXPECTED BEHAVIOR
Expect form to launch first time
STEPS
The issue can be reproduced at will with the following steps:
1. Login to eBusiness R12 using IE 6.0.2900.2180.xpsp_sp2_rtm.040803-2158
2. Launch forms function
3. Forms will not launch the first time, so have to press F5 or launch the form again
BUSINESS IMPACT
The issue has the following business impact:
Due to this issue, users have to press F5 to launch forms the first time
The same PC can launch forms for Release 11i with no problem
Changes
The PC has just been upgraded from earlier version of IE 6 (v6.0.28)
Cause
Internet Explorer issue with processing ActiveX
Version 6.0.2900.2180.xpsp_sp2_rtm.040803-2158 of IE had trouble in processing ActiveX and Javascript when a new window is opened
Solution
To implement the solution, please execute the following steps:
1. Download the latest version of Internet Explorer 6 or Internet Explorer 7
2. Re-test for the problem
On 12.0.3 in Production: Only when using when using Internet Explorer 6.0.29, when launching any core forms function the forms launcher screen comes up but the form is not launched. Pressing F5 to refresh or relaunching the page will allow the form to launch
The problem is not apparent with IE 6.0.28
EXPECTED BEHAVIOR
Expect form to launch first time
STEPS
The issue can be reproduced at will with the following steps:
1. Login to eBusiness R12 using IE 6.0.2900.2180.xpsp_sp2_rtm.040803-2158
2. Launch forms function
3. Forms will not launch the first time, so have to press F5 or launch the form again
BUSINESS IMPACT
The issue has the following business impact:
Due to this issue, users have to press F5 to launch forms the first time
The same PC can launch forms for Release 11i with no problem
Changes
The PC has just been upgraded from earlier version of IE 6 (v6.0.28)
Cause
Internet Explorer issue with processing ActiveX
Version 6.0.2900.2180.xpsp_sp2_rtm.040803-2158 of IE had trouble in processing ActiveX and Javascript when a new window is opened
Solution
To implement the solution, please execute the following steps:
1. Download the latest version of Internet Explorer 6 or Internet Explorer 7
2. Re-test for the problem
Unable to access EBS R12 from AppsLogin page under the following URL
On 12.0.0 in Production:
Unable to access EBS R12 from AppsLogin page under the following URL :
http://domain:8000/OA_HTML/AppsLogin
ERROR
Unable to generate forwarding URL. Exception: java.lang.IllegalArgumentException:
WebAppsContext.getJDBCConnection() == null
Solution
1.Run AutoConfig as described in Note 387859.1 Using AutoConfig to Manage System Configurations with Oracle E-Business Suite Release 12
2.Restart the eBusiness middle tier services
3.Re-test the issue to confirm the problem is now resolved
Unable to access EBS R12 from AppsLogin page under the following URL :
http://domain:8000/OA_HTML/AppsLogin
ERROR
Unable to generate forwarding URL. Exception: java.lang.IllegalArgumentException:
WebAppsContext.getJDBCConnection() == null
Solution
1.Run AutoConfig as described in Note 387859.1 Using AutoConfig to Manage System Configurations with Oracle E-Business Suite Release 12
2.Restart the eBusiness middle tier services
3.Re-test the issue to confirm the problem is now resolved
Login link in R12 shows Blank Page
Document Title
Login link in R12 shows Blank Page
Error Message
There is no obvious error message, but when Login link is clicked Nothing happens, Only Blank Page comes
Cause Identified
There were Some Triggers enabled in FND_USER table
Solution/Work Around
1.Disable the Trigger on FND_USER table
2.Check if Any event Alert is created on FND_USER
3.It is advised not to create event alert on FND_USER and FND_RESONSIBILITY
Login link in R12 shows Blank Page
Error Message
There is no obvious error message, but when Login link is clicked Nothing happens, Only Blank Page comes
Cause Identified
There were Some Triggers enabled in FND_USER table
Solution/Work Around
1.Disable the Trigger on FND_USER table
2.Check if Any event Alert is created on FND_USER
3.It is advised not to create event alert on FND_USER and FND_RESONSIBILITY
Monday, February 1, 2010
Andaman and Nicobar Islands
A clear history of the Andaman & Nicobar Islands can be had only from a British Survey of these islands conducted in 1777. The Andaman & Nicobar Islands remained the abode of the Negritos and the Mongoloids respectively, who occupied the Islands for centuries. These islands remained secluded from the mainland till the end of the 18th Century when people from the outside world first arrived. The history of these islands could be divided into four broad periods the period of seclusion and piratical disturbances
a) the British regime - a period of foreign intrusion and settlement
b) the Japanese regime
c) and the Post-Independence period.
In the Second Century, the Andaman and Nicobar Islands were located in the maps prepared by the great Greek astronomer, mathematician and geographer, Claudius Ptolemaeus, which possibly is the earliest reference to these islands. The early history of Nicobar is not well known although these islands were familiar to traders in ancient times, the islands being situated close to the trade route to the Far East. Though little is known about Portuguese activities in these islands, it is evident that the Portuguese missionaries started preaching Christianity among the islanders. The Nicobarese language also reflects a few Portuguese words.
a) the British regime - a period of foreign intrusion and settlement
b) the Japanese regime
c) and the Post-Independence period.
In the Second Century, the Andaman and Nicobar Islands were located in the maps prepared by the great Greek astronomer, mathematician and geographer, Claudius Ptolemaeus, which possibly is the earliest reference to these islands. The early history of Nicobar is not well known although these islands were familiar to traders in ancient times, the islands being situated close to the trade route to the Far East. Though little is known about Portuguese activities in these islands, it is evident that the Portuguese missionaries started preaching Christianity among the islanders. The Nicobarese language also reflects a few Portuguese words.
Subscribe to:
Posts (Atom)