,

How to change sys password

вторник, 29 октября 2013 г. 1 коммент.

Use the following:


SQL> connect / as sysdba
SQL> alter user identified by ;

Action:


C:\>sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 29 15:08:10 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> alter user sys identified by oracle;

User altered.

SQL> exit

SQL> conn sys/oracle@sieb361 as sysdba
Connected.
SQL> exit;

Читать полностью

, ,

Size of CLOB or BLOB in Oracle DB

пятница, 25 октября 2013 г. 0 коммент.

This is a simple query that returns the size of the blob or clob.


select '&TABLE' as table_name, '&COLUMN_NAME' as column_name, sum(dba_segments.BYTES) / 1024 / 1024 as Mb
from dba_segments
where (dba_segments.owner, dba_segments.segment_name) in
(
-- lob segments
select dba_lobs.owner, dba_lobs.segment_name
from dba_lobs
where dba_lobs.owner = '&OWNER' and
dba_lobs.table_name = '&TABLE' and
dba_lobs.column_name = '&COLUMN_NAME'
union all
-- lob index segments
select dba_lobs.owner, dba_lobs.index_name
from dba_lobs
where dba_lobs.owner = '&OWNER' and
dba_lobs.table_name = '&TABLE' and
dba_lobs.column_name = '&COLUMN_NAME'
);

See also:

Size of table in Oracle
Размер Clob


Читать полностью

,

How to transform the set of values in a table

четверг, 24 октября 2013 г. 0 коммент.

Transform the set of values in a table:


SQL>
SQL> select column_value
2 from table ( sys.odcinumberlist(20, 30) );

COLUMN_VALUE
------------
20
30

SQL>
SQL> select column_value
2 from table ( sys.odcivarchar2list('First', 'Second') );

COLUMN_VALUE
--------------------------------------------------------------------------------
First
Second

SQL>
SQL> select column_value
2 from table ( sys.odcidatelist(sysdate, sysdate + 1) );

COLUMN_VALUE
------------
24.10.2013 1
25.10.2013 1

SQL>
Читать полностью

,

ORA-01031: insufficient privileges (when logging in as sqlplus "/ as sysdba")

Error: ORA-01031: insufficient privileges (when logging in as sqlplus "/ as sysdba")

Cause: User does not have sufficient rights.

Action:


C:\sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 24 14:44:37 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges

SQL> exit

Check if the OS user you are logged on to the machine is member of the ORA_DBA group. Add the user to the ORA_DBA group.



C:\sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 24 14:45:03 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> conn / as sysdba
Connected.

See also ORA-01031: insufficient privileges (when the inline view is updated)

Читать полностью

,

ORA-01031: insufficient privileges (when the inline view is updated)

пятница, 18 октября 2013 г. 0 коммент.

Error: ORA-01031: insufficient privileges (when the inline view is updated).

Cause: This user has no right to "update" on the table joined in the select.

Action:

I got an error when the script is executed.


update (
select emp.comm
from scott.dept dept, scott.emp
where dept.loc = 'CHICAGO' and
emp.deptno = dept.deptno
)
set comm = comm + 100;

User "test" had the following rights:


grant select, update on emp to test;
grant select on dept to test;

To execute update user "test" must have the right to "update" to all fields of tables joined in the select.

grant update (deptno, loc) on dept to test;

Or the entire table:

grant update on dept to test;

UPD: ORA-01031: insufficient privileges (when logging in as sqlplus "/ as sysdba")

Читать полностью

,

ORA-28000: the account is locked

четверг, 17 октября 2013 г. 0 коммент.

Error:

C:\>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 17 17:55:05 2013

Copyright (c) 1982, 2010, Oracle. All rights reserved.

SQL> conn SCOTT/TIGER@SIEB306
ERROR:
ORA-28000: the account is locked

SQL> exit

Cause: The user has entered wrong password consequently for maximum number of times specified by the user's profile parameter FAILED_LOGIN_ATTEMPTS, or the DBA has locked the account


Action: Wait for PASSWORD_LOCK_TIME or Unlock the account.



SQL>
SQL> select dba_users.username, dba_users.account_status
2 from dba_users
3 where dba_users.username = 'SCOTT';

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SCOTT EXPIRED & LOCKED

SQL> ALTER USER SCOTT ACCOUNT UNLOCK;

User altered

SQL>
SQL> select dba_users.username, dba_users.account_status
2 from dba_users
3 where dba_users.username = 'SCOTT';

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SCOTT EXPIRED

SQL>

See also: ORA-28001: the password has expired


And prevent account locks to not occur again:


sql> ALTER PROFILE "DEFAULT" LIMIT PASSWORD_LIFE_TIME UNLIMITED;
sql> ALTER PROFILE "DEFAULT" LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
Читать полностью

"Process J001 died" And/Or "Process W000 died" And/Or "kkjcre1p: unable to spawn jobq slave process"

пятница, 11 октября 2013 г. 0 коммент.

I got an error:


Process J001 died, see its trace file
kkjcre1p: unable to spawn jobq slave process
Process W000 died, see its trace file

Cause: A resource problem


Action:

1. Increase the parameter JOB_QUEUE_PROCESSES. Using Both DBMS_JOB and Oracle Scheduler

alter system set job_queue_processes=10;

2. Make sure Swap is correctly configured and increase it if necessary.

    4 ~ 8 GBs of RAM, set Swap to at least double RAM size.

    8~16 GBs of RAM, set Swap to double RAM.

    16~32 GBs of RAM or more, set Swap at a rate of 0.75xRAM.

3. Make sure all the Kernel parameters are configured correctly.

4. Check In-Memory File System size. ORA-00845 MEMORY_TARGET not supported on this system

4. Check the ulimits.


Читать полностью

, ,

Permission denied when loading data into hive table

вторник, 8 октября 2013 г. 0 коммент.

I got an error:


[root ~]# sudo -u hive hive
hive> LOAD DATA INPATH '/user/hdfs/d_product_type_hive_exp.csv' OVERWRITE INTO TABLE D_PRODUCT_TYPE_HIVE;
Loading data to table default.d_product_type_hive
rmr: DEPRECATED: Please use 'rm -r' instead.
Moved: 'hdfs://xxxxx:8020/user/hive/warehouse/d_product_type_hive' to trash at: hdfs://xxxxx:8020/user/hive/.Trash/Current
Failed with exception Permission denied: user=hive, access=WRITE, inode="/user/hdfs":hdfs:supergroup:drwxr-xr-x
at org.apache.hadoop.hdfs.server.namenode.FSPermissionChecker.check(FSPermissionChecker.java:224)
at org.apache.hadoop.hdfs.server.namenode.FSPermissionChecker.check(FSPermissionChecker.java:204)
at org.apache.hadoop.hdfs.server.namenode.FSPermissionChecker.checkPermission(FSPermissionChecker.java:152)
at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.checkPermission(FSNamesystem.java:4716)
at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.checkPermission(FSNamesystem.java:4698)
at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.checkParentAccess(FSNamesystem.java:4666)
at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.renameToInternal(FSNamesystem.java:2698)
at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.renameToInt(FSNamesystem.java:2666)
at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.renameTo(FSNamesystem.java:2645)
at org.apache.hadoop.hdfs.server.namenode.NameNodeRpcServer.rename(NameNodeRpcServer.java:589)
at org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolServerSideTranslatorPB.rename(ClientNamenodeProtocolServerSideTranslatorPB.java:381)
at org.apache.hadoop.hdfs.protocol.proto.ClientNamenodeProtocolProtos$ClientNamenodeProtocol$2.callBlockingMethod(ClientNamenodeProtocolProtos.java:44964)
at org.apache.hadoop.ipc.ProtobufRpcEngine$Server$ProtoBufRpcInvoker.call(ProtobufRpcEngine.java:453)
at org.apache.hadoop.ipc.RPC$Server.call(RPC.java:1002)
at org.apache.hadoop.ipc.Server$Handler$1.run(Server.java:1701)
at org.apache.hadoop.ipc.Server$Handler$1.run(Server.java:1697)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:396)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1408)
at org.apache.hadoop.ipc.Server$Handler.run(Server.java:1695)

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.MoveTask
hive>

Cause: Command "LOAD DATA"(without LOCAL) moves the data to the final location and the user hive don't have the rights to delete the file.
Action: Give the right to "write" on the directory with the file.


[root ~]# sudo -u hdfs hadoop fs -ls -d /user/hdfs/
Found 1 items
drwxr-xr-x - hdfs supergroup 0 2013-10-08 18:28 /user/hdfs
[root ~]# sudo -u hdfs hadoop fs -chmod o+w /user/hdfs/
[root ~]# sudo -u hdfs hadoop fs -ls -d /user/hdfs/
Found 1 items
drwxr-xrwx - hdfs supergroup 0 2013-10-08 18:28 /user/hdfs

Try again:


[root ~]# sudo -u hive hive
hive> LOAD DATA INPATH '/user/hdfs/d_product_type_hive_exp.csv' OVERWRITE INTO TABLE D_PRODUCT_TYPE_HIVE;
Loading data to table default.d_product_type_hive
rmr: DEPRECATED: Please use 'rm -r' instead.
Moved: 'hdfs://xxxxx:8020/user/hive/warehouse/d_product_type_hive' to trash at: hdfs://xxxxx:8020/user/hive/.Trash/Current
Table default.d_product_type_hive stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 354, raw_data_size: 0]
OK
Time taken: 5.638 seconds
hive>
Читать полностью

,

Hive: mismatched input '<EOF>' expecting StringLiteral near 'BY' in table row format's field separator

понедельник, 7 октября 2013 г. 3 коммент.

I got an error “FAILED: ParseException line 1:155 mismatched input '<EOF>' expecting StringLiteral near 'BY' in table row format's field separator” when created table in hive.


hive> CREATE TABLE IF NOT EXISTS D_PRODUCT_TYPE_HIVE (PROD_TYPE_ID DOUBLE, PROD_TYPE_NAME STRING, DESCRIPTION STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n';
FAILED: ParseException line 1:155 mismatched input '< EOF>' expecting StringLiteral near 'BY' in table row format's field separator
hive>

Semi-colon is reserved character. It needs to be escaped, '\;' or octal code '\073', but not hex code '\03B'.



hive> CREATE TABLE IF NOT EXISTS D_PRODUCT_TYPE_HIVE (PROD_TYPE_ID DOUBLE, PROD_TYPE_NAME STRING, DESCRIPTION STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\;' LINES TERMINATED BY '\n';
OK
Time taken: 0.308 seconds
hive>
Читать полностью

, ,

ORA-00845 MEMORY_TARGET not supported on this system

четверг, 3 октября 2013 г. 0 коммент.

Error:


SQL> conn / as sysdba
Connected to an idle instance.
SQL> shutdown immediate;
ORA-01034: ORACLE not available
ORA-27102: out of memory
Linux-x86_64 Error: 12: Cannot allocate memory
Additional information: 1
Additional information: 3407880
Additional information: 8
SQL> startup;
ORA-00845: MEMORY_TARGET not supported on this system

Cause: The MEMORY_TARGET parameter was not supported on this operating system or /dev/shm was not sized correctly on Linux(the size of /dev/shm needs to be greater than MEMORY_TARGET or MEMMORY_MAX_TARGET).


Action: Check the size of /dev/shm and increase the size of /dev/shm


Check /dev/shm:

[root]# df -h /dev/shm
Filesystem Size Used Avail Use% Mounted on
tmpfs 2.0G 1.2G 0.8G 60% /dev/shm

Change your system file /etc/fstab:

[root]# vi /etc/fstab
tmpfs /dev/shm tmpfs defaults,size=3G 0 0

And remount:

[root]# mount -o remount /dev/shm

Chech changes:

[root]# df -h /dev/shm
Filesystem Size Used Avail Use% Mounted on
tmpfs 3.0G 1.2G 1.9G 40% /dev/shm

Startup database:


SQL> conn / as sysdba;
Connected to an idle instance.
SQL> startup;
ORACLE instance started.

Total System Global Area 1937457152 bytes
Fixed Size 2214576 bytes
Variable Size 1476396368 bytes
Database Buffers 452984832 bytes
Redo Buffers 5861376 bytes
Database mounted.
Database opened.
SQL>
Читать полностью