Archive for the 'Oracle' Category
Jan 23, 2014 in Oracle, Uncategorized
$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 24 00:05:54 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> grant create session to schema;
Grant succeeded.
SQL> connect schema/password;
Connected.
SQL> ALTER TABLE sometable CONSTRAINT sometable_user_c1;
Table altered.
SQL> ALTER TABLE sometable add CONSTRAINT sometable_user_c1 CHECK (credential_store IN ('east', 'west', 'north', 'south' ));
Table altered.
SQL> commit;
Commit complete.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real ....
$
Comments Off on Oracle connect to schema sqlplus commands
Oct 08, 2012 in Database, Oracle
From: http://www.orafaq.com/node/854
SQL> select * from v$lock ;
SQL> select l1.sid, ' IS BLOCKING ', l2.sid
2 from v$lock l1, v$lock l2
3 where l1.block =1 and l2.request > 0
4 and l1.id1=l2.id1
5 and l1.id2=l2.id2
SQL> /
Comments Off on Finding Oracle locks ..
Sep 28, 2012 in Database, Oracle
From
http://blogs.oracle.com/rajeshthekkadath/entry/resolving_oracle_error_ora_28000
$ sqlplus "/ as sysdba"
SQL> alter user scott account unlock;
SQL> grant connect, resource to scott;
.
Comments Off on Resolving ORACLE ERROR:ORA-28000: the account is locked
Jun 09, 2009 in Database, Oracle, Uncategorized
Example of range partitioning:
CREATE TABLE sales (year NUMBER(4),
product VARCHAR2(10),
amt NUMBER(10,2))
PARTITION BY RANGE (year)
PARTITION p1 VALUES LESS THAN (1992) TABLESPACE u1,
PARTITION p2 VALUES LESS THAN (1993) TABLESPACE u2,
PARTITION p3 VALUES LESS THAN (1994) TABLESPACE u3,
PARTITION p4 VALUES LESS THAN (1995) TABLESPACE u4,
PARTITION p5 VALUES LESS THAN (MAXVALUE) TABLESPACE u5;
http://www.oracle-dba-online.com/sql/oracle_table_partition.htm
Comments Off on Oracle create table with partitioning ..
Feb 23, 2007 in Database, Oracle
JProfiler indicated a memory leak in the Oracle JDBC 9.0.x.3 package at TTCItem.
We replaced the 9i JDBC package with the 10g version and we started getting $B!HClassCastException$B!I on Timestamp to Date conversions. It turns out the Oracle JDBC 10g package has an option to treat this issue as a version 8 backwards compatibility by doing one of two things:
– Set system property props.put(oracle.jdbc.V8Compatible, true);
or
– Start Tomcat with cli option -Doracle.jdbc.V8Compatible=true
Comments Off on Oracle JDBC 9i/10g problem..
Mar 02, 2006 in Database, Oracle
sqlplus user@db/password
- Execute a script from sqlplus
@[/path/]script.sql
- List schemas:
SELECT username FROM all_users ORDER BY username;
- List tables in a schema:
SELECT object_name, created FROM user_objects WHERE object_type=’TABLE’;
- Get number of rows
SELECT COUNT(*) FROM table;
- Show table schema
DESC table;
- Limit number of rows
SELECT * FROM table WHERE rownum < N;
- Get current datetime
SELECT to_char(sysdate, ‘mm/dd/yyyy hh:mi:ss’) FROM dual;
See http://www.oracle.com/technology/oramag/code/tips2004/011104.html for
a procedure to list table names and number of rows.
See http://www-it.desy.de/systems/services/databases/oracle/sqlplus/sqlplus.html.en
for an introduction to sqlplus.
Objects and resources:
http://www-it.desy.de/systems/services/databases/oracle/at_desy/user_objects.html.en
Comments Off on Oracle basic info ..