Common Ports: FTP, HTTP, TELNET, SMTP, BIOS, IMAP, POP3, SSH, DHCP

20 FTP data (File Transfer Protocol)
21 FTP (File Transfer Protocol)
22 SSH (Secure Shell)
23 Telnet
25 SMTP (Send Mail Transfer Protocol)
43 whois
53 DNS (Domain Name Service)
68 DHCP (Dynamic Host Control Protocol)
79 Finger
80 HTTP (HyperText Transfer Protocol)
110 POP3 (Post Office Protocol, version 3)
115 SFTP (Secure File Transfer Protocol)
119 NNTP (Network New Transfer Protocol)
123 NTP (Network Time Protocol)
137 NetBIOS-ns
138 NetBIOS-dgm
139 NetBIOS
143 IMAP (Internet Message Access Protocol)
161 SNMP (Simple Network Management Protocol)
194 IRC (Internet Relay Chat)
220 IMAP3 (Internet Message Access Protocol 3)
389 LDAP (Lightweight Directory Access Protocol)
443 SSL (Secure Socket Layer)
445 SMB (NetBIOS over TCP)
666 Doom
993 SIMAP (Secure Internet Message Access Protocol)
995 SPOP (Secure Post Office Protocol) 

Oracle useful commands & DDL, DML, DCL, TCL Operations


// To find out total number of rows from table
select count(*) from tablename

// To find out all db users table count
select count(*) from dba_tables

// To find out table names of all db users
SELECT TABLE_NAME FROM DBA_TABLES;

// To find out current db user table count
SELECT COUNT(*) FROM USER_TABLES;

CREATE TABLE - BY COPYING ALL COLUMNS FROM ANOTHER TABLE

// The syntax for the SQL CREATE TABLE AS statement copying all of the columns is:

CREATE TABLE new_table
  AS (SELECT * FROM old_table);

  CREATE TABLE - BY COPYING SELECTED COLUMNS FROM ANOTHER TABLE

// The syntax for the CREATE TABLE AS statement copying the selected columns is:

CREATE TABLE new_table
  AS (SELECT column_1, column2, ... column_n
      FROM old_table);

      CREATE TABLE - BY COPYING SELECTED COLUMNS FROM MULTIPLE TABLES

// The syntax for the CREATE TABLE AS statement copying columns from multiple tables is:

CREATE TABLE new_table
  AS (SELECT column_1, column2, ... column_n
      FROM old_table_1, old_table_2, ... old_table_n);


// How can I create a SQL table from another table without copying any values from the old table?

CREATE TABLE new_table
  AS (SELECT *
      FROM old_table WHERE 1=2);


SELECT dbms_metadata.get_ddl( 'TABLE', 'tablename' ) FROM DUAL;

// To get index details of table
select DBMS_METADATA.GET_DDL('INDEX','') from DUAL;

// Create index
create index INDEX_NAME on tablename ( columnname ); 

=========================================================================

DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

• CREATE - to create objects in the database
• ALTER - alters the structure of the database
• DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
• COMMENT - add comments to the data dictionary
• RENAME - rename an object

DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

• SELECT - retrieve data from the a database
• INSERT - insert data into a table
• UPDATE - updates existing data within a table
• DELETE - deletes all records from a table, the space for the records remain
• MERGE - UPSERT operation (insert or update)
• CALL - call a PL/SQL or Java subprogram
• EXPLAIN PLAN - explain access path to data
• LOCK TABLE - control concurrency

DCL

Data Control Language (DCL) statements. Some examples:

• GRANT - gives user's access privileges to database
• REVOKE - withdraw access privileges given with the GRANT command

TCL

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

• COMMIT - save work done
• SAVEPOINT - identify a point in a transaction to which you can later roll back
• ROLLBACK - restore database to original since the last COMMIT
• SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

Let me know for queries.

Apache POI - Password Protected Excel


Apache POI, a project run by the Apache Software Foundation, and previously a sub-project of the Jakarta Project, provides pure Java libraries for reading and writing files in Microsoft Office formats, such as Word, PowerPoint and Excel.

The name POI was originally an acronym for Poor Obfuscation Implementation, referring humorously to the fact that the file formats seemed to be deliberately obfuscated, but poorly, since they were successfully reverse-engineered.

In this tutorial we will use Apache POI library to perform different functions on Microsoft Excel spreadsheet.

Tools & dependencies:

1. Java JDK 1.5 or above
2. Apache POI library v3.8 or above (download)
3. Eclipse 3.2 above (optional)

Create a password protected excel file or use an existing template and make it password protected. This will give the users a "read only" access though. Here's an example where I have an excel file that has a password "sachin"


import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.record.crypto.Biff8EncryptionKey;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;

public class ProtectedExcelFile { 

    public static void main(final String... args) throws Exception {     

        String fname = "C:/Users/username/excel.xls"; // Add your excel sheet path

        FileInputStream fileInput = null;       
        BufferedInputStream bufferInput = null;      
        POIFSFileSystem poiFileSystem = null;    
        FileOutputStream fileOut = null;

        try {           

            fileInput = new FileInputStream(fname);         
            bufferInput = new BufferedInputStream(fileInput);            
            poiFileSystem = new POIFSFileSystem(bufferInput);            

            Biff8EncryptionKey.setCurrentUserPassword("sachin");      // Use 'sachin' as  a password
            HSSFWorkbook workbook = new HSSFWorkbook(poiFileSystem, true);            
            HSSFSheet sheet = workbook.getSheetAt(0);           

            HSSFRow row = sheet.createRow(0);
            Cell cell = row.createCell(0);

            cell.setCellValue("THIS WORKS!"); 

            fileOut = new FileOutputStream(fname);
            workbook.writeProtectWorkbook(Biff8EncryptionKey.getCurrentUserPassword(), "");
            workbook.write(fileOut);

        } catch (Exception ex) {

            System.out.println(ex.getMessage());      

        } finally {         

              try {            

                  bufferInput.close();     

              } catch (IOException ex) {

                  System.out.println(ex.getMessage());     

              }    

              try {            

                  fileOut.close();     

              } catch (IOException ex) {

                  System.out.println(ex.getMessage());     

              } 
        }       

    }

}

Let me know your queries.