SQL> show sga ; Total System Global Area 6547296256 bytes Fixed Size 2213856 bytes Variable Size 4429187104 bytes Database Buffers 2013265920 bytes Redo Buffers 102629376 bytes SQL> show PARAMETERs memory ; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ hi_shared_memory_address integer 0 memory_max_target big integer 6272M […]
oracle : dblink limit, open_links Hint
SELECT name, VALUE FROM v$parameter WHERE name = 'open_links'; ALTER SYSTEM SET open_links = 50 SCOPE = spfile; COMMIT; systemctl restart oracle.service
Toad for oracle over ssh with putty Hint
Download & Install putty https://www.putty.org/ Putty Configuration Connection > SSH > Tunnels Check [Local ports ....] Check [Remote ports ....] Source Port : 15219 Destination : yourdb.server.com:1521 Click [Add] Do not click Open button, You must save this configure... Session : Input your Host, ssh Port, Saved Session name, and Click [Save] […]
KeepAlive : ssh, oracle Hint
# vi /etc/ssh/sshd_config KeepAlive yes ClientAliveInterval 60 # vi /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora SQLNET.EXPIRE_TIME=1
oracle : use dblink by command line
must be added tnsnames [DB Links] object not enough ~ # vi /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora REMOTETNS1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.10)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = XE))) # su - oracle -c "expdp dbuser/xxxxxx@REMOTETNS1 dumpfile=file.dmp logfile= file.log directory=BACKUP_DIR reuse_dumpfiles=y"
centos 7 : oracle listener config & log Hint with /etc/hosts
# vi /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora ----------------------- LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = owl)(PORT = 1521)) ) ) ADR_BASE_LISTENER = /home/oracle/app/oracle ----------------------- # vi /etc/hosts ----------------------- 127.0.0.1 owl ----------------------- […]
oracle session Hint
select * from v$resource_limit where resource_name in ('processes','sessions');
oracle : move & relocate dbf datafile Hint
Manual Online SQL> SELECT name FROM v$datafile WHERE name LIKE '%test%'; SQL> ALTER TABLESPACE test OFFLINE NORMAL; SQL> HOST mv /oracle0/test0.dbf /oracle1/test1.dbf SQL> ALTER TABLESPACE test RENAME DATAFILE '/oracle0/test0.dbf' TO '/oracle1/test1.dbf'; SQL> ALTER TABLESPACE test ONLINE; SQL> SELECT name FROM v$datafile; SQL> SHUTDOWN IMMEDIATE Manual Offline SQL> HOST MOVE /oracle0/test0.dbf /oracle1/test1.dbf SQL> STARTUP MOUNT […]
oracle : the password has expried to unlimited Hint
Conn as sysdba $ sqlplus /nolog SQL> conn /as sysdba Change password first! SQL> ALTER USER [USERID] IDENTIFIED by [PASSWORD]; Check password life time SQL> select * from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME'; PROFILE RESOURCE_NAME RESOURCE ------------------------------ -------------------------------- -------- LIMIT ---------------------------------------- DEFAULT PASSWORD_LIFE_TIME PASSWORD 180 Set password life time to […]
oracle : change charset test => csscan
$ sqlplus /nolog SQL> conn /as sysdba SQL> @/home/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/csminst.sql $ csscan $ csscan Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production on 일 9월 13 23:11:54 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Username: derp Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With […]