Technical Notes

Friday, 10 October 2014

[SOLUTION] ORA-28003 problem

Please execute the command as "sys";

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL;

How to see related user process in Linux?

In command window;

$ ps –u alper

(“alper”, linux system user)

[SOLUTION] ORA-00845 problem

By default /dev/shm is created to use half size of your server physical memory

If you see that MEMORY_TARGET not supported on this system while starting Oracle Database Instance in sqlplus window, please follow the instructions as below;

For this case, you should increase memory that using by Oracle as root user (e.g. size=10240m means 10 GB RAM)
-> umount tmpfs
-> mount -t tmpfs shmfs -o size=10240m /dev/shm

If you encounter any error like that "the system is using already", (e.g. process id of /dev/shm is "5265")
-> fuser -m /dev/shm
or
-> lsof /dev/shm
-> kill 5265

Ref: http://blog.yannickjaquier.com/linux/linux-memory-usage.html

How to change oracle max connection limits for "maximum connection exceed problem"?

$ sqlplus
Enter user-name: sys as sysdba @sys
to see parameter process for altering system sessions (processes (integer) = sessions)

SQL> show parameter session
SQL> ALTER system SET sessions= 300 scope=spfile;
SQL> show parameter process
SQL> ALTER system SET processes= 300 scope=spfile;

Finally, restart the database to activate all changes

How to drop a user from database in Oracle?

Please login to database server with ssh as oracle user and run that commands.

sqlplus / as sysdba
SQL> DROP USER testuser CASCADE; --If testuser's schema contains objects such as tables, then you must use the CASCADE clause to drop testuser.
SQL> exit

How to see the size of users on specific tablespace in Oracle?

Please execute following command as "sys";

SELECT tablespace_name, owner, segment_type "Object Type",
COUNT(owner) "Number of Objects",
ROUND(SUM(bytes) / 1024 / 1024, 2) "Total Size in MB"
FROM sys.dba_segments
WHERE tablespace_name IN ('ALPERTABLESPACE')
GROUP BY tablespace_name, owner, segment_type
ORDER BY tablespace_name, owner, segment_type;

How to login sqlplus on linux command window in Linux?

$ sqlplus scott/tiger@orcl

@orc1 -> tnsnames.ora – connection node name
scott -> db username
tiger -> db username password

$ sqlplus / as sysdba@orcl

Login as sysdba with this.

How to see tablespace usage for Oracle Database?

Please execute following commands as "sys";

select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space from dba_free_space
group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files
group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name

How to add a alias for using by all users in Linux?

Please edit /etc/bash.bashrc, for example;

alias c='cd'
alias a='dir'
alias l='ls -l'
alias p='cd -'
alias '..'='cd ..'

How to trace Linux CPU usage on system?

mpstat -P ALL 20

Shows that CPU usage information related to each core and all cpu in every 20 seconds.

sar 10

Shows that CPU usage information related to all cpu in every 10 seconds.

Examples:

oracle@alperdb:~> mpstat -P ALL 1
Linux 2.6.32.12-0.7-default (alperdb) 02/21/2014 _x86_64_

09:12:46 AM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %idle
09:12:47 AM all 0.53 0.00 0.00 0.53 0.00 0.00 0.00 0.00 98.94
09:12:47 AM 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00
09:12:47 AM 1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00
09:12:47 AM 2 1.11 0.00 0.00 0.00 0.00 0.00 0.00 0.00 98.89
09:12:47 AM 3 1.14 0.00 0.00 2.27 0.00 0.00 0.00 0.00 96.59

oracle@alperdb:~> sar 10
Linux 2.6.32.12-0.7-default (alperdb) 02/21/2014 _x86_64_

09:16:13 AM CPU %user %nice %system %iowait %steal %idle
09:16:23 AM all 0.20 0.00 0.05 0.18 0.00 99.57
09:16:33 AM all 0.90 0.00 0.32 0.22 0.00 98.56
09:16:43 AM all 1.89 0.00 0.47 0.32 0.00 97.32

Linux - command line keyboard shortcuts

Here are some basic shortcuts while you are in command window;

CTRL + a -> go to begin of the line
CTRL + e -> go to end of the line
CTRL + d -> log-out
CTRL + w -> remove one word
CTRL + u -> remove all line
CTRL + r -> search for a word in history
CTRL + ins -> paste the clipboard

"cd -" -> go to last directory

How to kill a session with custom procedure?

-If you are authorized to sys user.

Firstly run this procedure to compile and save.
---
CREATE OR REPLACE PROCEDURE P_KILL_SESSION(DATABASE_USER_NAME IN VARCHAR2, STR_RESULT OUT VARCHAR2)
IS
str_exec VARCHAR2(1000);
BEGIN
FOR cur_session IN (SELECT sid, serial#
FROM v$session
WHERE username = upper(DATABASE_USER_NAME)
AND status != 'KILLED') LOOP
str_exec := 'alter system kill session ''' || cur_session.sid || '' || ',' || cur_session.serial# ||'''';
BEGIN
execute immediate str_exec;
STR_RESULT := 'Sessions are terminated successfully';
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR ( -20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM ) ;
END;
END LOOP;
END;
---

To use it;
In command window-editor,

var STR_RESULT VARCHAR2
EXEC P_KILL_SESSION('test_user',:STR_RESULT)
--test_user session could be killed

How to overcome special character problem in Windows?

You can type special character with using ASCII Table.
eg. If you want to type backslash (\), press ALT + 92

You can reach all ASCII table via this link. (Refer to DEC)

Note: Please be sure that NUM_LOCK is on.

[SOLUTION] ORA-06550 and PLS-009005 problems

Please login to database server with ssh as oracle user and run that commands.

sqlplus / as sysdba
SQL> grant execute on DBMS_JOB to ALPERDB_USER;
SQL> exit

How to define distribution list in Microsoft Office Outlook?

To do that please follows the instructions;

Contacts -> New -> Distribution List -> Enter the name of the list and Select Members
Save and close

To use it;

Enter the list name on To/CC/BCC and click Check Names then send your mail.

How to revert all sql lines into one line in Ultra Edit and Notepad++ ?

For example, you have a long SQL file and you must run it in command line or in such tool or program. For this, you can use Ultra Edit or Notepad++ tool for convert it.

Ultra Edit:
Open file via Ultra Edit -> Press CTRL + R -> Input ^p and space character in related columns -> Click Start button -> Click Replace All button

Notepad++:
Open file via Notepad++ -> Select desired lines -> Press CTRL + J

That's it.

Thursday, 9 October 2014

How to define network path for Windows Operating System?

To do that please follows the instructions;

Open My Computer -> Right mouse click -> Select “Add a new network location” -> Click as twice “Next” -> Enter your network address and click Next -> Enter your new network shortcut name -> Click finish

Notes;
This feature is able to use for “FTP” and “remote connection”

Like; ftp://IP or \\Your_IP

How to change system date in Linux?

Please run that command after you logged-in as root (su) user.

# date -s "9 OCT 2014 18:00:00"

to check it:
# date
e.g. Thu Oct 9 18:00:05 EEST 2014

How to know username information in Windows OS?

Please follow below steps:

Press Windows + R
Type and press enter; cmd
Type and press enter; echo %username%

How to shutdown Oracle Database Server in Windows OS?