COMMENT ON COLUMN SCOTT.EMP.DOB IS 'Date of Birth';
Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies. When relevant, I also write about other technologies, like Linux or PostgreSQL. Many of the posts starts with "how to" since they derive directly from my own personal experience. My goal is to provide simple examples, so that they can be easily adapted to other situations.
Wednesday, November 11, 2015
How to add a comment on a column
Use this syntax:
Tuesday, November 10, 2015
How to find the default tablespace type (smallfile vs bigfile)
SQL> select property_value from database_properties where property_name = 'DEFAULT_TBS_TYPE'; PROPERTY_VALUE --------------- SMALLFILE
Change like this:
alter database set default bigfile tablespace;The change will of course only take effect for future tablespaces, not existing ones.
Link to Oracle documentation
Monday, November 9, 2015
How to append text at beginning and at end of each line in vi (unix)
Go to top of line with :0
Append text to the end of each line, globally:
Add text to the beginning of each line, globally:
Append text to the end of each line, globally:
:%s/$/text/g
Add text to the beginning of each line, globally:
:%s/^/text/g
How to install and deinstall Oracle Spatial and Oracle Multimedia
The following advice is an excerpt from a Service Request with Oracle support services, on how to administer Spatial and Multimedia:
To remove Spatial:
To Install Multimedia:
To remove Spatial:
connect / as sysdba drop user MDSYS cascade;To remove Multimedia:
connect / as sysdba @?/rdbms/admin/catcmprm.sql ORDIMYou can reinstall both of these components later if needed by running the following scripts. Note, Multimedia must be installed first.
To Install Multimedia:
connect / as sysdba @?/ord/admin/ordinst.sql SYSAUX SYSAUX @?/ord/im/admin/catim.sql execute sys.validate_ordim;To install Spatial:
connect / as sysdba @?/md/admin/mdinst.sql
How to check if spatial and multimedia is installed in the database
The following advice was given to me by Oracle Support services, to determine if either of these components are being used in the database:
connect / as sysdba set pagesize 10000 col owner format a12 col table_name format a35 col column_name format a25 col index_name format a25 -- Is Spatial being used? select owner, index_name from dba_indexes where ityp_name = 'SPATIAL_INDEX'; select owner, table_name, column_name from dba_tab_columns where data_type= 'SDO_GEOMETRY' and owner != 'MDSYS';If both of these queries return no rows, Spatial is not being used.
-- Is Multimedia being used? set serveroutput on; @?/ord/im/admin/imremchk.sqlIf you're not using this component, you will get the message returned "Oracle Multimedia is not being used".
Tuesday, November 3, 2015
How to check if the database is in restricted mode
To check what kind of logins that are allowed in your instance, query the LOGINS field of the v$instance view:
Join with v$database for more information:
Which will show the following output if your database is in restricted mode, and in this example, mounted:
and for normal mode:
select logins from v$instance;Output here will be either ALLOWED or RESTRICTED
Join with v$database for more information:
select a.INSTANCE_NAME, a.ACTIVE_STATE,a.logins, b.open_mode from v$instance a inner join v$database b on UPPER(a.instance_name) = b.name;
Which will show the following output if your database is in restricted mode, and in this example, mounted:
INSTANCE_NAME ACTIVE_ST LOGINS OPEN_MODE ---------------- --------- ---------- -------------------- mydb01 NORMAL RESTRICTED MOUNTED
and for normal mode:
INSTANCE_NAME ACTIVE_ST LOGINS OPEN_MODE ---------------- --------- ---------- -------------------- mydb01 NORMAL ALLOWED READ WRITE
Monday, November 2, 2015
ORA-01722 when running ausy1120.sql during preparation of new target database for EBS
If you are following note Doc ID 741818.1 "Export/import process for 12.0 or 12.1 using 11gR1 or 11gR2" and run into the follwing problem when running the ausy1120.sql script:
the solution is to modify the script slightly and correct a typo.
Open the ausy1120.sql file in an editor and change
Then rerun the script. It should finish almost immediately and the output should be similar to:
The same error seems to occur when running the aujv1120.sql and the aumsc1120.sql as well. The solution is the same for all three.
sqlplus system/*** @ausy1120.sql SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 2 14:19:31 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning option PL/SQL procedure successfully completed. declare * ERROR at line 1: ORA-01722: invalid number ORA-06512: at line 5
the solution is to modify the script slightly and correct a typo.
Open the ausy1120.sql file in an editor and change
select to_number(substr(version,1,instr(version,'.'))) into :dbver from v$instance where rownum=1;to
select to_number(substr(version,1,instr(version,'.')-1)) into :dbver from v$instance where rownum=1;
Then rerun the script. It should finish almost immediately and the output should be similar to:
PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. -------------------------------------------------------------------------------- --- ausy1120 started at 02-NOV-2015 14:40:04 --- '---AUSY1120COMPLETEDAT'||TO_CHAR(SYSDATE,'DD-MON-YYYYHH24:MI:SS')||'----' -------------------------------------------------------------------------------- --- ausy1120 completed at 02-NOV-2015 14:40:04 ---- Commit complete.
The same error seems to occur when running the aujv1120.sql and the aumsc1120.sql as well. The solution is the same for all three.
Subscribe to:
Posts (Atom)