Expat-IT Tech Bits

Home

Contact

Links

Search this site:

Categories:

/ (287)
  Admin/ (122)
    Apache/ (10)
      HTTPS-SSL/ (4)
      PHP/ (3)
      performance/ (2)
    Cherokee/ (1)
    LAN/ (4)
    LVM/ (6)
    Monitoring/ (2)
      munin/ (2)
    SSH/ (6)
    SSL/ (1)
    Samba/ (1)
    VPN-options/ (6)
      OpenVPN/ (1)
      SSH-Proxy/ (3)
      Tinc/ (1)
      sshuttle/ (1)
    backups/ (17)
      SpiderOak/ (1)
      backuppc/ (5)
      dirvish/ (1)
      misc/ (6)
      rdiff-backup/ (1)
      rsync/ (1)
      unison/ (2)
    commandLine/ (24)
      files/ (8)
      misc/ (10)
      network/ (6)
    crontab/ (1)
    databases/ (15)
      MSSQL/ (2)
      MySQL/ (8)
      Oracle/ (3)
      PostgreSQL/ (1)
    dynamicDNS/ (2)
    email/ (11)
      Dovecot/ (1)
      deliverability/ (1)
      misc/ (1)
      postfix/ (7)
      puppet/ (1)
    iptables/ (3)
    tripwire/ (1)
    virtualization/ (9)
      VMware/ (1)
      virtualBox/ (8)
  Coding/ (14)
    bash/ (1)
    gdb/ (1)
    git/ (3)
    php/ (5)
    python/ (4)
      Django/ (2)
  Education/ (1)
  Hosting/ (27)
    Amazon/ (18)
      EBS/ (3)
      EC2/ (10)
      S3/ (1)
      commandline/ (4)
    Godaddy/ (2)
    NearlyFreeSpeech/ (3)
    Rackspace/ (1)
    vpslink/ (3)
  Linux/ (30)
    Android/ (1)
    Awesome/ (3)
    CPUfreq/ (1)
    China/ (2)
    Debian/ (8)
      APT/ (3)
      WPA/ (1)
    audio/ (1)
    encryption/ (3)
    fonts/ (1)
    misc/ (6)
    remoteDesktop/ (1)
    router-bridge/ (3)
  SW/ (45)
    Micro$soft/ (1)
    browser/ (2)
      Chrome/ (1)
      Firefox/ (1)
    business/ (28)
      Drupal/ (9)
      KnowledgeTree/ (6)
      Redmine/ (2)
      SugarCRM/ (7)
      WebERP/ (2)
      WordPress/ (1)
      eGroupware/ (1)
    chat/ (1)
    email/ (1)
    fileSharing/ (2)
      btsync/ (1)
      mldonkey/ (1)
    graphics/ (2)
    research/ (2)
    website/ (6)
      blog/ (6)
        blosxom/ (3)
        rss2email/ (1)
        webgen/ (1)
  Security/ (15)
    IMchat/ (2)
    circumvention/ (2)
    cryptoCurrency/ (1)
    e-mail/ (4)
    greatFirewall/ (1)
    hacking/ (1)
    password/ (1)
    privacy/ (2)
    skype/ (1)
  Services/ (1)
    fileSharing/ (1)
  TechWriting/ (1)
  xHW/ (14)
    Lenovo/ (1)
    Motorola_A1200/ (2)
    Thinkpad_600e/ (1)
    Thinkpad_a21m/ (3)
    Thinkpad_i1300/ (1)
    Thinkpad_x24/ (1)
    USB_audio/ (1)
    scanner/ (1)
    wirelessCards/ (2)
  xLife/ (17)
    China/ (9)
      Beijing/ (5)
        OpenSource/ (3)
    Expatriation/ (1)
    Vietnam/ (7)

Archives:

  • 2016/07
  • 2016/05
  • 2016/02
  • 2016/01
  • 2015/12
  • 2015/11
  • 2015/06
  • 2015/01
  • 2014/12
  • 2014/11
  • 2014/10
  • 2014/09
  • 2014/07
  • 2014/04
  • 2014/02
  • 2014/01
  • 2013/12
  • 2013/10
  • 2013/08
  • 2013/07
  • 2013/06
  • 2013/05
  • 2013/04
  • 2013/02
  • 2013/01
  • 2012/12
  • 2012/10
  • 2012/09
  • 2012/08
  • 2012/07
  • 2012/06
  • 2012/05
  • 2012/04
  • 2012/03
  • 2012/01
  • 2011/12
  • 2011/11
  • 2011/10
  • 2011/09
  • 2011/08
  • 2011/07
  • 2011/06
  • 2011/05
  • 2011/04
  • 2011/02
  • 2010/12
  • 2010/11
  • 2010/10
  • 2010/09
  • 2010/08
  • 2010/07
  • 2010/06
  • 2010/05
  • 2010/04
  • 2010/03
  • 2010/02
  • 2010/01
  • 2009/12
  • 2009/11
  • 2009/10
  • 2009/09
  • 2009/08
  • 2009/07
  • 2009/06
  • 2009/05
  • 2009/04
  • 2009/03
  • 2009/02
  • 2009/01
  • 2008/12
  • 2008/11
  • 2008/10
  • 2008/09
  • Subscribe XML RSS Feed

    Creative Commons License
    This work is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.
    PyBlosxom

    This site has no ads. To help with hosting, crypto donations are accepted:
    Bitcoin: 1JErV8ga9UY7wE8Bbf1KYsA5bkdh8n1Bxc
    Zcash: zcLYqtXYFEWHFtEfM6wg5eCV8frxWtZYkT8WyxvevzNC6SBgmqPS3tkg6nBarmzRzWYAurgs4ThkpkD5QgiSwxqoB7xrCxs

    Tue, 01 Dec 2015


    /Admin/databases/Oracle: Resetting Administrative Passwords in Oracle

    Login to the server as the "oracle" user (typically).

    Find the sqlplus binary, and login as SYS, for instance:

    /home/oracle/app/oracle/product/12.1.0/dbhome_1/bin/sqlplus "/ as sysdba"

    Then change the SYSTEM password:

    SQL> show user
    SQL> passw system
    SQL> quit
    Then login as SYSTEM:
    /home/oracle/app/oracle/product/12.1.0/dbhome_1/bin/sqlplus "system/password"

    and change the SYS password:

    SQL> show user
    SQL> passw sys
    SQL> quit

    References:
    [1] https://rolfje.wordpress.com/2007/01/16/lost-oracle-sys-and-system-password/

    posted at: 02:11 | path: /Admin/databases/Oracle | permanent link to this entry

    Sat, 13 Dec 2014


    /Admin/databases/Oracle: Importing a Dump File in Oracle

    In Oracle, there is an old way to import ("imp") and a new way ("impdp"). Here I have examples of both.

    There is a one-to-one relationship between users and schemas (="database"), but not all users have schemas. The first step in creating a new schema/database is to create a new user (C## prefix required):

    SQL> create user C##anewuser identified by anewuser;

    Grant some minimal privileges:

    SQL> grant connect, create session, imp_full_database to C##anewuser;
    SQL> alter user C##anewuser quota 200M on users;

    Prepare to import the dump file:

    su oracle
    cd /home/oracle/app/oracle/product/12.1.0/dbhome_1/bin

    Import an old-style dump file:

    ./imp \"/ as sysdba\" file=/home/oracle/app/oracle/admin/orcl/dpdump/dumpfile.dmp fromuser=olduser touser=C##anewuser log=/tmp/dumpfile.log;

    Import an new-style dump file:

    ./impdp C##newuser/password dumpfile=expdp_dumpfile.dmp remap_schema=olduser:C##newuser log=expdp_dumpfile.log full=y;

    Some gotchas..... Both of the above require knowing the old schema name, which is not terribly easy to figure out. Try leaving out the fromuser/touser/remap_schema stuff on the first pass and just look at the error messages in the log. Sometimes an import is looking for a tablespace that does not exist. Create the stupid thing:

    SQL> CREATE TABLESPACE ONBASETEMP DATAFILE 'ONBASETEMP.dat' SIZE 40M ONLINE;

    and then re-run the import.

    Now verify new schema shows up in list of existing schemas:

    SQL> select distinct owner from dba_objects;

    Verify tables exist in newly imported schema:

    SQL> SELECT DISTINCT OWNER, OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND OWNER = 'C##anewuser';

    Verify a table has columns:

    SQL> describe C##anewuser.sometablename;

    Verify a table has contents:

    SQL> select * from C##anewuser.sometablename;

    posted at: 05:20 | path: /Admin/databases/Oracle | permanent link to this entry

    Tue, 23 Sep 2014


    /Admin/databases/Oracle: Oracle Admin / Client Basics

    Assuming you have an Oracle database running and need to do "stuff" with it, sqlplus[1] is probably the tool (DB client) you are looking for. There are some instructions[2][3] out there for installing an sqlplus client on your Debian desktop, but (so far) I have found making remote connections to an Oracle database to be problematic. It is easiest to run sqlplus on the Oracle machine itself, where sqlplus will always be a part of the install, and configuration is much easier.

    First set up some environment variables required by sqlplus by adding the following to /home/oracle/.bashrc:

    export ORACLE_HOME=/home/oracle/app/oracle/product/12.1.0/dbhome_1/
    export ORACLE_SID=orcl

    These settings are obviously environment-dependent. I believe that orcl is the default name for an Oracle database in a default installation, but per[4] you can find the name(s) of Oracle database(s) configured in /etc/oratab.

    To connect:

    su oracle
    cd /home/oracle/app/oracle/product/12.1.0/dbhome_1/bin
    ./sqlplus "/as sysdba"

    If your background is MySQL, for instance, things now start getting funky. An Oracle "database" is more akin to a MySQL instance. An Oracle "schema" within that "database" is the equivalent, it seems, of a MySQL database. And there is no straight-forward way to get a list of schemas[5][6]. Apparently in Oracle there is a one-to-one relationship between Oracle username and schema. So a list of users is a superset of the list of schemas (since not all users have a schema). These

    SQL> SELECT username FROM dba_users WHERE default_tablespace not in ('SYSTEM','SYSAUX');
    SQL> select distinct owner from dba_objects;

    for instance get you an approximation of such a list of schemas, but produce slightly different results. And this[7]

    SQL> SELECT DISTINCT OWNER, OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND OWNER = '[some other schema]';

    (godawful!!) query will then show you the list of tables associated with a schema (OWNER parameter above). And

    SQL> describe schema.tablename

    then describes the columns of a table.

    [1] http://docs.oracle.com/cd/E11882_01/server.112/e16604/qstart.htm
    [2] https://www.debian-administration.org/article/430/Oracle_XE_on_Debian
    [3] https://help.ubuntu.com/community/Oracle
    [4] http://www.perlmonks.org/?node_id=520376
    [5] https://stackoverflow.com/questions/4833459/oracle-sql-query-for-listing-all-schemas-in-a-db
    [6] http://datawarehouse.ittoolbox.com/groups/technical-functional/informatica-l/listing-all-schemas-in-oracle-866951
    [7] https://stackoverflow.com/questions/2247310/how-do-i-list-all-tables-in-a-schema-in-oracle-sql

    posted at: 04:44 | path: /Admin/databases/Oracle | permanent link to this entry